Count of elements in lists within pandas data frame

Question

I need to get the frequency of each element in a list when the list is in a pandas data frame columns

In data:

din=pd.DataFrame({'x':[['a','b','c'],['a','e','d', 'c']]})`

              x
0     [a, b, c]
1  [a, e, d, c]

Desired Output:

   f  x
0  2  a
1  1  b
2  2  c
3  1  d
4  1  e

I can expand the list into rows and then perform a group by but this data could be large ( million plus records ) and was wondering if there is a more efficient/direct way.

Thanks

Answer 1

First flatten values of lists and then count by value_counts or size or Counter:

a = pd.Series([item for sublist in din.x for item in sublist])

Or:

a = pd.Series(np.concatenate(din.x))

---

df = a.value_counts().sort_index().rename_axis('x').reset_index(name='f')

Or:

df = a.groupby(a).size().rename_axis('x').reset_index(name='f')

---

from collections import Counter
from  itertools import chain

df = pd.Series(Counter(chain(*din.x))).sort_index().rename_axis('x').reset_index(name='f')

print (df)
   x  f
0  a  2
1  b  1
2  c  2
3  d  1
4  e  1

Answer 2

You can also have an one liner like this:

df = pd.Series(sum([item for item in din.x], [])).value_counts()

Answer 3

It is actually pretty easy with flattened lists and counters

from matplotlib.cbook import flatten
from collections import Counter

din={'x':[['a','b','c'],['a','e','d', 'c']]}
for a,i in din.items() :
    u=pd.DataFrame.from_dict(dict(Counter([*flatten(i)])), orient ='index').reset_index().rename(columns ={'index':a,0:str(a)+'_number'})

output:

However if din has several keys and values you will need a function to do the same trick

from matplotlib.cbook import flatten
from collections import Counter
din={'x':[['a','b','c'],['a','e','d', 'c']], 'y': [['h','j'],['h','j','j']]}

def foo(x):
    df = pd.DataFrame()
    for a,i in x.items() :
        u=pd.DataFrame.from_dict(dict(Counter([*flatten(i)])), orient ='index').reset_index().rename(columns ={'index':a,0:str(a)+'_number'})
        df=pd.concat([df,u])
    return df
foo(din)

Answer 4

I'd use pandas' explode and the value_counts then finally assign it to a frame.

din.explode('x').value_counts().to_frame('fq').reset_index().sort_values('x')
   x  fq
0  a   2
2  b   1
1  c   2
3  d   1
4  e   1