Pandas pd.pivot_table with NaN index

user_192296 0
Viewed 4646

When using pd.pivot_table(df, index=[col1], values=[val1]), the resulting pivot table drops results from df where col1 is NaN.

I can pre-fill the NaN with df[col1].fillna('#', inplace=True) but that's amending the data, which I do not want to do.

I can also do df2 = df.copy() before doing pre-fill to df2 as above, but my data is large, thus do not want to do extra copies unnecessarily.

Is there a way I can pivot a dataframe with NaN values in columns designated as index, without the resulting pivot dropping the NaN index and its aggregated values?

pandas python
1 Answers

At this time there is not a way to use NaN in the index for pivot tables. It will be silently dropped as you've experienced. I think your best option today is to use the fillna route to perform your pivot.

This has been asked on StackOverflow here if you need a little more color.

I also had trouble finding a clear answer; I suspected it being related to groupby dropping NaN by default. I opened an issue on Github and got confirmation. This is a known issue and even has a PR on it (which might be stale).

user_7091660
Related