Search code examples
rexcelapriorimarket-basket-analysis

Converting multiple row data to single row in R/Excel

I am working on a transaction data for Market Basket Analysis which has below mention table format:

Id Product    
 1  Prod A    
 1  Prod B    
 1  Prod C    
 1  Prod D   
 2  Prod A    
 2  Prod B

I want to convert the layout of the data so that the apriori algorithm can work, taking the data as single transaction data. So for the purpose, I want to convert the data in to the following format:

Id Column1 Column2 Column3 Column3    
 1  Prod A  Prod B  Prod C  Prod D    
 2  Prod A  Prod B

  1. Can anyone help me with a way to convert this data in R or Excel?

  2. Will this data work for running the apriori algorithm in R (hope it will work)?

Solution

  • Use dcast of reshape2 package in R:

    df <- data.frame(Id=c(1,1,1,1,2,2), Product=c("Prod A", "Prod B", "Prod C", "Prod D", "Prod A", "Prod B"))

library(reshape2)
dcast(df, Id~Product, value.var="Product")
#    Id Prod A Prod B Prod C Prod D
#  1  1 Prod A Prod B Prod C Prod D
#  2  2 Prod A Prod B   <NA>   <NA>