Bsrgvty

I have DataFrame in the following form:

ID Product
1 A
1 B
2 A 
3 A
3 C 
3 D 
4 A
4 B

I would like to count the most common combination of two values from Product column grouped by ID.
So for this example expected result would be:

Combination Count
A-B 2
A-C 1
A-D 1
C-D 1

Is this output possible with pandas?

Use itertools.combinations, explode and value_counts

import itertools

(df.groupby('ID').Product.agg(lambda x: list(itertools.combinations(x,2)))
 .explode().str.join('-').value_counts())

Out[611]:
A-B 2
C-D 1
A-D 1
A-C 1
Name: Product, dtype: int64

Or:

import itertools

(df.groupby('ID').Product.agg(lambda x: list(map('-'.join, itertools.combinations(x,2))))
 .explode().value_counts())

Out[597]:
A-B 2
C-D 1
A-D 1
A-C 1
Name: Product, dtype: int64

We can merge within ID and filter out duplicate merges (I assume you have a default RangeIndex). Then we sort so that the grouping is regardless of order:

import pandas as pd
import numpy as np

df1 = df.reset_index()
df1 = df1.merge(df1, on='ID').query('index_x > index_y')

df1 = pd.DataFrame(np.sort(df1[['Product_x', 'Product_y']].to_numpy(), axis=1))
df1.groupby([*df1]).size()

0 1
A B 2
 C 1
 D 1
C D 1
dtype: int64

You can use combinations from itertools along with groupby and apply

from itertools import combinations

def get_combs(x):
 return pd.DataFrame('Combination': list(combinations(x.Product.values, 2)))

(df.groupby('ID').apply(get_combs)
 .reset_index(level=0)
 .groupby('Combination')
 .count()
)

 ID
Combination 
(A, B) 2
(A, C) 1
(A, D) 1
(C, D) 1

Using itertools and Counter.

import itertools
from collections import Counter

agg_ = lambda x: tuple(itertools.combinations(x, 2))
product = list(itertools.chain(*df.groupby('ID').agg('Product': lambda x: agg_(sorted(x))).Product))
# You actually do not need to wrap product with list. The generator is ok
counts = Counter(product)

Output

Counter(('A', 'B'): 2, ('A', 'C'): 1, ('A', 'D'): 1, ('C', 'D'): 1)

You could also do the following to get a dataframe

pd.DataFrame(list(counts.items()), columns=['combination', 'count'])

 combination count
0 (A, B) 2
1 (A, C) 1
2 (A, D) 1
3 (C, D) 1

Another trick with itertools.combinations function:

from itertools import combinations
import pandas as pd

test_df = ... # your df
counts_df = test_df.groupby('ID')['Product'].agg(lambda x: list(combinations(x, 2)))
 .apply(pd.Series).stack().value_counts().to_frame()
 .reset_index().rename(columns='index': 'Combination', 0:'Count')
print(counts_df)

The output:

 Combination Count
0 (A, B) 2
1 (A, C) 1
2 (A, D) 1
3 (C, D) 1