Pfthb

I have some data on store level purchases, a random sample of the data looks like the following:

 PANID WEEK L1
966 3357632 2011-02-21 PIZZA
352 3357632 2009-11-09 SALTY
68 3357632 2012-06-18 BEER
65 3357632 2012-03-05 BEER
43 3108696 2011-10-31 BEER
672 3144766 2010-03-29 SALTY
70 3357632 2012-06-18 BEER
810 3144766 2012-06-18 SALTY
546 3144766 2008-05-05 SALTY
933 3357632 2009-06-15 PIZZA

(EDIT: This random sample contains 2012 info, the data I provided was filtered from 2007 - 2010 - due to character count)

There are 3 PANIDs across 4 years worth of data for 3 categories of products (BEER, SALTY, PIZZA). I am trying to find out whether people who bought BEER on a given week also bought SALTY / construct a probability that given that PANID: 3144766 bought SALTY in WEEK: 2009-06-15 what is the probability that they will also by BEER. Doing the same for pizza, i.e. given that they bought BEER the probability of them buying PIZZA and finally given that they bought PIZZA the probability of them buying SALTY.

E.G. the person below bought 3 packets of SALTY and one unit of BEER but on a different week they would have just bought BEER and PIZZA or just SALTY.

> data %>%
+ group_by(PANID) %>%
+ filter(WEEK == "2009-06-15") %>%
+ filter(PANID == "3144766")
# A tibble: 6 x 3
# Groups: PANID [1]
 PANID WEEK L1 
 <int> <date> <chr>
1 3144766 2009-06-15 BEER 
2 3144766 2009-06-15 SALTY
3 3144766 2009-06-15 SALTY
4 3144766 2009-06-15 SALTY
5 3144766 2009-06-15 PIZZA
6 3144766 2009-06-15 PIZZA

Data:

EDIT1: Data removed due to character limit. Can be found here: https://textuploader.com/db1kf

EDIT2:

I run the following code to get the below output:

#Probability of buying BEER or SALTY if PIZZA was bought
dat %>% 
 group_by(PIZZA > 0) %>% 
 summarise(beer = sum(BEER > 0) / n(), nobeer = sum(BEER == 0) / n(),
 salty = sum(SALTY > 0) / n(), nosalty = sum(SALTY == 0) / n())

#Probability of buying SALTY or PIZZA if BEER was bought
dat %>% 
 group_by(BEER > 0) %>% 
 summarise(pizza = sum(PIZZA > 0) / n(), nopizza = sum(PIZZA == 0) / n(),
 salty = sum(SALTY > 0) / n(), nosalty = sum(SALTY == 0) / n())


#Probability of buying BEER or PIZZA if SALTY was bought
dat %>% 
 group_by(SALTY > 0) %>% 
 summarise(pizza = sum(PIZZA > 0) / n(), nopizza = sum(PIZZA == 0) / n(),
 beer = sum(BEER > 0) / n(), nobeer = sum(BEER == 0) / n())

Output:

1)

# A tibble: 2 x 5
 `PIZZA > 0` beer nobeer salty nosalty
 <lgl> <dbl> <dbl> <dbl> <dbl>
1 FALSE 0.333 0.667 0.833 0.167
2 TRUE 0.257 0.743 0.586 0.414

2)

# A tibble: 2 x 5
 `BEER > 0` pizza nopizza salty nosalty
 <lgl> <dbl> <dbl> <dbl> <dbl>
1 FALSE 0.371 0.629 0.843 0.157
2 TRUE 0.290 0.710 0.532 0.468

3)

# A tibble: 2 x 5
 `SALTY > 0` pizza nopizza beer nobeer
 <lgl> <dbl> <dbl> <dbl> <dbl>
1 FALSE 0.569 0.431 0.569 0.431
2 TRUE 0.272 0.728 0.219 0.781

Just so my understanding is correct. If I buy PIZZA I have a 0.586 probability of buying SALTY and a 0.414 probability of not buying SALTY (table 1). However if I buy SALTY then I would have a 0.272 probability of buying PIZZA and a 0.728 of not buying PIZZA (table 3)?

I'm not 100% sure this is what you're looking for, so let me know if I'm off track.

We start with what I suggested in the comment (slightly adjusted to replace the NAs with 0):

df<- df %>% group_by(PANID, year, WEEK,L1) %>% summarize(n=n()) %>% tidyr::spread(L1, n, fill=0)

This gives us a wide-data format where for each person-week, we see the number of purchases of each of the three food types, e.g.:

> head(df,3)
# A tibble: 3 x 6
# Groups: PANID, year, WEEK [3]
 PANID year WEEK BEER PIZZA SALTY
 <int> <int> <date> <dbl> <dbl> <dbl>
1 3108696 2007 2007-12-31 2 4 6
2 3108696 2008 2008-01-21 0 2 2
3 3108696 2008 2008-02-04 1 0 2

Now we can create a table that gives the probability for buying either BEER or SALTY (of any amount) if PIZZA (of any amount) was purchased in the same week:

df %>% group_by(PIZZA>0) %>% 
 summarise(beer=sum(BEER>0)/n(),nobeer=sum(BEER==0)/n(),
 salty=sum(SALTY>0)/n(),nosalty=sum(SALTY==0)/n())

Result:

# A tibble: 2 x 5
 `PIZZA > 0` beer nobeer salty nosalty
 <lgl> <dbl> <dbl> <dbl> <dbl>
1 FALSE 0.333 0.667 0.833 0.167
2 TRUE 0.257 0.743 0.586 0.414

So we can see that if Pizza was purchased, the likelihood of both beer and salty goes down compared to weeks when pizza is not purchased.

The same can be done for BEER and SALTY, of course.

An alternative, since we have a numerical variable for each of the three foods, would be to calculate correlation or even regression, but that's not what you asked for.