使用Pandas转置前3行，将三层表头Excel数据规整为结构化表格

需要用Pandas处理带三层列表头的Excel工作表：

• 第一层为合并单元格形式的年份
• 第二层为合并单元格形式的月份
• 第三层为交替出现的Rent与Other字段

原始宽表结构（示例）：

已完成年份和月份的前向填充，得到如下结构：

目标是转换为结构化表格，每行对应唯一年月组合：

步骤1：构建多层列索引 #

假设已读取Excel数据并完成年份、月份的前向填充，将前3行设置为多层列索引，再删除表头行保留数据：

# 假设df是读取后的DataFrame
df.columns = pd.MultiIndex.from_arrays(df.iloc[:3].values)
df = df.iloc[3:]

步骤2：宽表转长表 #

通过stack方法将年份、月份两层列索引转为行索引，再重置索引并整理列名：

# 堆叠前两层索引，保留第三层作为数据类型列
df_long = df.stack(level=[0,1]).reset_index()
# 重命名列
df_long.columns = ['Year', 'Month', 'Type', 'Value']

步骤3：透视生成目标格式 #

将Type列的Rent和Other转为独立列，调整列顺序：

final_df = df_long.pivot(index=['Year', 'Month'], columns='Type', values='Value').reset_index()
# 按目标顺序排列列
final_df = final_df[['Year', 'Month', 'Rent', 'Other']]

完整代码示例 #

import pandas as pd

# 读取Excel文件，header=None表示不自动识别表头
df = pd.read_excel('your_file.xlsx', header=None)

# 前向填充年份和月份的空值
df.iloc[0] = df.iloc[0].ffill()
df.iloc[1] = df.iloc[1].ffill()

# 设置多层列索引并清理表头行
df.columns = pd.MultiIndex.from_arrays(df.iloc[:3].values)
df = df.iloc[3:]

# 转换为长表再透视得到目标结构
df_long = df.stack(level=[0,1]).reset_index()
df_long.columns = ['Year', 'Month', 'Type', 'Value']
final_df = df_long.pivot(index=['Year', 'Month'], columns='Type', values='Value').reset_index()
final_df = final_df[['Year', 'Month', 'Rent', 'Other']]

# 输出结果
print(final_df)

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