Transpose the Data

Question

I have the following data:

mydata <- data.frame(VAR1 = c("A",  "A",    "A",    "A",    "A",    "A",    "B",    "B",    "B",            
                              "B",  "B",    "B",    "C",    "C",    "C",    "C",    "C",    "C",            
                              "D",  "D",    "D",    "D",    "D",    "D",    "E",    "E",    "E",    "E",    "E",    "E"), 
                     VAR2 = c("A1", "A1",   "A1",   "A2",   "A2",   "A2",   "B1",   "B1",   "B1",           
                              "B2", "B2",   "B2",   "C1",   "C1",   "C1",   "C2",   "C2",   "C2",           
                              "D1", "D1",   "D1",   "D2",   "D2",   "D2",   "E1",   "E1",   "E1",   "E2",   "E2","E2"),
                     VAR3 = c("alpha",  "beta", "gamma",    "alpha",    "beta", "gamma",    "alpha",    "beta", "gamma",    "alpha",
                              "beta",   "gamma",    "alpha",    "beta", "gamma",    "alpha",    "beta", "gamma",    "alpha",    "beta",
                              "gamma",  "alpha",    "beta", "gamma",    "alpha",    "beta", "gamma",    "alpha",    "beta", "gamma"),
                     VALUE = c(56,  38, 83, 29, 73, 75, 99, 88, 30, 58,
                               97,  42, 64, 67, 45, 99, 62, 65, 7,  40,
                               49,  62, 82, 63, 65, 40, 45, 29, 73, 51))

I want to transpose the data in wide format, where VAR1 & VAR2 are columns, values of VAR3 will be 3 extra columns and VALUE column will be populated as values in this structure.

My question is how to do it using tidyverse & data.table::dcast? Which will be more efficient if the data size is very large, say 2 GB in raw form (my RAM size is 8 GB)?

Thanks!

tidyr/tidyverse way will be simply `mydata %>% spread(VAR3,VALUE)` — moodymudskipper, Feb 13 '18 at 10:28
@moody: thanks for your quick reply. In 'tidyr' why didn't you consider other 2 vars for spread? — Beta, Feb 13 '18 at 10:30
and `data.table::dcast` when used on regular data.frames is a wrapper around `reshape2::dcast`, `reshape2` being pretty much the ancestor of `tidyr` — moodymudskipper, Feb 13 '18 at 10:31
by default unmentioned columns are the grouping columns in `spread` — moodymudskipper, Feb 13 '18 at 10:33

score 1 · Accepted Answer · answered Feb 13 '18 at 10:30

You can use dcast() from data.table.

library(data.table)

dcast(mydata,
      VAR1 + VAR2 ~ VAR3,
      value.var = "VALUE")

Which will return

   VAR1 VAR2 alpha beta gamma
1     A   A1    56   38    83
2     A   A2    29   73    75
3     B   B1    99   88    30
4     B   B2    58   97    42
5     C   C1    64   67    45
6     C   C2    99   62    65
7     D   D1     7   40    49
8     D   D2    62   82    63
9     E   E1    65   40    45
10    E   E2    29   73    51

I am glad I could be of help! – clemens Feb 14 '18 at 07:25 — clemens, Feb 14 '18 at 07:25

Transpose the Data

1 Answers1