如何垂直合并含相同列与不同索引列的多个Excel文件

需求说明 #

需要垂直合并多个Excel文件，这些文件包含5个固定相同列，左侧最多有3个不同的索引列（如Account Manager、Category、Station Manager等）。要求合并时：

• 不隐藏任何列
• 对应列的数据在下方依次排列
• 保留所有索引列，缺失的索引列对应位置填充空值

原代码问题分析 #

原合并代码直接使用pd.concat拼接，但未处理不同文件列名不一致的情况，导致索引列和固定列无法正确对齐，最终合并结果列错位，不符合预期。

修改后的合并代码 #

import os
import pandas as pd

def merge_excel_files(input_folder, output_file):
    # 获取目标文件夹下所有Excel文件
    excel_files = [f for f in os.listdir(input_folder) if f.endswith('.xlsx')]
    dfs = []
    
    # 替换为你的5个固定列实际名称（确保列名在所有文件中统一，有差异可添加映射）
    fixed_columns = ['Year', 'Month', '列名3', '列名4', '列名5']  

    for excel_file in excel_files:
        file_path = os.path.join(input_folder, excel_file)
        # 读取Excel文件，保留所有列
        df = pd.read_excel(file_path)
        
        # 可选：如果不同文件中固定列名有差异，在这里做列名映射
        # 示例：df.rename(columns={'年度':'Year', '月份':'Month'}, inplace=True)
        
        # 可选：添加空行分隔不同文件的数据，不需要可删除这段
        empty_row = pd.Series([None]*len(df.columns), index=df.columns)
        df = pd.concat([df, pd.DataFrame([empty_row])], ignore_index=True)
        
        dfs.append(df)
    
    # 垂直拼接所有DataFrame，自动对齐列，缺失列填充空值
    merged_df = pd.concat(dfs, ignore_index=True)
    
    # 调整列顺序：索引列在前，固定列在后
    index_columns = [col for col in merged_df.columns if col not in fixed_columns]
    merged_df = merged_df[index_columns + fixed_columns]
    
    # 保存合并结果
    merged_df.to_excel(output_file, index=False)

# 配置路径
input_folder = r'CSV\AL_KABEER_OSA_PLANO_ACCOUNT_MANAGER_SUMMARY'
output_file = 'merged_output.xlsx'
merge_excel_files(input_folder, output_file)

代码关键说明 #

• 列自动对齐：pd.concat会自动识别所有文件的列名，不同文件中不存在的列会填充NaN，确保对应列数据正确排列。
• 列顺序优化：自动提取所有非固定列作为索引列放在左侧，固定列放在右侧，匹配需求中的结构。
• 灵活适配：如果不同文件固定列名有差异，可在读取文件后添加列名映射逻辑，保证固定列完全一致。

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