Pivottable (2 levels) NaN not showing

user_9093700 0
Viewed 26

I have a dataframe, which I create from another dataframe:

df_exp_fix = df_aus[df_aus['CATEGORY'] == "Fixkosten"]

When I display df_exp_fix i get:

MONTHYEAR Comment LEVEL2 LEVEL3 AMOUNT
0 01.01.2022 bla Lebensmittel Metro 10
1 01.02.2022 bla Taschengeld NaN 10

Next I do

table_exp_fix = df_exp_fix.pivot_table(values='AMOUNT', index=['LEVEL2', 'LEVEL3'], 
                               columns=['MONTHYEAR'], aggfunc=np.sum, margins=True)

But the Line "Taschengeld" is not displayed? And with that all Sums are not the same if I only aggregate on LEVEL2

I played around with dropna.... and other parameters but it doesn't get displayed

Any help?

pandas pivot-table
1 Answers

This is, because you have a NaN value in your index columns.

Try:

df_exp_fix['LEVEL3'].fillna('Unknown', inplace=True)

If you also have (possible) NaN's in LEVEL2, replace these as well.

Also, to remove NaN values from your output, add fill_value parameter like this:

table_exp_fix = df.pivot_table(values='AMOUNT', index=['LEVEL2', 'LEVEL3'], 
                           columns=['MONTHYEAR'], aggfunc=np.sum, margins=True, fill_value=0)

Output:

                MONTHYEAR   01.01.2022  01.02.2022  All
LEVEL2          LEVEL3          
Lebensmittel    Metro       10          0           10
Taschengeld     Unknown     0           10          10
All                         10          10          20
user_71944740
Related