上一篇中，我們對(duì)比了pandas與SQL常見的一些操作，我們的例子雖然是以MySQL為基礎(chǔ)的，但換作其他的數(shù)據(jù)庫軟件，也一樣適用。工作中除了MySQL，也經(jīng)常會(huì)使用Hive SQL，相比之下，后者有更為強(qiáng)大和豐富的函數(shù)。本文將延續(xù)上一篇文章的風(fēng)格和思路，繼續(xù)對(duì)比Pandas與SQL，一方面是對(duì)上文的補(bǔ)充，另一方面也繼續(xù)深入學(xué)習(xí)一下兩種工具。方便起見，本文采用hive環(huán)境運(yùn)行SQL，使用jupyter lab運(yùn)行pandas。關(guān)于hive的安裝和配置，我在之前的文章提到過，如果你覺得比較困難，可以考慮使用postgreSQL，它比MySQL支持更多的函數(shù)(不夠代碼可能需要進(jìn)行一定的改動(dòng))。而jupyter lab和jupyter notebook功能相同，界面相似，完全可以用notebook代替。希望本文可以幫助各位讀者在工作中進(jìn)行pandas和Hive SQL的快速轉(zhuǎn)換。

在公眾號(hào)“超哥的雜貨鋪”后臺(tái)回復(fù)“對(duì)比二”可以獲取本文的PDF版本以及全部的數(shù)據(jù)和代碼。

數(shù)據(jù)概況

數(shù)據(jù)上，我們還是使用上一篇中虛擬的數(shù)據(jù)，在ts的格式上有些小改動(dòng)，在使用之前同樣需要先用read_csv的方式讀取，具體可以參考上篇文章。本文不做這一步的演示。hive方面我們新建了一張表，并把同樣的數(shù)據(jù)加載進(jìn)了表中，直接使用即可。

開始學(xué)習(xí)

一、字符串的截取

對(duì)于原始數(shù)據(jù)集中的一列，我們常常要截取其字串作為新的列來使用。例如我們想求出每一條訂單對(duì)應(yīng)的日期。需要從訂單時(shí)間ts或者orderid中截取。在pandas中，我們可以將列轉(zhuǎn)換為字符串，截取其子串，添加為新的列。代碼如下圖左側(cè)所示，我們使用了.str將原字段視為字符串，從ts中截取了前10位，從orderid中截取了前8位。經(jīng)驗(yàn)表明有時(shí)在.str之前需要加上astype，能夠避免不必要的麻煩。兩種寫法供參考。

對(duì)于字符串截取的操作，Hive SQL中有substr函數(shù)，它在MySQL和Hive中的用法是一樣的substr(string A，int start，int len)表示從字符串A中截取起始位置為start，長度為len的子串，其中起始位置從1開始算。實(shí)現(xiàn)上面效果的代碼如下：

圖片中的代碼：

#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;

二、字符串匹配

這一節(jié)我們來研究提取包含特定字符的字段。沿用上一節(jié)的寫法，在pandas中我們可以使用字符串的contains，extract，replace方法，支持正則表達(dá)式。而在hive SQL中，既有簡易的Like關(guān)鍵字匹配特定的字符，也可以使用regexp_extract，regexp_replace這兩個(gè)函數(shù)更靈活地實(shí)現(xiàn)目標(biāo)。接下來我們舉例說明。

假設(shè)要實(shí)現(xiàn)篩選訂單時(shí)間中包含“08-01”的訂單。pandas和SQL代碼如下所示，注意使用like時(shí)，%是通配符，表示匹配任意長度的字符。

圖片中的代碼：

#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.假設(shè)要實(shí)現(xiàn)提取ts中的日期信息(前10位)，pandas里支持正則表達(dá)式的extract函數(shù)，而hive里除了前文提到的substr函數(shù)可以實(shí)現(xiàn)外，這里我們可以使用regexp_extract函數(shù)，通過正則表達(dá)式實(shí)現(xiàn)。

圖片中的代碼

#pythonorder['dt3'] = order['ts'].astype(str).str.extract('(d{4}-d{2}-d{2}).*')#這個(gè)正則表達(dá)式表示"4位數(shù)字橫杠兩位數(shù)字橫杠兩位數(shù)字"，后面是任意字符，#我們提取的目標(biāo)要放在小括號(hào)里order.head()#Hive SQLselect *, regexp_extract(ts, '(d{4}-d{2}-d{2}).*', 1) as dt3from t_order;#我們的目標(biāo)同樣是在小括號(hào)里，1表示取第一個(gè)匹配的結(jié)果

3.假設(shè)我們要去掉ts中的橫杠，即替換ts中的“-”為空，在pandas中可以使用字符串的replace方法，hive中可以使用regexp_replace函數(shù)。代碼如下：

圖片中代碼：

#pythonorder['dt4'] = order['ts'].astype(str).str.replace('-', '')order.head()#Hive SQLselect *, regexp_replace(ts, '-', '') as dt4from t_order;

三、帶條件的計(jì)數(shù)：count(distinct case when …end)

我們?cè)谏弦黄恼轮蟹謩e討論過分組聚合和case操作。實(shí)際中，經(jīng)常會(huì)遇到二者嵌套的情況，例如，我們想統(tǒng)計(jì)：ts中含有‘2019-08-01’的不重復(fù)訂單有多少，ts中含有‘2019-08-02’的不重復(fù)訂單有多少，這在Hive SQL中比較容易，代碼和得到的結(jié)果為：

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;#運(yùn)行結(jié)果：5 11

你當(dāng)然可以直接對(duì)日期進(jìn)行分組，同時(shí)計(jì)算所有日期的訂單數(shù)，此處我們僅僅是為了演示兩種操作的結(jié)合。

pandas中實(shí)現(xiàn)這個(gè)問題可能比較麻煩，也可能有很多不同的寫法。這里說一下我的思路和實(shí)現(xiàn)方式。

我定義了兩個(gè)函數(shù)，第一個(gè)函數(shù)給原數(shù)據(jù)增加一列，標(biāo)記我們的條件，第二個(gè)函數(shù)再增加一列，當(dāng)滿足條件時(shí)，給出對(duì)應(yīng)的orderid，然后要對(duì)整個(gè)dataframe應(yīng)用這兩個(gè)函數(shù)。對(duì)于我們不關(guān)心的行，這兩列的值都為nan。第三步再進(jìn)行去重計(jì)數(shù)操作。代碼和結(jié)果如下：

#第一步：構(gòu)造一個(gè)輔助列def func_1(x): if '2019-08-01' in x['ts']: return '2019-08-01'#這個(gè)地方可以返回其他標(biāo)記 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#應(yīng)用兩個(gè)函數(shù)，查看結(jié)果#注意這里必須加上axis=1，你可以嘗試下不加會(huì)怎樣order['cnt_condition'] = order.apply(func_1, axis=1)order['cnt'] = order.apply(func_2, axis=1)order[order['cnt'].notnull()]#進(jìn)行分組計(jì)數(shù)order.groupby('cnt_condition').agg({'cnt': 'nunique'})

可以看到，同樣得到了5，11的結(jié)果。如果你有其他更好的實(shí)現(xiàn)方法，歡迎一起探討交流。

四、窗口函數(shù) row_number

hive中的row_number函數(shù)通常用來分組計(jì)數(shù)，每組內(nèi)的序號(hào)從1開始增加，且沒有重復(fù)值。比如我們對(duì)每個(gè)uid的訂單按照訂單時(shí)間倒序排列，獲取其排序的序號(hào)。實(shí)現(xiàn)的Hive SQL代碼如下，可以看到，每個(gè)uid都會(huì)有一個(gè)從1開始的計(jì)數(shù)，這個(gè)計(jì)數(shù)是按時(shí)間倒序排的。

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

pandas中我們需要借助groupby和rank函數(shù)來實(shí)現(xiàn)同樣的效果。改變r(jià)ank中的method參數(shù)可以實(shí)現(xiàn)Hive中其他的排序，例如dense，rank等。

#由于我們的ts字段是字符串類型，先轉(zhuǎn)換為datetime類型order['ts2'] = pd.to_datetime(order['ts'], format='%Y-%m-%d %H:%M:%S')#進(jìn)行分組排序，按照uid分組，按照ts2降序，序號(hào)默認(rèn)為小數(shù)，需要轉(zhuǎn)換為整數(shù)#并添加為新的一列rkorder['rk'] = order.groupby(['uid'])['ts2'].rank(ascending=False, method='first').astype(int)#為了便于查看rk的效果,對(duì)原來的數(shù)據(jù)按照uid和時(shí)間進(jìn)行排序，結(jié)果和SQL一致order.sort_values(['uid','ts'], ascending=[True, False])

五、窗口函數(shù) lag，lead

lag和lead函數(shù)也是Hive SQL中常用的窗口函數(shù)，他們的格式為：

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

lag函數(shù)表示，取分組排序之后比該條記錄序號(hào)小N的對(duì)應(yīng)記錄的指定字段的值。lead剛好相反，是比當(dāng)前記錄大N的對(duì)應(yīng)記錄的指定字段值。我們來看例子。

例子中的lag表示分組排序后，前一條記錄的ts，lead表示后一條記錄的ts。不存在的用NULL填充。

對(duì)應(yīng)的代碼為：

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中我們也有相應(yīng)的shift函數(shù)來實(shí)現(xiàn)這樣的需求。shift的參數(shù)為負(fù)數(shù)時(shí)，表示lag，為正數(shù)時(shí)，表示lead。

代碼如下：

order['lag'] = order.groupby(['uid'])['ts2'].shift(-1)order['lead'] = order.groupby(['uid'])['ts2'].shift(1)#依然是為了看效果，對(duì)原來的數(shù)據(jù)按照uid和時(shí)間進(jìn)行排序，結(jié)果和SQL一致order.sort_values(['uid','ts'], ascending=[True, False])

六、列轉(zhuǎn)行，collect_list

在我們的數(shù)據(jù)中，一個(gè)uid會(huì)對(duì)應(yīng)多個(gè)訂單，目前這多個(gè)訂單id是分多行顯示的。現(xiàn)在我們要做的是讓多個(gè)訂單id顯示在同一行，用逗號(hào)分隔開。在pandas中，我們采用的做法是先把原來orderid列轉(zhuǎn)為字符串形式，并在每一個(gè)id末尾添加一個(gè)逗號(hào)作為分割符，然后采用字符串相加的方式，將每個(gè)uid對(duì)應(yīng)的字符串類型的訂單id拼接到一起。代碼和效果如下所示。為了減少干擾，我們將order數(shù)據(jù)重新讀入，并設(shè)置了pandas的顯示方式。

可以看到，同一個(gè)uid對(duì)應(yīng)的訂單id已經(jīng)顯示在同一行了，訂單id之間以逗號(hào)分隔。

在Hive中實(shí)現(xiàn)同樣的效果要方便多了，我們可以使用collect_set/collect_list函數(shù),，二者的區(qū)別在于前者在聚合時(shí)會(huì)進(jìn)行去重，別忘了加上group by。

select uid, collect_set(orderid) as order_listfrom t_ordergroup by uid;

可以看出hive實(shí)現(xiàn)的效果中，將同一個(gè)uid的orderid作為一個(gè)“數(shù)組”顯示出來。雖然和pandas實(shí)現(xiàn)的效果不完全一樣，但表達(dá)的含義是一致的。我沒有找到pandas實(shí)現(xiàn)這樣數(shù)組形式比較好的方法，如果你知道，歡迎一起交流.另外，pandas在聚合時(shí)，如何去重，也是一個(gè)待解決的問題。

七 行轉(zhuǎn)列 later view explode

行轉(zhuǎn)列的操作在Hive SQL中有時(shí)會(huì)遇到，可以理解為將上一小節(jié)的結(jié)果還原為每個(gè)orderid顯示一行的形式。hive中有比較方便的explode函數(shù)，結(jié)合lateral view，可以很容易實(shí)現(xiàn)。代碼和效果如下：

-- 使用上一節(jié)的結(jié)果，定義為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中的實(shí)現(xiàn)。目標(biāo)是把上一節(jié)合并起來的用逗號(hào)分隔的數(shù)組拆分開。這里給出一個(gè)參考鏈接：https://blog.csdn.net/sscc_learning/article/details/89473151。

首先我們要把groupby的結(jié)果索引重置一下，然后再進(jìn)行遍歷，和賦值，最后將每一個(gè)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

這樣的結(jié)果中會(huì)有一個(gè)空行，這是因?yàn)橛枚禾?hào)分隔的時(shí)候，最后一個(gè)元素為空。后續(xù)可以使用我們之前學(xué)習(xí)的方法進(jìn)行過濾或刪除。這里省略這一步驟。

八、數(shù)組元素解析

這一小節(jié)我們引入一個(gè)新的數(shù)據(jù)集，原因是我想分享的內(nèi)容，目前的數(shù)據(jù)集不能夠體現(xiàn)，哈哈。下面是在Hive和pandas中查看數(shù)據(jù)樣例的方式。我們的目標(biāo)是將原始以字符串形式存儲(chǔ)的數(shù)組元素解析出來。

先來看pandas中如何實(shí)現(xiàn)，這里我們需要用到literal_eval這個(gè)包，能夠自動(dòng)識(shí)別以字符串形式存儲(chǔ)的數(shù)組。我定義了一個(gè)解析函數(shù)，將arr列應(yīng)用該函數(shù)多次，解析出的結(jié)果作為新的列，代碼如下：

這里需要注意解析出的結(jié)果是object類型的，如果想讓它們參與數(shù)值計(jì)算，需要再轉(zhuǎn)換為int類型，可以在解析的時(shí)候增加轉(zhuǎn)換的代碼。

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

回到Hive SQL，實(shí)現(xiàn)起來比較容易。我們可以通過split函數(shù)將原來的字符串形式變?yōu)閿?shù)組，然后依次取數(shù)組的元素即可，但是要注意使用substr函數(shù)處理好前后的中括號(hào)，代碼如下：

可以看到最終我們得到的結(jié)果是字符串的形式，如果想要得到數(shù)值，可以再進(jìn)行一步截取。

可以看到，我們這里得到的依然是字符串類型，和pandas中的強(qiáng)制轉(zhuǎn)換類似，hive SQL中也有類型轉(zhuǎn)換的函數(shù)cast，使用它可以強(qiáng)制將字符串轉(zhuǎn)為整數(shù)，使用方法如下面代碼所示。

小結(jié)

本文涉及的操作概括如下表所示，雖然內(nèi)容沒有上篇文章多，但相對(duì)難度還是比上篇高一些。

如果你認(rèn)真讀了本文，會(huì)發(fā)現(xiàn)有一些情況下，Hive SQL比pandas更方便，為了達(dá)到同樣的效果，pandas可能要用一種全新的方式來實(shí)現(xiàn)。實(shí)際工作中，如果數(shù)據(jù)存在數(shù)據(jù)庫中，使用SQL語句來處理還是方便不少的，尤其是如果數(shù)據(jù)量大了，pandas可能會(huì)顯得有點(diǎn)吃力。本文的出發(fā)點(diǎn)僅僅是對(duì)比兩者的操作，方便從兩個(gè)角度理解常見的數(shù)據(jù)處理手段，也方便工作中的轉(zhuǎn)換查閱，不強(qiáng)調(diào)孰優(yōu)孰劣。對(duì)于文中遺留的不是很完美的地方，如果您想到了好的方案，歡迎一起探討交流~文中用到的數(shù)據(jù)和代碼我已經(jīng)打包整理好，在公眾號(hào)“超哥的雜貨鋪”后臺(tái)回復(fù)“對(duì)比二”即可獲得，祝您練習(xí)愉快！

總結(jié)

以上是生活随笔為你收集整理的pandas loc 正则匹配字符串_一场pandas与SQL的巅峰大战（二）的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。