如何用pd.IndexSlice在双层MultiIndex数据框中同时切片行与列

问题背景 #

已有双层行MultiIndex的数据框，会用pd.IndexSlice做行切片，现在需要用同样方法对三层列MultiIndex进行筛选，目标是保留KIND=ACTIVE且DEPARTMENT=A2的列。

示例数据生成代码 #

import pandas as pd
import numpy as np

df = pd.DataFrame(data=np.random.randint(0, 10, size=(9, 5)))
# 构建行MultiIndex
list1 = ['2021-01-01','2022-02-01','2022-03-01']
list2 = ['PHOTO', 'QUE','TXR']
combinations = [(x, y) for x in list1 for y in list2]     
df.index = pd.MultiIndex.from_tuples(combinations, names = ["DATE","DB"])
# 构建列MultiIndex
list1c = [('AB30','ACTIVE','A2'),('CD55','ACTIVE','A1'),('ZT52','UNACTIVE','A2'),('MIKE','PENSIONER','A2'),('ZZ00001','ACTIVE','A1')]
df.columns = pd.MultiIndex.from_tuples(list1c, names = ["UserID","KIND","DEPARTMENT"])

完整的行+列切片代码 #

idx = pd.IndexSlice

###### 行切片 #######
date_start = '2021-01-01'
date_end   = '2021-02-01'
databases = ['PHOTO','QUE']
i_s = idx[date_start:date_end, databases]

###### 列切片 #######
# 筛选KIND=ACTIVE且DEPARTMENT=A2的列，UserID不限
i_c = idx[:, 'ACTIVE', 'A2']

# 同时应用行和列切片
result_df = df.loc[i_s, i_c]
print(result_df)

列切片逻辑说明 #

列MultiIndex的层级顺序是["UserID","KIND","DEPARTMENT"]，idx[:, 'ACTIVE', 'A2']的含义是：

• 第一个:：匹配所有UserID层级的内容
• 第二个'ACTIVE'：匹配KIND层级等于ACTIVE的项
• 第三个'A2'：匹配DEPARTMENT层级等于A2的项

如果需要更复杂的列筛选，可灵活调整：

• 筛选多个KIND值：i_c = idx[:, ['ACTIVE', 'UNACTIVE'], 'A2']
• 指定UserID范围+条件：i_c = idx[['AB30', 'MIKE'], 'ACTIVE', 'A2']

内容的提问来源于stack exchange，提问作者JFerro