dcast - concatenate column values with column names

Question

I would like to concatenate column values with column names to create new columns. I am experimenting with library(reshape2), dcast however I can't get the required output.

Is there a method that doesn't involve performing dcast multiple times then merging the resulting sets back together?

Current data frame:

  observation=c(1,1,1,2,2,2,3,3,3)
  event=c('event1','event2','event3','event1','event2','event3','event1','event2','event3')
  value1=c(1,2,3,4,5,6,7,8,9)
  value2=c(11,12,13,14,15,16,17,18,19)
  current=data.frame(observation,event,value1,value2)
  current

Required data frame:

  observation=c(1,2,3)
  event1_value1 =c(1,4,7)
  event2_value1 =c(2,5,8)
  event3_value1 =c(3,6,9)
  event1_value2 =c(11,14,17)
  event2_value2 =c(12,15,18)
  event3_value2 =c(13,16,19)  
  required=data.frame(observation,event1_value1,event2_value1,event3_value1,event1_value2,event2_value2,event3_value2)
  required

The method below works but I feel there must be a quicker way!

library(reshape2)    
value1 <-    dcast(current,observation~event,value.var ="value1")
value2 <-    dcast(current,observation~event,value.var ="value2")
merge(value1,value2,by="observation",suffixes = c("_value1","_value2"))

This is an extension of reshape from long to wide

score 5 · Accepted Answer · answered Jul 01 '15 at 13:16

You can use the devel version of data.table i.e. v1.9.5 which can take multiple value.var columns. Instructions to install the devel version are here

library(data.table)#v1.9.5+
dcast(setDT(current), observation~event, value.var=c('value1', 'value2'))
#    observation event1_value1 event2_value1 event3_value1 event1_value2
#1:           1             1             2             3            11
#2:           2             4             5             6            14
#3:           3             7             8             9            17
#   event2_value2 event3_value2
#1:            12            13
#2:            15            16
#3:            18            19

Or reshape from base R

reshape(current, idvar='observation', timevar='event', direction='wide')
#  observation value1.event1 value2.event1 value1.event2 value2.event2
#1           1             1            11             2            12
#4           2             4            14             5            15
#7           3             7            17             8            18
#  value1.event3 value2.event3
#1             3            13
#4             6            16
#7             9            19

Thanks akrun, I thought it was a bit of a limitation of dcast to not take multiple value.var columns. I'm glad the functionality is being added! — Chris, Jul 01 '15 at 13:31
@Chris It was recently added. Also, you can check the `?melt` in the devel version which have similar type of reverse functionality. — akrun, Jul 01 '15 at 13:35

score 2 · Answer 2 · answered Jul 01 '15 at 13:29

2

I'm not sure of the efficiency but you could try this -

> dcast(melt(current,id.vars = c('observation','event')),observation~event+variable)
  observation event1_value1 event1_value2 event2_value1 event2_value2 event3_value1 event3_value2
1           1             1            11             2            12             3            13
2           2             4            14             5            15             6            16
3           3             7            17             8            18             9            19

answered Jul 01 '15 at 13:29

TheComeOnMan

12,535
8
39
54

2

You could wrap it up into `recast` – David Arenburg Jul 01 '15 at 13:35
1

I didn't know this existed. Each day we learn. Thanks! – TheComeOnMan Jul 01 '15 at 13:42
Thanks, this also works. I'll see how the different methods compare on a larger data frame. – Chris Jul 01 '15 at 13:43
1

@Chris `data.table` approach will *always* win. You can be certain in that. The advantage of `reshape2` approach is that it uses a stable version on cran compared to a devel version of `data.table`. And it should be faster than the base `reshape` approach. Though `data.table` v 1.9.6 should be released any day now. – David Arenburg Jul 01 '15 at 13:44

dcast - concatenate column values with column names

2 Answers2