How to convert rows of dataframe in columns in r with groupby

Question

I have following data frame in r

Client_ID      code       Losses        Scrips
 ACS23         1234       -3456       Apple Inc. 
 ACS23         4356      -4567.78     Microsoft
 ACS23         6677       -32567        XYZ
 VF568         2365       -44666      ABC Inc. 
 VF568         4356      -4567.78     Microsoft
 FGE45         6677       -32567        XYZ

Every client will have maximum 3 losses(3 rows) in different scrips. Some clients may have losses in only 2 scrips or may have in 1. I want to get this dataframe at client level,so every client will have one row. Following is my desired dataframe.

Client_ID  code_1   Losses_1  Scrips_1   code_2   Losses_2  Scrips_2     code_3    Losses_3   Scrips_3
  ACS23     1234    -3456    Apple Inc.   4356    -4567.78  Microsoft    6677       -32567     XYZ
  VF568     2365    -44666   ABC Inc.     4356    -4567.78  Microsoft     NA          NA       NA
  FGE45     6677    -32567    XYZ          NA       NA        NA          NA          NA       NA

How Can I achieve this in R. My column names will remain same. Currently I am doing it with for loop but it gives me an error.

unq_clients <- as.vector(unique(df$Client_ID)
new_list <- list()
for(i in 1:length(unq_clients){
   dataframe <- df[df$Client_ID == unq_clients[i],]
   for(j in 1:nrow(dataframe){
      new_list[[,paste0("code_",j,sep="")]][i] <- dataframe$code
      new_list[[,paste0("Losses_",j,sep="")]][i] <- dataframe$Losses
      new_list[[,paste0("Scrips_",j,sep="")]][i] <- dataframe$Scrips
    }
  }

But when I try to convert above list back to dataframe I get error as all the list elements are not of same length. Can I achieve this with reshape? above code is quite complicated as I have list of 40000 clients.

score 2 · Accepted Answer · answered Jan 30 '17 at 17:04

Here is one option using dcast from data.table which can take multiple value.var columns

library(data.table)
dcast(setDT(df1), Client_ID~rowid(Client_ID), value.var = c("code", "Losses", "Scrips"))
#    Client_ID code_1 code_2 code_3 Losses_1 Losses_2 Losses_3   Scrips_1  Scrips_2 Scrips_3
#1:     ACS23   1234   4356   6677    -3456 -4567.78   -32567 Apple Inc. Microsoft      XYZ
#2:     FGE45   6677     NA     NA   -32567       NA       NA        XYZ        NA       NA
#3:     VF568   2365   4356     NA   -44666 -4567.78       NA   ABC Inc. Microsoft       NA

It worked like charm. Thanks a ton :) – Neil Jan 30 '17 at 17:17 — Neil, Jan 30 '17 at 17:17

How to convert rows of dataframe in columns in r with groupby

1 Answers1