-1

My data has multiple rows per memberID. I mocked it up below (see #this is what I have). I need to convert it to a the shape where I have one row per memberID, creating mulitple columns. I mocked up the desired output below as well (see #this is what I want). I am looking for the simplest way to do this transormation in R. I am a brand new R and RStudio user with little background in coding. Would be ever so grateful for help with code.

#this is what I have

ID <- c(1,1,2,3,3,3)  
Date <- as.Date (c("2015/01/01","2016/01/03", "2011/03/01", "2015/01/09", "2017/12/11","2016/09/09" )) 
Score <- c(5,15,2,6,12,18)  
df <- data.frame(ID, Date, Score)
df

#this is what i want

ID2 <- c(1,2,3)  
Date1 <- as.Date (c("2015/01/01","2011/03/01","2015/01/09"))
Date2 <- as.Date (c("2016/01/03", NA, "2017/12/11"))
Date3 <- as.Date (c(NA, NA,"2016/09/09"))
Score1 <- c(5,2,6)
Score2 <- c(15,NA,12) 
Score3 <- c("NA","NA",18) 
df2 <- data.frame (ID2, Date1, Date2, Date3, Score1, Score2, Score3)
df2
ZhenyABB
  • 17
  • 3

1 Answers1

-2

We can use dcast from data.table which can multiple value.var columns

library(data.table)
dcast(setDT(df), ID~rowid(ID), value.var = c("Date", "Score"), sep="")
#   ID      Date1      Date2      Date3 Score1 Score2 Score3
#1:  1 2015-01-01 2016-01-03       <NA>      5     15     NA
#2:  2 2011-03-01       <NA>       <NA>      2     NA     NA
#3:  3 2015-01-09 2017-12-11 2016-09-09      6     12     18
akrun
  • 874,273
  • 37
  • 540
  • 662