Collapsing data with the same ID, into single rows

Question

I have a dataframe that looks like the below:

  B0022HOME_NO  Device_Detail
1      1043036  Laptop/Netbook
2      1043036         Tablet
3      3753259  Laptop/Netbook   
4      3753259  Laptop/Netbook   
5      3753259         Tablet   
6      3753259         Tablet

What I want to get t is something like this, in which I have a single row for every unique B0022HOME_NO value, containing all Device_Detail listings.

  B0022HOME_NO  Device_Detail1  Device_Detail2  Device_Detail3  Device_Detail4
1      1043036  Laptop/Netbook  Tablet
2      3753259  Laptop/Netbook  Laptop/Netbook  Tablet          Tablet

I am quite new to Dplyr (and R) so have been trying to use the gather function, but to no avail.

score 3 · Accepted Answer · answered Nov 25 '15 at 12:38

We can try

library(data.table)
setDT(df2)[, N:= paste0('Device_Detail',1:.N), B0022HOME_NO]
dcast(df2, B0022HOME_NO~ N, value.var='Device_Detail', fill='')
# B0022HOME_NO Device_Detail1 Device_Detail2 Device_Detail3 Device_Detail4
#1:      1043036 Laptop/Netbook         Tablet                              
#2:      3753259 Laptop/Netbook Laptop/Netbook         Tablet         Tablet

Or

library(dplyr)
library(tidyr)
df2 %>% 
    group_by(B0022HOME_NO) %>%
    mutate(N = paste0('Device_Detail', row_number())) %>%
    spread(N, Device_Detail, fill='')
# B0022HOME_NO Device_Detail1 Device_Detail2 Device_Detail3 Device_Detail4
#         (int)          (chr)          (chr)          (chr)          (chr)
#1      1043036 Laptop/Netbook         Tablet                              
#2      3753259 Laptop/Netbook Laptop/Netbook         Tablet         Tablet

Collapsing data with the same ID, into single rows

1 Answers1