python - 像常规数据框一样过滤 pandas hub_table 结果

Question

当我尝试过滤 Pandas 数据框时，如下面的示例所示，它工作正常:

print data[data['ProductCategory'].isin(['ProductA'])]

但是当我尝试对生成的数据透视表执行相同操作时:

pivot = pandas.pivot_table(
      data,index=['ProductCategory', 'Currency', 'Producer'], 
      values=['Price','Quantity','FxRate'],
      aggfunc={'Price': np.sum, 'Quantity': np.sum,'FxRate': np.mean})

print pivot[pivot['ProductCategory'].isin(['ProductA'])]

我收到一个关键错误:

File "pandas\index.pyx", line 134, in pandas.index.IndexEngine.get_loc (pandas\index.c:3838) File "pandas\index.pyx", line 154, in pandas.index.IndexEngine.get_loc (pandas\index.c:3718) File "pandas\hashtable.pyx", line 686, in pandas.hashtable.PyObjectHashTable.get_item (pandas\hashtable.c:12294) File "pandas\hashtable.pyx", line 694, in pandas.hashtable.PyObjectHashTable.get_item (pandas\hashtable.c:12245) KeyError: 'ProductCategory'

数据透视表的describe()给出:

        FxRate           Price        Quantity
count   48.00           48.00           48.00
mean     0.93    4,717,051.34   46,446,338.82
std      0.20   20,603,134.35  188,008,495.83
min      0.64  -16,088,043.02 -137,804,378.35
25%      0.73      -87,306.39           83.75
50%      1.00       41,198.26      682,025.97
75%      1.00    2,999,491.53    7,489,198.82
max      1.25  137,804,362.15  939,610,000.00

pivot.info() 给出以下输出:

 <class 'pandas.core.frame.DataFrame'>
MultiIndex: 48 entries, (ProductA, ProductB, ProductC) to (ProducerA, ProducerB, ProducerC)
Data columns (total 3 columns):
FxRate         48 non-null float64
Price          48 non-null float64
Quantity       48 non-null float64
dtypes: float64(3)

memory usage: 2.3+ KB
None

pivot.head() 给出了这个:

                                          FxRate      Price   Quantity  
ProductCategory Currency      Producer                                 
ProductA        EUR           ProducterA   1.00       0.90       1.10   
                              ProducterB   1.00       0.90       1.10   
                GBP           ProducterB   1.25       1.12       1.37   
ProductB        EUR           ProducterA   1.00       0.90       1.10   
                GBP           ProducterC   1.25       1.12       1.37

Answer 1

ProductCategory、Currency 和 Producer 现在是 groupby 操作后索引的一部分。尝试重置名为 pivot.reset_index(inplace=True) 的 DataFrame 索引，然后使用 loc 照常进行选择。

>>> pivot[pivot['ProductCategory'].isin(['ProductA'])]
  ProductCategory Currency    Producer  FxRate  Price  Quantity
0        ProductA      EUR  ProducterA    1.00   0.90      1.10
1        ProductA      EUR  ProducterB    1.00   0.90      1.10
2        ProductA      GBP  ProducterB    1.25   1.12      1.37

如果需要，您可以重置结果的索引。

或者，您可以在原始 pivot 上使用 loc，如下所示:

>>> pivot.loc['ProductA']
                     FxRate  Price  Quantity
Currency Producer                           
EUR      ProducterA    1.00   0.90      1.10
         ProducterB    1.00   0.90      1.10
GBP      ProducterB    1.25   1.12      1.37