每学期期末那几天教务办公室里最热闹的就是各种手忙脚乱算成绩的场景。几十个班的成绩表散落在好几张Excel表格里光是把平均分、及格率、优秀率一个个算出来就得加班好几天更别提还得对比班级差距、找出挂科风险学生。我自己就是在那个时候开始写这套成绩分析系统的前后迭代了两个学期现在把完整的设计思路、核心代码和踩过的坑整理出来希望对正在做类似项目的朋友有点帮助。这个系统解决的核心问题很明确把成绩分析从“手动Excel粘贴公式”变成“一条命令全出结果”。它基于Python的pandas做数据处理matplotlib做可视化支持一键读取成绩表、自动清洗数据、计算描述性统计指标、按班级/课程/年级多维度对比、成绩预警和学生风险识别最后自动生成图表和汇总报表。无论你是计算机专业的学生要做课程设计还是教务人员想用自动化减轻工作负担又或者只是想学一下pandas和matplotlib怎么配合完成数据分析任务这篇都能给你一套可以直接复用的完整方案。1. 系统设计思路先想清楚要解决什么问题1.1 需求拆解老师真正关心的是哪些指标很多人一听到“成绩分析系统”就开始埋头写代码结果写完才发现功能跟实际需求对不上。我在动手之前专门跟几位任课老师和辅导员聊了一圈把他们的真实诉求梳理了一遍。老师们最惦记的事情有两类。第一类是常规统计口径包括班级平均分、最高分、最低分、及格率、优秀率这些指标用来快速判断“这个班学得怎么样”。第二类是更深层的教学诊断需求比如某个分数段人数特别密集说明可能大部分学生都卡在某个难点上或者两个平行班的平均分差不多但标准差差了一大截说明班级内部两极分化程度完全不同。如果把这两类需求混在一起做系统就会变成一个只出平均数的小工具价值非常有限。辅导员的关注点又不一样他们更需要的是“哪些学生需要重点关注”。这里的重点不光是成绩不及格的学生还包括成绩波动很大的学生、平时表现不错但某次考试突然崩掉的学生。所以我在设计的时候把成绩预警单独做成一个模块和基础统计分开处理这样不同角色拿到系统之后都能各取所需。1.2 技术选型为什么是pandas而不是Excel VBA说实话Excel自带的VBA也能做成绩分析我第一版方案就是VBA宏写到一半放弃了。原因有三第一VBA的表格操作代码非常啰嗦同样的功能用pandas实现代码量能少一半以上第二VBA跑数据处理逻辑时一旦报错错误提示很晦涩排查起来非常痛苦第三想想后面还要扩展Web界面、对接数据库VBA这套体系基本走不通。Python这边选型就顺理成章了。pandas是Python数据分析的事实标准处理二维表格数据DataFrame能力非常强读写Excel也方便。matplotlib则是Python可视化最成熟的库虽然上手API有些老派但胜在稳定、可定制性强。整个系统的依赖只有三个核心库pandas、matplotlib、openpyxlpandas读取新版Excel文件时需要再配合Python自带的os、datetime做文件路径和时间处理完全没有必要引入重型框架。1.3 整体框架设计数据流是怎么串起来的这个系统的数据流可以概括成五步读入、清洗、计算、可视化、输出。第一步读入pandas直接读取Excel或CSV文件。第二步清洗把缺考标记、空值、异常分数处理掉因为真实世界的数据永远不会规规矩矩。第三步计算按班级、课程、年级等维度做分组聚合算出所有统计指标同时跑一遍预警规则找出需要关注的学生名单。第四步可视化把统计结果绘制成柱状图、饼图、箱线图。第五步输出把分析结果整理成Excel汇总表图表保存成PNG文件还能生成一份简单的文本报告。我把这五步拆成了独立的Python函数每个函数只做一件事。这样做的好处是后期维护特别方便比如老师提了新的统计需求只需要改analyze_data函数的聚合逻辑其他部分完全不用动。整套系统没有一个类完全是函数式风格属于典型的“脚本工具型”项目简单直接对新手非常友好。2. 核心功能模块从数据导入到图表的完整链路2.1 数据导入兼容常见的成绩表格式成绩表这种东西每个老师交上来的格式都不一样有的表头占一行有的表头占两行有的列名叫“分数”有的叫“最终成绩”还有的干脆叫“期末总评”。因此数据导入模块的兼容性直接决定这个系统好不好用。我用的是pandas的read_excel函数它能自动识别最常见的.xlsx格式。关键参数是sheet_name默认读第一个工作表如果有多个工作表还可以通过列表把全部数据合并进来。实际操作中经常遇到的情况是一个老师把不同班次的成绩放在同一个Excel的不同sheet里这时候用pd.concat把多个sheet拼接成一个大DataFrame就行。还有一个细节值得注意读入之后最好统一列名。我在read_data函数里会做一次列名映射把“姓名”和“名字”统一改成“姓名”把“分数”“成绩”“最终成绩”统一改成“成绩”。这个映射逻辑看似简单但能省掉后面分析步骤里大量的if else判断。2.2 数据清洗把脏数据改造成可分析的数据数据清洗是整个流程里最枯燥但最关键的环节也是很多初学者最容易忽略的环节。我的清洗逻辑主要有四步。第一步处理重复值用drop_duplicates把重复记录去掉。这里提醒一下保险的做法是基于学号加课程名加考试时间去重而不是只看整行是否重复。第二步处理非数值内容。成绩列里经常混入“缺考”“缓考”“/”“-”“None”这类字符串直接用pd.to_numeric转成数值类型时会报错或者变成缺失值。我的做法是先用errorscoerce把所有无法转成数字的内容统一变成NaN再单独统计缺失值数量和占比看情况决定是剔除还是保留。第三步处理异常分数。正常成绩范围是0到100如果出现负数或者超过100大概率是录入错误需要人工核对。我一般会把异常值标记出来生成一个exception_list返回而不是直接静默剔除。第四步处理缺考标记。这里要特别小心缺考和考了0分是两个完全不同的概念不能混在一起处理。如果成绩表里明确写了“缺考”我的策略是把该成绩置为NaN但单独保存一个缺考名单后面统计及格率时这部分不计入分母。关于第二点我要强调一下编码问题的处理有些CSV文件是GBK编码直接用pandas读会乱码read_csv函数加上encodinggbk参数就能解决当然如果文件是UTF-8就不要额外指定编码。2.3 统计指标平均分及格率是最基础的标准差更关键成绩分析最常用的指标是平均分、最高分、最低分、及格率、优秀率、标准差这些指标通过pandas的groupby加agg函数可以一次性算完。这里的核心技巧是分组聚合。以班级为单位做统计groupby(班级)就能把同一个班级的所有学生聚到一起然后通过agg传入多个聚合函数。注意及格率和优秀率不能直接用现成的mean、max需要传入自定义lambda函数及格率是成绩大于等于60的人数占比优秀率是成绩大于等于90的人数占比。标准差这个指标经常被忽略但它比平均分更能说明问题。举个例子A班和B班的平均分都是75分但A班标准差是5说明学生成绩比较集中B班标准差是15说明成绩离散程度高有大量低分也有大量高分。如果只看平均分两个班似乎教学效果一样但标准差告诉你B班可能有两极分化的苗头。综合排名这个功能也很实用。我通常会在统计完各班级指标后再按平均分做一个排名排出一个班级综合水平的高低顺序。需要注意的是这个排名容易引起争议所以我会在报告中加一句温馨提示说明这只是基于平均分的粗略对比不代表班级整体教学质量评价。2.4 可视化哪些图最适合展示成绩真正用过matplotlib的人都知道画图本身不复杂难的是选对图表类型。我根据成绩分析的实际需求总结了几种最常用的组合。柱状图最适合展示各班级或各课程的平均分对比一目了然。饼图适合展示及格和不及格的比例关系能直观反映挂科面的大小。直方图适合展示单门课程的成绩分布能看出分数段是不是正态分布教学效果好不好。箱线图是最适合班级间对比的图一张图能同时展示中位数、四分位数、异常值比单纯柱状图信息量多很多。还有一个容易被忽略的细节就是图表的中文字体设置。matplotlib默认字体不支持中文直接绘图会显示方框乱码。需要在绘图前加上两行配置plt.rcParams[font.sans-serif] [SimHei] plt.rcParams[axes.unicode_minus] False这行代码的意思是设置全局字体为黑体同时修复负号显示异常的问题。如果不加这两行柱状图上的班级名称和标题都会乱码这是我第一版脚本踩过最典型的坑。2.5 成绩预警真正能帮到学生工作的功能预警模块是整系统里最能提升使用体验的功能也是普通统计工具里不会有的模块。预警规则我设置了三条。第一条是及格线预警成绩低于60分直接进入预警名单。第二条是退步预警比较同一位学生本次成绩和上次成绩如果下降超过10分即使没挂科也要重点关注因为成绩断崖式下滑往往意味着生活状态出问题了。第三条是多次不及格预警同一个学生如果多门课都不及格需要辅导员重点关注学业风险。退步预警这个逻辑在实现上有个技巧需要先把数据按学号和考试时间排序然后用groupby加shift方法把上一次成绩取到同一行这样就能直接用当前成绩减上次成绩算出波动幅度。这个技术点也值得展开讲讲shift是pandas里特别实用但不太起眼的函数它可以取某列的上一个值或下一个值用在成绩对比场景中非常合适。3. 实操全流程一整套可直接运行的代码3.1 环境准备先花一分钟装好依赖开始写代码之前先把环境准备好。我建议用Python 3.9以上的版本然后用pip安装以下几个库pip install pandas matplotlib openpyxl如果你的电脑已经装了Anaconda那pandas和matplotlib大概率已经有了缺的只是openpyxl单独补装一下就行。安装完成后可以用以下命令验证环境是否正常。import pandas as pd import matplotlib.pyplot as plt print(pd.__version__) print(plt.__version__)如果都能正常打印出版本号说明环境已经就绪。另外建议在项目目录下建两个文件夹一个叫data放原始成绩表一个叫output放分析结果这样文件管理比较清晰。3.2 成绩表格式规范建议先统一再导入为了减少清洗模块的工作量我自己会先做一次源数据规范。一套标准的成绩表应该包含以下几列学号、姓名、班级、课程名称、考试时间、成绩、学分。其中学号用于去重和学生画像跟踪班级用于分组统计课程名称用于多课程分析考试时间用于退步预警。如果只有单次考试成绩考试时间列可以省略但推荐保留因为下学期继续使用时能直接做跨学期对比。成绩列只填写数字缺考情况在备注列里写“缺考”两个字。这样设计的好处是清洗逻辑可以非常稳定读取后先看备注列是否包含“缺考”有就把成绩置为NaN同时把该学生加入缺考名单其他的交给正则表达式统一处理。3.3 代码逐步实现读取、清洗、分析、绘图、报告下面给出完整可运行的代码每一步都有详细注释说明。首先是数据读取模块import pandas as pd import numpy as np import matplotlib.pyplot as plt import os def read_data(file_path): 读取成绩表Excel文件返回DataFrame df pd.read_excel(file_path, sheet_name0, engineopenpyxl) # 统一列名把常见变体映射到标准列名 column_map { 学号: 学号, 学生编号: 学号, 姓名: 姓名, 名字: 姓名, 班级: 班级, 专业班级: 班级, 分数: 成绩, 成绩: 成绩, 最终成绩: 成绩, 总评: 成绩, 课程: 课程名称, 课程名: 课程名称, 考试时间: 考试时间, 年月: 考试时间, } df df.rename(columnscolumn_map) # 只保留标准列其他列自动忽略 required_cols [学号, 姓名, 班级, 成绩] for col in required_cols: if col not in df.columns: raise ValueError(f缺少必要列{col}) print(f成功读取 {len(df)} 条数据) return df然后是清洗模块def clean_data(df): 清洗成绩数据处理缺失值、异常值、重复记录 df df.copy() # 1. 去除完全重复的行 df df.drop_duplicates() # 2. 把成绩列转为数值类型非数值内容变成NaN df[成绩] pd.to_numeric(df[成绩], errorscoerce) # 3. 检查缺失值 missing_count df[成绩].isna().sum() print(f发现 {missing_count} 条缺失或无效成绩) # 4. 检查异常值大于100或小于0 abnormal df[(df[成绩] 100) | (df[成绩] 0)] if len(abnormal) 0: print(f警告发现 {len(abnormal)} 条异常成绩) print(abnormal[[学号, 姓名, 班级, 成绩]].head()) return df然后是统计分析模块这里我加入了自定义的及格率与优秀率计算逻辑def analyze_data(df): 按班级进行分组统计计算核心描述性指标 result df.groupby(班级)[成绩].agg( 人数count, 平均分mean, 最高分max, 最低分min, 中位数median, 标准差std, 及格率lambda x: round((x 60).mean() * 100, 2), 优秀率lambda x: round((x 90).mean() * 100, 2) ).reset_index() # 按平均分降序排列 result result.sort_values(平均分, ascendingFalse).reset_index(dropTrue) # 生成综合排名 result.insert(0, 排名, range(1, len(result) 1)) return result然后是成绩预警模块退步预警的逻辑稍微复杂一些def warning_analysis(df, fail_line60, drop_threshold10): 成绩预警不及格预警 退步预警 # 不及格预警 fail df[df[成绩] fail_line].copy() fail fail.sort_values([班级, 成绩]) # 退步预警需要上一次成绩数据 df_sorted df.sort_values([学号, 考试时间]) df_sorted[上次成绩] df_sorted.groupby(学号)[成绩].shift(1) drop df_sorted.dropna(subset[上次成绩]).copy() drop[退步幅度] drop[上次成绩] - drop[成绩] drop drop[drop[退步幅度] drop_threshold] drop drop[[学号, 姓名, 班级, 课程名称, 上次成绩, 成绩, 退步幅度]] return fail, drop然后是可视化模块def plot_charts(result, save_diroutput): 生成核心可视化图表并保存 os.makedirs(save_dir, exist_okTrue) plt.rcParams[font.sans-serif] [SimHei] plt.rcParams[axes.unicode_minus] False # 1. 各班级平均分柱状图 fig, ax plt.subplots(figsize(12, 6)) bars ax.bar(result[班级], result[平均分], colorsteelblue, width0.6) ax.set_title(各班级平均分对比, fontsize14) ax.set_xlabel(班级) ax.set_ylabel(平均分) ax.set_ylim(0, 100) # 在柱子上标注具体数值 for bar in bars: height bar.get_height() ax.text(bar.get_x() bar.get_width()/2., height 0.5, f{height:.2f}, hacenter, vabottom, fontsize9) plt.tight_layout() plt.savefig(f{save_dir}/class_avg_compare.png, dpi150) plt.show() # 2. 各班级成绩分布箱线图 # 注意这里需要原始明细数据才能画箱线图result只有聚合数据 # 真正的实现需要把明细数据传进来这里给出思路 return上面这段代码里我留了一个说明箱线图需要原始明细数据函数签名需要带完整数据。我在实际项目中把plot_charts拆成了两个函数一个draw_compare_bar接收聚合结果一个draw_box_by_class接收原始明细。然后是报告生成模块把所有分析结果写入一个Excel文件def generate_report(result, fail, drop, save_diroutput): 把分析结果写入Excel汇总文件 file_path f{save_dir}/成绩分析报告.xlsx with pd.ExcelWriter(file_path, engineopenpyxl) as writer: result.to_excel(writer, sheet_name班级统计, indexFalse) fail.to_excel(writer, sheet_name不及格名单, indexFalse) drop.to_excel(writer, sheet_name退步预警, indexFalse) print(f报告已保存到{file_path}) return file_path最后是主流程函数把所有模块串起来def main(file_path): df read_data(file_path) df_clean clean_data(df) result analyze_data(df_clean) fail, drop warning_analysis(df_clean) chart_dir output plot_charts(result, df_clean, save_dirchart_dir) report_path generate_report(result, fail, drop, save_dirchart_dir) print(分析完成请查看output目录下的报告和图表。) return result, fail, drop3.4 运行效果与输出结果我用一份模拟数据测试过这个系统。假设有一张成绩表包含3个班共90条记录每条记录有学号、姓名、班级、课程名称、成绩、考试时间六列。运行main函数后控制台会打印出读取记录数、缺失值数量、异常值警告然后在output目录下生成一个Excel报告和两张图表。Excel报告里能看到三个班的排名、平均分从高到低排列并附带及格率、优秀率、标准差等指标同时还有单独的sheet列出不及格学生和退步预警学生名单。把图打开之后柱状图能直观看出某个班的平均分明显领先或者落后箱线图能看出某班成绩分布的离散程度。这个过程比人工翻Excel快太多了原来要花一整个下午的工作现在跑一次脚本只需要几秒钟。4. 开发踩坑实录环境、数据、分组计算的疑难杂症4.1 依赖安装与文件读取报错很多刚接触pandas读Excel的同学都会在第一步卡住明明安装了pandasread_excel依然报错。ModuleNotFoundError: No module named openpyxl这个错误非常典型。pandas读取Excel文件需要一个底层引擎读.xls旧格式需要xlrd读.xlsx新格式需要openpyxl。pandas本身不打包这些依赖所以必须单独安装。解决办法很简单pip install openpyxl还有一个容易踩的坑是pandas的read_excel函数默认引擎会根据文件扩展名自动选择如果你的环境中xlrd和openpyxl都装了但文件是.xlsxpandas会优先使用openpyxl如果是.xls文件则会用xlrd。有些老版本xlrd只支持.xls不支持.xlsx会报“Excel xlsx file; not supported”的错误。遇到这种情况升级xlrd到2.0以上版本或者干脆统一把所有文件都转成.xlsx再处理。4.2 中文显示与编码问题matplotlib中文乱码问题我在前面提过这里展开讲一下彻底解决方案。SimHei字体在Windows系统下基本都有但在Linux服务器上不一定存在。如果你部署在Linux环境需要先确认系统里有没有中文字体可以通过以下代码查看当前matplotlib能识别的所有字体from matplotlib import font_manager fonts [f.name for f in font_manager.fontManager.ttflist if Hei in f.name or Song in f.name] print(fonts)如果没有中文字体一个简单的办法是下载一个开源中文字体文件放到代码目录里然后动态注册到matplotlibimport matplotlib matplotlib.font_manager.fontManager.addfont(fonts/NotoSansSC-Regular.ttf) plt.rcParams[font.sans-serif] [Noto Sans SC]编码问题不只在绘图时出现。CSV文件的读取也经常遇到乱码尤其是从国内一些教务系统导出的CSV默认编码可能是GBK或者GB2312。pandas读取时用encodinggbk就能解决。如果文件编码不确定可以用一个非常小的探测函数先猜编码不过我试下来比较费劲所以还是建议都转成UTF-8格式的文件统一处理。4.3 groupby的索引与列名陷阱pandas的groupby用起来方便隐藏的坑也不少。第一个坑是groupby之后的结果是一个GroupBy对象不是DataFrame必须配合agg、apply、transform方法使用。新手经常直接打印groupby对象然后发现只有内存地址误以为代码坏了。第二个坑是groupby加agg之后结果的索引是分组依据的列。比如按班级分组后索引是班级名如果后面要和其他DataFrame做merge操作必须先用reset_index把班级变成普通列否则合并时会因为索引不匹配报错。第三个坑是agg时如果传入多个聚合函数列名会变成元组比如(成绩, mean)这样。这种列名在后续代码里直接访问会有问题我通常会在agg之后用rename把列名改回去或者直接用字典传参的方式指定输出列名。上面代码里用了agg(人数count, 平均分mean)这种写法输出列名就是“人数”“平均分”不会出现元组推荐大家用这种方式。4.4 数据量大时的性能表现高校单次考试的成绩数据一般不会太大一个年级几百到几千条记录pandas处理起来毫无压力。但如果把几年的历史数据全部导入数据量达到几万甚至几十万条就要注意两个问题了。第一个问题是重复绘图导致的内存占用。在循环里绘制大量图表时如果每张图都plt.show()但不用plt.close()matplotlib会把所有Figure对象保存在内存中程序跑半个小时内存可能就爆了。我习惯用plt.savefig保存后立刻plt.close()释放内存。第二个问题是groupby在大量数据下依然很快但lambda函数会拖慢速度。尤其是及格率这种需要逐行判断的聚合逻辑数据量大了以后效率会下降。优化办法是提前生成一个布尔列比如df[是否及格] df[成绩] 60然后agg时直接对该列求平均就不需要跑lambda了。这个优化在几十万条数据上效果立竿见影。5. 后续还能怎么玩扩展方向与升级路径5.1 从命令行脚本升级为Web服务这个系统目前是命令行脚本形式已经能满足日常分析需求。但如果想给更多同事用做成Web应用就是必经之路。我建议用Flask或FastAPI包装一层HTTP接口前端用最简单的HTML表单实现文件上传后端接收文件后调用已有的分析函数把生成的图表和Excel报告返回给浏览器下载。FastAPI在这几年非常流行它天然支持文件上传下载、异步处理而且自动生成接口文档开发效率很高。如果想做更复杂的交互比如选班级、选课程、动态切换统计维度可以引入一个简单的表格组件或者图表库前端和后端之间通过JSON传递数据。做成Web服务的好处是非技术用户不需要安装Python环境直接用浏览器就能上传成绩表、查看结果。考虑给系里的老师试用的话这是最理想的形态。5.2 对接数据库与历史数据积累第二个值得扩展的方向是数据库。当前系统每个学期的成绩表都是独立的Excel文件跨学期对比需要手动把数据合并效率不高。如果引入SQLite或者MySQL把成绩明细存入数据库再按学期、课程、班级做查询分析系统的能力上限会大很多。SQLite是最轻量级的选择整个数据库就是一个单一文件不需要额外部署数据库服务非常适合单机个人工具。想升级的话代码改动也不大pandas的read_sql_query函数可以直接读取SQL查询结果为DataFrame后面分析模块完全不用改。有了数据库之后就能做更有价值的分析了。比如跟踪同一个班级在多个学期的成绩走势判断整体是在进步还是退步再比如分析同一门课程在不同教师授课下的成绩差异做教学参考。5.3 更多维度的教学分析场景除了基础的成绩统计还有几个教学分析场景可以考虑。第一个是学分绩点分析。如果成绩表里有学分列可以按学分加权计算绩点这个指标比平均分更能反映学生的学习质量。高学分课程的成绩差对总绩点的影响远大于低学分课程所以要按学分加权处理。第二个是难度分析。通过比较同一门课程不同年份的成绩分布可以评估试卷难度是否稳定。如果今年的成绩分布比去年左偏平均分明显下降可能是试卷偏难也可能是生源质量变化这些都能从数据上看出线索。第三个是关联分析。如果有多门课程的成绩数据可以计算课程之间的相关性看看哪些课程学得好的学生在其他课程上也普遍学得好。这种分析对教学安排的优化有参考价值。我个人在实际开发中最大的体会是这个系统能真正解决效率问题。以前我帮系里做期末成绩分析光是把不同格式的成绩表整理成统一模板、人工核对数据、用Excel透视表做统计前后要折腾好几天。现在从拿到成绩表到输出完整分析报告整个过程不到十分钟而且不会再出现复制粘贴公式时不小心选错区域的那种低级错误。如果未来能把Web界面和数据库补齐这个工具就能在更大范围内帮助更多老师。
