1

I would like to reshape my data frame from long to wide by 2 variables (whose combinations create new unique identifier) while aggregating values by product, product2, and date so that the following: 

Date           product product2 value
03/03/2011       a        z        7
03/03/2011       a        z        2
03/05/2015       b        z       89
03/01/2017       a        z        2
03/03/2017       c        z        6

which would yield the following: 

      03/03/2011  03/03/2011  03/05/2015  03/01/2017  03/03/2017 
a z       9                                   2
b z                               89
c z                                                        6

Should I use dplyr, reshape, reshape2? 

df <- structure(list(Date = c("03/03/2011", "03/03/2011", "03/05/2015", "03/01/2017", "03/03/2017"),
             product= c("a", "a", "b", "a", "c"),
             product2= c("z", "z", "z", "z", "z"), 
             value= c(7L, 2L, 89L, 2L, 6L)), 
             .Names= c("Date", "product", "product2", "value"), 
             class= "data.frame", row.names=c(NA, -5L))
flightless13wings
  • 109
  • 10

1 Answers1

1
library(data.table)
data<-fread("Date           product product2 value
03/03/2011       a        z        7
            03/03/2011       a        z        2
            03/05/2015       b        z       89
            03/01/2017       a        z        2
            03/03/2017       c        z        6")
data<-dcast(data,product+product2~Date,value.var="value",fun.aggregate = sum)
data[,unique_id:=paste(product,product2,sep="")]
data
   product product2 03/01/2017 03/03/2011 03/03/2017 03/05/2015 unique_id
1:       a        z          2          9          0          0        az
2:       b        z          0          0          0         89        bz
3:       c        z          0          0          6          0        cz
Vitalijs
  • 938
  • 7
  • 18