Zrjydtj

I am trying to figure out what combination of clothing customers are buying together. I can figure out the exact combination, but the problem I can't figure out is the count that includes the combination + others.

For example, I have:

Cust_num Item Rev
Cust1 Shirt1 $40
Cust1 Shirt2 $40
Cust1 Shorts1 $40
Cust2 Shirt1 $40
Cust2 Shorts1 $40

This should result in:

Combo Count
Shirt1,Shirt2,Shorts1 1
Shirt1,Shorts1 2

The best I can do is unique combinations:

Combo Count
Shirt1,Shirt2,Shorts1 1
Shirt1,Shorts1 1

I tried:

df = df.pivot(index='Cust_num',columns='Item').sum()
df[df.notnull()] = "x"
df = df.loc[:,"Shirt1":].replace("x", pd.Series(df.columns, df.columns))
col = df.stack().groupby(level=0).apply(','.join)
df2 = pd.DataFrame(col)
df2.groupby([0]).size().reset_index(name='counts')

But that is just the unique counts.

Using pandas.DataFrame.groupby:

grouped_item = df.groupby('Cust_num')['Item']
subsets = grouped_item.apply(lambda x: set(x)).tolist()
Count = [sum(s2.issubset(s1) for s1 in subsets) for s2 in subsets]
combo = grouped_item.apply(lambda x:','.join(x))
combo = combo.reset_index()
combo['Count']=Count

Output:

 Cust_num Item Count
0 Cust1 Shirt1,Shirt2,Shorts1 1
1 Cust2 Shirt1,Shorts1 2

I think you need to create a combination of items first.

How to get all possible combinations of a list’s elements?

I used the function from Dan H's answer.

from itertools import chain, combinations
def all_subsets(ss):
 return chain(*map(lambda x: combinations(ss, x), range(0, len(ss)+1)))

Then get the unique items.

uq_items = df.Item.unique()

list(all_subsets(uq_items))

[(),
 ('Shirt1',),
 ('Shirt2',),
 ('Shorts1',),
 ('Shirt1', 'Shirt2'),
 ('Shirt1', 'Shorts1'),
 ('Shirt2', 'Shorts1'),
 ('Shirt1', 'Shirt2', 'Shorts1')]

And use groupby each customer to get their items combination.

ls = []

for _, d in df.groupby('Cust_num', group_keys=False):
 # Get all possible subset of items
 pi = np.array(list(all_subsets(d.Item)))

 # Fliter only > 1
 ls.append(pi[[len(l) > 1 for l in pi]])

Then convert to Series and use value_counts().

pd.Series(np.concatenate(ls)).value_counts()

(Shirt1, Shorts1) 2
(Shirt2, Shorts1) 1
(Shirt1, Shirt2, Shorts1) 1
(Shirt1, Shirt2) 1

My version which I believe is easier to understand

new_df = df.groupby("Cust_num").agg(lambda x: ''.join(x.unique()))

new_df ['count'] = range(1, len(new_df ) + 1)

Output:

 Item Rev count
 <lambda> <lambda> 
Cust_num 
Cust1 Shirt1 Shirt2 Shorts1 $40 1
Cust2 Shirt1 Shorts1 $40 2

Since you do not need the Rev column, you can drop it:

new_df = new_df = new_df.drop(columns=["Rev"]).reset_index()

new_df

Output:

 Cust_num Item count
 <lambda> 
0 Cust1 Shirt1 Shirt2 Shorts1 1
1 Cust2 Shirt1 Shorts1 2