如何在Pandas pivot_table操作中设置多级列的层级顺序

解决方案 #

核心思路是在执行reset_index之前先调整多级列的层级顺序，避免索引列转普通列后引入多余的空层级。你可以直接用pandas内置的reorder_levels方法重排列层级，比转置+交换层级的写法更简洁直观。

完整实现代码如下：

import pandas as pd

task=['Task',"Task","Task","Task","Task","Task",'Task','Task',"Task","Task","Task","Task","Task",'Task','Task',"Task"]
ba=['SA','SA','SA','SA','SA','SA','SA','SA','SB','SB','SB','SB','SB','SB','SB','SB']
bb=['C1','C1','C2','C2','C1','C1','C2','C2','C1','C1','C2','C2','C1','C1','C2','C2']

nn=['T1','T1','T1','T1','T2','T2','T2','T2','T1','T1','T1','T1','T2','T2','T2','T2']
val=[0.244130039,0.124959401,0.212280307,0.111595529,0.162715589,0.097576324,0.219837052,0.138536738,0.118780642,0.047991315,0.092171826,0.046345554,0.170150394,0.110773621,0.076100716,0.042808913,]

df = pd.DataFrame(list(zip(task,ba,bb,nn,val,val,val)),columns =['mtask', 'sub','task','type','var','var2','var3'])
vars=['var','var2','var3']
idx_label=['mtask', 'sub']
stack_level=['task','type']

# 生成透视表，先不执行reset_index
df_pv = df.pivot_table(values=vars, index=idx_label, columns=stack_level)
# 重排列层级：原层级顺序为[变量名, task, type]，调整为[task, type, 变量名]
# 可以传层级名称，也可以传层级位置[1,2,0]
df_pv = df_pv.reorder_levels(['task', 'type', None], axis=1)
# 调整完成后再重置索引
df_pv = df_pv.reset_index()
# 展平列名，自动过滤空层级，索引列保持原名
df_pv.columns = df_pv.columns.map(lambda x: '_'.join([str(i) for i in x if i]))

print(df_pv.columns.tolist())
# 输出的列名格式为：['mtask', 'sub', 'C1_T1_var', 'C1_T1_var2', 'C1_T1_var3', 'C1_T2_var', ...] 完全符合要求，无需额外重命名

原方法问题说明 #

你之前的操作是先reset_index把索引列转为普通列，再调整层级顺序，此时索引列对应的多级列上层都是空值，调整顺序后空值跑到最前面，拼接就会出现__mtask这类多余下划线的列名。调整操作顺序后可以直接规避这个问题。

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