python pandas:pivot_table使用nans静默删除索引 [英] python pandas: pivot_table silently drops indices with nans

问题描述

是否可以选择不删除带有"nan"的索引?我认为，从枢轴上静默删除这些行有时会给某人带来严重的痛苦.

Is there an option not drop the the indices with 'nan' in them? I think silently dropping these rows from the pivot will at some point cause someone serious pain.

import pandas
import numpy

a = [['a', 'b', 12, 12, 12], ['a', numpy.nan, 12.3, 233., 12], ['b', 'a', 123.23, 123, 1], ['a', 'b', 1, 1, 1.]]

df = pandas.DataFrame(a, columns=['a', 'b', 'c', 'd', 'e'])

df_pivot = df.pivot_table(rows=['a', 'b'], values=['c', 'd', 'e'], aggfunc=sum)
print(df)
print(df_pivot)

输出:

   a    b       c    d   e
0  a    b   12.00   12  12
1  a  NaN   12.30  233  12
2  b    a  123.23  123   1
3  a    b    1.00    1   1
          c    d   e
a b                 
a b   13.00   13  13
b a  123.23  123   1

推荐答案

当前不支持此功能，请参见此问题以获取增强功能:

This is currently not supported, see this issue for the enhancement: https://github.com/pydata/pandas/issues/3729.

一种解决方法，以使用虚拟对象，透视图和替换填充索引

Workaround to fill the index with a dummy, pivot, and replace

In [28]: df = df.reset_index()

In [29]: df['b'] = df['b'].fillna('dummy')

In [30]: df['dummy'] = np.nan

In [31]: df
Out[31]: 
   a      b       c    d   e  dummy
0  a      b   12.00   12  12    NaN
1  a  dummy   12.30  233  12    NaN
2  b      a  123.23  123   1    NaN
3  a      b    1.00    1   1    NaN

In [32]: df.pivot_table(rows=['a', 'b'], values=['c', 'd', 'e'], aggfunc=sum)
Out[32]: 
              c    d   e
a b                     
a b       13.00   13  13
  dummy   12.30  233  12
b a      123.23  123   1

In [33]: df.pivot_table(rows=['a', 'b'], values=['c', 'd', 'e'], aggfunc=sum).reset_index().replace('dummy',np.nan).set_index(['a','b'])
Out[33]: 
            c    d   e
a b                   
a b     13.00   13  13
  NaN   12.30  233  12
b a    123.23  123   1

这篇关于python pandas:pivot_table使用nans静默删除索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！