本文介绍使用pandas高效合并excel中多个工作表的方法：以“roll number”为键，将各表的“brief”列分别作为独立列展开，避免传统循环merge导致的笛卡尔积重复问题。
在处理多班级、多学期或分批次采集的学生成绩/课程数据时，常遇到一个excel文件包含多个工作表（如ClassA、ClassB），每张表结构不完全一致，但均包含关键字段Roll Number和Brief。目标是将所有工作表中的Brief信息，按Roll Number对齐、横向展开为宽表——即每个工作表对应一列，同一学号的多条记录按出现顺序逐行对齐（而非交叉匹配）。
你最初尝试的pd.merge循环方式失败的根本原因在于：merge默认基于键做全连接（outer join），当某学号在一张表中出现多次（如Roll Number=11在ClassA中有两条记录），而另一张表中仅出现一次时，merge会生成所有组合（即11×11 → 2行×1行 = 2行结果，但ClassB列被重复填充），造成逻辑错误与数据冗余。
✅ 正确解法的核心思路是：为每个学号在各表内的多条记录添加唯一序号（row number within group），再以(Roll Number, 序号)为复合索引进行拼接。这样可确保同组内第1条、第2条……严格对齐，彻底规避错位与爆炸式连接。
以下是完整、健壮的实现步骤：
✅ 步骤 1：一次性读取全部工作表为字典
import pandas as pd # 用 sheet_name=None 直接读取所有sheet到字典 {sheet_name: DataFrame} df_dict = pd.read_excel("input.xlsx", sheet_name=None)✅ 步骤 2：为每张表提取并标记行序号
对每张表：
- 仅保留 Roll Number 和 Brief 列；
- 使用 groupby(‘Roll Number’).cumcount() 为每个学号内部的记录编号（从0开始）；
- 将 Roll Number 和该序号设为双重索引；
- 将 Brief 列重命名为对应工作表名（如 ‘ClassA’）。
dfs = [] for sheet_name, df in df_dict.items(): # 提取必要列 + 添加组内序号 temp = df[['Roll Number', 'Brief']].copy() temp['seq'] = temp.groupby('Roll Number').cumcount() # 设置复合索引并重命名列 temp = temp.set_index(['Roll Number', 'seq'])[['Brief']].rename(columns={'Brief': sheet_name}) dfs.append(temp)✅ 步骤 3：沿列方向拼接并整理索引
# 按列（axis=1）拼接所有带复合索引的DataFrame result = pd.concat(dfs, axis=1).reset_index(level='seq', drop=True).reset_index()? 关键说明：pd.concat(…, axis=1) 在索引对齐时天然支持“相同 (Roll Number, seq) 对应同一行”，因此无需手动merge；.droplevel(1) 或 .reset_index(level=’seq’, drop=True) 用于丢弃无意义的seq索引层级，最终得到干净的三列结构：Roll Number, ClassA, ClassB……
✅ 完整可运行示例（含测试数据）
import pandas as pd # 模拟原始Excel多Sheet数据 data_classa = {'Roll Number': [11, 11, 12], 'Brief': ['Maths 11', 'Science 12', 'History']} data_classb = {'Roll Number': [11, 13, 12], 'Brief': ['Art 71', 'Science 12', 'Maths']} df_dict = {'ClassA': pd.DataFrame(data_classa), 'ClassB': pd.DataFrame(data_classb)} # 执行核心逻辑 dfs = [] for name, df in df_dict.items(): temp = df[['Roll Number', 'Brief']].copy() temp['seq'] = temp.groupby('Roll Number').cumcount() temp = temp.set_index(['Roll Number', 'seq'])[['Brief']].rename(columns={'Brief': name}) dfs.append(temp) out = pd.concat(dfs, axis=1).reset_index(level='seq', drop=True).reset_index() print(out) # 输出： # Roll Number ClassA ClassB # 0 11 Maths 11 Art 71 # 1 11 Science 12 NaN # 2 12 History Maths # 3 13 NaN Science 12 # 保存结果 out.to_excel("combined_output.xlsx", index=False)⚠️ 注意事项
- 若某学号在某表中无记录，对应单元格自动为 NaN，符合预期；
- cumcount() 默认从 0 开始编号，确保各表第0条、第1条……严格对齐；
- 若原始数据含空值（如缺失Roll Number），建议提前清洗：df.dropna(subset=[‘Roll Number’])；
- 工作表名若含空格或特殊字符，rename() 后列名会自动保留，导出Excel时无影响；
- 内存优化：对于超大文件，可改用 chunksize 分批处理，但本场景通常无需。
此方法简洁、高效、可扩展，轻松应对数十个工作表、百万级行数的合并需求，是处理教育类、问卷类多源Excel数据的标准实践。