Pandas - Handle NaN in Pivot Table with Categorical

Question

I'm creating multiple pivot tables using a Categorical dtype then merging them into one big pivot table / dataframe.

However, in some cases I get NaN when I perform the merge, and when I try to fillna(0), I get the following error: ValueError: fill value must be in categories

pv1 = pd.PivotTable(df, index='Category', values='Sales', aggfunc='sum')    
pv2 = pd.PivotTable(df, index='Category', values='Quantity', aggfunc='sum')    
chart = pv1.merge(pv2, on='Category', how='outer').fillna(0)

Actual Output:

 Category   Sales  Quantity
 Boxes      100    NaN
 Staples    20     10
 Paper      NaN    20

Desired Output:

 Category   Sales  Quantity
 Boxes      100    0
 Staples    20     10
 Paper      0      20

@SuperStew I haven't, but that might be the trick. I guess doing `df.fillna(0)` will try to fill the entire dataframe, not just `Sales` and `Quantity` — Walt Reed, Jul 12 '18 at 14:49
`df[['Sales','Quantity']].fillna(0)`. You just indicate the columns — W Stokvis, Jul 12 '18 at 14:51

score 1 · Answer 1 · answered Jul 12 '18 at 16:19

1

The most straightforward way to do this was to define the columns I want to do the fillna() on, then perform it only on those columns (basically exclude the categorical column.

fill_cols = ['Sales','Quantity'] df[fill_cols] = df[fill_cols].fillna(0)

answered Jul 12 '18 at 16:19

Walt Reed

1,336
2
17
26

score 0 · Answer 2 · answered Jul 12 '18 at 14:51

0

Perhaps you should try using fillna on the final output instead of in middle steps. This works perfectly:

In [120]: df
Out[120]: 
  Category  Sales  Quantity
0    Boxes  100.0       NaN
1  Staples   20.0      10.0
2    Paper    NaN      20.0

In [122]: df.fillna(0, inplace=True)

In [123]: df
Out[123]: 
  Category  Sales  Quantity
0    Boxes  100.0       0.0
1  Staples   20.0      10.0
2    Paper    0.0      20.0

answered Jul 12 '18 at 14:51

Ashish Acharya

3,349
1
16
25

I still wasn't able to get this to work. Was your `Category` a `categorical` dtype? – Walt Reed Jul 12 '18 at 15:01

Pandas - Handle NaN in Pivot Table with Categorical

2 Answers2