如何在pandas中结合布尔索引器和多索引器？

Question

我有一个多索引数据帧，我希望根据索引值和布尔条件提取一个子集。我希望使用多索引键和布尔索引器覆盖特定新值的值以选择要修改的记录。在

import pandas as pd 
import numpy as np

years        = [1994,1995,1996]
householdIDs = [ id for id in range(1,100) ]

midx = pd.MultiIndex.from_product( [years, householdIDs], names = ['Year', 'HouseholdID'] )

householdIncomes = np.random.randint( 10000,100000, size = len(years)*len(householdIDs) )
householdSize    = np.random.randint( 1,5, size = len(years)*len(householdIDs) )
df = pd.DataFrame( {'HouseholdIncome':householdIncomes, 'HouseholdSize':householdSize}, index = midx ) 
df.sort_index(inplace = True)

示例数据如下…

^{pr2}$

我能够使用索引和列标签成功地查询数据帧。在

这个例子给出了1996年住户3的户主规模

   df.loc[  (1996,3 ) , 'HouseholdSize' ]
=> 1

但是，我无法将布尔选择与多索引查询相结合…

pandas docs on Multi-indexing表示有一种方法可以将布尔索引和多重索引结合起来，并给出了一个示例。。。在

In [52]: idx = pd.IndexSlice
In [56]: mask = dfmi[('a','foo')]>200

In [57]: dfmi.loc[idx[mask,:,['C1','C3']],idx[:,'foo']]
Out[57]: 
lvl0           a    b
lvl1         foo  foo
A3 B0 C1 D1  204  206
      C3 D0  216  218
         D1  220  222
   B1 C1 D0  232  234
         D1  236  238
      C3 D0  248  250
         D1  252  254

…我似乎无法在我的数据帧上复制

    idx = pd.IndexSlice
    housholdSizeAbove2 = ( df.HouseholdSize > 2 )
    df.loc[ idx[ housholdSizeAbove2, 1996, :] , 'HouseholdSize' ] 
Traceback (most recent call last):
  File "python", line 1, in <module>
KeyError: 'MultiIndex Slicing requires the index to be fully lexsorted tuple len (3), lexsort depth (2)'

在本例中，我希望看到1996年住户规模大于2的所有家庭

Answer 1

Pandas.query()在这种情况下应该有效：

df.query("Year == 1996 and HouseholdID > 2")

演示：

^{pr2}$

更新：

Is there a way to select a specific column?

In [333]: df.loc[df.eval("Year == 1996 and HouseholdID > 2"), 'HouseholdIncome']
Out[333]:
Year  HouseholdID
1996  3              28664
      4              11057
      5              36321
      6              89469
      7              35711
      8              85741
      9              34758
      10             56085
      11             32275
      12             77096
                     ...
      90             40276
      91             10594
      92             61080
      93             65334
      94             21477
      95             83112
      96             25627
      97             24830
      98             85693
      99             84653
Name: HouseholdIncome, dtype: int32

and ultimately I want to overwrite the data on the dataframe.

In [331]: df.loc[df.eval("Year == 1996 and HouseholdID > 2"), 'HouseholdSize'] *= 10

In [332]: df.loc[df.eval("Year == 1996 and HouseholdID > 2")]
Out[332]:
                  HouseholdIncome  HouseholdSize
Year HouseholdID
1996 3                      28664             40
     4                      11057             10
     5                      36321             20
     6                      89469             40
     7                      35711             20
     8                      85741             10
     9                      34758             30
     10                     56085             20
     11                     32275             40
     12                     77096             40
...                           ...            ...
     90                     40276             40
     91                     10594             20
     92                     61080             40
     93                     65334             20
     94                     21477             40
     95                     83112             40
     96                     25627             20
     97                     24830             40
     98                     85693             10
     99                     84653             40

[97 rows x 2 columns]

更新2:

I want to pass a variable year instead of a specific value. Is there a cleaner way to do it than Year == " + str(year) + " and HouseholdID > " + str(householdSize) ?

In [5]: year = 1996

In [6]: household_ids = [1, 2, 98, 99]

In [7]: df.loc[df.eval("Year == @year and HouseholdID in @household_ids")]
Out[7]:
                  HouseholdIncome  HouseholdSize
Year HouseholdID
1996 1                      42217              1
     2                      66009              3
     98                     33121              4
     99                     45489              3