python-3.x - 选择作为 MultiIndex 一部分的两个 DatetimeIndex 之间的行

Question

鉴于此数据框

import pandas as pd

idx_str = [
    'blue',
    'blue',
    'blue',
    'blue',
    'red',
    'red',
    'red',
    'red',
]
idx_date = [
    pd.Timestamp('2010-01-01'),
    pd.Timestamp('2010-01-05'),
    pd.Timestamp('2010-01-06'),
    pd.Timestamp('2010-01-10'),
    pd.Timestamp('2010-01-01'),
    pd.Timestamp('2010-01-05'),
    pd.Timestamp('2010-01-06'),
    pd.Timestamp('2010-01-10'),
]
data = [1,2,3,4,5,6,7,8]

df = pd.DataFrame(
    data=zip(idx_str, idx_date, data),
    columns=['idx_str', 'idx_dates', 'data'],
)
df.set_index([idx_str, idx_date], drop=True, inplace=True)

print(df)
                 data
blue 2010-01-01     1
     2010-01-05     2
     2010-01-06     3
     2010-01-10     4
red  2010-01-01     5
     2010-01-05     6
     2010-01-06     7
     2010-01-10     8

我想要查询 datetimeIndex 级别的两个日期之间的行，并且仅限于第一级别的颜色 red

所以我想要这样:

                  data
red   2010-01-02     5
      2010-01-03     6

由于多重索引，我无法使用我找到的解决方案，例如

df.query("'2010-01-02' <= index <= '2010-01-09'")

df.loc['2010-01-02':'2010-01-09']

可能第一次不太清楚，所以我编辑了我想要的最终结果

Answer 1

第一个切片(slice(None), slice(start_date, end_date))分别对应于应用于二级索引的切片。通过将此切片传递给 .loc[]，您可以选择满足指定日期范围条件的数据。

start_date = pd.Timestamp('2010-01-02')
end_date = pd.Timestamp('2010-01-09')
selected_data = df.loc[(slice(None), slice(start_date, end_date)), :]
print(selected_data)

输出

                idx_str  idx_dates  data                                                      
blue 2010-01-05    blue 2010-01-05     2                                                      
     2010-01-06    blue 2010-01-06     3                                                      
red  2010-01-05     red 2010-01-05     6                                                      
     2010-01-06     red 2010-01-06     7 

---

Question: I wanted to filter both level of the MultiIndex so the answer also filter the color, why the second , slicing at the end -> , :]

Answer: You can get 'red' with below code line.The last part is for columns filtering. A colon at the end means extract all columns.

selected_data = df.loc[(slice('red','red'), slice(start_date, end_date)), 'idx_dates':'data']

输出

                idx_dates  data
red 2010-01-05 2010-01-05     6
    2010-01-06 2010-01-06     7

有很多 pandas 索引选项。请引用pandas官方文档。

MultiIndex / advanced indexing