# install
install.packages("tidyverse") # reshaping data
install.packages("gt") # creating tablesReshaping Data
Introduction
When working with data, you will often encounter situations where the information you need is organized in a format that is not convenient for the task you want to perform. For example, you might have measurements stored across several columns when you need them in a single column, or you might have multiple rows for each observation when you would prefer each observation to occupy a single row.
Reshaping data is the process of reorganizing a dataframe without changing the underlying information it contains. Rather, reshaping changes how that information is distributed across rows and columns. Two common ways of organizing data are long format and wide format, and understanding the difference between these formats is an important part of working with data.
The goal of this vignette is to introduce the structure of long and wide data and demonstrate how to move between these formats using two tidyverse functions: pivot_longer and pivot_wider. We will begin by examining how the same information can be organized in long and wide formats and how the two structures differ.
We will then use pivot_wider to transform data from long to wide format and pivot_longer to perform the reverse operation. Along the way, we will examine the key arguments used by each function, including how to specify which columns should be transformed and how to name the resulting columns.
Finally, we will consider why the choice between long and wide format matters. Using simple examples, we will see how long format can make it easier to group, summarize, and visualize data, while wide format can make it easier to compare or combine measurements stored for the same observation.
By the end of this vignette, you should be able to recognize long and wide data, understand how the two formats differ, use pivot_longer and pivot_wider to reshape your own data, and choose an appropriate data format for a particular task.
R Packages
You will need the following R packages. If you have already installed them, skip the install.packages lines.
# load
library(tidyverse)
library(gt)Data Formats
Two of the most common ways to organize data are long format and wide format. The difference between the two has to do with how repeated measurements across the same observation are handled.
Long
In long format, different measurements or categories are stored as values within a column, resulting in more rows. The resultant dataframe is one that is naturally longer, hence the name.
For example, suppose we have recorded the sales of three products during two months. We can create a simple dataframe using the data.frame function. We will call our products A, B, and C (product column), assume these products were sold in January and February (month column), and record the number of sales for each product-month combination (sales column).
sales_long <- data.frame(
# product being sold
product = c("A", "A", "B", "B", "C", "C"),
# month it was sold
month = c("January", "February",
"January", "February",
"January", "February"),
# quantity sold
sales = c(100, 120, 80, 95, 150, 140)
)
sales_long |> gt()| product | month | sales |
|---|---|---|
| A | January | 100 |
| A | February | 120 |
| B | January | 80 |
| B | February | 95 |
| C | January | 150 |
| C | February | 140 |
In the table above, each row represents a product-month combination. For example, the first row tells us that Product A had 100 sales in January, while the second row tells us that Product A had 120 sales in February.
Importantly, notice that each product (A, B, and C) appears twice - once for each month. This repetition is the defining feature of the long format structure: rather than creating a separate column for each month, the month is stored as a value in the month column.
Wide
In wide format, different measurements or categories are stored in separate columns, resulting in more columns. The resultant dataframe is one that is naturally wider, hence the name.
Thinking again about our product sales example, we could just as well have recorded the number of sales for each product in separate columns that are named based on the month they were sold. We will still call our products A, B, and C (product column), but rather than storing the month as a value in its own column, we will create separate columns for January and February and record the number of sales for each product underneath the appropriate month.
sales_wide <- data.frame(
# product being sold
product = c("A", "B", "C"),
# quantity sold in January
January = c(100, 80, 150),
# quantity sold in February
February = c(120, 95, 140)
)
sales_wide |> gt()| product | January | February |
|---|---|---|
| A | 100 | 120 |
| B | 80 | 95 |
| C | 150 | 140 |
In the table above, each row represents a product. For example, the first row tells us that product A had 100 sales in January and 120 sales in February.
Importantly, notice that each product (A, B, and C) appears only once. This is the defining feature of the wide format structure: rather than storing the month as a value in a month column, each month is represented by its own column, with the corresponding sales values stored underneath it.
Reshaping
The tidyverse package provides two functions for moving between long and wide formats:
pivot_longertransforms data from wide to long formatpivot_widertransforms data from long to wide format
The names of these functions describe what they do. pivot_longer makes a dataframe longer by moving information from columns into rows, while pivot_wider makes a dataframe wider by moving information from rows into columns.
We can demonstrate both functions using the sales_long and sales_wide dataframes we created above.
Long to Wide
Let’s begin with pivot_wider. As its name suggests, pivot_wider transforms a dataframe from long to wide format.
Recall our long format data:
sales_long |> gt()| product | month | sales |
|---|---|---|
| A | January | 100 |
| A | February | 120 |
| B | January | 80 |
| B | February | 95 |
| C | January | 150 |
| C | February | 140 |
In this dataframe, the month is stored as a value in the month column, while the corresponding number of sales is stored in the sales column. Because each product has a separate row for each month, each product (A, B, and C) appears twice.
Suppose we instead want each product to appear only once, with separate columns for January and February. We can use pivot_wider to reorganize these data:
sales_pivot_wide <- sales_long |>
pivot_wider(
# column containing values that will become new column names
names_from = month,
# column containing values that will populate the new columns
values_from = sales
)
sales_pivot_wide |> gt()| product | January | February |
|---|---|---|
| A | 100 | 120 |
| B | 80 | 95 |
| C | 150 | 140 |
The pivot_wider function uses two necessary arguments:
names_fromtells R which column contains the values that should become the new column namesvalues_fromtells R which column contains the values that should populate those new columns
In this example, month is the column that contains the values that we want to become column names (i.e., January and February), while sales contains the values that we want to appear underneath those new columns. Thus, the month column is supplied to the names_from argument and the sales column is supplied to the values_from argument.
Importantly, notice that our original sales_wide dataframe, created manually, and the sales_pivot_wide dataframe, created using pivot_wider, are identical:
sales_wide |> gt()| product | January | February |
|---|---|---|
| A | 100 | 120 |
| B | 80 | 95 |
| C | 150 | 140 |
sales_pivot_wide |> gt()| product | January | February |
|---|---|---|
| A | 100 | 120 |
| B | 80 | 95 |
| C | 150 | 140 |
Wide to Long
We can perform the reverse operation using pivot_longer. As its name suggests, pivot_longer transforms a dataframe from wide to long format.
Recall our wide format data:
sales_wide |> gt()| product | January | February |
|---|---|---|
| A | 100 | 120 |
| B | 80 | 95 |
| C | 150 | 140 |
Here, the months are stored in separate columns (January and February). If we wanted to combine these columns back into a single month column, we would use pivot_longer:
sales_pivot_long <- sales_wide |>
pivot_longer(
# columns containing values we want to combine
cols = c(January, February),
# name for new column containing the former column names
names_to = "month",
# name for new column containing the values
values_to = "sales"
)
sales_pivot_long |> gt()| product | month | sales |
|---|---|---|
| A | January | 100 |
| A | February | 120 |
| B | January | 80 |
| B | February | 95 |
| C | January | 150 |
| C | February | 140 |
The pivot_longer function uses three necessary arguments:
colstells R which columns we want to combine- Here, we want to combine the January and February columns
names_tospecifies the name of the new column that will contain the names of the columns being combined- We call this new column month
values_tospecifies the name of the new column that will contain the values stored in those columns- We call this new column sales
The January and February columns have been combined into two columns: month and sales. The former column names (January and February) become values in the month column, while the values previously stored in the January and February columns become values in the new sales column.
Importantly, notice that our original sales_long dataframe, created manually, and the sales_pivot_long dataframe, created using pivot_longer, are identical:
sales_long |> gt()| product | month | sales |
|---|---|---|
| A | January | 100 |
| A | February | 120 |
| B | January | 80 |
| B | February | 95 |
| C | January | 150 |
| C | February | 140 |
sales_pivot_long |> gt()| product | month | sales |
|---|---|---|
| A | January | 100 |
| A | February | 120 |
| B | January | 80 |
| B | February | 95 |
| C | January | 150 |
| C | February | 140 |
In sum, pivot_wider and pivot_longer perform opposite operations. pivot_wider takes values stored in rows and spreads them across columns; pivot_longer takes values stored across columns and combines them into rows. Critically, the data stored in each dataframe are identical. All that has changed is the organization of those data.
Applications with Data Formats
These two formats are useful for different purposes. Neither long nor wide is universally better. Instead, the appropriate format depends on what you want to do with the data.
Long
Long format is often useful when you want to perform the same operation across multiple measurements or categories. Because the measurements are stored as values within a column, functions can treat them consistently as a single variable.
For example, suppose we want to calculate the average sales across all product-month combinations in our dataset. In long format, all of the sales values are stored in a single sales column, making this operation straightforward.
sales_long |> summarise(mean_sales = mean(sales)) |> gt()| mean_sales |
|---|
| 114.1667 |
We can also use group_by to calculate the average sales separately for each month:
sales_long |>
# group observations by month
group_by(month) |>
# summarize sales within those groups
summarise(mean_sales = mean(sales)) |>
gt()| month | mean_sales |
|---|---|
| February | 118.3333 |
| January | 110.0000 |
Here, month is a variable containing two possible values (January and February), while sales is a single variable containing the corresponding measurements.
Long format is also particularly useful for visualization. For example, ggplot can use the values in the month and sales columns directly:
sales_long |>
ggplot(aes(x = month, y = sales, fill = month)) + # `fill = month` colors the bars
geom_col() +
labs(x = "Month",
y = "Sales") +
theme_minimal() +
# remove legend for cleaner look
theme(legend.position = "none")Wide
Wide format is preferred when you want to compare related measurements side by side. Because each measurement has its own column, it can be easier to inspect the values for a particular observation.
For example, suppose we want to compare each product’s January and February sales. In wide format, both measurements are already available in the same row, so no additional operations are required:
sales_wide |> gt()| product | January | February |
|---|---|---|
| A | 100 | 120 |
| B | 80 | 95 |
| C | 150 | 140 |
This also makes it straightforward to calculate the change in sales between the two months. Because January and February are separate columns, we can directly subtract one from the other:
sales_wide |>
# calculate change
mutate(change = February - January) |>
gt()| product | January | February | change |
|---|---|---|---|
| A | 100 | 120 | 20 |
| B | 80 | 95 | 15 |
| C | 150 | 140 | -10 |
We could also calculate the percentage change:
sales_wide |>
# calculate percent change
mutate(percent_change = (February - January) / January) |>
gt() |>
# format percent column for cleaner look
fmt_percent(columns = percent_change,
decimals = 1)| product | January | February | percent_change |
|---|---|---|---|
| A | 100 | 120 | 20.0% |
| B | 80 | 95 | 18.8% |
| C | 150 | 140 | −6.7% |
Thus, wide format makes sense when the columns represent different measurements that you want to compare or combine within the same observation.
Choosing a Format
The choice between long and wide format ultimately depends on the task you want to perform.
| Question | Format |
|---|---|
| What were the average sales for each month? | Long |
| How do sales vary across months? | Long |
| How can I plot sales by month? | Long |
| What is the change in product sales from January to February? | Wide |
| What is the percentage change for each product? | Wide |
| How many sales did each product have in each month? | Either |