[FIXED] How do I find the most frequent pairing with an item in Python

Issue

I have a dataframe that shows order transactions but I am struggling to find a way to list down the most frequent purchased pairing with red wine. I saw it somewhere before but I can't seem to recall how to.

import pandas as pd
df_orders = pd.DataFrame(
    {'Order':[300,300,301,301,301,302,302,303,303],
    'Item':['Red wine','Chicken chop','Red wine',
            'Hawaiian Pizza','Garden Salad','Chicken chop',
            'Mineral Water','Garden Salad','Red wine']})

   Order           Item  
0   300        Red wine    
1   300    Chicken chop    
2   301        Red wine    
3   301  Hawaiian Pizza
4   301    Garden Salad    
5   302    Chicken chop    
6   302   Mineral Water    
7   303    Garden Salad
8   303        Red wine

My aim is to find the most frequent items that is ordered at the same time as red wine. Intended output:

              Item             
0     Garden Salad     2    
1     Chicken chop     1   
2   Hawaiian Pizza     1  

I tried using groupby, counter and a few other stuff but didn't get the desired output. Hopefully someone better than me can help me with this. Thank you.

Solution

Filter only order numbers which has 'Red wine' and then use Series.value_counts to count items.

df[df.Order.isin(df[df.Item == 'Red wine'].Order)].Item.value_counts().drop('Red wine')

Answered By - Håken Lid