preparation data set for basket analysis in R

Question

I am currently working with a food hall data set and would like to analysis the baskets. However, I have the following problem:

Here is an extract from my data:

basket_id   item_name         item_id
2345        coke 0.5          98
2345        salad 300g        103
7876        water             88
7876        diet coke         95
7876        CANCEL diet coke  95
7876        sushi             143
3498        coffee            23

To be able to actually analysis the purchases made by the consumers I need to delete all the cancellations from the data set. Unfortunately, I can't just delete the items containing "CANCEL" because that would distort my results, since I need to delete the canceled plus the item that was canceled. For example the basket no. 7876 contains water, diet coke, CANCEL diet coke and sushi. But the consumer only bought the water and the sushi in the end. What I need is a function that recognizes the cancelation and the item that was canceled and deletes it so that I get a data set that looks like this:

basket_id   item_name   item_id
2345    coke 0.5        98
2345    salad 300g      103
7876    water           88
7876    sushi           143
3498    coffee          23

So that the actual basket 7876 only contains only the item the consumer purchased in the end. Thanks for any help or suggestions!

quant quant · Accepted Answer · 2017-04-28T09:39:17

Lets call your data set data. Then

library(data.table)

data <- data.table(
basket_id=c(2345, 
            2345, 
            7876, 
            7876, 
            7876, 
            7876, 
            3498 ),   item_name=c("coke 0.5"        ,
                                  "salad 300g"      ,
                                  "water"           ,
                                  "diet coke"       ,
                                  "CANCEL diet coke",
                                  "sushi"           ,
                                  "coffee"              ),         item_id=c(98,
                                                                             103,
                                                                             88,
                                                                             95,
                                                                             95,
                                                                             143,
                                                                             23)







)

dt <- as.data.table(data)
temp.basket <- dt[item_name%like%"^CANCEL",.(basket_id)]
temp.item <- dt[item_name%like%"^CANCEL",.(item_id)]
dt2 <- dt[!(basket_id%in%temp.basket &item_id%in%temp.item)]

The symbol ^ ensures that the item_name starts with the word CANCEL

preparation data set for basket analysis in R

1 Answers