Python Pandas实战之Excel成绩分析自动化处理教程详解

带了几期 python 入门课后，我发现学员最容易卡住的不是语法，而是“学完之后到底能干嘛”。尤其是不少学员本身就是老师、教务员或者需要经常跟成绩单打交道的人，excel 操作熟练得很，但遇到跨班级汇总、多科目统计、几十张表重复处理时，效率就直线下降。这门第 5 课就是专门解决这个问题的：用 pandas 把 excel 成绩分析做成自动化流程。准备好了，这一课学完，你再也不想手动拖公式了。

1. 这一课要解决什么问题

先搞清楚需求。日常的考试成绩分析，看起来就是算算总分、平均分、排名、及格率，但真做起来并不轻松。我见过一位教务老师，每次考完试要花一整个下午处理 12 个班的成绩 excel：打开表、复制公式、检查格式、按总分排一次序、按班级筛选再做一次统计，最后还要把结果手工填进汇总表。这些操作步骤固定、逻辑重复，但就是绕不开。

第 5 课的核心思路是：把这类重复劳动变成一段可复用的 python 脚本。用 pandas 读取 excel、清洗数据、计算指标、汇总对比，最后一键导出结果。整个过程不依赖 excel 公式，不手工操作单元格，数据一变重新运行脚本就出全套结果。学员掌握了这条流程以后，应对的不只是成绩表，任何结构化的表格数据处理工作都能套用同一套方法。

这个场景放在教学里也很合适。pandas 本身就是 python 数据分析的核心库，excel 又是大家最熟悉的数据载体。拿成绩分析做案例，学员对业务需求有天然感知，不会觉得“这是在学一个抽象的工具”。而且成绩数据量不大（几百行到几千行）但列结构典型，非常适合练习 dataframe 的切片、分组、聚合、合并等核心操作。可以说，这是新手从“会写 python 语法”过渡到“用 python 解决实际问题”的最佳桥梁。

2. 环境准备：让 pandas 跑起来

2.1 安装 python 与 pandas

在开始写代码之前，先把环境弄顺。python 3.9 以上版本都可以，建议直接从官网下载安装包，安装时一定勾选“add python to path”。这一步漏了，后面在命令行里敲 python 就会提示找不到命令，安装完再补 path 比较麻烦。

pandas 和 excel 文件读写需要的库，用 pip 一条命令装齐：

pip install pandas openpyxl

这里出现的第二项很多人会忽略：openpyxl。pandas 的 read_excel 和 to_excel 只是一个调度层，真正负责读写 .xlsx 文件的是底层的 openpyxl 引擎。只装了 pandas 没装 openpyxl，运行时会直接报 valueerror: excel file format cannot be determined 之类的错误。建议顺手装上，省得后面排查半天。

装好以后，在命令行输入 python 进入交互环境，敲一行验证：

import pandas as pd
print(pd.__version__)

能输出版本号就说明环境没问题了。如果你用的是 pycharm，可以在 project interpreter 里安装同样的包，操作路径不同但效果一致。vscode 用户记得选择正确的 python 解释器，否则 import pandas 一样会报 modulenotfounderror。

2.2 准备一份可练习的成绩数据

环境就绪后，先别急着一上来就分析真实数据。我建议你按下面的格式做一份模拟成绩表（示例数据见代码注释），用于练习代码。真实场景中，excel 表的格式五花八门，标题行不固定、有合并单元格、有多余空行都很常见。教学时让学生先用标准格式练熟，再逐步提高难度去处理“脏数据”。

假设你的工作目录下有一个成绩文件夹，里面放着 3 个班的成绩表：

• 高一1班.xlsx
• 高一2班.xlsx
• 高一3班.xlsx

每张表的格式统一，第一行是列名，从第 2 行开始是学生数据：

这种“一列一个科目、一行一个学生”的宽表结构是最常见的成绩表格式，也是最适合用 pandas 分析的结构。如果将来遇到“一列存所有科目、一行一个成绩”的长表，做透 视表转回宽表即可，后面的章节我会专门讲。

3. 数据读取与初步体检

3.1 用 read_excel 正确读入成绩单

pandas 读取 excel 用 pd.read_excel()。看似一行代码，但有几个参数在成绩分析场景里必须搞清楚。

import pandas as pd

df = pd.read_excel("高一1班.xlsx", sheet_name=0)

sheet_name=0 表示读取第一个工作表。如果 excel 里有多个 sheet（比如“成绩表”“分析结果”“备注”），建议显式指定要读哪个。传入工作表名称更直观，例如 sheet_name="成绩表"。

接下来是表头问题。成绩表如果第 1 行就是列名，默认 head=0 就能正确解析。但如果第 1 行是“高一1班期末考试成绩表”这类大标题，第 2 行才是字段名，就要用 header=1 跳过一行。还有一种情况是第 1 行字段名下方有合并单元格或者科目分成两层（比如“语文”下面再分“客观题”“主观题”），这种情况推荐处理方式是用 pd.read_excel() 读原始数据后，再手动指定 columns，比硬调 header 参数稳定得多。

读进来以后，立刻对数据做一次“体检”。这是我在课上反复强调的习惯——拿到任何数据，先看结构再分析。

print(df.shape)          # (35, 7)  35行 7列
print(df.columns)        # 列名列表
print(df.head())         # 前5行
print(df.info())         # 各列类型和非空数量
print(df.describe())     # 数值列的统计描述

shape 帮你确认有没有读漏数据。比如某班实际有 35 人，读出来只有 30 行，就要回头检查是不是有空行或者 excel 底部残留了多余内容。describe() 返回的 count、mean、min、max 是最直观的“成绩健康度”指标——如果最小值出现负数或者最大值超过 150，说明原始数据有问题，要回到 excel 里核对。

3.2 看清列类型：成绩数据最容易被忽略的坑

info() 输出的信息里，最需要留意的是各列的 dtype。正常成绩列应该是 int64 或 float64，但如果你发现某列是 object，那说明这一列里混入了文本内容。常见原因有三种：

第一，缺考学生在单元格里填了“缺考”两个字，而不是留空。第二，录入时有人的成绩写成了 86.5，导致整列变成浮点型。第三，学号列被 excel 自动转成了科学计数法，读进来变成“1.001e+07”之类的文本。

碰到这种情况，不要直接 df["数学"].astype(float)，因为如果里面有“缺考”这种文本，astype 会直接抛 valueerror。更稳妥的做法是用 pd.to_numeric() 配合 errors="coerce"：

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

这行代码会把所有能转成数字的内容转成数字，不能转的变成 nan。之后再统一处理这些 nan，到底是按 0 分计，还是按缺考标记剔除，取决于考试规则。我一般建议在原始 excel 里就把缺考信息单独放一列“是否缺考”，成绩列留空，这样后续统计逻辑更干净。

4. 数据清洗：成绩分析里最容易踩的坑

4.1 处理缺失值：先判断业务含义再动手

数据清洗阶段，最怕的就是“无脑删”。拿到有 nan 的数据，你得先想一个问题：这个空值代表什么？

如果一名学生缺考，他的成绩列是空。此时你想算“班级平均分”，数学上应该把缺考生排除，还是把他按 0 分计入？大多数老师会选排除。那么代码里就不是 dropna 删除整行，而是只在计算平均分时忽略缺失值。pandas 的 mean()、sum() 等聚合函数默认 skipna=true，会直接跳过 nan，所以这一步通常不需要额外处理。

反过来，如果一行数据有 5 门成绩，只有 1 门缺考，你要算“六科总分”，这个学生的总分就是其他 5 科之和。这时候直接 df["总分"] = df.iloc[:, 2:8].sum(axis=1) 就行，sum 遇到 nan 默认也跳过。真正需要思考的是要不要替换成 0：体育这种科目缺考按 0 分比较合理，但语文缺考按 0 分明显不公平。这属于业务规则，不是技术问题。

有一种情况必须显式处理：数据录入时某行学生信息不全，比如姓名和学号都为空，那这一行是废数据，应该删掉。

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

dropna 的 subset 参数指定判断依据列，只有这些列为空才会删除，比整行 dropna 安全得多。整行 dropna 会把“部分科目缺考但学生信息完整”的行全部误删，造成的后果远比保留几个 nan 严重。

4.2 重复值处理：合并班级表前的必要操作

另一个高频问题是重复值。如果同一学号在一张表里出现两次，基本可以断定是录入重复。检查方法：

print(df.duplicated(subset=["学号"]).sum())
duplicated 返回布尔序列，true 表示重复。subset 指定判断重复的列，这里用学号比较合理，因为姓名可能同名。

确认有重复后，再用 drop_duplicates 去重：

df = df.drop_duplicates(subset=["学号"], keep="first")

keep="first" 表示保留第一次出现的行。但你要想清楚，保留第一行是不是正确的？如果两条记录成绩不一样，说明有人工修改过，保留哪条需要跟原始材料核对。我的处理习惯是：先把重复的学号筛出来，把对应的行打印出来人工看一遍，再决定保留策略。批量处理虽然快，但该人工判断的地方不能偷懒。

4.3 列名规范化：统一字段名的风格

成绩表来自不同班级时，列名经常不统一。一班叫“语文”，二班叫“语文成绩”，三班可能加了个空格叫“语文 ”。三个班合并后，这些列会被当成独立的三列，直接导致后续计算全部出错。

合并之前必须统一列名。手动改比较费力，更好用的方式是 read_excel 时用 names 参数直接指定列名：

columns = ["学号", "姓名", "语文", "数学", "英语", "物理", "化学"]
df = pd.read_excel("高一1班.xlsx", header=0, names=columns)

注意，用了 names 之后，原来的表头会被忽略。如果原表第 1 行就是字段名，header=0 会把第一行数据当成字段名，names 再覆盖，这样等于你把第一行数据丢掉了——这个细节我在课上讲过好几遍，每次都有同学踩坑。正确写法是 header=0 表示忽略表头，或者把表头那一行读进来之后再 rename。我常用的一种方案是原样读入，然后手动 rename：

df = df.rename(columns={"语文成绩": "语文", "语文 ": "语文"})

这里列名完全可控时会更放心。无论如何，动手写代码前先打印 df.columns 确认实际列名再操作，永远是好习惯。

5. 成绩统计与业务计算

5.1 总分、平均分、最高最低、及格率

数据干净了，就开始算“业务指标”。成绩分析最常用的几项指标，用 pandas 都是一行代码的事。

score_cols = ["语文", "数学", "英语", "物理", "化学"]

# 总分
df["总分"] = df[score_cols].sum(axis=1)

# 各科平均分
avg_scores = df[score_cols].mean()

# 各科最高分
max_scores = df[score_cols].max()

# 各科最低分
min_scores = df[score_cols].min()

先解释 sum(axis=1)。axis=0 是沿着行向下操作（默认），axis=1 是沿着列向右操作。score_cols 选出来的是一个二维的 dataframe，sum(axis=1) 对每一行求和，得到每个学生的总分。很多初学者会在 axis 上搞混，记住一句话：axis=0 算“列的统计”，axis=1 算“行的统计”。

按科目统计最高最低分，mean()、max()、min() 默认 axis=0，算的是每一列，所以不需要额外指定。

及格率的计算稍微绕一点。以 60 分为及格线为例，核心是把“是否及格”变成一个布尔判断，再求平均。因为 true 会被当成 1，false 当成 0，所以布尔序列的 mean 就是及格率。

pass_rate = (df[score_cols] >= 60).mean()

这段代码的逻辑是：score_cols 的每个元素跟 60 比较，生成一个同形状的布尔 dataframe；mean() 按列求平均，输出每个科目的及格率。比如语文及格率 0.94，表示 94% 的学生语文及格。

5.2 排名：两种方法彻底讲透

排名是成绩分析里学生和家长最看重的指标。pandas 的 rank() 方法默认按照“平均排名”方式计算，即相同分数共享一个排名，后续排名会跳过。举个具体例子：

• 有三名学生总分分别是 580、580、570。
• 默认 rank() 的结果是 1.5、1.5、3（前两名并列第一，第三名从 3 开始）。

但大多数老师习惯的是中国式排名：并列第一后，下一位是第二名，即 1、1、2。这个用 method="min" 实现：

df["总分排名"] = df["总分"].rank(ascending=false, method="min")

rank 的 ascending=false 表示降序排列，总分最高的排第 1。method="min" 表示同一组名次的取最小值。两种排名方法的区别对比如下：

默认 rank 返回的是浮点数，因为涉及平均排名。method="min" 在某些版本下还是浮点型（1.0、2.0），导出到 excel 后显示为“1”“2”倒也正常，不影响的。如果想要整数，可以再套一层 .astype(int)。不过要注意，astype 之前先确认没有 nan，否则会报错。

各科单科排名也可以同样计算，比如语文排名：

df["语文排名"] = df["语文"].rank(ascending=false, method="min")
df["数学排名"] = df["数学"].rank(ascending=false, method="min")

批量算多科排名，可以用循环：

for col in score_cols:
    df[f"{col}排名"] = df[col].rank(ascending=false, method="min")

f-string 是 python 3.6 以后的标准写法，这里用来动态构造新列名，非常方便。

5.3 等级划分：用 cut 做分箱

除了分数本身，很多学校还要求把成绩划分为 a、b、c、d 等级。用 pandas 的 cut() 函数做分箱，比在 excel 里套多层 if 函数爽快得多。

等级规则一般是：>=85 为 a，>=70 且 <85 为 b，>=60 且 <70 为 c，<60 为 d。注意 cut 的分箱边界是左开右闭还是左闭右开，这是最容易搞错的点。

bins = [0, 59, 69, 84, 100]
labels = ["d", "c", "b", "a"]
df["总分等级"] = pd.cut(df["总分"], bins=bins, labels=labels, right=true)

pd.cut() 的 bins 指定区间边界，labels 指定每个区间对应的等级。right=true 表示区间是左开右闭，即 (0, 59] 对应 d，(59, 69] 对应 c，(69, 84] 对应 b，(84, 100] 对应 a。这样 59 分落在 d，60 分落在 c，边界值不会丢。

这里有个细节：如果总分超过 100（比如附加题加分的特殊情况），cut 会返回 nan，因为区间没覆盖到。所以 bins 的最后一个边界要留足余量，或者用 np.inf 作为上界：

import numpy as np
bins = [0, 59, 69, 84, np.inf]

用 np.inf 表示正无穷，任何超高分都能正确落在最后一档。等级划分后，可以用 value_counts 看各等级的人数分布：

print(df["总分等级"].value_counts().sort_index())

sort_index() 按等级排序输出，d、c、b、a 的顺序展示，比默认按数量排序更清晰。

5.4 按班级分组统计与数据透 视表

单班分析做完了，接下来是跨班对比。这就要用到 groupby。

假设现在有一个合并了多个班级的大表 df_all，列里有“班级”字段。按班级统计总分平均分：

class_avg = df_all.groupby("班级")["总分"].mean()

groupby("班级") 把数据按班级分成若干组，然后对“总分”列取平均。结果是按班级索引的 series，班级名是索引。如果想同时看各科平均分：

class_stats = df_all.groupby("班级")[score_cols].mean()

groupby 加列名列表，输出的就是各班各科的平均分矩阵，行是班级，列是科目。

更进阶的用法是 agg()，可以一次传入多种聚合函数：

class_summary = df_all.groupby("班级")["总分"].agg(["mean", "max", "min", "count"])

agg 后面的列表里写什么函数名，输出列名就是什么，清晰直观。count 统计的是非空值数量，用于确认各班人数。这里再延伸一下，agg 也可以传字典，对不同列用不同聚合方式：

class_summary = df_all.groupby("班级").agg(
    {"总分": ["mean", "max"], "语文": "mean", "数学": "mean"}
)

这种写法自由度很高，适合老板临时加需求的场景。不过列名会变成多层索引，导出到 excel 时表头是两层的，观赏性一般，但数据是准确的。

数据透 视表是 groupby 的另一种形态。如果想把班级作为行索引、科目作为列索引、值为平均分，用 pivot_table：

pivot = pd.pivot_table(
    df_all,
    values="成绩",
    index="班级",
    columns="科目",
    aggfunc="mean"
)

注意 pivot_table 处理的数据结构是长表——每一行是一名学生的一个科目成绩，而不是一行一个学生的宽表。如果你的原始数据还是宽表（一行一个学生、每科一列），可以直接用 groupby 在多列上求均值来实现同样的效果，不必非得 reshape 成长表：

pivot2 = df_all.groupby("班级")[score_cols].mean()

两条路殊途同归。至于选哪种，取决于原表格式和你后续要做的分析。宽表场景 groupby 就够了，长表场景 pivot_table 更顺手。两种都建议练熟。

6. 批量自动化与结果导出

6.1 批量读取多个 excel 文件

前面讲了单张表的处理，但“自动化”的真正价值在于批量处理。这一节讲怎么把多个班级的文件一次性读进来合并成大表。

首先用 python 的 glob 模块匹配目标文件：

import glob

files = glob.glob("成绩文件夹/*.xlsx")
print(files)

glob 返回匹配模式的所有文件路径列表。这里的 *.xlsx 匹配成绩文件夹下所有 xlsx 文件。注意，如果文件夹里有临时文件（比如 excel 没关闭产生的 ~$开头文件），glob 的 * 也会匹配到，后续读取会报错。稳妥一点，可以再过滤一下：

files = [f for f in files if not os.path.basename(f).startswith("~$")]

用列表推导式过滤掉 excel 临时文件，这类文件以 ~$ 开头。这属于实战中才会遇到的坑，提前预防能省不少排查时间。

然后用循环逐个读取，并在读取时加一个“班级”列标记来源：

import os

df_list = []
for file in files:
    temp = pd.read_excel(file)
    class_name = os.path.basename(file).replace(".xlsx", "")
    temp["班级"] = class_name
    df_list.append(temp)

df_all = pd.concat(df_list, ignore_index=true)

这个模板是我反复用的。os.path.basename 提取文件名，replace 去掉扩展名，得到“高一1班”作为班级名。temp["班级"] = class_name 把班级信息塞进每一行。最后 pd.concat 把列表里的多个 dataframe 纵向拼接，ignore_index=true 让合并后的行索引重新从 0 编号，避免多个表的行号重复。

如果六个班各 50 人，合并后就是 300 行。df_all 就是一个完整的年级成绩总表，后面所有跨班分析都基于这张表做。

6.2 批量导出结果到多个 sheet

分析完了，怎么把结果交付给同事？答案是写进一个新的 excel 文件，多张表放进多个工作表（sheet），这样一份文件就能包含所有信息。

with pd.excelwriter("成绩分析结果.xlsx") as writer:
    df_all.to_excel(writer, sheet_name="总表", index=false)
    class_summary.to_excel(writer, sheet_name="班级汇总")
    avg_scores.to_excel(writer, sheet_name="各科平均分")

pd.excelwriter 是 pandas 写入 excel 的上下文管理器。with 块结束时自动保存并关闭文件。to_excel 的第一个参数是 writer，第二参数 sheet_name 指定工作表名称，index 参数控制是否把行索引写入 excel。

这么说可能有点抽象——比如班级汇总这张表，groupby 的结果是班级作为索引，如果你不设置 index=false，导出后 a 列就是班级名；如果你传 index=false，班级名就丢了，只剩成绩数据。处理这种情况可以用 reset_index() 把索引变成普通列：

class_summary_reset = class_summary.reset_index()
class_summary_reset.to_excel(writer, sheet_name="班级汇总", index=false)

reset_index() 把原来的索引（班级名）变成一列，列名默认是“班级”。这样导出后表头就是“班级、总分_mean、总分_max……”干净多了。

多个 sheet 的数据结构不一样，列数不同，excel 里看起来可能会有点参差，但这是最直观的交付方式。更高端一点的方案是在一个 sheet 里用多个 dataframe 拼出完整的分析报告布局，用 pd.concat(axis=1) 把不同结构的表横向合并，再用 excelwriter 写出去。操作复杂一些，但报告感更强。

7. 常见问题与排查技巧实录

7.1 运行报错“no module named pandas”

这个报错分成两种情况。第一种是你根本没装 pandas，执行 pip install pandas 即可。第二种是你装在了 a 解释器，但运行代码用的是 b 解释器。这种情况在 pycharm 里最常见——创建项目时会自动建一个虚拟环境，你在终端里用 pip install pandas 装到了全局环境，项目里却用的是虚拟环境。

排查方式很简单，在你的运行环境里执行：

import sys
print(sys.executable)

输出的是当前 python 解释器的实际路径。然后把 pandas 安装到对应的环境里。pycharm 用户直接在 settings 里找到 project interpreter，点加号搜索 pandas 安装就行，图形界面操作最不容易出错。

7.2 中文列名和中文路径

pandas 本身完全支持中文列名，读写也支持中文路径，不需要额外配置。如果在读取中文路径的 excel 时报 unicodedecodeerror，八成是编码问题。解决办法是在 read_excel 里加 engine 参数，或者检查 openpyxl 版本：

pip install --upgrade openpyxl

新版 openpyxl 对中文路径和特殊字符的处理更完善。另外提醒一句，有些旧代码里会用 encoding="utf-8" 传给 read_excel，这是多余且不对的——read_excel 并不接受 encoding 参数，只有 read_csv 才用。加了会报 typeerror，直接把参数去掉就行。

7.3 成绩列是文本格式，求和全变 0

这是我在教学里见到最多的问题之一。excel 里有些成绩列虽然显示的是数字，但它是以文本格式存储的，单元格左上角通常有个绿色小三角。pandas 读进来后，这列的类型是 object，直接 sum 之后会得到一串拼接的字符串（比如“85879288”），而不是数值相加。

解决方案就是前面提过的 pd.to_numeric 强制转换：

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

转换后再用 isinstance(df["数学"].dtype, ...) 或者直接打印 df.dtypes 确认列类型已经变为 int64 或 float64。一个检查技巧：执行 df["数学"].sum() 如果结果不是数字，基本可以确定类型有问题。

7.4 整数变成小数：看似奇怪其实是特性

成绩数据里如果混入了一个 nan，pandas 为了在同一个列里同时存储数字和空值，会把整列的类型提升为 float64。比如语文成绩本来是 int64，某个学生语文缺考，这列就变成 float64，显示为 85.0、92.0 这种带 .0 的值。这是 pandas 的特性，不是 bug。

想恢复成整数显示，可以分两步：先把空值填掉，再 astype(int)。比如缺考按 0 分处理：

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

如果缺考不想按 0 分算，但导出后想避免小数点，可以在 to_excel 时保留 float，或者对计算结果先 round 再导出。说实话，在分析阶段保留 float 是最安全的做法，等到最终输出的展示文件里再考虑格式美化，分析过程中不必过度纠结这个。

7.5 导出 excel 后打开提示“文件已损坏”

这个问题的元凶大多是后台的 excelwriter 没有被正确关闭。如果你用老式的写法：

writer = pd.excelwriter("结果.xlsx")
... # 写入内容
writer.save()     # 不推荐

某一步代码发生异常，writer 没有触发 save()，文件就是损坏的。推荐用 with 块，因为 with 会在代码块结束或异常时自动关闭文件，不会出现半截文件的情况。或者用 try/finally 手动释放。除非必要，别再写 writer.save() 这种老 api 了。

还有一个相关的问题是：想写入的 excel 文件正在被 excel 程序打开。windows 下文件被占用时，to_excel 会抛 permissionerror。解决办法很朴实——关掉正在打开该文件的 excel 窗口，再重新运行。脚本里也可以加一个 try/except，检测到 permissionerror 时提示用户先关闭文件，而不是直接崩溃。

7.6 常见问题速查表

这一课的代码逻辑不算难，真正的门槛在“习惯”。我在带学员的时候发现，很多人学会了函数和语法，却处理不好一张带合并单元格的真实成绩表。所以最后想说的是：不要只拿我给的模拟数据练习，把你的真实工作数据、班级真实的 excel 表拿出来跑一遍。第一次跑通可能很狼狈，到处报错，但错一次你就记住了这些数据里的坑。把脚本保存下来，下次考试直接复用，那种感觉真的很爽。

以上就是python pandas实战之excel成绩分析自动化处理教程详解的详细内容，更多关于python pandas自动化处理excel的资料请关注代码网其它相关文章！