Pandas批量处理Excel成绩单自动化与报错排查

脚本专家

用 Pandas 处理 Excel 成绩单，重点不是替代一两个公式，而是把多班、多科、多文件的重复处理固化成可复用脚本。案例覆盖 Python 3.9+ 环境、read_excel 读取、数据体检、清洗、总分/平均分/及格率、排名、cut 分箱、groupby/pivot_table、批量读写和常见报错排查。

一、环境准备
Python 3.9 以上可用，安装时勾选 Add Python to PATH。Pandas 读写 .xlsx 依赖 openpyxl；只装 pandas 可能报 ValueError: Excel file format cannot be determined。安装命令：

2. pip install pandas openpyxl

复制代码

验证：

2. import pandas as pd
3. print(pd.__version__)

复制代码

PyCharm 在 Project Interpreter 安装包，VSCode 要选对解释器，否则会出现 ModuleNotFoundError 或 No module named pandas。

二、准备成绩数据
准备 3 个班文件：高一1班.xlsx、高一2班.xlsx、高一3班.xlsx。每张表第一行是列名，第二行开始是学生数据，字段为：学号、姓名、语文、数学、英语、物理、化学。这种“一行一个学生、一列一个科目”的宽表最适合 Pandas 分析。

三、读取与初步体检

2. import pandas as pd
3. df = pd.read_excel('高一1班.xlsx', sheet_name=0)
4. # 多 sheet 时显式指定，如 sheet_name='成绩表'
5. # 若首行是大标题、第二行才是字段名，可用 header=1
6. print(df.shape)
7. print(df.columns)
8. print(df.head())
9. print(df.info())
10. print(df.describe())

复制代码

sheet_name=0 表示第一个工作表。shape 用来确认行数是否读漏，例如实际 35 人只读出 30 行，要检查空行或表尾残留。info() 看各列 Dtype 和非空数量；describe() 的 count、mean、min、max 是成绩健康度指标，最小值出现负数或最大值超过 150，应回 Excel 核对。

四、列类型与清洗
正常成绩列应为 int64 或 float64。如果某列是 object，常见原因是缺考填了“缺考”、录入 86.5 形成浮点、学号被 Excel 转成科学计数法。不要直接 astype(float)，更稳妥的是：

2. df['数学'] = pd.to_numeric(df['数学'], errors='coerce')

复制代码

能转数字的转数字，不能转的变成 NaN，再按考试规则决定按 0 分还是剔除。

缺失值要先判断业务含义。缺考通常不参与平均分，mean/sum 默认 skipna=True，会跳过 NaN。只有姓名和学号都为空的行才属于废数据：

2. df = df.dropna(subset=['学号', '姓名'])

复制代码

不要直接整行 dropna，否则会误删“部分科目缺考但学生信息完整”的记录。算六科总分可直接按行求和：

2. df['总分'] = df.iloc[:, 2:8].sum(axis=1)

复制代码

重复值按学号检查，姓名可能同名：

2. print(df.duplicated(subset=['学号']).sum())
3. df = df.drop_duplicates(subset=['学号'], keep='first')

复制代码

keep='first' 只代表保留第一条；如果两条记录成绩不同，应人工核对原始材料再决定。

列名规范化同样关键。不同班级可能叫“语文”“语文成绩”“语文 ”，合并后会变成独立列，导致统计错误。可原样读入后 rename：

2. columns = ['学号', '姓名', '语文', '数学', '英语', '物理', '化学']
3. df = pd.read_excel('高一1班.xlsx', header=0, names=columns)
4. df = df.rename(columns={'语文成绩': '语文', '语文 ': '语文'})

复制代码

names 会覆盖原列名，使用前先用 df.columns 确认实际列名，避免表头与数据行关系处理错。

五、常用成绩指标

2. score_cols = ['语文', '数学', '英语', '物理', '化学']
3. df['总分'] = df[score_cols].sum(axis=1)
4. avg_scores = df[score_cols].mean()
5. max_scores = df[score_cols].max()
6. min_scores = df[score_cols].min()
7. pass_rate = (df[score_cols] >= 60).mean()

复制代码

axis=1 是沿列向右求和，得到每个学生总分；axis=0 是默认的按列统计。及格率利用 True 等于 1、False 等于 0，布尔序列的 mean 就是及格比例。

六、排名与等级划分
默认 rank() 是平均排名。580、580、570 会得到 1.5、1.5、3；中国式排名想让并列第一后下一位是第二，用 method='min'：

2. df['总分排名'] = df['总分'].rank(ascending=False, method='min')
3. for col in score_cols:
4.     df[f'{col}排名'] = df[col].rank(ascending=False, method='min')

复制代码

rank 可能返回浮点，需要整数且确认没有 NaN 后再 astype(int)。等级划分用 cut：

2. import numpy as np
3. bins = [0, 59, 69, 84, np.inf]
4. labels = ['D', 'C', 'B', 'A']
5. df['总分等级'] = pd.cut(df['总分'], bins=bins, labels=labels, right=True)
6. print(df['总分等级'].value_counts().sort_index())

复制代码

right=True 表示左开右闭，(0,59] 为 D，(59,69] 为 C，(69,84] 为 B，(84,100] 为 A。总分可能超过 100 时，用 np.inf 作为上界，避免 cut 返回 NaN。

七、按班级分组与透视

2. class_avg = df_all.groupby('班级')['总分'].mean()
3. class_stats = df_all.groupby('班级')[score_cols].mean()
4. class_summary = df_all.groupby('班级')['总分'].agg(['mean', 'max', 'min', 'count'])
5. class_summary2 = df_all.groupby('班级').agg({'总分': ['mean', 'max'], '语文': 'mean', '数学': 'mean'})
6. pivot = pd.pivot_table(df_all, values='成绩', index='班级', columns='科目', aggfunc='mean')
7. pivot2 = df_all.groupby('班级')[score_cols].mean()

复制代码

agg 列表里写什么函数名，输出列名就是什么，count 是各班非空人数。pivot_table 适合“一行一个学生一个科目成绩”的长表；如果原始数据是一行一个学生的宽表，直接 groupby 多列求均值即可，不必强行 reshape。

八、批量读取多个 Excel

2. import glob
3. import os
4. files = glob.glob('成绩文件夹/*.xlsx')
5. files = [f for f in files if not os.path.basename(f).startswith('~$')]
6. df_list = []
7. for file in files:
8.     temp = pd.read_excel(file)
9.     class_name = os.path.basename(file).replace('.xlsx', '')
10.     temp['班级'] = class_name
11.     df_list.append(temp)
12. df_all = pd.concat(df_list, ignore_index=True)

复制代码

glob 匹配所有 xlsx，~$ 开头的是 Excel 临时文件，必须过滤。文件名去掉扩展名后写入“班级”列，pd.concat 纵向拼接，ignore_index=True 让合并后的行索引从 0 重新编号。

九、导出到多个 Sheet

2. class_summary_reset = class_summary.reset_index()
3. with pd.ExcelWriter('成绩分析结果.xlsx') as writer:
4.     df_all.to_excel(writer, sheet_name='总表', index=False)
5.     class_summary_reset.to_excel(writer, sheet_name='班级汇总', index=False)
6.     avg_scores.to_excel(writer, sheet_name='各科平均分')

复制代码

ExcelWriter 作为上下文管理器，with 结束时自动保存关闭。groupby 结果是班级索引，想导出成普通列先用 reset_index()；index=False 避免把行号写进 Excel。

十、常见问题排查
No module named pandas 分两种情况：没装，或装到了别的解释器。先确认当前解释器路径：

2. import sys
3. print(sys.executable)

复制代码

然后把 pandas 安装到该环境。PyCharm 在 Settings 的 Project Interpreter 操作，VSCode 选择正确解释器。

中文列名和中文路径 Pandas 本身支持，不需要额外配置。若读取时报 UnicodeDecodeError，可升级 openpyxl：

2. pip install --upgrade openpyxl

复制代码

read_excel 不接受 encoding 参数，只有 read_csv 才用 encoding；传了会报 TypeError。

成绩列是文本格式时，object 列求和可能变成字符串拼接或结果异常。先强制转数值，再看 dtype：

2. df['数学'] = pd.to_numeric(df['数学'], errors='coerce')
3. print(df.dtypes)

复制代码

整数变成小数是因为列里混入 NaN，Pandas 为同时存数字和空值会把整列提升为 float64，85 显示为 85.0，这是特性不是 bug。缺考按 0 分处理时可：

2. df['语文'] = df['语文'].fillna(0).astype(int)

复制代码

如果导出 Excel 后提示文件已损坏，优先检查 ExcelWriter 是否通过 with 正常关闭、目标文件是否正被 Excel 占用，以及 openpyxl 是否需要升级；不要在 writer 尚未关闭时手工打开结果文件。

整体流程可以固定为：环境准备、read_excel 读取、shape/info/describe 体检、缺失值/重复值/列名清洗、总分平均分及格率计算、rank 排名、cut 分箱、groupby/pivot_table 汇总、批量 glob 合并、ExcelWriter 多 Sheet 导出。数据变化后重跑脚本即可，适合成绩单以及其它结构化表格的重复自动化处理。