Python Pandas可以像SQL那樣，進行數據篩選統計

作者：軟件測試開發技術棧 2020-12-14 13:24:17

大數據后端

本文通過Pandas實現SQL語法中條件過濾、排序、關聯、合并、更新、刪除等簡單及復雜操作，使得我們對方法的理解更加深刻，更加得心應手。

相對于學習Pandas各種數據篩選操作，SQL語法顯得更加簡潔清晰，若能夠將SQL語法與Pandas中對應的函數的使用方法關聯起來，對于我們應用Pandas進行數據篩選來講無疑是一個福音。

本文通過Pandas實現SQL語法中條件過濾、排序、關聯、合并、更新、刪除等簡單及復雜操作，使得我們對方法的理解更加深刻，更加得心應手。

演示數據集

本文采用安德森鳶尾花卉(iris)數據集進行演示，iris數據集包含150個樣本，對應數據集的每行數據。每行數據包含每個樣本的四個特征和樣本的類別信息，因此iris數據集是一個150行*5列的二維表。

我們可以 UCI Iris dataset 獲取或者使用 from sklearn.datasets import load_iris 方式獲取，為了演示方便我們只取其中10行數據，如下：

接下來，就讓我們一起學習一下，如何Pandas實現SQL語法中條件過濾、排序、關聯、合并、更新、刪除等數據查詢操作。

字段查詢 SELECT

SELECT sl, sw, pl, pw FROM iris LIMIT 2;

如上SQL實現返回每行記錄的 sl,sw,pl,pw 字段，僅返回2行記錄。我們使用Pandas實現如上SQL的功能，代碼如下：

import pandas as pd 
 
iris = 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   pw 
0  5.1  3.5  1.4  0.2 
1  4.9  3.0  1.4  0.2

簡單的條件過濾查詢 WHERE

SELECT * FROM iris WHERE classes = 1 LIMIT 2;

如上SQL實現了查詢滿足classes=1的記錄，并返回2行。我們使用Pandas實現該SQL，代碼如下：

import pandas as pd 
 
iris = 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  classes 
3  6.8  2.8  4.8  1.4        1 
4  6.7  3.0  5.0  1.7        1

多條件的與或過濾查詢 WHERE AND|OR

與關系 &

SELECT * FROM iris WHERE classes = 1 AND pl >= 5 LIMIT 2;

如上SQL實現查詢同時滿足classes=1 和 pl >=5 兩個條件的記錄，并返回2行。我們使用Pandas實現該SQL，代碼如下：

import pandas as pd 
 
iris = 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  classes 
4  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 pd 
 
iris = 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  classes 
0  5.1  3.5  1.4  0.2        0 
2  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 pd 
 
iris = 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 pd 
 
iris = 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  classes 
0  5.1  3.5  1.4  0.2        0 
1  4.9  3.0  1.4  0.2        0 
2  5.4  3.9  1.7  0.4        0 
3  6.8  2.8  4.8  1.4        1 
4  6.7  3.0  5.0  1.7        1 
5  6.0  2.9  4.5  1.5        1 
6  5.6  2.8  4.9  2.0        2 
7  7.7  2.8  6.7  2.0        2 
8  6.3  2.7  4.9  1.8        2 
9  6.7  3.3  5.7  2.1        2

排序 ORDER BY ASC|DESC

SELECT * FROM iris WHERE sl >= 6 ORDER BY DESC classes;

如上SQL實現將滿足sl字段值大于等于5的記錄，按照classes降序排序。我們使用Pandas實現該SQL，代碼如下：

import pandas as pd 
 
iris = 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  classes 
7  7.7  2.8  6.7  2.0        2 
8  6.3  2.7  4.9  1.8        2 
9  6.7  3.3  5.7  2.1        2 
3  6.8  2.8  4.8  1.4        1 
4  6.7  3.0  5.0  1.7        1 
5  6.0  2.9  4.5  1.5        1

更新 UPDATE

UPDATE iris SET classes = 2 WHERE pw = 1.7 AND pl >= 5;

如上SQL實現將同時滿足pw = 1.7 和 pl >= 5的記錄中的classes字段值更新為2。我們使用Pandas實現該SQL，代碼如下：

import pandas as pd 
 
iris = 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"] = 2 
 
print(iris[iris["pw"] == 1.7]) 
 
# 執行上述代碼，輸出結果為： 
    sl   sw   pl   pw  classes 
4  6.7  3.0  5.0  1.7        2

分組統計 GROUP BY

SELECT classes, COUNT(*) FROM iris GROUP BY classes;

如上SQL實現根據classes進行分組，返回classes 及每組數量。我們使用Pandas實現該SQL，代碼如下：

import pandas as pd 
 
iris = pd.read_excel(io="iris.xlsx", 
                     sheet_name='iris', 
                     usecols=["sl", "sw", "pl", "pw", "classes"]) 
 
count = iris.groupby("classes").size() 
 
print(count) 
 
# 執行上述代碼，輸出結果為： 
classes 
0    3 
1    3 
2    4 
dtype: int64

分組統計聚合輸出

SELECT classes, avg(pl), max(sl) FROM iris GROUP BY classes;

如何SQL實現根據classes進行分組，返回classes值，每個分組的pl平均值以及每個分組的sl最大值。我們使用Pandas實現該SQL，代碼如下：

import pandas as pd 
import numpy as np 
iris = 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        pl 
classes                
0        5.4  1.500000 
1        6.8  4.766667 
2        7.7  5.550000

刪除

DELETE FROM iris WHERE pw = 1.7 AND pl >=5;

如上SQL實現將同時滿足pw = 1.7 和 pl >= 5的記錄刪除。我們使用Pandas實現該SQL，代碼如下：

import pandas as pd 
 
iris = 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  classes 
6  5.6  2.8  4.9  2.0        2 
7  7.7  2.8  6.7  2.0        2 
8  6.3  2.7  4.9  1.8        2 
9  6.7  3.3  5.7  2.1        2

UNION & JOIN 演示數據集

接下來介紹如何使用Pandas進行合并查詢及多表關聯查詢，為了演示方便，我們上面示例中的iris數據集，拆分成iris_a，iris_b兩部分，如下：

UNION 合并查詢

合并結果 UNION ALL 可能存在重復記錄

合并如下兩個 SELECT 語句的結果集，需注意，UNION ALL 內部的 SELECT 語句必須擁有相同數量的列，列也必須擁有相似的數據類型。同時，每條 SELECT 語句中的列的順序必須相同。

SELECT * FROM iris_a WHERE classes = 1 
UNION ALL 
SELECT * FROM iris_b WHERE classes = 1 ;

如上SQL實現將兩個查詢結果進行合并，允許存在重復記錄。我們使用 pandas.concat 方法實現該SQL，代碼如下：

import pandas as pd 
 
iris_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  classes 
3  6.8  2.8  4.8  1.4        1 
4  6.7  3.0  5.0  1.7        1 
0  6.7  3.0  5.0  1.7        1 
1  6.0  2.9  4.5  1.5        1

合并結果 UNION 不存在重復記錄

合并如下兩個 SELECT 語句的結果集，同時也需注意，UNION 內部的 SELECT 語句必須擁有相同數量的列，列也必須擁有相似的數據類型。同時，每條 SELECT 語句中的列的順序必須相同。

SELECT * FROM iris_a WHERE classes = 1 
UNION 
SELECT * FROM iris_b WHERE classes = 1 ;

如上SQL實現將兩個select查詢結果進行合并，不允許存在重復記錄。我們使用 pandas.concat.drop_duplicates 方法實現該SQL，代碼如下：

import pandas as pd 
 
iris_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  classes 
3  6.8  2.8  4.8  1.4        1 
4  6.7  3.0  5.0  1.7        1 
1  6.0  2.9  4.5  1.5        1

JOIN 連接查詢

同樣，我們依舊使用如上演示數據，如下：

內連接 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 pd 
 
iris_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_y 
0   6.8   2.8   4.8   1.4        1   6.7   3.0   5.0   1.7 
1   6.8   2.8   4.8   1.4        1   6.0   2.9   4.5   1.5 
2   6.7   3.0   5.0   1.7        1   6.7   3.0   5.0   1.7 
3   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 pd 
 
iris_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_y 
0   5.1   3.5   1.4   0.2        0   NaN   NaN   NaN   NaN 
1   4.9   3.0   1.4   0.2        0   NaN   NaN   NaN   NaN 
2   5.4   3.9   1.7   0.4        0   NaN   NaN   NaN   NaN 
3   6.8   2.8   4.8   1.4        1   6.7   3.0   5.0   1.7 
4   6.8   2.8   4.8   1.4        1   6.0   2.9   4.5   1.5 
5   6.7   3.0   5.0   1.7        1   6.7   3.0   5.0   1.7 
6   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 pd 
 
iris_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_y 
0   6.8   2.8   4.8   1.4        1   6.7   3.0   5.0   1.7 
1   6.7   3.0   5.0   1.7        1   6.7   3.0   5.0   1.7 
2   6.8   2.8   4.8   1.4        1   6.0   2.9   4.5   1.5 
3   6.7   3.0   5.0   1.7        1   6.0   2.9   4.5   1.5 
4   NaN   NaN   NaN   NaN        2   5.6   2.8   4.9   2.0 
5   NaN   NaN   NaN   NaN        2   7.7   2.8   6.7   2.0 
6   NaN   NaN   NaN   NaN        2   6.3   2.7   4.9   1.8 
7   NaN   NaN   NaN   NaN        2   6.7   3.3   5.7   2.1

責任編輯：未麗燕來源：今日頭條