Python: Pandas: get all the combination and their frequency of different column

Question

I have a python dataframe has three columns.

    a   b   c
0   1   2   3
1   1   2   3
2   1   2   8
3   1   5   9
4   1   3   7
5   1   3   4

I want to find all the combination of a,b,c, my expected result is like:

[1,2,3]: 2  # from row 0 and row 1
[1,2]  : 3  # from row 0 and row 1 and row 2
[1,3]  : 4  # from row 0, 1, 4, 5
[1,4]  : 1
[1,5]  : 1
[1,7]  : 1
[1,8]  : 1
[1,9]  : 1
[2,3]  : 2
............

Feel free to use any package.

import pandas as pd
pd.DataFrame(data=[[1,2,3],[1,2,3],[1,2,8],[1,5,9],[1,3,7],[1,3,4]],columns=['a','b','c'])

3kt · Accepted Answer · 2017-09-21T08:17:08.223

A disgusting one-liner:

In [114]: collections.Counter(map(str, itertools.chain.from_iterable(list(df.apply(lambda x: list(itertools.chain.from_iterable([list(itertools.combinations(x, k)) for k in range(1, 4)])), axis=1).values))))
Out[114]: 
Counter({'(1, 2)': 3,
         '(1, 2, 3)': 2,
         '(1, 2, 8)': 1,
         '(1, 3)': 4,
         '(1, 3, 4)': 1,
         '(1, 3, 7)': 1,
         '(1, 4)': 1,
         '(1, 5)': 1,
         '(1, 5, 9)': 1,
         '(1, 7)': 1,
         '(1, 8)': 1,
         '(1, 9)': 1,
         '(1,)': 6,
         '(2, 3)': 2,
         '(2, 8)': 1,
         '(2,)': 3,
         '(3, 4)': 1,
         '(3, 7)': 1,
         '(3,)': 4,
         '(4,)': 1,
         '(5, 9)': 1,
         '(5,)': 1,
         '(7,)': 1,
         '(8,)': 1,
         '(9,)': 1})

Some explanations:

It starts with applying a lambda function on each of the rows, thanks to the df.apply(..., axis=1.
The lambda function creates all the possible combinations of the row's values, regardless of the number of entries.
We merge all found values in a single list, per row. That's where the first itertools.chain.from_iterable kicks in.
We merge all row values into a single list, the second itertools.chain.from_iterable is used.
We describe the result, thanks to collections.Counter, and get the frequencies.

Edit

The same solution, but without using itertools.chain.from_iterable

In [25]: collections.Counter([str(k) for l in df.apply(lambda x: [c for i in range(1, 4) for c in itertools.combinations(x, i)], axis=1).values for k in l])

This time, I leveraged list comprehension to achieve the same result, which may lead to a more readable solution. The steps are roughly the same, without the "lists merging" fuss.

piRSquared · Answer 2 · 2017-09-20T23:19:40.203

2

from cytoolz import concat, mapcat
from functools import partial
from itertools import combinations

c = lambda x, k: combinations(x, k)

pd.value_counts(list(concat(concat(map(
    partial(c, x),
    range(2, df.shape[1] + 1)
)) for x in df.values.tolist())))

(1, 3)       4
(1, 2)       3
(1, 2, 3)    2
(2, 3)       2
(5, 9)       1
(1, 2, 8)    1
(1, 3, 4)    1
(2, 8)       1
(1, 4)       1
(1, 3, 7)    1
(1, 5, 9)    1
(1, 8)       1
(1, 9)       1
(1, 7)       1
(3, 7)       1
(3, 4)       1
(1, 5)       1
dtype: int64

With @juanpa.arrivillaga's suggestion of mapcat

pd.value_counts(list(concat(
    (mapcat(partial(c, x), range(2, df.shape[1] + 1)) for x in df.values.tolist())
)))

(1, 3)       4
(1, 2)       3
(1, 2, 3)    2
(2, 3)       2
(5, 9)       1
(1, 2, 8)    1
(1, 3, 4)    1
(2, 8)       1
(1, 4)       1
(1, 3, 7)    1
(1, 5, 9)    1
(1, 8)       1
(1, 9)       1
(1, 7)       1
(3, 7)       1
(3, 4)       1
(1, 5)       1
dtype: int64

edited Sep 20 '17 at 23:19

answered Sep 20 '17 at 22:14

piRSquared

285,575
57
475
624

1

I can never figure out this kind of awesome answer ... So I noted it in my python note .. – BENY Sep 20 '17 at 22:57
1

Nice, I'd never heard of `toolz`, but it sounds super useful... even moreso a Cythonized version. Just a thought, maybe you can use `mapcat `? I've dreamed of an efficient flat-map function in Python! – juanpa.arrivillaga Sep 20 '17 at 23:09
@juanpa.arrivillaga ok, I'll look at it. – piRSquared Sep 20 '17 at 23:10
@piRSquared I *believe* it can replace `concat(map(partial(c, x), ...))` with just `mapcat(partial(c, x), ...)` – juanpa.arrivillaga Sep 20 '17 at 23:12
that's brilliant! – piRSquared Sep 20 '17 at 23:13
@piRSquared yep, *much better* than `chain.from_iterable(map(...))` – juanpa.arrivillaga Sep 20 '17 at 23:18

score 1 · Answer 3 · answered Sep 20 '17 at 22:42

There may be efficient way, one way may be following:

import pandas as pd
from  itertools import combinations

from collections import Counter

df = pd.DataFrame(data=[[1,2,3],[1,2,3],[1,2,8],[1,5,9],[1,3,7],[1,3,4]],columns=['a','b','c'])


# Get columns combination
# https://stackoverflow.com/a/43348187/5916727
cc = list(combinations(df.columns, 2))

# Append to new list for combinations
tmp_list = []

for columns in cc:
    tmp_list.append(list(zip(df[columns[0]], df[columns[1]])))

# https://stackoverflow.com/a/32786226/5916727
tmp_list.append(list(zip(df.a, df.b, df.c)))

# Flatten the list
# https://stackoverflow.com/a/952952/5916727
flat_list = [item for sublist in tmp_list for item in sublist]

print(['{0}:{1}'.format(list(item), count) for item, count in Counter(flat_list).items()])

Result:

['[1, 2]:3',
 '[5, 9]:1',
 '[1, 2, 8]:1',
 '[1, 3]:4',
 '[2, 8]:1',
 '[1, 3, 4]:1',
 '[1, 3, 7]:1',
 '[1, 4]:1',
 '[1, 2, 3]:2',
 '[1, 5]:1',
 '[1, 8]:1',
 '[2, 3]:2',
 '[1, 9]:1',
 '[1, 7]:1',
 '[3, 7]:1',
 '[3, 4]:1',
 '[1, 5, 9]:1']

Python: Pandas: get all the combination and their frequency of different column

3 Answers3