相對於學習Pandas各種資料篩選操作，SQL語法顯得更加簡潔清晰，若能夠將SQL語法與Pandas中對應的函式的使用方法關聯起來，對於我們應用Pandas進行資料篩選來講無疑是一個福音。
演示資料集本文采用安德森鳶尾花卉(iris)資料集進行演示，iris資料集包含150個樣本，對應資料集的每行資料。每行資料包含每個樣本的四個特徵和樣本的類別資訊，因此iris資料集是一個150行*5列的二維表。
我們可以 UCI Iris dataset 獲取或者使用 from sklearn.datasets import load_iris 方式獲取，為了演示方便我們只取其中10行資料，如下：
欄位查詢 SELECTSELECT sl, sw, pl, pw FROM iris LIMIT 2;如上SQL實現返回每行記錄的 sl,sw,pl,pw 欄位，僅返回2行記錄。我們使用Pandas實現如上SQL的功能，程式碼如下：
import pandas as pdiris = pd.read_excel(io="iris.xlsx", sheet_name='iris', usecols=["sl", "sw", "pl", "pw", "classes"])search = iris[["sl", "sw", "pl", "pw"]].head(2)print(search)# 執行上述程式碼，輸出結果為： sl sw pl pw0 5.1 3.5 1.4 0.21 4.9 3.0 1.4 0.2SELECT * FROM iris WHERE classes = 1 LIMIT 2;如上SQL實現了查詢滿足classes=1的記錄，並返回2行。我們使用Pandas實現該SQL，程式碼如下：
import pandas as pdiris = pd.read_excel(io="iris.xlsx", sheet_name='iris', usecols=["sl", "sw", "pl", "pw", "classes"])search = iris[iris["classes"]==1].head(2)print(search)# 執行上述程式碼，輸出結果為： sl sw pl pw classes3 6.8 2.8 4.8 1.4 14 6.7 3.0 5.0 1.7 1與關係 &
SELECT * FROM iris WHERE classes = 1 AND pl >= 5 LIMIT 2;如上SQL實現查詢同時滿足classes=1 和 pl >=5 兩個條件的記錄，並返回2行。我們使用Pandas實現該SQL，程式碼如下：
import pandas as pdiris = pd.read_excel(io="iris.xlsx", sheet_name='iris', usecols=["sl", "sw", "pl", "pw", "classes"])search = iris[ (iris["classes"] == 1) & (iris["pl"] >= 5) ].head(2)print(search)# 執行上述程式碼，輸出結果為： sl sw pl pw classes4 6.7 3.0 5.0 1.7 1或關係 |
SELECT * FROM iris WHERE sl >= 5 OR pl >=5 LIMIT 2;如上SQL實現查詢滿足 sl >=5 或者 pl >=5 任一條件的記錄，返回2行。我們使用Pandas實現該SQL，程式碼如下：
import pandas as pdiris = pd.read_excel(io="iris.xlsx", sheet_name='iris', usecols=["sl", "sw", "pl", "pw", "classes"])search = iris[ (iris["sl"] >= 5) | (iris["pl"] >= 5) ].head(2)print(search)# 執行上述程式碼，輸出結果為： sl sw pl pw classes0 5.1 3.5 1.4 0.2 02 5.4 3.9 1.7 0.4 0空判斷 is null
SELECT * FROM iris WHERE sl IS NULL;如上SQL實現查詢 sl 欄位為NULL的記錄，我們使用Pandas實現該SQL，程式碼如下：
import pandas as pdiris = pd.read_excel(io="iris.xlsx", sheet_name='iris', usecols=["sl", "sw", "pl", "pw", "classes"])search = iris[iris["sl"].isna()]print(search)非空判斷 is not null
SELECT * FROM iris WHERE sl IS NOT NULL;如上SQL實現查詢sl欄位不為 NULL 的記錄。我們使用Pandas實現該SQL，程式碼如下：
import pandas as pdiris = pd.read_excel(io="iris.xlsx", sheet_name='iris', usecols=["sl", "sw", "pl", "pw", "classes"])search = iris[iris["sl"].notna()]print(search)# 執行上述程式碼，輸出結果為： sl sw pl pw classes0 5.1 3.5 1.4 0.2 01 4.9 3.0 1.4 0.2 02 5.4 3.9 1.7 0.4 03 6.8 2.8 4.8 1.4 14 6.7 3.0 5.0 1.7 15 6.0 2.9 4.5 1.5 16 5.6 2.8 4.9 2.0 27 7.7 2.8 6.7 2.0 28 6.3 2.7 4.9 1.8 29 6.7 3.3 5.7 2.1 2SELECT * FROM iris WHERE sl >= 6 ORDER BY DESC classes;如上SQL實現將滿足sl欄位值大於等於5的記錄，按照classes降序排序。我們使用Pandas實現該SQL，程式碼如下：
import pandas as pdiris = pd.read_excel(io="iris.xlsx", sheet_name='iris', usecols=["sl", "sw", "pl", "pw", "classes"])search = iris[(iris["sl"] >= 6)].sort_values( by="classes", ascending=False)print(search)# 執行上述程式碼，輸出結果為： sl sw pl pw classes7 7.7 2.8 6.7 2.0 28 6.3 2.7 4.9 1.8 29 6.7 3.3 5.7 2.1 23 6.8 2.8 4.8 1.4 14 6.7 3.0 5.0 1.7 15 6.0 2.9 4.5 1.5 1UPDATE iris SET classes = 2 WHERE pw = 1.7 AND pl >= 5;如上SQL實現將同時滿足pw = 1.7 和 pl >= 5的記錄中的classes欄位值更新為2。我們使用Pandas實現該SQL，程式碼如下：
import pandas as pdiris = pd.read_excel(io="iris.xlsx", sheet_name='iris', usecols=["sl", "sw", "pl", "pw", "classes"])iris.loc[(iris["pw"] == 1.7) & (iris["pl"] >= 5), "classes"] = 2print(iris[iris["pw"] == 1.7])# 執行上述程式碼，輸出結果為： sl sw pl pw classes4 6.7 3.0 5.0 1.7 2SELECT classes, COUNT(*) FROM iris GROUP BY classes;如上SQL實現 根據classes進行分組，返回classes 及每組數量。我們使用Pandas實現該SQL，程式碼如下：
import pandas as pdiris = pd.read_excel(io="iris.xlsx", sheet_name='iris', usecols=["sl", "sw", "pl", "pw", "classes"])count = iris.groupby("classes").size()print(count)# 執行上述程式碼，輸出結果為：classes0 31 32 4dtype: int64 SELECT classes, avg(pl), max(sl) FROM iris GROUP BY classes;如何SQL實現根據classes進行分組，返回classes值，每個分組的pl平均值以及每個分組的sl最大值。我們使用Pandas實現該SQL，程式碼如下：
import pandas as pdimport numpy as npiris = pd.read_excel(io="iris.xlsx", sheet_name='iris', usecols=["sl", "sw", "pl", "pw", "classes"])search = iris.groupby("classes").agg( {"sl":np.max, "pl":np.mean})print(search)# 執行上述程式碼，輸出結果為： sl plclasses 0 5.4 1.5000001 6.8 4.7666672 7.7 5.550000DELETE FROM iris WHERE pw = 1.7 AND pl >=5;如上SQL實現將同時滿足pw = 1.7 和 pl >= 5的記錄刪除。我們使用Pandas實現該SQL，程式碼如下：
import pandas as pdiris = pd.read_excel(io="iris.xlsx", sheet_name='iris', usecols=["sl", "sw", "pl", "pw", "classes"])drop = iris.drop(iris[(iris["pw"] == 1.7) & (iris["pl"] >= 5)].index)print(drop[drop["pw"] > 1.6])# 執行上述程式碼，輸出結果為： sl sw pl pw classes6 5.6 2.8 4.9 2.0 27 7.7 2.8 6.7 2.0 28 6.3 2.7 4.9 1.8 29 6.7 3.3 5.7 2.1 2接下來介紹如何使用Pandas進行合併查詢及多表關聯查詢，為了演示方便，我們上面示例中的iris資料集，拆分成iris_a，iris_b兩部分，如下：
UNION 合併查詢合併結果 UNION ALL 可能存在重複記錄
合併如下兩個 SELECT 語句的結果集，需注意，UNION ALL 內部的 SELECT 語句必須擁有相同數量的列，列也必須擁有相似的資料型別。同時，每條 SELECT 語句中的列的順序必須相同。
SELECT * FROM iris_a WHERE classes = 1UNION ALLSELECT * FROM iris_b WHERE classes = 1 ;如上SQL實現將兩個查詢結果進行合併，允許存在重複記錄。我們使用 pandas.concat 方法實現該SQL，程式碼如下：
import pandas as pdiris_a = pd.read_excel(io="iris.xlsx", sheet_name='iris_a', usecols=["sl", "sw", "pl", "pw", "classes"])iris_b = pd.read_excel(io="iris.xlsx", sheet_name='iris_b', usecols=["sl", "sw", "pl", "pw", "classes"])data = pd.concat( [iris_a[iris_a["classes"] == 1], iris_b[iris_b["classes"] == 1]])print(data)# 執行上述程式碼，輸出結果為： sl sw pl pw classes3 6.8 2.8 4.8 1.4 14 6.7 3.0 5.0 1.7 10 6.7 3.0 5.0 1.7 11 6.0 2.9 4.5 1.5 1合併結果 UNION 不存在重複記錄
合併如下兩個 SELECT 語句的結果集，同時也需注意，UNION 內部的 SELECT 語句必須擁有相同數量的列，列也必須擁有相似的資料型別。同時，每條 SELECT 語句中的列的順序必須相同。
SELECT * FROM iris_a WHERE classes = 1UNIONSELECT * FROM iris_b WHERE classes = 1 ;如上SQL實現將兩個select查詢結果進行合併，不允許存在重複記錄。我們使用 pandas.concat.drop_duplicates 方法 實現該SQL，程式碼如下：
import pandas as pdiris_a = pd.read_excel(io="iris.xlsx", sheet_name='iris_a', usecols=["sl", "sw", "pl", "pw", "classes"])iris_b = pd.read_excel(io="iris.xlsx", sheet_name='iris_b', usecols=["sl", "sw", "pl", "pw", "classes"])data = pd.concat( [iris_a[iris_a["classes"] == 1], iris_b[iris_b["classes"] == 1]]).drop_duplicates()print(data)# 執行上述程式碼，輸出結果為： sl sw pl pw classes3 6.8 2.8 4.8 1.4 14 6.7 3.0 5.0 1.7 11 6.0 2.9 4.5 1.5 1同樣，我們依舊使用如上演示資料，如下：
內連線 INNER JOIN
獲取iris_a，iris_b兩個表中classes欄位相同的記錄，並返回滿足條件的兩張表中的所有記錄。
SELECT * FROM iris_a INNER JOIN iris_b ON iris_a.classes = iris_b.classes;如上SQL實現iris_a 與 iris_b 按照classes欄位進行內連線。我們使用 pandas.merge(iris_a, iris_b, on='classes') 實現該SQL，程式碼如下：
import pandas as pdiris_a = pd.read_excel(io="iris.xlsx", sheet_name='iris_a', usecols=["sl", "sw", "pl", "pw", "classes"])iris_b = pd.read_excel(io="iris.xlsx", sheet_name='iris_b', usecols=["sl", "sw", "pl", "pw", "classes"])inner = pd.merge(iris_a, iris_b, on="classes")print(inner)# 執行上述程式碼，輸出結果為： sl_x sw_x pl_x pw_x classes sl_y sw_y pl_y pw_y0 6.8 2.8 4.8 1.4 1 6.7 3.0 5.0 1.71 6.8 2.8 4.8 1.4 1 6.0 2.9 4.5 1.52 6.7 3.0 5.0 1.7 1 6.7 3.0 5.0 1.73 6.7 3.0 5.0 1.7 1 6.0 2.9 4.5 1.5左連線 LEFT OUTER JOIN
獲取左表 iris_a 所有記錄，判斷每條資料的 classes 欄位是否能匹配到右表iris_b的資料，無論能否匹配到，左表 iris_a 資料都會保留。若能匹配，則左右表都保留。若不能匹配，右表iris_b欄位都置空NULL，並返回保留的記錄。
SELECT * FROM iris_a LEFT JOIN iris_b ON iris_a.classes = iris_b.classes;如上SQL實現iris_a 與 iris_b 按照classes欄位進行左連線。我們使用 pandas.merge(iris_a, iris_b, on='classes', how='left') 方法實現該SQL，程式碼如下：
import pandas as pdiris_a = pd.read_excel(io="iris.xlsx", sheet_name='iris_a', usecols=["sl", "sw", "pl", "pw", "classes"])iris_b = pd.read_excel(io="iris.xlsx", sheet_name='iris_b', usecols=["sl", "sw", "pl", "pw", "classes"])inner = pd.merge(iris_a, iris_b, on="classes", how="left")print(inner)# 執行上述程式碼，輸出結果為： sl_x sw_x pl_x pw_x classes sl_y sw_y pl_y pw_y0 5.1 3.5 1.4 0.2 0 NaN NaN NaN NaN1 4.9 3.0 1.4 0.2 0 NaN NaN NaN NaN2 5.4 3.9 1.7 0.4 0 NaN NaN NaN NaN3 6.8 2.8 4.8 1.4 1 6.7 3.0 5.0 1.74 6.8 2.8 4.8 1.4 1 6.0 2.9 4.5 1.55 6.7 3.0 5.0 1.7 1 6.7 3.0 5.0 1.76 6.7 3.0 5.0 1.7 1 6.0 2.9 4.5 1.5右連線 RIGHT OUTER JOIN
獲取右表 iris_b 所有記錄，判斷每條資料的 classes 欄位是否能匹配到右表 iris_a 的資料，無論能否匹配到，右表 iris_b 資料都會保留。若能匹配，則左右表都保留。若不能匹配，左表iris_a欄位都置空NULL，並返回保留的記錄。
SELECT * FROM iris_a RIGHT JOIN iris_b ON iris_a.classes = iris_b.classes;如上SQL實現iris_a 與 iris_b 按照classes欄位進行右連線。我們使用 pandas.merge(iris_a, iris_b, on='classes', how='right')實現該SQL，程式碼如下：
import pandas as pdiris_a = pd.read_excel(io="iris.xlsx", sheet_name='iris_a', usecols=["sl", "sw", "pl", "pw", "classes"])iris_b = pd.read_excel(io="iris.xlsx", sheet_name='iris_b', usecols=["sl", "sw", "pl", "pw", "classes"])inner = pd.merge(iris_a, iris_b, on="classes", how="right")print(inner)# 執行上述程式碼，輸出結果為： sl_x sw_x pl_x pw_x classes sl_y sw_y pl_y pw_y0 6.8 2.8 4.8 1.4 1 6.7 3.0 5.0 1.71 6.7 3.0 5.0 1.7 1 6.7 3.0 5.0 1.72 6.8 2.8 4.8 1.4 1 6.0 2.9 4.5 1.53 6.7 3.0 5.0 1.7 1 6.0 2.9 4.5 1.54 NaN NaN NaN NaN 2 5.6 2.8 4.9 2.05 NaN NaN NaN NaN 2 7.7 2.8 6.7 2.06 NaN NaN NaN NaN 2 6.3 2.7 4.9 1.87 NaN NaN NaN NaN 2 6.7 3.3 5.7 2.1