上一篇文章一场pandas与SQL的巅峰大战中，我们对比了pandas与SQL常见的一些操作，我们的例子虽然是以MySQL为基础的，但换作其他的数据库软件，也一样适用。工作中除了MySQL，也经常会使用Hive SQL，相比之下，后者有更为强大和丰富的函数。本文将延续上一篇文章的风格和思路，继续对比Pandas与SQL，一方面是对上文的补充，另一方面也继续深入学习一下两种工具。方便起见，本文采用hive环境运行SQL，使用jupyter lab运行pandas。关于hive的安装和配置，我在之前的文章MacOS 下hive的安装与配置提到过，不过仅限于mac版本，供参考，如果你觉得比较困难，可以考虑使用postgreSQL，它比MySQL支持更多的函数(不过代码可能需要进行一定的改动)。而jupyter lab和jupyter notebook功能相同，界面相似，完全可以用notebook代替，我在Jupyter notebook使用技巧大全一文的最后有提到过二者的差别，感兴趣可以点击蓝字阅读。希望本文可以帮助各位读者在工作中进行pandas和Hive SQL的快速转换。本文涉及的部分hive 函数我在之前也有总结过，可以参考常用Hive函数的学习和总结。
在公众号后台回复“对比二”可以获取本文的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开始算。实现上面效果的代码如下：
图片中的代码：
#pythonimport pandas as pdorder = 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 SQLselect *, substr(ts, 1, 10) as dt, substring(orderid, 1, 8) as dt2from t_order;
二、字符串匹配
这一节我们来研究提取包含特定字符的字段。沿用上一节的写法，在pandas中我们可以使用字符串的contains，extract，replace方法，支持正则表达式。而在hive SQL中，既有简易的Like关键字匹配特定的字符，也可以使用regexp_extract，regexp_replace这两个函数更灵活地实现目标。接下来我们举例说明。
- 假设要实现筛选订单时间中包含“08-01”的订单。pandas和SQL代码如下所示，注意使用like时，%是通配符，表示匹配任意长度的字符。
图片中的代码：
#pythonorder_08_01 = order[order['ts'].astype(str).str.contains('08-01')]order_08_01#Hive SQLselect * from t_orderwhere ts like "%08-01%";
2.假设要实现提取ts中的日期信息(前10位)，pandas里支持正则表达式的extract函数，而hive里除了前文提到的substr函数可以实现外，这里我们可以使用regexp_extract函数，通过正则表达式实现。
图片中的代码
#pythonorder['dt3'] = order['ts'].astype(str).str.extract('(\d{4}-\d{2}-\d{2}).*')#这个正则表达式表示"4位数字横杠两位数字横杠两位数字"，后面是任意字符，#我们提取的目标要放在小括号里order.head()#Hive SQLselect *, regexp_extract(ts, '(\\d{4}-\\d{2}-\\d{2}).*', 1) as dt3from t_order;#我们的目标同样是在小括号里，1表示取第一个匹配的结果
3.假设我们要去掉ts中的横杠，即替换ts中的“-”为空，在pandas中可以使用字符串的replace方法，hive中可以使用regexp_replace函数。代码如下：
图片中代码：
#pythonorder['dt4'] = order['ts'].astype(str).str.replace('-', '')order.head()#Hive SQLselect *, regexp_replace(ts, '-', '') as dt4from 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_cntfrom 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 rkfrom 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降序，序号默认为小数，需要转换为整数#并添加为新的一列rkorder['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 leadfrom 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_listfrom t_ordergroup 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_listfrom t_ordergroup by uid)select uid, o_listfrom 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_grouporder_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可能会显得有点吃力。本文的出发点仅仅是对比两者的操作，方便从两个角度理解常见的数据处理手段，也方便工作中的转换查阅，不强调孰优孰劣。对于文中遗留的不是很完美的地方，如果您想到了好的方案，欢迎一起探讨交流~文中用到的数据和代码我已经打包整理好，在公众号后台回复“对比二”即可获得，祝您练习愉快！
推荐阅读：
