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

ab
x  112131
y  112230