How to include unobserved values of categorical variables in Pandas value_counts

In Pandas, calling value_counts on a Categorical series will make sure that each possible value gets a count even when that count is zero, all of which is subtle, lightly documented, and maybe seldom cared-about, but hold on tight. We're going down a rabbit hole.

Let's say I define a DataFrame with two Categorical columns like this:

import pandas as pd
abc_categorical = pd.CategoricalDtype(['a', 'b', 'c'], ordered=True)
size_categorical = pd.CategoricalDtype(['s', 'm', 'l'], ordered=True)

df = pd.DataFrame({
        'a':     pd.Categorical(list('aaabababababa'), dtype=abc_categorical),
        'tsize': pd.Categorical(list('smmssmlmlslsl'), dtype=size_categorical),
    }
)

	a	tsize
0	a	s
1	a	m
2	a	m
3	b	s
4	a	s
5	b	m
6	a	l
7	b	m
8	a	l
9	b	s
10	a	l
11	b	s
12	a	l

Series.value_counts

I have no rows where the 'a' column has the value 'c', so Series.value_counts gives us a zero count for it.

df.a.value_counts(sort=False)

a    8
b    5
c    0
Name: a, dtype: int64

Because we bothered to define a Categorical, Pandas knows that the column 'a' could take on the value 'c', but that never occurs in the data, so we get a 0. So far, so good.

DataFrame.value_counts

However, if I call DataFrame.value_counts to count values of both columns in combination, I don't get a zero either for the 'b' & 'l' combination, of which there are zero, and nothing for 'c'.

df.value_counts(sort=False)

a  tsize
a  s        2
   m        2
   l        4
b  s        3
   m        2
dtype: int64

Bummer!

Crosstab?

The pandas.crosstab function does a little better, giving a zero count for 'b' & 'l', but still leaves out the 'c' values.

pd.crosstab(df.a, df.tsize)

tsize   s   m   l
a
    a   2   2   4
    b   3   2   0

Expected results

I think, value_counts should return something like this:

            count
a   tsize
a   s       2
    m       2
    l       4
b   s       3
    m       2
    l       0
c   s       0
    m       0
    l       0

DataFrame.value_counts should do like Series.value_counts, or at least provide an option to do so, maybe "value_counts(include_zeros=True)". For that matter, crosstab should do likewise.

My actual question

My question: Is there a concise idiomatic way to get Pandas to do the counts I'm looking for here including the zeros?

Note: asked in context of Pandas 2.0.1

Yep, DataFrame.groupby respects Categorical!

Groupby

counts = df.groupby(["a", "tsize"]).size()
counts

DataFrameGroupBy.size returns the number of rows in each group as a Series whose index is the cartesian product of the two categorical types.

a  tsize
a  s        2
   m        2
   l        4
b  s        3
   m        2
   l        0
c  s        0
   m        0
   l        0
dtype: int64

Awesome! All the counts and the zeros. Also, note that the tshirt sizes are in proper small, medium, large order rather than alphabetical. This is thanks to another smart bit of Pandas kit.

CategoricalIndex

The way the index prints itself makes it look like an array of tuples of strings, but that's not what's under the hood.

counts.index

MultiIndex([('a', 'l'),
            ('a', 'm'),
            ('a', 's'),
            ('b', 'l'),
            ('b', 'm'),
            ('b', 's'),
            ('c', 'l'),
            ('c', 'm'),
            ('c', 's')],
           names=['a', 'tsize'])

Pandas provides a CategoricalIndex and we can see them at work if we drill into the levels of the MultiIndex.

counts.index.get_level_values('a')

CategoricalIndex(['a', 'a', 'a', 'b', 'b', 'b', 'c', 'c', 'c'], categories=['a', 'b', 'c'], ordered=True, dtype='category', name='a')

counts.index.get_level_values('tsize')

CategoricalIndex(['s', 'm', 'l', 's', 'm', 'l', 's', 'm', 'l'], categories=['s', 'm', 'l'], ordered=True, dtype='category', name='tsize')

Pivot table

For completeness, pivot_table also respects Categoricals and gives the same results as groupby.

pd.pivot_table(df, index=["a", "tsize"], aggfunc='size')

Observed option

What if you don't want the zeros? Both groupby and pivot_table accept an 'observed' parameter that defaults to False. If observed is set to True, we only get observed values for categorical groupers, replicating the behavior of value_counts.

Nice!