如何将Excel多工作表两行表头合并为单个表头并整合为DataFrame

核心思路 #

• 读取Excel文件中所有工作表名称
• 处理首行合并单元格导致的空值：用前一个非空值填充（Excel合并单元格仅左上角有值，其余位置为空）
• 将两行表头合并为一行：优先保留两行有效信息，空值自动忽略
• 读取每个工作表的实际数据（从第三行开始），绑定合并后的表头
• 把所有工作表的数据合并为单个DataFrame

代码实现 #

import pandas as pd

# 1. 加载目标Excel文件
excel_file = pd.ExcelFile("你的文件路径.xlsx")
# 获取所有工作表名称
sheet_names = excel_file.sheet_names

# 初始化空列表存储每个工作表处理后的数据
processed_data = []

for sheet in sheet_names:
    # 2. 读取前两行表头数据
    header_raw = excel_file.parse(sheet, nrows=2, header=None)
    # 填充首行合并单元格的空值
    header_top = header_raw.iloc[0].ffill()
    # 提取第二行表头
    header_bottom = header_raw.iloc[1]
    
    # 3. 合并两行表头
    combined_header = []
    for h_top, h_bottom in zip(header_top, header_bottom):
        if pd.isna(h_bottom):
            combined_header.append(str(h_top))
        elif pd.isna(h_top):
            combined_header.append(str(h_bottom))
        else:
            combined_header.append(f"{h_top}_{h_bottom}")
    
    # 4. 读取当前工作表的实际数据（跳过前两行表头）
    sheet_df = excel_file.parse(sheet, skiprows=2, header=None)
    # 设置合并后的表头
    sheet_df.columns = combined_header
    
    # 可选：添加列标记数据来源的工作表（比如日期）
    sheet_df["数据来源"] = sheet
    
    # 5. 将处理后的工作表数据加入列表
    processed_data.append(sheet_df)

# 6. 合并所有工作表数据为一个DataFrame
final_df = pd.concat(processed_data, ignore_index=True)

# 查看处理结果
print(final_df.head())

注意事项 #

• 若需要调整表头合并格式（比如不用下划线，改用空格），直接修改f"{h_top}_{h_bottom}"的拼接逻辑即可
• 若存在两行表头都为空的列，可在合并时添加默认名称，比如combined_header.append("未命名列")
• 针对大文件，建议分批次处理避免内存溢出
• 确保pandas版本≥1.3.0，避免合并单元格解析异常

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