1

What I'm currently trying to do is spread all the columns onto multiple columns, for example this dataframe

Person_ID  Car_ID Car_Type Car_Speed
a          1      x        50
a          2      y        70
a          3      z        100
b          4      y        70

I want to turn it to this

Person_ID  Car_ID1 Car_Type1 Car_Speed1 Car_ID2 Car_Type2 Car_Speed2 Car_ID3 Car_Type3 Car_Speed3
a          1        x        50         2      y        70           3       z         100    
b          4        y        70

Can someone help? Thank you.

newRuser
  • 885
  • 1
  • 7
  • 9

1 Answers1

2

This can be easily done with dcast from data.table which can take multiple value.var columns

library(data.table)#v1.9.7+
dcast(setDT(df1), Person_ID~rowid(Person_ID), 
              value.var = c("Car_ID", "Car_Type", "Car_Speed"))
#     Person_ID Car_ID_1 Car_ID_2 Car_ID_3 Car_Type_1 Car_Type_2 Car_Type_3 Car_Speed_1 Car_Speed_2
#1:         a        1        2        3          x          y          z          50          70
#2:         b        4       NA       NA          y         NA         NA          70          NA
#    Car_Speed_3
#1:         100
#2:          NA

Or with reshape from base R after creating a sequence column grouped by 'Person_ID'

df2 <- transform(df1, Seq = ave(seq_along(Person_ID), Person_ID, FUN = seq_along))
reshape(df2, idvar = "Person_ID", timevar = "Seq", direction = "wide")
akrun
  • 874,273
  • 37
  • 540
  • 662
  • got a 'could not find function "rowid"' or i understood the rowid incorrectly? – newRuser Jun 15 '16 at 11:50
  • 1
    @newRuser It is the `1.9.7` version which you can download from the github. Otherwise, create a Seq column as in the `base R` solution by `setDT(df1)[, Seq := 1:.N, Person_ID]` and then do the `dcast` i.e. `dcast(df1, Person_ID~Seq, value.var = c("Car_ID", "Car_Type", "Car_Speed"))` – akrun Jun 15 '16 at 12:37