Pivoting to Insights: Unlocking the Power of pivot

Image by Editor | Midjourney

Data doesn’t always come in the best format for analysis. In R, pivot_longer() helps fix this. Data is often in a wide format, with each type of information having its own column.

For example, sales for January, February, and March are in separate columns. This format can be hard to analyze, especially when comparing values over time. Using pivot_longer(), you can convert the wide format into a long format. In a long format, each row represents one observation.

In this article, we’ll show you how to use pivot_longer() to clean and reshape your data for analysis.

Basic Usage of pivot_longer()

The syntax for pivot_longer() is simple. The basic structure looks like this:

pivot_longer(data, cols, names_to, values_to)

data: The dataset you are working with.
cols: The columns that you want to pivot (typically the “wide” columns).
names_to: The name of the new column that will contain the variable names.
values_to: The name of the new column that will contain the values from the pivoted columns.

Pivoting Sales Data

Let’s see how pivot_longer() works with an example. Suppose you have sales data in a wide format. Each month has its own column. You want to reshape this data into a long format.

library(tidyr)
library(dplyr)

# Sample data in wide format
sales_data <- tibble(
  ID = c(1, 2),
  Jan_Sales = c(100, 150),
  Feb_Sales = c(120, 180),
  Mar_Sales = c(90, 160)
)

# Pivot the data to long format
long_sales_data %
  pivot_longer(
    cols = starts_with("Jan") : starts_with("Mar"),
    names_to = "Month",
    values_to = "Sales"
  )

print(long_sales_data)

In this example, the sales data is transformed from wide format to long format. Now, each row corresponds to a single month’s sales for a particular ID.

Customizing Column Names

You can customize the names of the new columns using the names_to and values_to arguments. For example, instead of having one column with all the month names, you can split them into two columns. One column will have the months. The other column will contain the measure, such as Sales.

# Pivot the data to long format
long_sales_data %
  pivot_longer(
    cols = starts_with("Jan"):starts_with("Mar"), # Specify columns to pivot
    names_to = c("Month", "Measure"), # Create new columns
    names_pattern = "(.*)_(.*)", # Split column names based on underscore
    values_to = "Value" # The new column that will hold the values
  )

# Print the reshaped data
print(long_sales_data)

Here, names_pattern uses a regular expression to split the column names like Jan_Sales into two parts: “Jan” (Month) and “Sales” (Measure).

Handling Multiple Columns

If you have more than one set of columns to pivot, you can pivot multiple columns at once. For example, let’s add profit data for each month:

# Example wide sales data
sales_data <- tibble(
  ID = c(1, 2),
  Jan_Sales = c(100, 150),
  Feb_Sales = c(120, 180),
  Mar_Sales = c(90, 160),
  Jan_Profit = c(30, 45),
  Feb_Profit = c(35, 50),
  Mar_Profit = c(25, 40)
)

# Pivot the data to long format
long_sales_profit_data %
  pivot_longer(
    cols = starts_with(c("Jan", "Feb", "Mar")), # Pivot columns that start with Jan, Feb, Mar
    names_to = c("Month", "Metric"), # Split column names into Month and Metric
    names_pattern = "(.*)_(.*)", # Use regular expression to split on the underscore
    values_to = "Value" # Create a new column 'Value' to hold the values
  )

# Print the reshaped data
print(long_sales_profit_data)

This would give you a long format with both “Sales” and “Profit” as metrics under the Metric column.

Pivoting Multiple Value Columns

pivot_longer() can also handle datasets where you want to pivot multiple value columns, each with a different unit or metric. By using names_to with multiple value columns, you can create a more flexible dataset.

# Example data
data <- tibble(
  ID = 1:2,
  Height_cm = c(170, 180),
  Weight_kg = c(70, 80)
)

# Pivot the data to long format
long_data %
  pivot_longer(
    cols = c(Height_cm, Weight_kg), # Specify columns to pivot
    names_to = "Measure", # Create a new column "Measure" for the original column names
    values_to = "Value"   # Create a new column "Value" for the values
  )

# Print the reshaped data
print(long_data)

This reshapes the data into a form where “Height_cm” and “Weight_kg” are now listed under the Measure column.

Why Use pivot_longer()

1. Data Consistency

pivot_longer() keeps your data clean and consistent. This makes it easier to use other tidyverse functions. For example, ggplot2 for visualizations or dplyr for filtering and summarizing.

2. Scalability

The pivot_longer() function is not just for small datasets. It works well with large datasets that have many columns to reshape. Its easy-to-understand commands and flexibility make it a strong tool for organizing different types of data.

3. Better Analysis

Many machine learning models and statistical tests prefer long-format data. pivot_longer() helps convert data into this format easily. This makes applying models and visualizations simpler.

Conclusion

The pivot_longer() function in R is very useful. It changes wide data into long format quickly. This makes your data easier to understand and analyze. It helps you clean and organize data. You can use it for graphs, tests, or machine learning. It works well for small and big datasets. The function saves time and reduces mistakes. It is easy to use with practice. Learning pivot_longer() makes data work simpler, and is a must-have skill for anyone using R.

Pivoting to Insights: Unlocking the Power of pivot_longer() in R