Excel多工作表合并需求：按Roll Number匹配Brief列并消除重复

问题场景 #

需要处理单个/多个含多工作表的Excel文件，提取所有表中的Roll Number和Brief列，将每个工作表的Brief列以工作表名为列名合并，按Roll Number匹配对应内容。但直接使用pd.merge会出现重复值问题：

示例输入 #

工作表「ClassA」：

Roll Number    Brief         email
11             Maths 11      abc@abc
11             Science 12    abc@abc
12             History

工作表「ClassB」：

Roll Number    Brief         email
11             Art 71        abc@abc
13             Science 12    abc@abc
12             Maths

错误输出（原代码结果） #

Roll Number     ClassA      ClassB
11              Maths 11    Art 71
11              Science 12  Art 71
12              History     Maths
13              Science 12

期望输出 #

Roll Number     ClassA      ClassB
11              Maths 11    Art 71
11              Science 12
12              History     Maths
13              Science 12

问题原因 #

原代码直接基于Roll Number做外连接，当同一个Roll Number在两个工作表中都存在多条记录时，会产生笛卡尔积，导致其中一个表的记录重复匹配到另一个表的多条记录中（比如ClassB的Art 71重复出现在ClassA的两条11记录里）。

解决方案 #

给每个Roll Number分组内的记录添加一个组内序号，合并时同时基于Roll Number和序号做连接，就能避免笛卡尔积问题。

单文件处理代码 #

import pandas as pd

inputfile = "your_input_file.xlsx"  # 替换为你的Excel文件路径
xls = pd.ExcelFile(inputfile)
sheet_names = xls.sheet_names
combined_df = None

for sheet in sheet_names:
    df = pd.read_excel(xls, sheet_name=sheet)
    # 确保目标列存在，避免KeyError
    if not all(col in df.columns for col in ['Roll Number', 'Brief']):
        print(f"工作表{sheet}缺少目标列，跳过")
        continue
    # 提取并重命名列
    df = df[['Roll Number', 'Brief']].copy()
    df.rename(columns={'Brief': sheet}, inplace=True)
    # 添加组内序号：同Roll Number下的每行分配唯一序号
    df['seq'] = df.groupby('Roll Number').cumcount()
    
    if combined_df is None:
        combined_df = df
    else:
        # 基于Roll Number和seq做外连接
        combined_df = pd.merge(combined_df, df, on=['Roll Number', 'seq'], how='outer')

# 删除序号列，还原成期望的格式
if combined_df is not None:
    combined_df.drop(columns='seq', inplace=True)
    combined_df.reset_index(drop=True, inplace=True)
    # 输出结果
    print(combined_df)
    combined_df.to_excel('combined_final.xlsx', index=False)
else:
    print("没有有效数据可合并")

多文件处理代码（支持批量处理） #

如果需要处理多个Excel文件，可扩展代码如下，同时避免不同文件的同名工作表列冲突：

import pandas as pd
import glob

# 获取所有目标Excel文件（可自定义匹配规则，比如"*.xlsx"匹配所有xlsx文件）
input_files = glob.glob("*.xlsx")
all_sheet_data = []

for file in input_files:
    xls = pd.ExcelFile(file)
    file_name = file.split('.')[0]  # 获取文件名（不含后缀）
    for sheet in xls.sheet_names:
        df = pd.read_excel(xls, sheet_name=sheet)
        if not all(col in df.columns for col in ['Roll Number', 'Brief']):
            print(f"文件{file}的工作表{sheet}缺少目标列，跳过")
            continue
        df = df[['Roll Number', 'Brief']].copy()
        # 用"文件名_工作表名"作为列名，避免重名
        df.rename(columns={'Brief': f"{file_name}_{sheet}"}, inplace=True)
        df['seq'] = df.groupby('Roll Number').cumcount()
        all_sheet_data.append(df)

# 合并所有工作表数据
if all_sheet_data:
    combined_df = all_sheet_data[0]
    for df in all_sheet_data[1:]:
        combined_df = pd.merge(combined_df, df, on=['Roll Number', 'seq'], how='outer')
    combined_df.drop(columns='seq', inplace=True)
    combined_df.reset_index(drop=True, inplace=True)
    combined_df.to_excel('combined_all_files.xlsx', index=False)
else:
    print("未找到符合条件的工作表数据")

关键说明 #

• df.groupby('Roll Number').cumcount()：给每个Roll Number分组内的行从0开始生成连续序号，确保同Roll Number的不同行有唯一标识，合并时不会产生笛卡尔积。
• 列存在性检查：避免因工作表缺少目标列导致的运行错误。
• 多文件处理时用「文件名_工作表名」作为列名，防止不同文件的同名工作表列名冲突。

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