Is there a way to keep nan values present when using pivot table in Pandas?

user_10281547 0
Viewed 278

I've run into an issue with pandas pivot_table function and how it deals with NaN values. An example of how pandas deals with NaN's is below:

import pandas as pd

dataframe = pd.DataFrame([[1, 1, 23501, 1000], 
                          [2, 1, 23501, 1000], 
                          [3, None, None, None], 
                          [4, 1, 23501, 1000]], 
                          columns=['A','B' , 
                                   'C', 'D'])

dataframe = dataframe.reset_index().pivot_table(index = ['index', 'A'], columns = ['B'])
print(dataframe)

Result:

                      C            D
B                     1.0          1.0
index A                        
0     1           23501.0       1000.0
1     2           23501.0       1000.0
3     4           23501.0       1000.0

The problem with this approach is that pivot_table completely removes the row that's filled with NaN values. I've looked to see that pivot_table has a dropna flag, but the result produced when used is strange and still excludes the NaN values of index 2.

Result with dropna:

                      C            D
B                     1.0          1.0
index A                        
0     1           23501.0       1000.0
      2               NaN          NaN
      4               NaN          NaN
1     1               NaN          NaN
      2           23501.0       1000.0
      4               NaN          NaN
3     1               NaN          NaN
      2               NaN          NaN
      4           23501.0       1000.0

I looked at this github page: https://github.com/pandas-dev/pandas/issues/18030 and when trying one of the solutions, which modified for me (I think) is df.groupby(["A", "B"]).agg('first').unstack([1]) it gives me the same result as just doing a pivot table.

Is there a possible solution using other pandas functions to keep the nan values present when pivoting to create something like:

                      C            D
B                     1.0          1.0
index A                         
0     1           23501.0       1000.0
1     2           23501.0       1000.0
2     3           NaN              NaN
3     4           23501.0       1000.0

Thanks for the help

pandas dataframe python
1 Answers

Append additional levels to the index and then unstack 'B'. This will create a NaN level that we don't want so we'll filter that out.

If you want, you can use the 'Int64' dtype for 'B' which will keep the level values the np.int64 1 as opposed to the float.

df.index.name = 'index'  # level will have a name now
# df['B'] = df['B'].astype('Int64') # prevents upcasting
df = df.set_index(['A', 'B'], append=True).unstack(-1)
df = df.loc[:, df.columns.get_level_values('B').notnull()]

               C       D
B            1.0     1.0
index A                 
0     1  23501.0  1000.0
1     2  23501.0  1000.0
2     3      NaN     NaN
3     4  23501.0  1000.0
user_43333590
Related