前言
无论是什么工具，做数据分析的时候一定会涉及到两类工作：
- 合并多个关联表
- 做数据透视表
这篇文章简单对比一下Excel、SQL和Python在这两类任务上的实现过程，从而对比其异同。
用到的数据表
01
如图所示，所涉及的共有三个表：
- student：sno 学生学号，sname 学生姓名，ssex 性别，sbirthday 学生生日，class 学生所在班级号；
- course：cno 课程号，cname 课程名；
- score：sno 学生学号，cno 学生选修课程对应的课程号，degree 学生所选课程对应的课程分数。
可以看到，score表通过sno和student表连接、通过cno和course表连接。
另外，这张截图截自Excel，主要是为了方便后面Excel部分的讨论。
合并多个关联表
现在，我想要合并三张表，得到新表merge_table，表包含的列一次为：sno，cno，degree，sname，cname。
即，新表中包含score表的所有列，student表的sname列，以及course表的cname列。
1. Excel
为了讨论方便，先上结果：
02
首先，在A17:E17单元格创建所需列名，然后通过简单复制粘贴得到A18:C28这三列的数据。
D、E列的数据可以通过以下两种方法实现：
- 使用
INDEX()+MATCH()函数； - 使用
VLOOKUP()函数。
两种方法实现逻辑和结果都一样，但前者调用的时候比后者稍复杂。为了说明，D列数据的提取我使用了方法1，E列数据的提取我使用了方法2。
D列：
首先在D18单元格输入以下函数（函数中的单元格所对应的数据请看图01）
=INDEX($B$3:$B$8,MATCH(A18,$A$3:$A$8,0))
接着下拉函数至D28。
E列：
在E18单元格输入以下函数（函数中的单元格所对应的数据请看图01）
=VLOOKUP(B18,$G$3:$H$6,2,0)
接着下拉函数至E28。
注意，如果要提取某个表中的多个列的数据，比如除了sname，我还想得到ssex、sbirthday和class的数据，由于这些列是一同储存在student表中的，用VLOOPKUP()显然更高效。
如果想要加快效率，还可以在原student表上新增一行，用数字x来表示第x列，然后在调用VLOOPKUP()时，直接把第三个参数指向这一行。
2. SQL
在合并关联表上，SQL非常便捷。实现的语句有两个（先创建或者导入原数据表）：
SELECT score.*, sname, cname FROM score, course, student
WHERE score.sno = student.sno AND score.cno = course.cno;
或
SELECT score.*, sname, cname FROM score
LEFT JOIN student USING(sno)
LEFT JOIN course USING(cno);
两种方法返回的结果相同，结果如下：
03
我用的MySQL，不知道为什么合并后行的顺序变了=。=
3. Python
在Python中，首先导入numpy和pandas模块：
import numpy as np
import pandas as pd
接着导入数据表。
之后通过以下语句实现merge_table表的建立：
merge_table = pd.merge(score, student[['sno','sname']], on = 'sno', how = 'left')
merge_table = merge_table.merge(course[['cno','cname']], on = 'cno', how = 'left')
结果如下：
04
如果合并的表中数据不匹配怎么办
现在假设score表多了一行数据：
05
如图所示，蓝色部分为多出的数据，且课程6-106在course表中不存在。请无视逻辑问题，主要是为了方便讨论:）
1. Excel
遇到这种情况，上述的实现方法会出现一个问题：
06
因为课程号6-106在course表里并不存在，所以函数在返回值的时候出错了。
解决的办法有一个，就是在原函数上嵌套IF()函数。比如我把E29的函数更改为：
=IF(ISERROR(VLOOKUP(B29,$G$3:$H$6,2,0)),0,VLOOKUP(B29,$G$3:$H$6,2,0))
如果函数计算结果错误，则返回0。
07
2. SQL
在SQL中，如果出现此类情况，LEFT JOIN会返回NULL值：
08
如果想把NULL值替换为0，查询合并表的时候可以加上isnull()函数（MySQL中此函数写作ifnull()）：
SELECT score.*, isnull(sname,0), isnull(cname,0) FROM score
LEFT JOIN student USING(sno)
LEFT JOIN course USING(cno);
如果函数计算结果错误，则返回0
返回结果和Excel的差不多，就不上图了。
3. Python
Python中情况类似：
09
如果想把NaN值替换为0，只需要在创建merge_table表之后，添加一行语句：
merge_table = merge_table.fillna(0)
返回结果也不上图了，和Excel的一样。
4. PS
面对合并表中数据不匹配，SQL和Python中都可以在合并表的时候把多出项忽略不计，只要把LEFT JOIN换成INNER JOIN就行了。但Excel不能自动删除多出项所在行。
数据透视表
为了方便，现在做一个透视表，该表返回选了课的同学的学号和其平均课程成绩。
三个软件对于透视表的实现都很友好，并且效率相近。
1. Excel
10
Excel在数据透视表工具下把列各种拖拽就行了。
另外，Excel的数据透视表可以选择返回合计（Grand Total）或者不返回。
2. SQL
语句：
SELECT sno, ROUND(AVG(degree),2) AS 'mean' FROM score
GROUP BY sno;
结果：
11
3. Python
语句：
score.groupby(['sno'])['degree'].agg(['mean']).reset_index()
结果：
12
一般做透视表的最终目的是作图，毕竟一图胜千语。
从这个目的出发，Python比SQL、Excel更实用，一来Python比Excel作图高效很多，二来SQL不能作图。
总结
通过上述对比可以发现，Excel合并关联表比SQL、Python要低效得多，而且在“数据不匹配”问题上解决得不好；而在另一方面，三者在创建透视表上表现相似，就看你习惯用哪个了:)
网友评论