CODEWITHSUNDEEP
rdcast
Neil
Neil

Reputation: 33

Casting data in R with more than one variable

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!

Upvotes: 3

Views: 94

Answers (2)

Wimpel
Wimpel

Reputation: 27762

first melt to long format, then re-cast to wide

library( data.table )

dcast( 
  melt( setDT(df1), 
        id.vars = c("Area", "Period"), 
        measure.vars = patterns( var = "^var") ), 
  Area + variable ~ Period, 
  value.var = "value" )

#    Area variable  1  2
# 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

Upvotes: 2

tmfmnk
tmfmnk

Reputation: 40131

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

Upvotes: 1

Related Questions