How to convert a group of variables into multiple columns in R?

Question

I have a dataset like this:

vars    Year    Data
abc12   2014    2630898
abc12   2015    2619763
abc12   2016    2692241
bgh23   2014    2844513
bgh23   2015    2832818
bgh23   2016    3009455
drt98   2014    3166243
drt98   2015    3339208
drt98   2016    3339208

I want to turn it into this:

Year    var1     var2    var3
2014    2630898  2844513 3166243
2015    2619763  2619763 3339208
2016    2692241  2832818 3339208

Essentially taking the groups of variables and building a column out of them. How can I do it? Thanks!

`reshape2::dcast(df,Year~vars)` or `aggregate(Data~Years,df,I)` or `spread` — Onyambu, Feb 12 '18 at 21:08

score 3 · Accepted Answer · answered Feb 12 '18 at 21:04

3

This can be done with spread from tidyr:

tidyr::spread(df, vars, Data)

answered Feb 12 '18 at 21:04

tyluRp

4,678
2
17
36

Awesome! I have been trying to use group by and separate to no avail. Thanks a lot! – Feb 12 '18 at 21:06
np, happy to help – tyluRp Feb 12 '18 at 21:07

jay.sf · Answer 2 · 2018-02-12T23:08:48.373

The base R solution would be

reshape(df1, timevar = "vars", idvar = "Year", direction = "wide")

Nasty but useful for tougher challenges since it it is a lot faster—or, better still, aggregate(Data~Year, df1, I) as @Onyambu suggests in the comment.

Unit: milliseconds
      expr      min       lq      mean   median        uq       max neval
 aggregate 1.107592 1.288484  1.396685 1.377006  1.474080  10.58327  1000
   reshape 1.519411 1.684908  1.820591 1.775141  1.873498  10.40494  1000
    spread 8.670002 9.469691 10.365579 9.789567 10.116285 308.85757  1000

How to convert a group of variables into multiple columns in R?

2 Answers2