Can I use pandas' pivot_table to aggregate over a column with missing values?

Question

Can I use pandas pivot_table to aggregate over a column with missing values and have those missing values included as separate category?

In:
df = pd.DataFrame({'a': pd.Series(['X', 'X', 'Y', 'Y', 'N', 'N'], dtype='category'), 
                   'b': pd.Series([None, None, 'd', 'd', 'd', 'd'], dtype='category')})

Out:
    a   b
0   X   NaN
1   X   NaN
2   Y   d
3   Y   d
4   N   d
5   N   d

In:
df.groupby('a')['b'].apply(lambda x: x.value_counts(dropna=False)).unstack(1)

Out:
    NaN d
a       
N   NaN 2.0
X   2.0 0.0
Y   NaN 2.0

Can I achieve the same result using pandas pivot_table? If yes than how? Thanks.

Answer 1

For some unknown reason, dtype="category" does not work with pivot_table() when counting NaN values. Casting them to regular strings enables regular pivot_table(aggfunc="size").

df.astype(str).pivot_table(index="a", columns="b", aggfunc="size")    

Result

b    d  nan
a          
N  2.0  NaN
X  NaN  2.0
Y  2.0  NaN

One can optionally do .fillna(0) to replace nans with 0s