Pandas : Use groupby on each element of list

Question

Maybe I'm missing the obvious.

I have a pandas dataframe that looks like this :

   id        product              categories
    0        Silmarillion         ['Book', 'Fantasy']
    1        Headphones           ['Electronic', 'Material']
    2        Dune                 ['Book', 'Sci-Fi']

I'd like to use the groupby function to count the number of appearances of each element in the categories column, so here the result would be

Book       2
Fantasy    1
Electronic 1
Material   1
Sci-Fi     1

However when I try using a groupby function, pandas counts the occurrences of the entire list instead of separating its elements. I have tried multiple different ways of handling this, using tuples or splits, but this far I've been unsuccessful.

Answer 1

You can also call pd.value_counts directly on a list.
You can generate the appropriate list via numpy.concatenate, itertools.chain, or cytoolz.concat

from cytoolz import concat
from itertools import chain

cytoolz.concat

pd.value_counts(list(concat(df.categories.values.tolist())))

itertools.chain

pd.value_counts(list(chain(*df.categories.values.tolist())))

numpy.unique + numpy.concatenate

u, c = np.unique(np.concatenate(df.categories.values), return_counts=True)
pd.Series(c, u)

---

All yield

Book          2
Electronic    1
Fantasy       1
Material      1
Sci-Fi        1
dtype: int64

---

time testing

Answer 2

You can normalize the records by stacking them then call value_counts():

pd.DataFrame(df['categories'].tolist()).stack().value_counts()
Out: 
Book          2
Fantasy       1
Material      1
Sci-Fi        1
Electronic    1
dtype: int64

Answer 3

try this:

In [58]: df['categories'].apply(pd.Series).stack().value_counts()
Out[58]:
Book          2
Fantasy       1
Electronic    1
Sci-Fi        1
Material      1
dtype: int64