使用Pandas创建多级列的方法咨询（支持多数值列通用场景）

解决方案 #

要实现多级列结构的宽表转换，可使用pandas的多重索引堆叠+反堆叠操作实现，该方案天然支持多数值列的通用场景，无需针对新增数值列修改逻辑。

实现逻辑 #

我们只需要提前定义三类配置列，即可通用完成转换：

• 行索引列：最终保留在表格左侧作为行标识的字段，示例中为['mtask']
• 列层级字段：按从上层到下层的顺序排列的、要转为多级列的字段，示例中为['sub', 'task', 'type']
• 数值列：需要展示的数值类字段，示例中为['var']，可传入任意数量的同类型字段

完整可运行代码 #

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)),columns =['mtask', 'sub','task','type','var'])

# --------------------转换逻辑--------------------
# 配置项，按需修改即可
row_cols = ['mtask']          # 行索引列
col_level_cols = ['sub', 'task', 'type'] # 多级列的字段顺序（从上到下）
value_cols = ['var']          # 数值列，可添加多个如['var1','var2']

# 1. 构造多重索引
df_temp = df.set_index(row_cols + col_level_cols)[value_cols]
# 2. 反堆叠得到多级列宽表
res = df_temp.unstack(col_level_cols)

# 可选：调整列层级顺序，比如把数值列放到最底层，列层级变为 sub→task→type→数值列
# res = res.swaplevel(0, -1, axis=1).sort_index(axis=1)

效果说明 #

运行后得到的res就是符合要求的多级列结构表格：

• 默认列层级顺序为：数值列→sub→task→type
• 取消注释可选代码后，列层级顺序调整为：sub→task→type→数值列，可匹配大部分业务展示需求
• 新增数值列时，仅需要往value_cols中添加字段名即可，无需修改其他逻辑

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