reshape2 and wide (imputed) time variables

Question

I know reshape from base R can convert to a long format where the time is imputed from the stub variable names A and B, for example:

wide = data.frame(A.2010 = c('a', 'b', 'c'),
                  A.2011 = c('f', 'g', 'd'),
                  B.2010 = c('A', 'B', 'C'),
                  B.2011 = c('G', 'G', 'H'),
                  z = runif(3),
                  x = runif(3))

wide
#  A.2010 A.2011 B.2010 B.2011         z          x
#1      a      f      A      G 0.3626823 0.67212468
#2      b      g      B      G 0.3726911 0.09663248
#3      c      d      C      H 0.9807237 0.31259394

Becomes:

reshape(wide, direction = 'long', sep = '.',
        varying = c('A.2010', 'A.2011', 'B.2010', 'B.2011'))
#               z          x time A B id
#1.2010 0.3626823 0.67212468 2010 a A  1
#2.2010 0.3726911 0.09663248 2010 b B  2
#3.2010 0.9807237 0.31259394 2010 c C  3
#1.2011 0.3626823 0.67212468 2011 f G  1
#2.2011 0.3726911 0.09663248 2011 g G  2
#3.2011 0.9807237 0.31259394 2011 d H  3

Can I accomplish the same with reshape2::melt?

acylam · Accepted Answer · 2016-11-28T03:16:34.937

It seems like reshape in base r is the best tool to do this, since there is no similar functionality in the melt function from the reshape2 package. You can however, achieve something similar with the patterns function in melt.data.table:

library(reshape2)
library(data.table)

wide = data.table(wide)

long = melt(wide, id.vars = c("z", "x"), measure = patterns("^A", "^B"),
            value.name = c("A", "B"), variable.name = "time")

> long
           z         x time A B
1: 0.3421681 0.8432707    1 a A
2: 0.1243282 0.5096108    1 b B
3: 0.3650165 0.1441660    1 c C
4: 0.3421681 0.8432707    2 f G
5: 0.1243282 0.5096108    2 g G
6: 0.3650165 0.1441660    2 d H

Notice that melt recognizes the varying "time", and groups them correctly, but does not use 2010 and 2011 as desired. A workaround is to recode the levels manually, which should be trivial.

levels(long$time) = c("2010", "2011")

> long
           z         x time A B
1: 0.3421681 0.8432707 2010 a A
2: 0.1243282 0.5096108 2010 b B
3: 0.3650165 0.1441660 2010 c C
4: 0.3421681 0.8432707 2011 f G
5: 0.1243282 0.5096108 2011 g G
6: 0.3650165 0.1441660 2011 d H

I hope this helps!

reshape2 and wide (imputed) time variables

1 Answers1