moman822 moman822 - 1 month ago 8
R Question

spread values across undefined columns

I have a data.frame of the following structure:

value group1 group2
1: 1 A a1
2: 2 A a2
3: 3 A a3
4: 4 B b1
5: 5 B b2


I would like to spread it out to this:

group1 var1 var2 var3 value1 value2 value3
1: A a1 a2 a3 1 2 3
2: B b1 c2 NA 4 5 NA


So basically there is an unspecified number of
varX
columns based on the number of unique
group2
in each
group1
, and then an accompanying
valueX
column as well.

Is there a good way to accomplish this?
spread
from tidyr doesn't quite do what I want as I understand it. Thanks!

...

Here you can build the first data.frame:

data.frame(value=1:5, group1=c("A","A","A","B","B"), group2=c("a1","a2","a3","b1","b2"))

Answer

We need to create a sequence column, using the development version of data.table, this can be done with rowid function. Also, as the dcast from data.table takes multiple value.var columns, it can be done in a single line.

library(data.table)#v1.9.7+
dcast(setDT(df1), group1~rowid(group1), value.var = c("value", "group2"), sep="")