How to count categorical values including zero occurrence?

Question

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

Answer 1

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

Answer 2

What you want to do is exactly the default behavior of the category type.

Convert your month value to the type category declaring all months (it has a somewhat weird interface to create a categorical type)

df.month= dd.month.astype(pd.api.types.CategoricalDtype(categories=range(12)))
df.month.value_counts()

will give you:

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

obs: you your column has all the objects of the category, it has a simpler interface: df.month.astype('category').