import pandas as pd
# 读取两张表格
new_df = pd.read_excel("本次成绩.xlsx")
old_df = pd.read_excel("上次成绩.xlsx")
# 拷贝一份要修改的数据,以免破坏源数据
ndf = new_df.copy()
# 首先将不在'上次成绩.xlsx'中的人直接修改'对比上次'字段为'上次缺席'
ndf['对比上次'][~ndf['姓名'].isin(old_df['姓名'].values)] = '上次缺席'
# 循环遍历'上次成绩.xlsx'中的每一行
for i in old_df.itertuples():
old_name = getattr(i, '姓名')
old_score = getattr(i, '上次成绩')
'''
当'本次成绩.xlsx'中的名字与 old_name 相同时
对比'本次成绩'与 old_score 的大小并修改'对比上次'为对应值
'''
ndf.loc[(ndf['姓名'] == old_name) &(ndf['本次成绩'] >old_score), '对比上次'] = '好'
ndf.loc[(ndf['姓名'] == old_name) &(ndf['本次成绩'] == old_score), '对比上次'] = '持平'
ndf.loc[(ndf['姓名'] == old_name) &(ndf['本次成绩'] <old_score), '对比上次'] = '差'
# 导出到新表格并隐藏索引列
ndf.to_excel('对比.xlsx', index=False)
仅供参考,请根据实际情况自行修改优化代码。
用SQL语句直接比对以上,是使用python操作数据库,好处是可视、可控,便于调试,便于控制结果输出,不足之处是,由于两次遍历全部数据,导致比对效率低。我们也可以直接使用SQL语句,完成两个表之前对某一列数据的比对。
无论是什么工具,做数据分析的时候一定会涉及到两类工作:
这篇文章简单对比一下Excel、SQL和Python在这两类任务上的实现过程,从而对比其异同。
如图所示,所涉及的共有三个表:
可以看到,score表通过sno和student表连接、通过cno和course表连接。
另外,这张截图截自Excel,主要是为了方便后面Excel部分的讨论。
现在,我想要合并三张表,得到新表merge_table,表包含的列一次为:sno,cno,degree,sname,cname。
即,新表中包含score表的所有列,student表的sname列,以及course表的cname列。
为了讨论方便,先上结果:
首先,在 A17:E17 单元格创建所需列名,然后通过简单复制粘贴得到 A18:C28 这三列的数据。
D、E列的数据可以通过以下两种方法实现:
两种方法实现逻辑和结果都一样,但前者调用的时候比后者稍复杂。为了说明,D列数据的提取我使用了方法1,E列数据的提取我使用了方法2。
D列:
首先在 D18 单元格输入以下函数(函数中的单元格所对应的数据请看图01)
接着下拉函数至 D28 。
E列:
在 E18 单元格输入以下函数(函数中的单元格所对应的数据请看图01)
接着下拉函数至 E28 。
注意,如果要提取某个表中的多个列的数据,比如除了sname,我还想得到ssex、sbirthday和class的数据,由于这些列是一同储存在student表中的,用 VLOOPKUP() 显然更高效。
如果想要加快效率,还可以在原student表上新增一行,用数字x来表示第x列,然后在调用 VLOOPKUP() 时,直接把第三个参数指向这一行。
在合并关联表上,SQL非常便捷。实现的语句有两个(先创建或者导入原数据表):
或
两种方法返回的结果相同,结果如下:
我用的MySQL,不知道为什么合并后行的顺序变了=。=
在Python中,首先导入 numpy 和 pandas 模块:
接着导入数据表。
之后通过以下语句实现merge_table表的建立:
结果如下:
现在假设score表多了一行数据:
如图所示,蓝色部分为多出的数据,且课程6-106在course表中不存在。请无视逻辑问题,主要是为了方便讨论:)
遇到这种情况,上述的实现方法会出现一个问题:
因为课程号6-106在course表里并不存在,所以函数在返回值的时候出错了。
解决的办法有一个,就是在原函数上嵌套 IF() 函数。比如我把 E29 的函数更改为:
如果函数计算结果错误,则返回0。
在SQL中,如果出现此类情况, LEFT JOIN 会返回NULL值:
如果想把NULL值替换为0,查询合并表的时候可以加上 isnull() 函数(MySQL中此函数写作 ifnull() ):
如果函数计算结果错误,则返回0
返回结果和Excel的差不多,就不上图了。
Python中情况类似:
如果想把NaN值替换为0,只需要在创建merge_table表之后,添加一行语句:
返回结果也不上图了,和Excel的一样。
面对合并表中数据不匹配,SQL和Python中都可以在合并表的时候把多出项忽略不计,只要把 LEFT JOIN 换成 INNER JOIN 就行了。但Excel不能自动删除多出项所在行。
为了方便,现在做一个透视表,该表返回 选了课的同学的学号和其平均课程成绩 。
三个软件对于透视表的实现都很友好,并且效率相近。
Excel在数据透视表工具下把列各种拖拽就行了。
另外,Excel的数据透视表可以选择返回合计(Grand Total)或者不返回。
语句:
结果:
语句:
结果:
一般做透视表的最终目的是作图,毕竟一图胜千语。
从这个目的出发,Python比SQL、Excel更实用,一来Python比Excel作图高效很多,二来SQL不能作图。
通过上述对比可以发现,Excel合并关联表比SQL、Python要低效得多,而且在“数据不匹配”问题上解决得不好;而在另一方面,三者在创建透视表上表现相似,就看你习惯用哪个了:)