在 Pandas 数据帧上使用 'slicer' 和 'where' 等效项的 Pythonic 方式

Question

我有一个分层索引 Pandas 数据框。因此，我通常使用 pd.IndexSlice 对索引进行切片。我正在尝试返回已被 IndexSliced 并且还根据列中的值选择的数据帧的片段。

我可以用两行代码做到这一点，但看起来很恶心:

foo = (gbed.loc[idx['Squat':'Squat Tempo', :], :])
print foo[foo['Weight'] > 120]

不过，我想把它写成一行，但它会引发错误

"ValueError: operands could not be broadcast together with shapes (2,) (12,)"

print (gbed.loc[idx['Squat':'Squat Tempo', :] & gbed['Weight']>100, :])

有没有办法在一行中指定这个查询？

下面的完整示例代码:

#make an index with a handful of duplicate dates
dates1 = pd.date_range('1/1/2011', periods=8, freq='D')
dates2 = pd.date_range('1/1/2011', periods=4, freq='D')
dates = dates1.append(dates2)

ex = ['Squat','Squat','Squat Chains','Squat Chains','Squat Pause','SquatPause','Squat Pause','Squat Tempo','Bench','Bench','Bench','Bench',]
wt = [100,120,140,150,150,140,160,172,90,90,100,110]
cols = {'Exercise': ex, 'Weight': wt, 'Date': dates}

sf = pd.DataFrame(cols)

gbed = sf.groupby(['Exercise','Date']).max().sortlevel()
print gbed

idx = pd.IndexSlice

print ("\nall types of squats over 120kg")
#ValueError: operands could not be broadcast together with shapes (2,) (12,) 
#print (gbed.loc[idx['Squat':'Squat Tempo', :] & gbed['Weight']>100, :])

foo = (gbed.loc[idx['Squat':'Squat Tempo', :], :])
print foo[foo['Weight'] > 120]

Answer 1

尝试这个:

In [110]: gbed.query("Weight > 100 and 'Squat' <= Exercise <= 'Squat Tempo'")
Out[110]:
                         Weight
Exercise     Date
Squat        2011-01-02     120
Squat Chains 2011-01-03     140
             2011-01-04     150
Squat Pause  2011-01-05     150
             2011-01-07     160
Squat Tempo  2011-01-08     172

要么:

In [108]: gbed.loc[idx['Squat':'Squat Tempo', :], :].query("Weight > 100")
Out[108]:
                         Weight
Exercise     Date
Squat        2011-01-02     120
Squat Chains 2011-01-03     140
             2011-01-04     150
Squat Pause  2011-01-05     150
             2011-01-07     160
Squat Tempo  2011-01-08     172