实践-汇总大量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乘以比例就得到转转的成绩了。
$$newscore=\frac{rawscore.sum}{maxrawsocre}\ast ratio\ast 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达到批量处理的效果。