Counting most common combination of values in dataframe column

Question

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?

Answer 1

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