R - dcast or transpose

Question

I am trying to take the following small sample of data:

IndID  J2015  E2015  Area
1      10     5      DC
2      15     7      DC
3      20     10     DC

Then use reshape or reshape2 to transform it into the following format:

Area  1_J2015  1_E2015  2_J2015  2_E2015  3_J2015  3_E2015
DC    10       5        15       7        20       10

I will be doing this over a few million rows of data, however I cannot figure out the correct formula to perform this, part of me believes this needs to be done in two or more steps.

I have tried various formats of the dcast function in the reshape package:

df <- dcast(AreaID + J2015 + E2015 ~ IndID, data = data)

Thank you in advance.

You could do `reshape(df, idvar = "Area", timevar = "IndID", direction = "wide")` or `library(data.table) ; dcast(setDT(df), Area ~ IndID, value.var = c("J2015", "E2015"))` — David Arenburg, Jan 26 '16 at 00:19
That works, just need to tweak it for my actual data. Thank you! — j riot, Jan 26 '16 at 00:21

G. Grothendieck · Accepted Answer · 2016-01-26T01:59:16.827

3

First melt it and then dcast it like this:

library(reshape2)

m <- melt(data, id.var = c("IndID", "Area"))
dcast(m, Area ~ IndID + variable)

giving:

  Area 1_J2015 1_E2015 2_J2015 2_E2015 3_J2015 3_E2015
1   DC      10       5      15       7      20      10

or combined into a single command:

recast(data, Area ~ IndID + variable, id.var = c("IndID", "Area"))

edited Jan 26 '16 at 01:59

answered Jan 26 '16 at 00:23

G. Grothendieck

254,981
17
203
341

R - dcast or transpose

1 Answers1