Casting data in R with more than one variable

Question

I would like to cast data from long to wide but show variables as rows and not columns

Dataset:

df1 <- data.frame(Area = c('a', 'a', 'b', 'b', 'c', 'c'),
                  Period =c(1,2,1,2,1,2), 
                  var1 = c(1,2,3,4,5,6),
                  var2 = c(7,8,9,10,11,12),
                  var3 = c(13,14,15,16,17,18)
                  )

Area Period var1 var2 var3
1   a   1   1   7   13
2   a   2   2   8   14
3   b   1   3   9   15
4   b   2   4   10  16
5   c   1   5   11  17
6   c   2   6   12  18

And I would like to get to:

             Period 
Area          1   2
 a    Var1      
 a    Var2      
 a    Var3      
 b    Var1      
 b    Var2      
 b    Var3      

I've tried with dcast and managed to get:

df1 = dcast(setDT(df1), Area ~ Period ,sum, value.var=c("var1", "var2", "var3"))

Area    var1_1    var1_2    var2_1    var2_2    var3_1    var3_2
1         a         1     2     7         8     13  14
2         b         3     4     9         10    15  16
3         c         5     6     11        12    17  18

..but this is not really what I am after. Any help would be much appreciated!

Answer 1

With dplyr and tidyr, you can do:

df1 %>% 
 pivot_longer(-c(1:2)) %>%
 pivot_wider(names_from = Period, values_from = value)

  Area  name    `1`   `2`
  <fct> <chr> <dbl> <dbl>
1 a     var1      1     2
2 a     var2      7     8
3 a     var3     13    14
4 b     var1      3     4
5 b     var2      9    10
6 b     var3     15    16
7 c     var1      5     6
8 c     var2     11    12
9 c     var3     17    18