一场pandas与SQL的巅峰大战(二)（pandas和sql哪个速度快）

上一篇中，我们对比了pandas与SQL常见的一些操作，我们的例子虽然是以MySQL为基础的，但换作其他的数据库软件，也一样适用。工作中除了MySQL，也经常会使用Hive SQL，相比之下，后者有更为强大和丰富的函数。本文将延续上一篇文章的风格和思路，继续对比Pandas与SQL，一方面是对上文的补充，另一方面也继续深入学习一下两种工具。方便起见，本文采用hive环境运行SQL，使用jupyter lab运行pandas。关于hive的安装和配置，我在之前的文章提到过，如果你觉得比较困难，可以考虑使用postgreSQL，它比MySQL支持更多的函数(不够代码可能需要进行一定的改动)。而jupyter lab和jupyter notebook功能相同，界面相似，完全可以用notebook代替。希望本文可以帮助各位读者在工作中进行pandas和Hive SQL的快速转换。

在公众号“超哥的杂货铺”后台回复“对比二”可以获取本文的PDF版本以及全部的数据和代码。

数据概况

数据上，我们还是使用上一篇中虚拟的数据，在ts的格式上有些小改动，在使用之前同样需要先用read_csv的方式读取，具体可以参考上篇文章。本文不做这一步的演示。hive方面我们新建了一张表，并把同样的数据加载进了表中，直接使用即可。

开始学习

一、字符串的截取

对于原始数据集中的一列，我们常常要截取其字串作为新的列来使用。例如我们想求出每一条订单对应的日期。需要从订单时间ts或者orderid中截取。在pandas中，我们可以将列转换为字符串，截取其子串，添加为新的列。代码如下图左侧所示，我们使用了.str将原字段视为字符串，从ts中截取了前10位，从orderid中截取了前8位。经验表明有时在.str之前需要加上astype，能够避免不必要的麻烦。两种写法供参考。

对于字符串截取的操作，Hive SQL中有substr函数，它在MySQL和Hive中的用法是一样的substr(string A，int start，int len)表示从字符串A中截取起始位置为start，长度为len的子串，其中起始位置从1开始算。实现上面效果的代码如下：

图片中的代码：

#python
import pandas as pd
order = pd.read_csv('order.csv', names=['id', 'ts', 'uid', 'orderid', 'amount'])
order.head()


order['dt'] = order['ts'].str[:10]
order.head()

order['dt2'] = order['orderid'].astype(str).str[:8]
order.head()

#Hive SQL
select *, substr(ts, 1, 10) as dt, substring(orderid, 1, 8) as dt2
from t_order;

二、字符串匹配

这一节我们来研究提取包含特定字符的字段。沿用上一节的写法，在pandas中我们可以使用字符串的contains，extract，replace方法，支持正则表达式。而在hive SQL中，既有简易的Like关键字匹配特定的字符，也可以使用regexp_extract，regexp_replace这两个函数更灵活地实现目标。接下来我们举例说明。

1. 假设要实现筛选订单时间中包含“08-01”的订单。pandas和SQL代码如下所示，注意使用like时，%是通配符，表示匹配任意长度的字符。

图片中的代码：

#python
order_08_01 = order[order['ts'].astype(str).str.contains('08-01')]
order_08_01

#Hive SQL
select * 
from t_order
where ts like "%08-01%"; 

2.假设要实现提取ts中的日期信息(前10位)，pandas里支持正则表达式的extract函数，而hive里除了前文提到的substr函数可以实现外，这里我们可以使用regexp_extract函数，通过正则表达式实现。

图片中的代码

#python
order['dt3'] = order['ts'].astype(str).str.extract('(\d{4}-\d{2}-\d{2}).*')
#这个正则表达式表示"4位数字横杠两位数字横杠两位数字"，后面是任意字符，
#我们提取的目标要放在小括号里
order.head()

#Hive SQL
select *, regexp_extract(ts, '(\\d{4}-\\d{2}-\\d{2}).*', 1) as dt3
from t_order;
#我们的目标同样是在小括号里，1表示取第一个匹配的结果

3.假设我们要去掉ts中的横杠，即替换ts中的“-”为空，在pandas中可以使用字符串的replace方法，hive中可以使用regexp_replace函数。代码如下：

图片中代码：

#python
order['dt4'] = order['ts'].astype(str).str.replace('-', '')
order.head()

#Hive SQL
select *, regexp_replace(ts, '-', '') as dt4
from t_order;

三、带条件的计数：count（distinct case when …end）

我们在上一篇文章中分别讨论过分组聚合和case操作。实际中，经常会遇到二者嵌套的情况，例如，我们想统计：ts中含有‘2019-08-01’的不重复订单有多少，ts中含有‘2019-08-02’的不重复订单有多少，这在Hive SQL中比较容易，代码和得到的结果为：

select count(distinct case when ts like '%2019-08-01%' then orderid end) as 0801_cnt,
count(distinct case when ts like '%2019-08-02%' then orderid end) as 0802_cnt
from t_order;
#运行结果：
5 11

你当然可以直接对日期进行分组，同时计算所有日期的订单数，此处我们仅仅是为了演示两种操作的结合。

pandas中实现这个问题可能比较麻烦，也可能有很多不同的写法。这里说一下我的思路和实现方式。

我定义了两个函数，第一个函数给原数据增加一列，标记我们的条件，第二个函数再增加一列，当满足条件时，给出对应的orderid，然后要对整个dataframe应用这两个函数。对于我们不关心的行，这两列的值都为nan。第三步再进行去重计数操作。代码和结果如下：

#第一步：构造一个辅助列
def func_1(x):
 if '2019-08-01' in x['ts']:
 return '2019-08-01'#这个地方可以返回其他标记
 elif '2019-08-02' in x['ts']:
 return '2019-08-02'
 else:
 return None

#第二步：将符合条件的order作为新的一列
def func_2(x):
 if '2019-08-01' in x['ts']:
 return str(x['orderid'])
 elif '2019-08-02' in x['ts']:
 return str(x['orderid'])
 else:
 return None

#应用两个函数，查看结果
#注意这里必须加上axis=1，你可以尝试下不加会怎样
order['cnt_condition'] = order.apply(func_1, axis=1)
order['cnt'] = order.apply(func_2, axis=1)
order[order['cnt'].notnull()]

#进行分组计数
order.groupby('cnt_condition').agg({'cnt': 'nunique'})

可以看到，同样得到了5，11的结果。如果你有其他更好的实现方法，欢迎一起探讨交流。

四、窗口函数 row_number

hive中的row_number函数通常用来分组计数，每组内的序号从1开始增加，且没有重复值。比如我们对每个uid的订单按照订单时间倒序排列，获取其排序的序号。实现的Hive SQL代码如下，可以看到，每个uid都会有一个从1开始的计数，这个计数是按时间倒序排的。

select *, row_number() over (partition by uid order by ts desc) as rk
from t_order;

pandas中我们需要借助groupby和rank函数来实现同样的效果。改变rank中的method参数可以实现Hive中其他的排序，例如dense，rank等。

#由于我们的ts字段是字符串类型，先转换为datetime类型
order['ts2'] = pd.to_datetime(order['ts'], format='%Y-%m-%d %H:%M:%S')

#进行分组排序，按照uid分组，按照ts2降序，序号默认为小数，需要转换为整数
#并添加为新的一列rk
order['rk'] = order.groupby(['uid'])['ts2'].rank(ascending=False, method='first').astype(int)

#为了便于查看rk的效果,对原来的数据按照uid和时间进行排序，结果和SQL一致
order.sort_values(['uid','ts'], ascending=[True, False])

五、窗口函数 lag，lead

lag和lead函数也是Hive SQL中常用的窗口函数，他们的格式为：

lag(字段名,N) over(partition by 分组字段 order by 排序字段 排序方式) 
lead(字段名,N) over(partition by 分组字段 order by 排序字段 排序方式) 

lag函数表示，取分组排序之后比该条记录序号小N的对应记录的指定字段的值。lead刚好相反，是比当前记录大N的对应记录的指定字段值。我们来看例子。

例子中的lag表示分组排序后，前一条记录的ts，lead表示后一条记录的ts。不存在的用NULL填充。

对应的代码为：

select *, 
lag(ts, 1) over (partition by uid order by ts desc) as lag,
lead(ts, 1) over (partition by uid order by ts desc) as lead
from t_order;

pandas中我们也有相应的shift函数来实现这样的需求。shift的参数为负数时，表示lag，为正数时，表示lead。

代码如下：

order['lag'] = order.groupby(['uid'])['ts2'].shift(-1)
order['lead'] = order.groupby(['uid'])['ts2'].shift(1)

#依然是为了看效果，对原来的数据按照uid和时间进行排序，结果和SQL一致
order.sort_values(['uid','ts'], ascending=[True, False])

六、列转行，collect_list

在我们的数据中，一个uid会对应多个订单，目前这多个订单id是分多行显示的。现在我们要做的是让多个订单id显示在同一行，用逗号分隔开。在pandas中，我们采用的做法是先把原来orderid列转为字符串形式，并在每一个id末尾添加一个逗号作为分割符，然后采用字符串相加的方式，将每个uid对应的字符串类型的订单id拼接到一起。代码和效果如下所示。为了减少干扰，我们将order数据重新读入，并设置了pandas的显示方式。

可以看到，同一个uid对应的订单id已经显示在同一行了，订单id之间以逗号分隔。

在Hive中实现同样的效果要方便多了，我们可以使用collect_set/collect_list函数,，二者的区别在于前者在聚合时会进行去重，别忘了加上group by。

select uid, collect_set(orderid) as order_list
from t_order
group by uid
;

可以看出hive实现的效果中，将同一个uid的orderid作为一个“数组”显示出来。虽然和pandas实现的效果不完全一样，但表达的含义是一致的。我没有找到pandas实现这样数组形式比较好的方法，如果你知道，欢迎一起交流.另外，pandas在聚合时，如何去重，也是一个待解决的问题。

七 行转列 later view explode

行转列的操作在Hive SQL中有时会遇到，可以理解为将上一小节的结果还原为每个orderid显示一行的形式。hive中有比较方便的explode函数，结合lateral view，可以很容易实现。代码和效果如下：

-- 使用上一节的结果，定义为tmp表，后面可以直接用
with tmp as 
(
select uid, collect_set(orderid) as order_list
from t_order
group by uid
)

select uid, o_list
from tmp lateral view explode(order_list) t as o_list;

我们来看在pandas中的实现。目标是把上一节合并起来的用逗号分隔的数组拆分开。这里给出一个参考链接：https://blog.csdn.net/sscc_learning/article/details/89473151。

首先我们要把groupby的结果索引重置一下，然后再进行遍历，和赋值，最后将每一个series拼接起来。我采用的是链接中的第一种方式。由于是遍历，效率可能比较低下，读者可以尝试下链接里的另一种方式。我先给出我的代码：

order_group = order_group.reset_index()
order_group

order_group1 = pd.concat([pd.Series(row['uid'], row['orderid'].split(',')) for _ , row in order_group.iterrows()]).reset_index()
order_group1

这样的结果中会有一个空行，这是因为用逗号分隔的时候，最后一个元素为空。后续可以使用我们之前学习的方法进行过滤或删除。这里省略这一步骤。

八、数组元素解析

这一小节我们引入一个新的数据集，原因是我想分享的内容，目前的数据集不能够体现，哈哈。下面是在Hive和pandas中查看数据样例的方式。我们的目标是将原始以字符串形式存储的数组元素解析出来。

先来看pandas中如何实现，这里我们需要用到literal_eval这个包，能够自动识别以字符串形式存储的数组。我定义了一个解析函数，将arr列应用该函数多次，解析出的结果作为新的列，代码如下：

这里需要注意解析出的结果是object类型的，如果想让它们参与数值计算，需要再转换为int类型，可以在解析的时候增加转换的代码。

new_data['arr_1'] = new_data.arr.apply(extract_num, args=(0,)).astype(int)

回到Hive SQL，实现起来比较容易。我们可以通过split函数将原来的字符串形式变为数组，然后依次取数组的元素即可，但是要注意使用substr函数处理好前后的中括号，代码如下：

可以看到最终我们得到的结果是字符串的形式，如果想要得到数值，可以再进行一步截取。

可以看到，我们这里得到的依然是字符串类型，和pandas中的强制转换类似，hive SQL中也有类型转换的函数cast，使用它可以强制将字符串转为整数，使用方法如下面代码所示。

小结

本文涉及的操作概括如下表所示，虽然内容没有上篇文章多，但相对难度还是比上篇高一些。

如果你认真读了本文，会发现有一些情况下，Hive SQL比pandas更方便，为了达到同样的效果，pandas可能要用一种全新的方式来实现。实际工作中，如果数据存在数据库中，使用SQL语句来处理还是方便不少的，尤其是如果数据量大了，pandas可能会显得有点吃力。本文的出发点仅仅是对比两者的操作，方便从两个角度理解常见的数据处理手段，也方便工作中的转换查阅，不强调孰优孰劣。对于文中遗留的不是很完美的地方，如果您想到了好的方案，欢迎一起探讨交流~文中用到的数据和代码我已经打包整理好，在公众号“超哥的杂货铺”后台回复“对比二”即可获得，祝您练习愉快！