Reshaping Data from Wide to Long (and Vice Versa)

R Data Wrangling Series

Introduction

Reshaping Data is about changing the way data is organized into rows and columns. There are situations when we need convert data to Long Form from Wide Form (and vice versa).

Examples of Resharping Data

We use tidyr::pivot_longer() for converting to long from wide:

# Generate a fake data for purpose of explanation: 

set.seed(29)
df_wide <- data.frame(time_month = rep(c(1, 2), time = 2, each = 1),
                      firm_name = rep(c("A", "B"), time = 1, each = 2), 
                      roe = rnorm(4), 
                      roa = rnorm(4))
# Show data: 
library(knitr)
library(magrittr)

df_wide %>% 
  kable(caption = "Table 1: Data Frame in Wide Form")

Table 1: Data Frame in Wide Form

time_month	firm_name	roe	roa
1	A	-1.2833712	-1.1750246
2	A	-1.2634498	2.1104419
1	B	0.2146729	-0.5214171
2	B	0.9469490	-0.9300566

# Convert to long form data frame: 
library(tidyr)

df_wide %>% 
  pivot_longer(cols = c("roe", "roa"), names_to = "type", values_to = "value") -> df_long

# Show data: 

df_long %>% 
  kable(caption = "Table 2: Data Frame in Long Form")

Table 2: Data Frame in Long Form

time_month	firm_name	type	value
1	A	roe	-1.2833712
1	A	roa	-1.1750246
2	A	roe	-1.2634498
2	A	roa	2.1104419
1	B	roe	0.2146729
1	B	roa	-0.5214171
2	B	roe	0.9469490
2	B	roa	-0.9300566

We use tidyr::pivot_wider() for converting to wide from long:

# Convert to wide form (or original form): 

df_long %>% 
  pivot_wider(names_from = type, values_from = value) -> to_df_wide

# Compare: 

to_df_wide %>% 
  kable(caption = "Table 3: Data after Converting from Long Form")

Table 3: Data after Converting from Long Form

time_month	firm_name	roe	roa
1	A	-1.0390170	1.7785191
2	A	-0.7982752	-0.6844104
1	B	-1.2531739	0.9564729
2	B	-0.7472431	-0.4286218

This post illustrates the power of R in its ability to reshape data. These two examples show common examples of reshaping data, but do not exhaustively demonstrate the different kinds of data reshaping that you could encounter.