4.2 Data Transformation from Wide to Long (or vice versa)

  • Sometimes its required to transform wide format data to long, which is often required to work with ggplot2 package (discussed in the graphics section)
  • R package tidyr provides two functions pivot_longer() and pivot_wider() to transform the data into long or wide format.
  • Let’s convert the stocks data to the long format
library(tidyr)

FinData_long = pivot_longer(data = data_stocks, cols = -Date, names_to = "Stock",
    values_to = "Price")
head(FinData_long)
# A tibble: 6 x 3
  Date      Stock Price
  <chr>     <chr> <dbl>
1 2/01/2002 MSFT   33.5
2 2/01/2002 IBM   122. 
3 2/01/2002 AAPL   11.6
4 2/01/2002 MCD    26.5
5 2/01/2002 PG     40  
6 2/01/2002 GOOG   NA  
  • A reverse operation can be conducted using pivot_wider()
FinData_wide = pivot_wider(FinData_long, names_from = Stock, values_from = Price)
head(FinData_wide)
# A tibble: 6 x 7
  Date       MSFT   IBM  AAPL   MCD    PG  GOOG
  <chr>     <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 2/01/2002  33.5  122.  11.6  26.5  40      NA
2 3/01/2002  34.6  124.  11.8  26.8  39.6    NA
3 4/01/2002  34.4  126.  11.8  27.0  39.2    NA
4 7/01/2002  34.3  124.  11.4  27.2  38.8    NA
5 8/01/2002  34.7  125.  11.3  27.4  38.9    NA
6 9/01/2002  34.4  124.  10.8  26.9  38.6    NA