1

There must be a simple and elegant way of doing this in R with data.table package, but I have trouble figuring it out. Vectorized operations are preferable.

library(data.table)    
d1 <- as.Date("01-13-2013", '%m-%d-%Y')    
d2 <- as.Date("12-31-2013", '%m-%d-%Y')    
data <- data.frame(Date=sample(seq(d1,d2,1),10), Customer_ID=sample(1:5,20,replace=T), Product=sample(letters[1:5]), Store=sample(c("S1","S2")))

create

ID Date_Prod_A Times_Purchased_A Date_Prod_C Times_Purchased_C

1 47  01-01-2012                 2           -                 -
2 26           -                 -  01-17-2012                 1

Find the list of all values for product and add two columns per each product. Also, create a table that shows how many different products appear for each ID.

table <- data.table(data)
Jaap
  • 81,064
  • 34
  • 182
  • 193
mel
  • 1,566
  • 5
  • 17
  • 29
  • Sorry, where is product_C coming from? is this perhaps a formatting issue with your text? – Tommy Levi Jun 05 '13 at 23:57
  • 2
    Welcome to stackoverflow. [Reproducible examples](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) are the way to go. Some evidence of effort on your behalf is usually considered polite. – mnel Jun 06 '13 at 00:09

1 Answers1

3

I think you mean how many times appears the product and not how many different products , for each ID.

Here a solution using data.table and reshape but in 2 steps : First I compute the number of product in the long format , then I transform my data to the wide one. I think that using plyr and ddply it is better here.

library(data.table)
DT <- as.data.table(dat1)
DT[,n := .N,by= ID]
reshape(DT,direction='wide',idvar='ID',timevar='Product',drop='Store')
  ID Purchase_date.Product_A n.Product_A Purchase_date.Product_C n.Product_C
1: 47              01-01-2012           2                      NA          NA
2: 26                      NA          NA              01-17-2012           1
mnel
  • 113,303
  • 27
  • 265
  • 254
agstudy
  • 119,832
  • 17
  • 199
  • 261
  • 3
    @DeanMacGregor You are confusing the the obsolete reshape package (updated by reshape2) and the reshape command I am using here – agstudy Jun 06 '13 at 02:45
  • 1
    @agstudy I didn't know there was a `reshape` command in the `reshape2` package. I always just use `melt` and `cast` – Dean MacGregor Jun 06 '13 at 11:19