Reshape Wide to Long with 2 time variables

December 12, 2022

Though there is an abundance of ‘wide to long’ threads for R, I haven’t found an answer that will help me with my issue. Any assistance is greatly appreciated!

Example of my dataframe (in wide format):

CODE  NAME  M_2010_1  M_2011_1  M_2012_1  M_2010_3  M_2011_3  M_2012_3
  1     A      10        11        10        9         10       13
  12    B      11        13        15        15        14       11
  8     C       9         2         4        2         8         8

Desired dataframe (in long):

CODE  NAME  YEAR  M1  M3
  1    A    2010  10  9
  1    A    2011  11  10
  1    A    2012  10  13
 12    B    2010  11  15
 12    B    2011  13  14
 12    B    2012  15  11
  8    C    2010   9   2
  8    C    2011   2   8
  8    C    2012   4   8

Thanks in advance!

>Solution :

Data

df<-
structure(list(CODE = c(1L, 12L, 8L), NAME = c("A", "B", "C"), 
    M_2010_1 = c(10L, 11L, 9L), M_2011_1 = c(11L, 13L, 2L), M_2012_1 = c(10L, 
    15L, 4L), M_2010_3 = c(9L, 15L, 2L), M_2011_3 = c(10L, 14L, 
    8L), M_2012_3 = c(13L, 11L, 8L)), class = "data.frame", row.names = c(NA, 
-3L))

Code

library(dplyr)
library(tidyr)

df %>% 
  pivot_longer(cols = -c(CODE,NAME)) %>% 
  separate(name,into = c("aux1","YEAR","aux2"),sep = "_") %>% 
  mutate(aux = paste0(aux1,aux2)) %>% 
  select(-aux1,-aux2) %>% 
  pivot_wider(names_from = aux,values_from = value)

Output

# A tibble: 9 x 5
   CODE NAME  YEAR     M1    M3
  <int> <chr> <chr> <int> <int>
1     1 A     2010     10     9
2     1 A     2011     11    10
3     1 A     2012     10    13
4    12 B     2010     11    15
5    12 B     2011     13    14
6    12 B     2012     15    11
7     8 C     2010      9     2
8     8 C     2011      2     8
9     8 C     2012      4     8