作为一名数据分析师,平常用的最多的工具是SQL(包括MySQL和Hive SQL等)。对于存储在数据库中的数据,自然用SQL提取会比较方便,但有时我们会处理一些文本数据(txt,csv),这个时候就不太好用SQL了。Python也是分析师常用的工具之一,尤其pandas更是一个数据分析的利器。虽然二者的语法,原理可能有很大差别,但在实现的功能上,他们有很多相通的地方,这里特进行一个总结,方便大家对比学习~
本次学习的数据是虚构的订单数据,和实际业务无关,目的只是为了学习。大概长下面这样子,分别表示,自增id,订单时间,用户id,订单id,订单金额。
![9d6fe4261f75c02da06356237657623f.png](https://i-blog.csdnimg.cn/blog_migrate/a5dffab285b8e75991c93015658c2df7.jpeg)
我们将用pandas和SQL来实现同样的目标,以此来联系二者,达到共同学习的目的。
准备工作:
- pandas准备,我们本次采用jupyter notebook进行演示。import pandas as pdorder_data = pd.read_csv('order.csv')
- SQL 准备只需将我提供的SQL文件运行一下即可将数据插入数据库表中。推荐使用navicate客户端连接数据库。
![c715683071292da3726fe70ecd4b1223.png](https://i-blog.csdnimg.cn/blog_migrate/c6e21ddd99215f591e6b03aa463fd6fc.jpeg)
开始学习
1.查看全部数据或者前n行数据
查看全部数据,pandas中直接打印dataframe对象即可,此处是order_data。而在SQL中,需要执行的语句是select * from t_order;表示从t_order表中查询全部的数据,*号表示查询所有的字段。结果如下:(点击图片可以查看大图)
![bb8e6bd8b9a844a26a32bee261b945d5.png](https://i-blog.csdnimg.cn/blog_migrate/b6df98b38c88a1aa16106562a6e80b27.jpeg)
如果只想查看前10行数据呢。pandas可以调用head(n)方法,n是行数。MySQL可以使用limit n,n同样表示行数。(点击图片可以查看大图)
![559a6c8554fe2a7530b61adcd60ad212.png](https://i-blog.csdnimg.cn/blog_migrate/fbabc683e7e52eb2ba9521d1a4ca1416.jpeg)
2.查询特定列的数据
有的时候我们只想查看某几列的数据。在pandas里可以使用中括号或者loc,iloc等多种方式进行列选择,可以选择一列或多列。loc方式可以直接写列名,iloc方式需要指定索引,即第几列。SQL里只需写相应的列名即可,举例如下,实际操作一下更容易理解,选择一种自己习惯的即可。(点击图片可以查看大图)
![77c02329c9e8002a49d1efa284ffdb10.png](https://i-blog.csdnimg.cn/blog_migrate/41f37ee20f3c5512c2a8aa4e1bc43efe.jpeg)
![659c5a7632b1b6ff3bb4934651ba0d62.png](https://i-blog.csdnimg.cn/blog_migrate/e3b5616f03348cdff03013f1683b4e6b.jpeg)
3.查询特定列去重后的数据
例如我们想查看一共有多少人(去重过的)下过单。pandas里有unique方法,SQL里有distinct关键字。如下面图左侧代码所示。两种方式输出的结果都含有9个uid,并且知道是哪9个。如果仅仅想知道有多少个uid,不关注具体值的话,可以参考右边的SQL,pandas用nunique()方法实现,而SQL里就需要用到一个count聚合函数与distinct组合的方式,表示去重并计数。(点击图片可以查看大图)
![2e42025b271522c5c3fa52143990fa4d.png](https://i-blog.csdnimg.cn/blog_migrate/90977ee9321ddaf79d4f1da9af7cdb6f.jpeg)
4.查询带有1个条件的数据
例如我们要查询uid为10003的所有记录。pandas需要使用布尔索引的方式,而SQL中需要使用where关键字。指定条件时,可以指定等值条件,也可以使用不等值条件,如大于小于等。但一定要注意数据类型。例如如果uid是字符串类型,就需要将10003加引号,这里是整数类型所以不用加。代码如下:(点击图片可以查看大图)
![50a4020409dfff28679ce96976b0dc27.png](https://i-blog.csdnimg.cn/blog_migrate/415527dd1b92cdfe8597ef5ba6363df3.jpeg)
5.查询带有多个条件的数据。
- 多个条件同时满足的情况在前一小结基础上,pandas需要使用&符号连接多个条件,每个条件需要加上小括号;SQL需要使用and关键字连接多个条件。例如我们查询uid为10003并且金额大于50的记录。两种方式的实现代码如下:(点击图片可以查看大图)
- 多个条件满足其中一个的情况与多个条件同时满足使用&相对应的,我们使用|符号表示一个条件满足的情况,而SQL中则用or关键字连接各个条件表示任意满足一个。例如我们查询uid为10003或者金额大于50的记录。(点击图片可以查看大图)这里需要特别说明的是有一种情况是需要判断某字段是否为空值。pandas的空值用nan表示,其判断条件需要写成isna(),或者notna()。例如#查找uid不为空的记录order_data[order_data['uid'].notna()]#查找uid为空的记录order_data[order_data['uid'].isna()]MySQL相应的判断语句需要写成 is null 或者is not null。select * from t_order where uid is not null;select * from t_order where uid is null;还需要注意的是,空字符串或者空格虽然是有值的,但由于“不显示”出来,我们通常认为是空值。这种情况的判断条件和前面一样使用等号即可。感兴趣的朋友可以自己尝试一下。
6.group by聚合操作
使用group by时,通常伴随着聚合操作,这时候需要用到聚合函数。前面提到的count是一种聚合函数,表示计数,除此外还有sum表示求和,max,min表示最大最小值等。pandas和SQL都支持聚合操作。例如我们求每个uid有多少订单量。两种工具的操作如下:(点击图片可以查看大图)
![607658ca211f57053ff31ed705c5a1e2.png](https://i-blog.csdnimg.cn/blog_migrate/5cc599edce784ce99a7f4fea2d544c60.jpeg)
如果想要同时对不同的字段进行不同的聚合操作。例如目标变成:求每个uid的订单数量和订单总金额。写法会稍微不同一些,如下图所示。(点击图片可以查看大图)
![26d4f96fd3ebd91f22740128c94ed203.png](https://i-blog.csdnimg.cn/blog_migrate/33b1a2d03337c39003eca007c40bb6da.jpeg)
更进一步的,我们可以对结果的数据集进行重新命名。pandas可以使用rename方法,MySQL可以使用as 关键字进行结果的重命名。(点击图片可以查看大图)
![ba0a478d9cdddbe38f222e137c9f8730.png](https://i-blog.csdnimg.cn/blog_migrate/f0a335568a88dcc6d339b5dc83c5dfda.jpeg)
7.join相关操作
join相关的操作有inner join,left join,right join,full join,等。pandas中统一通过pd.merge方法,设置不同的参数即可实现不同的dataframe的连接。而SQL里就可以直接使用相应的关键字进行两个表的连接。为了演示,我们此处引入一个新的数据集,user.csv(对应t_user表)。包含了用户的昵称,年龄信息。数据样例如下所示。(点击图片可以查看大图)
![32adcdb0bdbd2e5b6f80472bed944d8d.png](https://i-blog.csdnimg.cn/blog_migrate/4c609247da5b259b9e2cbe1a483f69e6.jpeg)
- left join首先需要把数据加载进来:user_data = pd.read_csv('user.csv')pandas的merge函数传入4个参数,第一个是连接的主表,第二个是连接从表,第三个连接的key值,第四个是连接的方式,how为left时表示是左连接。SQL操作时基本也是同样的逻辑,要指定主表,从表,连接方式和连接字段。此处我们使用user连接order并查询所有字段和所有记录。具体代码如下所示,由于我们的数据没有空值,所以体现不出左连接的特点,感兴趣的读者可以自己尝试下。(点击图片可以查看大图)
![002b739186fcd17a60780fb6f54405db.png](https://i-blog.csdnimg.cn/blog_migrate/95c18243b88d0d90974a67aab357c3d2.jpeg)
- 其他连接方式如果要实现inner join,outer join,right join,pandas中相应的how参数为inner或者不填,outer,right。SQL也是同样直接使用对应的关键字即可。其中inner join 可以缩写为join。本例子中inner join 和left join的结果是一样的,在这里不作结果展示,pandas和SQL代码如下。pd.merge(user_data, order_data, on='uid', how='inner')SELECT * FROM t_user ainner join t_order bon a.uid = b.uid;
8.union操作
union相关操作分为union和union all两种。二者通常用于将两份含有同样字段的数据纵向拼接起来的场景。但前者会进行去重。例如,我现在有一份order2的订单数据,包含的字段和order数据一致,想把两者合并到一个dataframe中。SQL场景下也是期望将order2表和order表合并输出。执行的代码如下:(点击图片可以查看大图)
![e1a4be1896e56b597f1bc8a7fa020609.png](https://i-blog.csdnimg.cn/blog_migrate/b542cd7aca9053188e2c656bf408f911.jpeg)
以上是没有去重的情况,如果想要去重,SQL需要用union关键字。而pandas则需要加上去重操作。
order_union = pd.concat([order_data, order_data2]).drop_duplicates()
select * fromt_order union select * fromt_order2
9.排序操作
我们在实际工作中经常需要按照某一列字段进行排序。pandas中的排序使用sort_values方法,SQl中的排序可以使用order_by关键字。我们用一个实例说明:按照每个uid的订单数从高到低排序。这是在前面聚合操作的基础上的进行的。相应的代码可以参考下方:(点击图片可以查看大图)
![7b6aef33e2130acfad24039274bc89c3.png](https://i-blog.csdnimg.cn/blog_migrate/8990079475475d83400b15fc2d4bed39.jpeg)
排序时,asc表示升序,desc表示降序,能看到两种方法都指定了排序方式,原因是默认是会按照升序排列。在此基础上,可以做到对多个字段的排序。pandas里,dataframe的多字段排序需要用by指定排序字段,SQL只要将多个字段依次卸载order by之后即可。例如,输出uid,订单数,订单金额三列,并按照uid降序,订单金额升序排列。(点击图片可以查看大图)
![e57ee38491975ac5706fb097fb9757ae.png](https://i-blog.csdnimg.cn/blog_migrate/23f8d056b2549dcc5c76954cd9e88ff5.jpeg)
在pandas中可能有一些细节需要注意,比如我们将聚合结果先赋值,然后重命名,并指定了inplace=True替换原来的命名,最后才进行排序,这样写虽然有点绕,但整体思路比较清晰。
10.case when 操作
相比于其他操作,case when 操作可能不是那么“通用”。它更常见于SQL场景中,可能会用于分组,可能会用于赋值,也可能用于其他场景。分组,比如按照一定的分数区间分成优良中差。赋值,比如当数值小于0时,按照0计算。我们来举例看一下分组的场景。将每个uid按照总金额分为[0-300),[300,600),[600,900),三组。分别用pandas和SQL实现如下,注意这里我们的基础数据是上一步的order_df,SQL中也需要用子查询来实现。(点击图片可以查看大图)
![e3ecd2a22749a895964c6f9012c09aea.png](https://i-blog.csdnimg.cn/blog_migrate/486342afa8800e91f496c1142a4ebf2b.jpeg)
熟悉pandas的朋友应该能想到,pandas的这种分组操作有一种专门的术语叫“分箱”,相应的函数为cut,qcut,能实现同样的效果。为了保持和SQL操作的一致性,此处采用了map函数的方式。您可以自己查阅资料了解另外的实现方式。
11.更新和删除操作
更新和删除都是要改变原有数据的操作。对于更新操作,操作的逻辑是:先选出需要更新的目标行,再进行更新。pandas中,可以使用前文提到的方式进行选择操作,之后可以直接对目标列进行赋值,SQL中需要使用update关键字进行表的更新。示例如下:将年龄小于20的用户年龄改为20。(点击图片可以查看大图)
![32fae0cd7be6604ab325fa75fd0e2e11.png](https://i-blog.csdnimg.cn/blog_migrate/cb9ae3c0563c5613269251342348863e.jpeg)
删除操作可以细分为删除行的操作和删除列的操作。对于删除行操作,pandas的删除行可以转换为选择不符合条件进行操作。SQL需要使用delete关键字。例如删除年龄为30岁的用户:(点击图片可以查看大图)
![3feff4361df7a063372d768d316421db.png](https://i-blog.csdnimg.cn/blog_migrate/7a3464fb31905d61b7fd17d4e1fd0c5c.jpeg)
对于删除列的操作。pandas需要使用drop方法。SQL也需要使用drop关键字。(点击图片可以查看大图)
![a6dda2fae3013f7d243ef084a9560696.png](https://i-blog.csdnimg.cn/blog_migrate/73e03efd15af03d63d4fecf273fae6dc.jpeg)
总结:
简单粗暴,小结如下图所示:
![c99af4e2d34a342ce354a9eca5ebfc8f.png](https://i-blog.csdnimg.cn/blog_migrate/74becdab4c384a62e83624c4049cc0ad.jpeg)