基于指定ID列表合并多份Excel数据生成目标pandas DataFrame

核心思路 #

以提前准备的全量ID列表作为基准主表，逐个读取Excel文件生成单月数据表后，基于ID列和主表左关联，最终补全缺失值即可得到目标格式结果，从根源避免数组长度不一致的报错。

具体实现步骤 #

• 第一步：初始化全量ID基准表

import pandas as pd
import os

# 替换为你的真实全量ID列表
full_ids = [100, 101, 102, 103, 200]
# 生成基准主表，所有后续关联都基于该表对齐
main_df = pd.DataFrame({'ID': full_ids})

• 第二步：遍历读取Excel并关联主表

# 替换为你的Excel文件所在文件夹路径
folder_path = './excel_files'

for filename in os.listdir(folder_path):
    # 过滤非Excel文件
    if not filename.endswith(('.xlsx', '.xls')):
        continue
    # 提取月份作为列名，可根据需求修改格式
    month_col_name = filename.rsplit('.', 1)[0]
    # 读取Excel文件
    excel_df = pd.read_excel(os.path.join(folder_path, filename))
    # 提取ID和对应的Color数据，将Color重命名为月份名
    single_month_data = excel_df[['ID', 'Color']].rename(columns={'Color': month_col_name})
    # 基于ID左关联到主表，自动对齐ID顺序，不会出现长度不匹配问题
    main_df = pd.merge(main_df, single_month_data, on='ID', how='left')

• 第三步：填充缺失值得到最终结果

# 将空值替换为要求的横杠
final_df = main_df.fillna('-')
# 可直接导出为Excel
final_df.to_excel('./merged_result.xlsx', index=False)

常见问题排查 #

如果出现ID匹配异常，可统一ID列的数据类型后再关联：

# 统一将ID列转为整数类型
main_df['ID'] = main_df['ID'].astype(int)
single_month_data['ID'] = single_month_data['ID'].astype(int)

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