如何在Pandas中拆分Excel合并单元格并转置为新列？解决代码报错

错误原因 #

你遇到的ValueError: Length of names must match number of levels in MultiIndex，是因为rename_axis中指定的7个索引名称，与stack(0)操作后生成的MultiIndex实际层级数不匹配。同时原代码未处理Excel合并单元格导致的空值，会引发后续数据异常。

修改后的代码 #

import pandas as pd

# 读取所有工作表，保留双层表头（年份+指标）
dfs = pd.read_excel('/Users/john/Desktop/Internal/Raw Files/Med/TVS/sample.xlsx', sheet_name=None, header=[0,4])

processed_dfs = []
for sheet_name, df in dfs.items():
    # 填充Date、WK列的合并单元格空值（向前填充，补全合并单元格内容）
    df.iloc[:, 0] = df.iloc[:, 0].ffill()
    df.iloc[:, 1] = df.iloc[:, 1].ffill()
    
    # 为列的双层索引命名，方便后续操作
    df.columns = df.columns.set_names(['Year', 'Metric'])
    
    # 将前5列设为索引，再把年份（列的第一层）转成行索引，实现结构扁平化
    stacked_df = df.set_index(df.columns[:5].tolist()).stack(0)
    
    # 重置索引并添加工作表名称列
    stacked_df = stacked_df.reset_index().assign(Sheet=sheet_name)
    
    # 重命名列名，匹配业务字段
    stacked_df = stacked_df.rename(columns={
        df.columns[0]: 'Date',
        df.columns[1]: 'WK',
        df.columns[2]: 'Brand',
        df.columns[3]: 'A_B',
        df.columns[4]: 'Foo_Foos',
        0: 'Value'
    })
    
    # 将Cur、Units转为单独列，最终得到扁平结构
    final_df = stacked_df.pivot(
        index=['Date', 'WK', 'Brand', 'A_B', 'Foo_Foos', 'Year', 'Sheet'],
        columns='Metric',
        values='Value'
    ).reset_index()
    
    processed_dfs.append(final_df)

# 合并所有工作表数据
df_2 = pd.concat(processed_dfs, ignore_index=True)

代码说明 #

1. 处理合并单元格空值：用ffill()向前填充Date和WK列的空值，补全合并单元格的内容。
2. 规范列索引命名：为双层列索引设置Year和Metric名称，避免后续操作混淆。
3. 结构扁平化：通过set_index+stack将年份维度从列转成行，打破合并单元格的层级结构。
4. 指标转单独列：用pivot将Cur、Units转为独立列，满足“合并单元格转为单独列”的需求。

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