1

I have a wide dataframe that looks something like this:

    ID Time Amount     CabMean CabNum     PartMean PartNum     DinnMean   DinNum  Ex
1    1    1    27  0.654621546      8           NA       7  0.316791872        6   0
2    1    2    82  0.667461321      3  0.327594876       4  0.346798127        2   1
3    1    3    52  0.313976132      1           NA       6  0.197837257        7   0
4    1    4    99  0.798328712      9  0.913751678       4  0.191679538        9   1

I would like to reshape (using the reshape2 package) it to a long format that takes this form (just making these numbers up):

  ID Time Amount Ex Type   Mean         Num
1  1    2    50   0  Cab   0.65654321   7
2  1    2    50   0 Part   0.65654321   1
3  1    2    50   0 Dinn   0.65654321   4

I have tried something like this:

reshaped <- melt(data, id.vars = c("ID", "Time", "Amount", "Ex"))

Which gets me something like this:

  ID Time Amount Ex  variable    value
1  1    1    10   0  CabMean 0.6565432
2  1    2    12   0  CabMean 0.6565432

So I'm only about half way there and can't quite figure out the rest. How do I (either from the code I'm currently using, or from completely new code) extract the type (Cab, Part, Dinn) as a separate column, and create 2 additional columns that hold Mean and Num values?

Simon
  • 8,310
  • 14
  • 49
  • 108
  • Base `reshape` can do it too: `reshape(dat, idvar=c("ID","Time"), direction="long", varying=list(c(4,6,8),c(5,7,9)), times=c("cab","part","dinn"))` – thelatemail Dec 08 '15 at 05:50

1 Answers1

2

We can use melt from data.table which can take multiple measure columns with the pattern argument. We convert the 'data.frame' to 'data.table' (setDT(data)), then melt to 'long' format.

library(data.table)
DT <- melt(setDT(data), measure=patterns('Mean$', 'Num$'), 
              variable.name='Type', value.name=c('Mean', 'Num'))
DT[, Type:=c('Cab', 'Part', 'Dinn')[Type]]
akrun
  • 674,427
  • 24
  • 381
  • 486
  • How would you then rename the values in the Type column to the correct label part from the header? – Simon Dec 08 '15 at 12:24
  • 1
    @Simon You can either `gsub` from the column names and use the Type as indicator or use it directly. I updated the post. – akrun Dec 08 '15 at 12:31