如何将DataFrame pivot后的多级列重命名为单级列匹配目标格式

问题描述 #

现有DataFrame df_res，包含subjid、pipeline、label_id字段，以及volume_(mm^3)、mean_jacobian、stdev_jacobian三个指标列。需要将每个label_id对应的指标转为独立列（如label0_volume_(mm^3)），最终每行对应一个subjid-pipeline组合。使用df.pivot()后得到多级列结构的结果，需重命名合并列以匹配目标格式。

原始数据示例 #

df_res = 
       subjid   pipeline   label_id    volume_(mm^3)    mean_jacobian    stdev_jacobian
0   100007_t0      Rigid          0       10100000.0             1.11             0.078
1   100007_t0      Rigid          1         315439.0          1.04635             0.283
2   100007_t0      Rigid          2         624165.0         0.968231             0.192
3   100007_t0      Rigid          3         515589.0           1.1273             0.229
4   100007_t1      Rigid          0       10084600.0           1.0935             0.033
5   100007_t1      Rigid          1         320533.0           1.0457             0.277
6   100007_t1      Rigid          2         621393.0            0.957             0.193
7   100007_t1      Rigid          3         507840.0          1.00573             0.232

目标数据格式 #

df_goal = 
subjid      pipeline    label0_volume_(mm^3)   ...  label3_volume_(mm^3)   ...  label3_mean_jacobian
100007_t0      Rigid              10100000.0                      515589.0          1.1273
100007_t1      Rigid              10084600.0                      507840.0         1.00573

当前pivot结果 #

>>> df_res_pivot = df_res.pivot(index="subjid", columns="label_id", values=["volume_(mm^3)", "mean_jacobian", "stdev_jacobian"])

df_res_pivot = 
          volume_(mm^3)                      ... stdev_jacobian                    
label_id              0         1         2  ...              1         2         3
subjid                                       ...                                   
100007_t0    10100000.0  315439.0  624165.0  ...       0.289318  0.192214  0.229341
100007_t1    10084600.0  320533.0  621393.0  ...       0.277735  0.193940  0.232486

[2 rows x 12 columns]

解决方案 #

步骤1：修正pivot索引 #

由于目标每行对应subjid-pipeline组合，pivot时需将这两个字段同时设为索引，避免丢失pipeline列：

df_res_pivot = df_res.pivot(index=["subjid", "pipeline"], columns="label_id", values=["volume_(mm^3)", "mean_jacobian", "stdev_jacobian"])

步骤2：重命名多级列 #

遍历多级列，将(指标名, label_id)的组合转换为label{label_id}_{指标名}的格式：

df_res_pivot.columns = [f"label{label}_{metric}" for metric, label in df_res_pivot.columns]

步骤3：重置索引 #

将subjid和pipeline从索引转回普通列，匹配目标格式：

df_goal = df_res_pivot.reset_index()

完整代码示例 #

import pandas as pd

# 模拟原始数据
data = [
    ["100007_t0", "Rigid", 0, 10100000.0, 1.11, 0.078],
    ["100007_t0", "Rigid", 1, 315439.0, 1.04635, 0.283],
    ["100007_t0", "Rigid", 2, 624165.0, 0.968231, 0.192],
    ["100007_t0", "Rigid", 3, 515589.0, 1.1273, 0.229],
    ["100007_t1", "Rigid", 0, 10084600.0, 1.0935, 0.033],
    ["100007_t1", "Rigid", 1, 320533.0, 1.0457, 0.277],
    ["100007_t1", "Rigid", 2, 621393.0, 0.957, 0.193],
    ["100007_t1", "Rigid", 3, 507840.0, 1.00573, 0.232]
]
df_res = pd.DataFrame(data, columns=["subjid", "pipeline", "label_id", "volume_(mm^3)", "mean_jacobian", "stdev_jacobian"])

# 执行pivot（包含subjid和pipeline作为索引）
df_res_pivot = df_res.pivot(index=["subjid", "pipeline"], columns="label_id", values=["volume_(mm^3)", "mean_jacobian", "stdev_jacobian"])

# 重命名多级列
df_res_pivot.columns = [f"label{label}_{metric}" for metric, label in df_res_pivot.columns]

# 重置索引得到目标格式
df_goal = df_res_pivot.reset_index()

print(df_goal)

最终输出结果 #

subjid pipeline  label0_volume_(mm^3)  label1_volume_(mm^3)  label2_volume_(mm^3)  label3_volume_(mm^3)  label0_mean_jacobian  label1_mean_jacobian  label2_mean_jacobian  label3_mean_jacobian  label0_stdev_jacobian  label1_stdev_jacobian  label2_stdev_jacobian  label3_stdev_jacobian
0  100007_t0    Rigid            10100000.0              315439.0              624165.0              515589.0                 1.110               1.04635              0.968231                1.1273                   0.078                   0.283                   0.192                   0.229
1  100007_t1    Rigid            10084600.0              320533.0              621393.0              507840.0                 1.0935               1.04570              0.957000                1.00573                   0.033                   0.277                   0.193                   0.232

内容的提问来源于stack exchange，提问作者florence-y