一場pandas與SQL的巔峰大戰

2019 年 11 月 5 日
筆記

作為一名數據分析師，平常用的最多的工具是SQL(包括MySQL和Hive SQL等)。對於存儲在資料庫中的數據，自然用SQL提取會比較方便，但有時我們會處理一些文本數據(txt,csv)，這個時候就不太好用SQL了。Python也是分析師常用的工具之一，尤其pandas更是一個數據分析的利器。雖然二者的語法，原理可能有很大差別，但在實現的功能上，他們有很多相通的地方，這裡特進行一個總結，方便大家對比學習~

本次學習的數據是虛構的訂單數據，和實際業務無關，目的只是為了學習。大概長下面這樣子，分別表示，自增id，訂單時間，用戶id，訂單id，訂單金額。

我們將用pandas和SQL來實現同樣的目標，以此來聯繫二者，達到共同學習的目的。數據可以在公眾號後台回復「對比」獲取，你將得到本文所有的excel數據和SQL腳本數據以及本文的清晰PDF版本，便於實操和查看。

準備工作：

pandas準備，我們本次採用jupyter notebook進行演示。 import pandas as pd order_data = pd.read_csv('order.csv')
SQL 準備只需將我提供的SQL文件運行一下即可將數據插入資料庫表中。推薦使用navicate客戶端連接資料庫。

開始學習

1.查看全部數據或者前n行數據

查看全部數據，pandas中直接列印dataframe對象即可，此處是order_data。而在SQL中，需要執行的語句是select * from t_order;表示從t_order表中查詢全部的數據，*號表示查詢所有的欄位。結果如下：(點擊圖片可以查看大圖)

如果只想查看前10行數據呢。pandas可以調用head(n)方法，n是行數。MySQL可以使用limit n，n同樣表示行數。(點擊圖片可以查看大圖)

2.查詢特定列的數據

有的時候我們只想查看某幾列的數據。在pandas里可以使用中括弧或者loc，iloc等多種方式進行列選擇，可以選擇一列或多列。loc方式可以直接寫列名，iloc方式需要指定索引，即第幾列。SQL里只需寫相應的列名即可，舉例如下，實際操作一下更容易理解，選擇一種自己習慣的即可。(點擊圖片可以查看大圖)

3.查詢特定列去重後的數據

例如我們想查看一共有多少人(去重過的)下過單。pandas里有unique方法，SQL里有distinct關鍵字。如下面圖左側程式碼所示。兩種方式輸出的結果都含有9個uid，並且知道是哪9個。如果僅僅想知道有多少個uid，不關注具體值的話，可以參考右邊的SQL，pandas用nunique()方法實現，而SQL里就需要用到一個count聚合函數與distinct組合的方式，表示去重並計數。(點擊圖片可以查看大圖)

4.查詢帶有1個條件的數據

例如我們要查詢uid為10003的所有記錄。pandas需要使用布爾索引的方式，而SQL中需要使用where關鍵字。指定條件時，可以指定等值條件，也可以使用不等值條件，如大於小於等。但一定要注意數據類型。例如如果uid是字元串類型，就需要將10003加引號，這裡是整數類型所以不用加。程式碼如下：(點擊圖片可以查看大圖)

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有多少訂單量。兩種工具的操作如下：(點擊圖片可以查看大圖)

如果想要同時對不同的欄位進行不同的聚合操作。例如目標變成：求每個uid的訂單數量和訂單總金額。寫法會稍微不同一些，如下圖所示。(點擊圖片可以查看大圖)

更進一步的，我們可以對結果的數據集進行重新命名。pandas可以使用rename方法，MySQL可以使用as 關鍵字進行結果的重命名。(點擊圖片可以查看大圖)

7.join相關操作

join相關的操作有inner join，left join，right join，full join，等。pandas中統一通過pd.merge方法，設置不同的參數即可實現不同的dataframe的連接。而SQL里就可以直接使用相應的關鍵字進行兩個表的連接。為了演示，我們此處引入一個新的數據集，user.csv(對應t_user表)。包含了用戶的昵稱，年齡資訊。數據樣例如下所示。(點擊圖片可以查看大圖)

left join 首先需要把數據載入進來： user_data = pd.read_csv('user.csv') pandas的merge函數傳入4個參數，第一個是連接的主表，第二個是連接從表，第三個連接的key值，第四個是連接的方式，how為left時表示是左連接。SQL操作時基本也是同樣的邏輯，要指定主表，從表，連接方式和連接欄位。此處我們使用user連接order並查詢所有欄位和所有記錄。具體程式碼如下所示，由於我們的數據沒有空值，所以體現不出左連接的特點，感興趣的讀者可以自己嘗試下。(點擊圖片可以查看大圖)

其他連接方式如果要實現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 a inner join t_order b on a.uid = b.uid;

8.union操作

union相關操作分為union和union all兩種。二者通常用於將兩份含有同樣欄位的數據縱向拼接起來的場景。但前者會進行去重。例如，我現在有一份order2的訂單數據，包含的欄位和order數據一致，想把兩者合併到一個dataframe中。SQL場景下也是期望將order2表和order表合併輸出。執行的程式碼如下：(點擊圖片可以查看大圖)

以上是沒有去重的情況，如果想要去重，SQL需要用union關鍵字。而pandas則需要加上去重操作。

order_union = pd.concat([order_data, order_data2]).drop_duplicates()

select * from  t_order  union  select * from  t_order2

9.排序操作

我們在實際工作中經常需要按照某一列欄位進行排序。pandas中的排序使用sort_values方法，SQl中的排序可以使用order_by關鍵字。我們用一個實例說明：按照每個uid的訂單數從高到低排序。這是在前面聚合操作的基礎上的進行的。相應的程式碼可以參考下方：(點擊圖片可以查看大圖)

排序時，asc表示升序，desc表示降序，能看到兩種方法都指定了排序方式，原因是默認是會按照升序排列。在此基礎上，可以做到對多個欄位的排序。pandas里，dataframe的多欄位排序需要用by指定排序欄位，SQL只要將多個欄位依次卸載order by之後即可。例如，輸出uid，訂單數，訂單金額三列，並按照uid降序，訂單金額升序排列。(點擊圖片可以查看大圖)

在pandas中可能有一些細節需要注意，比如我們將聚合結果先賦值，然後重命名，並指定了inplace=True替換原來的命名，最後才進行排序，這樣寫雖然有點繞，但整體思路比較清晰。

10.case when 操作

相比於其他操作，case when 操作可能不是那麼「通用」。它更常見於SQL場景中，可能會用於分組，可能會用於賦值，也可能用於其他場景。分組，比如按照一定的分數區間分成優良中差。賦值，比如當數值小於0時，按照0計算。我們來舉例看一下分組的場景。將每個uid按照總金額分為[0-300)，[300,600),[600,900)，三組。分別用pandas和SQL實現如下，注意這裡我們的基礎數據是上一步的order_df，SQL中也需要用子查詢來實現。(點擊圖片可以查看大圖)

熟悉pandas的朋友應該能想到，pandas的這種分組操作有一種專門的術語叫「分箱」，相應的函數為cut,qcut，能實現同樣的效果。為了保持和SQL操作的一致性，此處採用了map函數的方式。您可以自己查閱資料了解另外的實現方式。

11.更新和刪除操作

更新和刪除都是要改變原有數據的操作。對於更新操作，操作的邏輯是：先選出需要更新的目標行，再進行更新。pandas中，可以使用前文提到的方式進行選擇操作，之後可以直接對目標列進行賦值，SQL中需要使用update關鍵字進行表的更新。示例如下：將年齡小於20的用戶年齡改為20。(點擊圖片可以查看大圖)

刪除操作可以細分為刪除行的操作和刪除列的操作。對於刪除行操作，pandas的刪除行可以轉換為選擇不符合條件進行操作。SQL需要使用delete關鍵字。例如刪除年齡為30歲的用戶：(點擊圖片可以查看大圖)

對於刪除列的操作。pandas需要使用drop方法。SQL也需要使用drop關鍵字。(點擊圖片可以查看大圖)

總結：

簡單粗暴，小結如下圖所示：

reference:

https://pandas.pydata.org/pandas-docs/stable/getting_started/comparison/comparison_with_sql.html

https://juejin.im/post/5b5e5b2ee51d4517df1510c7

需要說明的是，pandas和SQL是兩種不同的工具，本文進行比較並不想說明孰優孰劣，只是為了對於二者的類似操作加深理解，從而方便實際工作中更高效的使用二者。實際工作中的操作可能比本文涉及到的複雜很多，甚至會有多種組合的方式出現，也可能會有本文沒有提及的情況。但我們掌握了本文的方法，就可以以不變應萬變，遇到複雜情況也可從容應對了，希望對你有所幫助！