本文介绍的是利用Python语言，做成绩分析并生成成绩分析动态图表。Python语言可以利用Pandas、Pyecharts等各种类库，进行数据分析。
本文介绍的成绩分析大体分为三步：
一、拼合单科成绩，合成学年成绩，计算总分，按总分成绩排名次，然后由学年成绩筛选出各个班级的成绩，将学年成绩，各班级成绩存入一个Excel文件中，工作表分别命名为学年成绩，高三（1）班……等
二、利用生成的第一步生成的Excel文件，做成绩分析，保存成绩分析表格。
三、利用成绩分析表格，做成绩分析动态图。
下面是部分源代码：
1、成绩整理与合并
import glob
import os
import pandas as pd
from functools import reduce
inputPath="./原始成绩/"
writer_lk = pd.ExcelWriter('./整理后的成绩/2020一模理科总成绩及各班级成绩.xlsx')
writer_wk = pd.ExcelWriter('./整理后的成绩/2020一模文科总成绩及各班级成绩.xlsx')
inputWorkbook=glob.glob(os.path.join(inputPath,"*.xls"))
#====================读取全部学生的所有科目成绩===================================
yw_score = pd.read_excel(inputWorkbook[2])
sxlk_score = pd.read_excel(inputWorkbook[1])
sxwk_score = pd.read_excel(inputWorkbook[0])
yy_score = pd.read_excel(inputWorkbook[5])
yy_score['英语'] = (yy_score['英语'] * 1.25).round(0)#英语成绩不计算听力成绩*1.25
lkzh_score = pd.read_excel(inputWorkbook[4])
wkzh_score = pd.read_excel(inputWorkbook[3])
#=======================================================================
#====================整理出理科成绩及分班成绩、计算总分、总分排名、班级排名=============================
lk_class = ['高三（1）班','高三（2）班','高三（3）班','高三（4）班']
wk_class = ['高三（5）班','高三（6）班']
lk_yw = yw_score.loc[(yw_score.班级.isin(lk_class)), ['班级','姓名','语文']]
lk_sx = sxlk_score[['姓名','数学']]
lk_yy = yy_score.loc[(yy_score.班级.isin(lk_class)), ['姓名','英语']]
lk_k3 = lkzh_score[['姓名','物理','化学','生物','理综']]
lk_list = [lk_yw, lk_sx, lk_yy, lk_k3]
score_lk = (reduce(lambda left, right: pd.merge(left, right, on='姓名'), lk_list))
score_lk['总分'] = (score_lk['语文'] + score_lk['数学'] + score_lk['英语'] + score_lk['理综']).round(0)
def sort_grade(score):
score_sort = score.sort_values(by=['总分'], ascending=False)
score_sort['年级排名'] = score_sort['总分'].rank(ascending=0,method='min')
return score_sort
def sort_class_lk(score_garde,name):
class_sort = score_garde.loc[score_garde.班级 == name, :]
class_sort = class_sort.sort_values(by=['总分'], ascending=False)
class_sort['班级排名'] = class_sort['总分'].rank(ascending=0,method='min')
class_sort.to_excel(writer_lk, index=None, sheet_name=name)
lk_grade_sort = sort_grade(score_lk)
lk_grade_sort.to_excel(writer_lk, index=None, sheet_name='学年成绩')
for lk in lk_class:
class_sort = sort_class_lk(score_lk, lk)
writer_lk.save()
writer_lk.close()
# #============整理出文科成绩及分班成绩、计算总分、总分排名、班级排名==================
wk_yw = yw_score.loc[(yw_score.班级.isin(wk_class)), ['班级','姓名','语文']]
wk_sx = sxwk_score[['姓名','数学']]
wk_yy = yy_score.loc[(yy_score.班级.isin(wk_class)), ['姓名','英语']]
wk_k3 = wkzh_score[['姓名','政治','历史','地理','文综']]
wk_list = [wk_yw, wk_sx, wk_yy, wk_k3]
score_wk = (reduce(lambda left, right: pd.merge(left, right, on='姓名'), wk_list))
score_wk['总分'] = (score_wk['语文'] + score_wk['数学'] + score_wk['英语'] + score_wk['文综']).round(0)
def sort_class_wk(score_garde,name):
class_sort = score_garde.loc[score_garde.班级 == name, :]
class_sort = class_sort.sort_values(by=['总分'], ascending=False)
class_sort['班级排名'] = class_sort['总分'].rank(ascending=0,method='min')
class_sort.to_excel(writer_wk, index=None, sheet_name=name)
wk_grade_sort = sort_grade(score_wk)
wk_grade_sort.to_excel(writer_wk, index=None, sheet_name='学年成绩')
for wk in wk_class:
class_sort = sort_class_wk(wk_grade_sort, wk)
writer_wk.save()
writer_wk.close()
2、成绩区间分割与统计
#coding:utf-8
import numpy as np
import pandas as pd
from functools import reduce
fpath_lk="./整理后的成绩/2020一模理科总成绩及各班级成绩.xlsx"
fpath_wk="./整理后的成绩/2020一模文科总成绩及各班级成绩.xlsx"
writer_lk = pd.ExcelWriter('./整理后的成绩/2020一模理科成绩区间分布统计.xlsx')
writer_wk = pd.ExcelWriter('./整理后的成绩/2020一模文科成绩区间分布统计.xlsx')
lk = pd.read_excel(fpath_lk, None) #获取表格中的所有工作表的内容
wk = pd.read_excel(fpath_wk, None)
#===================1.定义区间分割函数=====================================
def cut_750(score_750,len):
bins_750= [0,370,380,390,400,410,420,430,440,450,460,470,480,490,500,510,520,530,540,550,560,570,580,590,600,620,640,660,750]
labels_750 = ['0-370','370-379','380-389','390-399','400-409','410-419','420-429','430-439','440-449','450-459','460-469','470-479','480-489','490-499','500-509','510-519','520-529','530-539','540-549','550-559','560-569','570-579','580-589','590-599','600-619','620-639','640-659','660-750']
cut_750 = pd.cut(score_750, bins_750, labels=labels_750, right=False)
qj = pd.DataFrame({'区间':pd.value_counts(cut_750).index,'人数':pd.value_counts(cut_750),'百分比':((pd.value_counts(cut_750))/len).round(3).apply(lambda x: format(x, '.2%'))}).sort_values(by='区间', ascending=False)
qj = qj.reset_index(drop=True)
return qj
def cut_150(score_150,len):
bins_150 = [0,30,60,90,120,150]
labels_150 = ['0-30', '30-60', '60-90', '90-120', '120-150']
cut_150 = pd.cut(score_150, bins_150, labels=labels_150, right=False)
qj = pd.DataFrame({'区间':pd.value_counts(cut_150).index,'人数':pd.value_counts(cut_150),'百分比':((pd.value_counts(cut_150))/len).round(3).apply(lambda x: format(x, '.2%'))}).sort_values(by='区间')
其他源代码及始数据已上传，欢迎各位借鉴，第一次编程，希望网友们能指点不足之处，联系qq:912182988
1588
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
