如何将Excel多个工作表按学号合并为宽表结构

聖光之護

发布时间：2026-02-10 17:12:02

557人浏览过

来源于php中文网

原创

如何将Excel多个工作表按学号合并为宽表结构

本文介绍使用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条……严格对齐，彻底规避错位与爆炸式连接。

以下是完整、健壮的实现步骤：

Flowith

一款GPT4驱动的节点式 AI 创作工具

下载

✅ 步骤 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)