Pandas Category Sub-group 0 Counts

I'm new to Pandas, and trying to generate a table of subgroup counts maintaining the category order, and showing zero counts. It's a simple category with 4 options. Without groupi

Solution 1:

You can create a MultiIndex from all combinations of values of two grouping columns and reindex the groupby result with this multiindex. Then fill NaN values with zeros.

import pandas as pd
# example data
df = pd.DataFrame({'a':list('xxxyyy'), 'b':[1,2,3,1,2,2]})
#    a  b
# 0  x  1
# 1  x  2
# 2  x  3
# 3  y  1
# 4  y  2
# 5  y  2

multi_index = pd.MultiIndex.from_product([df.a.unique(), df.b.unique()], 
                                         names=['a', 'b'])\
                           .sort_values()
df.groupby(['a','b']).size().reindex(multi_index).fillna(0).astype(int)

This produces

a  b
x  1    1
   2    1
   3    1
y  1    1
   2    2
   3    0