Python合并多Excel工作表问题：部分数据缺失排查

问题描述 #

使用Python合并多个Excel文件，以base_file.xlsx为基准：匹配列追加新数据，不匹配列新增后填充对应行数据。但合并后仅cost、impression等少数列数据匹配，其余数据大量缺失，怀疑拼接逻辑存在问题。

原始代码 #

import pandas as pd

base_file = pd.read_excel("C:/Users/base_file.xlsx")

additional_files = [
    "C:/Users/File_1.xlsx",
    "C:/UsersFile_2.xlsx"
    "C:/Users/File_3.xlsx",
    ]

for file in additional_files:
    # Load the new file
    new_file = pd.read_excel(file)
    
    common_columns = base_file.columns.intersection(new_file.columns)
    
    new_file_common = new_file[common_columns]
    base_file = pd.concat([base_file, new_file_common.reindex(columns=base_file.columns)], axis=0, ignore_index=True, sort = False)
    
    new_columns = new_file.columns.difference(base_file.columns)
    
    # Add new columns to the base file with NaN values for existing rows
    for col in new_columns:
        base_file[col] = pd.NA
    
    # Append the data for new columns, ensuring alignment by index
    new_file_non_common = new_file[new_columns]
    base_file = pd.concat([base_file, new_file_non_common.reset_index(drop=True)], axis=1, sort = False)
    
    # Remove any duplicated columns
    base_file = base_file.loc[:,~base_file.columns.duplicated()]

with pd.ExcelWriter('final_combined_file_corrected1.xlsx') as writer:
    # Write the combined DataFrame to the first sheet
    base_file.to_excel(writer, sheet_name='Combined Data', index=False)

截图信息 #

• 输出文件：合并后表格仅部分列（如cost、impression）有有效数据，其余列存在大量缺失值，新增列的行数据未对应到刚追加的行
• 原始数据：各原始Excel文件的列均包含完整数据，既有与基准文件匹配的列，也有独有的非匹配列

问题根源 #

1. 行与列拼接逻辑错位：先追加匹配列的行数据，再单独拼接非匹配列的列数据，导致新列的数据被附加到整个表格的末尾行，而非对应到刚追加的那些行，造成数据错位缺失。
2. 列表语法错误：additional_files中第二个路径缺少逗号，导致字符串拼接成无效路径（"C:/UsersFile_2.xlsx"），可能导致该文件未被正确读取。
3. 列处理冗余且错误：分开处理匹配列与非匹配列的逻辑复杂且易出错，没有利用Pandas的列对齐机制一次性完成数据合并。

修正后的代码 #

采用先列对齐，再行追加的逻辑，确保每一行的所有数据都正确对应：

import pandas as pd

base_file = pd.read_excel("C:/Users/base_file.xlsx")

# 修正列表逗号错误，确保路径有效
additional_files = [
    "C:/Users/File_1.xlsx",
    "C:/Users/File_2.xlsx",
    "C:/Users/File_3.xlsx",
]

for file in additional_files:
    new_file = pd.read_excel(file)
    
    # 获取基准文件与新文件的所有列的并集，对齐新文件的列结构
    all_columns = base_file.columns.union(new_file.columns)
    aligned_new_data = new_file.reindex(columns=all_columns)
    
    # 追加对齐后的新数据到基准文件
    base_file = pd.concat([base_file, aligned_new_data], axis=0, ignore_index=True, sort=False)

# 导出最终合并文件
with pd.ExcelWriter('final_combined_file_corrected1.xlsx') as writer:
    base_file.to_excel(writer, sheet_name='Combined Data', index=False)

关键说明 #

• 使用columns.union()获取所有列的并集，自动保留基准文件和新文件的所有列
• reindex()自动为新文件补充基准文件已有的列（填充NaN），同时保留新文件独有的列
• 直接追加整个对齐后的DataFrame，确保每一行的匹配列和非匹配列数据一一对应
• 修正了列表中的语法错误，避免路径读取失败

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