实践-汇总大量EXCEL-Pandas
1.背景介绍
写这篇文章出于两个目的:
第一:肯定是想介绍一下标题的方法。
第二:想尝试着用markdown来写博客,顺便学习一下这个工具。
背景:
最近我导师要我帮忙在学习通上,导出他教的某两个课程(一个理论,一个实验)的各8次作业成绩表和8次签到表,1次期中成绩,1次期末成绩。也就是 实验课 有18张表汇总成一个统计表, 理论课 18张表汇总成一个统计表,总共要处理共36张表。然后把成绩复制到统计表中,要对应好每一个学号的学生。这些表都是按一定规则进行排序的,所以每个表的排序情况是不一样的,把每张表对应的学生的成绩复制到汇总表的中,难度和工作量非常大。首先我不可能老实到每一张表去找一个学生的成绩再复制到汇总表,那也太麻烦了。
2.手动复制到excel
本来一开始我就想用代码,使用pandas来处理的。但是仔细想了一想办法,发现手动的工作量应该也不是很大。首先我进行了以下操作:
2.1 处理统计表
从某张表中首先把学号,姓名复制出来放到统计表中,在excel中按学号升序排序;
2.2 处理作业表
在每一张表中也按学号升序排序,把作业成绩那一列复制到总表里。问题不大!!
2.3 处理签到表
麻烦就开始来了!
1. 前几行有多余的行,得先删除才能排序。
2. 以下图片中的值都是文字表示的签到状态,我总不可能一个个手动更改成数字成绩吧。
其中没有成绩,只有几种状态的文字,所以调用 excel函数:
=IF(OR(F2="已签",F2="教师代签"),1,IF(F2="签到已过期",0.5,IF(F2="缺勤",0,)))
OK!搞定复制到统计表。但是这只是一张表,我还有7张+8张 。。ToT。。
2.4 期中,期末成绩表
同样删掉前几各无关行,按学号排序,取成绩,复制到统计表。
2.5 汇总-总成绩
8个作业(每门100分)占总成绩的16%,签到占8%,期中成绩占%30,期末成绩占46%。咋把这个成绩按比例转换汇总到总成绩呢?
想了想就是尺度缩放,有点像机器学习里面的归一化的思想。
比如作业:先把8门成绩加起来,再除以最大800分,得到一个比例,然后 16%最大占总成绩16分,用16乘以比例就得到转转的成绩了。
n
e
w
s
c
o
r
e
=
r
a
w
s
c
o
r
e
.
s
u
m
m
a
x
r
a
w
s
o
c
r
e
∗
r
a
t
i
o
∗
m
a
x
n
e
w
s
c
o
r
e
newscore=\frac{rawscore.sum}{maxrawsocre} *ratio*maxnewscore
newscore=maxrawsocrerawscore.sum∗ratio∗maxnewscore
=(SUM(C4:J4)*16/800)+SUM(K4:R4)+(S4*30/100)+(T4*46/100)
以上,处理完毕!!从开始到找方法,操作解决各种问题,花了我将近4个小时才完成。 要是再来一次肯定就快了,大概一到两个小时的样子吧。1个课程作业就2个小时,要是我导师把他教的课程,期末成绩都丢给我,岂不是人都要搞傻。。。
3.使用Python+Pandas处理excel文件
痛定思痛,避免后面花过多时间折腾,还不如一次就使用代码来批处理搞定,这样你丢再多,咱也接着。还能避免导师查岗,摸摸鱼。爽!开干!
3.1 Pandas-读取excel
3.1.1 读取总表
statistic_table=pd.read_excel('./总评成绩计算表.xlsx',sheet_name='理论')
第一个参数:路径+文件名
第二个参数(可选):Excel的Sheet名,默认是选择该表第一个sheet
3.1.2 读取作业表,签到表
#读取作业表
#使用list来将读取出的表暂存起来
assignments=[]
for ass_i in range(1,9):
ass_table=pd.read_excel('./intro/ass{0}.xls'.format(ass_i))
assignments.append(ass_table)
3.1.3 读取期中,期末表
#读取期中表
midterm=pd.read_excel('./intro/Midterm.xls')
#读取期末表
finalterm=pd.read_excel('./intro/Final.xls')
3.2 预处理
因为表的结构不是标准结构,可以看到前面都是很乱的,要么自己手动处理原表删掉不相干的行,要么使用索引来操作。由于习惯使用numpy我倾向于后者。
不同表对应的统计表的列号
#统计表中列名对应的列
name2index=dict()
#作业
for i in range(2,10):
name2index['ass{}'.format(i-1)]=i
#签到
for i in range(10,18):
name2index['sign{}'.format(i-9)]=i
name2index['mid']=18
name2index['final']=19
name2index['total']=20
name2index
3.3 Pandas索引切片
pd.iloc[raw,line]
raw:行号
line:列号
使用参考:
https://blog.csdn.net/w_weiying/article/details/81411257
3.4 Pandas遍历
pd .iteritems()
将DataFrame迭代为(列名, Series)对
使用参考:
https://www.cnblogs.com/keye/p/9673393.html
3.5 Pandas查找并取出某值所在的行
table.isin([name]),查找name所在的索引,返回bool值
table.iloc[:,1].isin([name]):查找table第一列name所在的行号
table[table.iloc[:,1].isin([name])]:将找到的name行号,将该所在行值取出来。
3.6 Pandas 给表赋值
table.iloc[i,j]=val
给表i和j位置赋值为val
3.7 综合代码
#先取出2至最后一行的第0列,也就是取出所有学号
sid_table=statistic_table.iloc[2:,0]
#遍历表,对应每一个学号所在的行号以及学号
for sid_ix,sid in sid_table.iteritems():
#遍历学号
#统计表的行值:sid_ix
ass_scores=[] #用来保存该学号的所有作业成绩
#遍历作业表把对应成绩放到对应列
for ass_ix,ass_table in enumerate(assignments):
#该表在统计表的列值
name_ix=name2index['ass{}'.format(ass_ix+1)]
#取出每个表对应学号的作业成绩
score=float(ass_table[ass_table.iloc[:,1].isin([sid])].iloc[:,9].values[0])
ass_scores.append(score)
#将对应值插入统计表中
statistic_table.iloc[sid_ix,name_ix]=score
后面的表操作都差不多了,按照代码仔细debug,把对应表的值都取出来然后赋值给总表,就ok了。
总结:
本篇博客的内容主要是来自我自己处理excel的真实案例,首先介绍如何手动操作excel,通过一些技巧批量处理和复制。随后使用python,利用pandas的一些操作来处理excel达到批量处理的效果。