pandas pivot_table returns empty dataframe

Question

I get an empty dataframe when I try to group values using the pivot_table. Let's first create some stupid data:

import pandas as pd
df = pd.DataFrame({"size":['large','middle','xsmall','large','middle','small'],
                   "color":['blue','blue','red','black','red','red']})

When I use:

df1 = df.pivot_table(index='size', aggfunc='count')

returns me what I expect. Now I would like to have a complete pivot table with the color as column:

df2 = df.pivot_table(index='size', aggfunc='count',columns='color')

But this results in an empty dataframe. Why? How can I get a simple pivot table which counts me the number of combinations? Thank you.

Answer 1

You need to use len as the aggfunc, like so

df.pivot_table(index='size', aggfunc=len, columns='color')

If you want to use count, here are the steps:

1. First add a frequency columns, like so:

   df['freq'] = df.groupby(['color', 'size'])['color'].transform('count')
   

2. Then create the pivot table using the frequency column:

   df.pivot_table(values='freq', index='size', aggfunc='count', columns='color')