如何对多层表头DataFrame执行melt与unpivot操作？

原始数据与初始DataFrame构建 #

重构后的数据字典 #

data = {
    "id": {
        0: "month",
        1: "11/30/2021",
        2: "12/31/2021",
        3: "1/31/2022",
        4: "2/28/2022",
        5: "3/31/2022",
    },
    "A48": {0: "storage", 1: "0", 2: "29", 3: "35", 4: "33", 5: "30"},
    "A48.1": {0: "use", 1: "0", 2: "1", 3: "0", 4: "0", 5: "0"},
    "A62": {0: "direct", 1: "0", 2: "0", 3: "2", 4: "3", 5: "2"},
    "A62.1": {0: "storage", 1: "0", 2: "57", 3: "69", 4: "65", 5: "59"},
    "A62.2": {0: "use", 1: "0", 2: "1", 3: "0", 4: "0", 5: "0"},
}

生成多层表头DataFrame的代码 #

import pandas as pd

dfc = pd.DataFrame.from_dict(data)
dfc.columns = pd.MultiIndex.from_arrays([dfc.columns, dfc.iloc[0]])
dfc = dfc.iloc[1:].reset_index(drop=True)

当前DataFrame结构 #

id     A48 A48.1    A62   A62.1 A62.2
        month storage   use direct storage   use
0  11/30/2021       0     0      0       0     0
1  12/31/2021      29     1      0      57     1
2   1/31/2022      35     0      2      69     0
3   2/28/2022      33     0      3      65     0
4   3/31/2022      30     0      2      59     0

转换为目标结构的代码 #

通过melt结合字符串处理、透视表操作实现结构转换，代码如下：

# 提取month列作为基准列
month_col = dfc.pop(('id', 'month')).rename('month')

# 对剩余列执行melt操作，拆分出id和类型字段
melted = dfc.melt(var_name=['id', 'type'], value_name='value', ignore_index=False)

# 清理id字段，去除后缀（如A48.1转为A48）
melted['id'] = melted['id'].str.split('.').str[0]

# 透视表重塑为目标列结构
result = melted.pivot_table(index=[month_col, 'id'], columns='type', values='value', fill_value=pd.NA).reset_index()

# 调整列顺序匹配目标结构
result = result[['month', 'id', 'direct', 'storage', 'use']]

# 可选：将数值列转为整数类型
for col in ['direct', 'storage', 'use']:
    result[col] = pd.to_numeric(result[col], errors='coerce')

目标结果 #

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