Python将含合并单元格的多表头宽格式DataFrame转长格式

问题分析 #

你需要处理的是带有合并单元格双行表头的Excel文件：第一行是日期（如May,2022），第二行是每个日期对应的子列（status/type），目标是将这种宽格式转换为包含Task、Name、Date、status、type的长格式表格。之前的代码因为表头读取错误、列名处理不当导致KeyError 'Task'，且未正确拆分日期与子列的对应关系。

正确解决方案 #

步骤1：正确读取双行表头 #

使用header=[0,1]参数读取Excel，让Pandas识别前两行作为多级索引（MultiIndex）列名，保留日期和子列的对应关系：

import pandas as pd

# 替换为你的文件路径
path = "your_file.xlsx"
df = pd.read_excel(path, header=[0, 1])

此时列名会是类似[('Task', ''), ('Name', ''), ('May,2022', 'status'), ('May,2022', 'type'), ...]的多级结构。

步骤2：修正前两列的多级列名 #

前两列（Task、Name）没有子列，统一它们的多级列名避免后续处理出错：

# 重新设置前两列的多级列名
new_columns = []
for col in df.columns:
    if col[0] == 'Task':
        new_columns.append(('Task', 'Task'))
    elif col[0] == 'Name':
        new_columns.append(('Name', 'Name'))
    else:
        new_columns.append(col)
df.columns = new_columns

步骤3：转换为长格式表格 #

利用stack()方法将日期列（多级索引的第一层）“堆叠”为行，然后重置索引并整理列名：

# 设置Task和Name为索引，堆叠日期层
df_long = df.set_index([('Task', 'Task'), ('Name', 'Name')]).stack(level=0).reset_index()

# 重命名列名到目标格式
df_long.columns = ['Task', 'Name', 'Date', 'status', 'type']

另一种方法：使用wide_to_long #

如果更习惯用wide_to_long，可以先将多级列名转换为“日期_子列”的格式，再进行转换：

# 将多级列名转为单级，格式如"May,2022_status"
df.columns = [f"{col[0]}_{col[1]}" if col[1] != '' else col[0] for col in df.columns]

# 使用wide_to_long拆分日期和子列
df_long = pd.wide_to_long(
    df,
    stubnames=['status', 'type'],  # 要拆分的子列前缀
    i=['Task', 'Name'],  # 保持不变的标识符列
    j='Date',  # 拆分出的日期列名
    sep='_',  # 日期和子列的分隔符
    suffix='.+',  # 匹配日期的正则表达式
).reset_index()

错误原因说明 #

1. 表头读取错误：之前用header=[1,2]，实际表头是前两行（索引0和1），导致读取的列名中没有'Task'，触发KeyError。
2. 列名处理不当：直接用ffill()合并多级列名会破坏Task/Name与日期子列的对应关系，无法正确拆分出status和type列。

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