How to count categorical values including zero occurrence? I want to count number of code by month.
This is my example dataframe.

        id    month  code
0     sally    0  s_A
1     sally    0    s_B
2     sally    0   s_C
3     sally    0   s_D
4     sally    0    s_E
5     sally    0   s_A
6     sally    0    s_A
7     sally    0   s_B
8     sally    0   s_C
9     sally    0   s_A


I transformed to this Series using count().

df.groupby(['id', 'code', 'month']).m.count()

id      code   month  count
sally  s_A      0    12
                1    10
                2     3
                7    15


But, I want to include zero occurrence, like this.

id      code   month  count
sally  s_A      0    12
                1    10
                2     3
                3    0
                4    0
                5    0
                6    0
                7    15
                8    0
                9    0
                10   0
                11   0
Based on the short example DataFrame you provided, this block of code will include all of the months. It is based on using the Series.reindex method and creating a new MultiIndex with the additional values for the months:

import pandas as pd
# Load example data into DataFrame
df = pd.read_table("categorical_data.txt", delim_whitespace=True)

# Transform to a count
count = df.groupby(['id', 'code', 'month']).month.count()

# Re-create a new array of levels, now including all 12 months
levels = [count.index.levels[0].values, count.index.levels[1].values, range(12)]
new_index = pd.MultiIndex.from_product(levels, names=count.index.names)

# Reindex the count and fill empty values with zero (NaN by default)
count = count.reindex(new_index, fill_value=0)
print(count)


Printing the result, I get something like this (only showing the first entry for sally/s_A):

id     code  month
sally  s_A   0        4
             1        0
             2        0
             3        0
             4        0
             5        0
             6        0
             7        0
             8        0
             9        0
             10       0
             11       0