python - 切片 pandas 层次索引的内部索引会引发关键错误

Question

我正在尝试对 pandas 分层索引数据框进行 sql 样式查询。 level(0) 索引是“Exercise”，level(1) 是“Date”日期时间。如果我为 level(o) 索引指定一个值，我可以很高兴地进行切片。

print gbed.loc['Bench', pd.to_datetime('2011-01-03')]

但是如果我尝试使用冒号来表示 level(0) 索引的“所有行”，则切片会失败，并在 level(1) 上出现键错误

# KeyError: 'the label [2011-01-03 00:00:00] is not in the [columns]'
print gbed.loc[:, pd.to_datetime('2011-01-03')]

Visualizing Pandas 一书表明，这种内部索引在某些情况下是可能的，但我无法弄清楚它何时/为何不起作用。

Selection is even possible in some cases from an “inner” level:
In [267]: data[:, 2]
Out[267]:
a 0.852965

摘自《Python 数据分析》第 147 页

想知道我在最后一种情况下是否错误地指定了切片？示例代码如下。

import pandas as pd
#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','Squat','Squat','Squat','Squat','Squat','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()
print gbed

#These two work: return rows for a specific exercise on 2011-01-03
# SELECT * WHERE Exercise = 'Bench' AND  Date = 2011-01-03
print gbed.loc['Bench', pd.to_datetime('2011-01-03')]
print gbed.loc['Squat', pd.to_datetime('2011-01-03')]

#I am trying to return all rows that have a dated of '2011-01-03'
# SELECT * WHERE Date = 2011-01-03
# KeyError: 'the label [2011-01-03 00:00:00] is not in the [columns]'
print gbed.loc[:, pd.to_datetime('2011-01-03')]

Answer 1

要通过MultiIndex进行选择，请使用DataFrame.xs或slicers ，这对于复杂的选择很有用:

print (gbed.xs('2011-01-03', level=1, axis=0))
          Weight
Exercise        
Bench        100
Squat        140

print (gbed.xs('2011-01-03', level=1, axis=0, drop_level=False))
                     Weight
Exercise Date              
Bench    2011-01-03     100
Squat    2011-01-03     140

idx = pd.IndexSlice
print (gbed.loc[idx[:, '2011-01-03'], :])
                     Weight
Exercise Date              
Bench    2011-01-03     100
Squat    2011-01-03     140

idx = pd.IndexSlice
print (gbed.loc[idx['Bench', '2011-01-03'], :])
                     Weight
Exercise Date              
Bench    2011-01-03     100