對比sql，學(xué)習(xí)pandas操作

↑↑↑關(guān)注后"星標"簡說Python
人人都可以簡單入門Python、爬蟲、數(shù)據(jù)分析
 簡說Python推薦 
譯者：黃偉呢   來源：數(shù)據(jù)分析與統(tǒng)計學(xué)之美

閱讀須知

本文翻譯自pandas官方文檔。

由于許多潛在的 Pandas 用戶對SQL有一定的了解 ，因此本頁旨在提供一些示例，說明如何使用 Pandas 執(zhí)行各種 SQL 操作。

如果您不熟悉 Pandas，您可能需要先閱讀 10 Minutes的官方文檔，以熟悉該庫。按照慣例，我們按如下方式，導(dǎo)入 pandas 和 NumPy：

import pandas as pd
import numpy as np

大多數(shù)示例，將使用tip數(shù)據(jù)集，用于Pandas 測試。我們將數(shù)據(jù)讀入一個名為 DataFrame 的數(shù)據(jù)框，tips并假設(shè)我們有一個同名和結(jié)構(gòu)相同的數(shù)據(jù)庫表。

url = ("https://raw.github.com/pandas-dev/pandas/master/pandas/tests/io/data/csv/tips.csv")
tips = pd.read_csv(url)
tips

1. 副本與就地操作

大多數(shù) Pandas 操作返回 Series/DataFrame 的副本。要使更改“保持不變”，您需要分配給一個新變量。

sorted_df = df.sort_values("col1")

或覆蓋原來的。

df = df.sort_values("col1")

如果您想就地操作，您將看到某些方法可用的 inplace=True 關(guān)鍵字參數(shù)。

df.sort_values("col1", inplace=True)

2. select關(guān)鍵字

在 SQL 中，選擇是使用逗號分隔的列列表完成的，您要選擇（或* 選擇所有列）：

SELECT total_bill, tip, smoker, time
FROM tips;

使用 Pandas，列選擇是通過將列名列表，傳遞給您的 DataFrame 來完成的：

tips[["total_bill", "tip", "smoker", "time"]]

結(jié)果如下：

在沒有列名列表的情況下，調(diào)用 DataFrame 將顯示所有列（類似于 SQL 的 *）。

在 SQL 中，您可以添加計算列：

SELECT *, tip/total_bill as tip_rate
FROM tips;

您可以使用DataFrame.assign()方法追加新列：

tips.assign(tip_rate=tips["tip"] / tips["total_bill"])

結(jié)果如下：

3. where關(guān)鍵字

SQL 中的過濾是通過 WHERE 子句完成的。

SELECT *
FROM tips
WHERE time = 'Dinner';

可以通過多種方式過濾數(shù)據(jù)幀；其中最直觀的是使用布爾索引。

tips[tips["total_bill"] > 10]

結(jié)果如下：

上面的語句只是將一個True/False對象傳遞給 DataFrame，返回所有帶有True的行。

is_dinner = tips["time"] == "Dinner"
is_dinner.value_counts()
tips[is_dinner]

結(jié)果如下：

就像 SQL 的OR和AND一樣，可以使用| ( OR) 和&( AND)將多個條件傳遞給 DataFrame 。

例子：晚餐超過 5 美元的小費；

SELECT *
FROM tips
WHERE time = 'Dinner' AND tip > 5.00;

在Pandas中：

tips[(tips["time"] == "Dinner") & (tips["tip"] > 5.00)]

結(jié)果如下：

例子：至少 5 人的派對小費或賬單總額超過 45 美元；

SELECT *
FROM tips
WHERE size >= 5 OR total_bill > 45;

在Pandas中：

tips[(tips["size"] >= 5) | (tips["total_bill"] > 45)]

結(jié)果如下：

NULL 檢查是使用notna()和isna() 方法完成的。

frame = pd.DataFrame({"col1": ["A", "B", np.NaN, "C", "D"], "col2": ["F", np.NaN, "G", "H", "I"]})
frame

結(jié)果如下：

假設(shè)我們有一個與上面的 DataFrame 結(jié)構(gòu)相同的表。col2通過以下查詢，我們只能看到IS NULL的記錄：

SELECT *
FROM frame
WHERE col2 IS NULL;

在Pandas中：

frame[frame["col2"].isna()]

結(jié)果如下：

獲取其中的物品col1，IS NOT NULL可以做到的，notna()也可以做到。

SELECT *
FROM frame
WHERE col1 IS NOT NULL;

在Pandas中：

frame[frame["col1"].notna()]

結(jié)果如下：

4. group by關(guān)鍵字

在 Pandas 中，SQL 的 GROUP BY 操作是使用類似命名的 groupby() 方法執(zhí)行的。groupby() 通常是指我們希望將數(shù)據(jù)集拆分為多個組，應(yīng)用一些函數(shù)（通常是聚合），然后將這些組組合在一起的過程。

一個常見的 SQL 操作是獲取整個數(shù)據(jù)集中每個組中的記錄數(shù)。例如，一個查詢讓我們知道性別留下的小費數(shù)量：

SELECT sex, count(*)
FROM tips
GROUP BY sex;
/*
Female     87
Male      157
*/

在Pandas中：

tips.groupby("sex").size()

結(jié)果如下：

請注意，在 Pandas 代碼中，我們使用了 size() 而不是 count()。這是因為 count() 將函數(shù)應(yīng)用于每一列，返回每一列中 NOT NULL 記錄的數(shù)量。

tips.groupby("sex").count()

結(jié)果如下：

或者，我們可以將 count() 方法應(yīng)用于單個列：

tips.groupby("sex")["total_bill"].count()

結(jié)果如下：

也可以同時應(yīng)用多個功能。例如，假設(shè)我們想查看小費金額在一周中的某天有何不同 - agg() 允許您將字典傳遞給分組的 DataFrame，指示將哪些函數(shù)應(yīng)用于特定列。

SELECT day, AVG(tip), COUNT(*)
FROM tips
GROUP BY day;
/*
Fri   2.734737   19
Sat   2.993103   87
Sun   3.255132   76
Thu  2.771452   62
*/

在Pandas中：

tips.groupby("day").agg({"tip": np.mean, "day": np.size})

結(jié)果如下：

通過將列列表傳遞給 groupby() 方法，來完成按多列分組。

SELECT smoker, day, COUNT(*), AVG(tip)
FROM tips
GROUP BY smoker, day;
/*
smoker day
No     Fri      4  2.812500
       Sat     45  3.102889
       Sun     57  3.167895
       Thu    45  2.673778
Yes    Fri     15  2.714000
       Sat     42  2.875476
       Sun     19  3.516842
       Thu    17  3.030000
*/

在Pandas中：

tips.groupby(["smoker", "day"]).agg({"tip": [np.size, np.mean]})

結(jié)果如下：

5. JOIN關(guān)鍵字

可以使用 join() 或 merge() 執(zhí)行 JOIN。默認情況下，join() 將在其索引上加入 DataFrame。每個方法都有參數(shù)，允許您指定要執(zhí)行的連接類型（LEFT、RIGHT、INNER、FULL）或要連接的列（列名或索引）。

df1 = pd.DataFrame({"key": ["A", "B", "C", "D"], "value": np.random.randn(4)})
df2 = pd.DataFrame({"key": ["B", "D", "D", "E"], "value": np.random.randn(4)})

假設(shè)我們有兩個與 DataFrame 名稱和結(jié)構(gòu)相同的數(shù)據(jù)庫表。

現(xiàn)在讓我們來看看各種類型的 JOIN。

① 內(nèi)連接

SELECT *
FROM df1
INNER JOIN df2
  ON df1.key = df2.key;

在Pandas中：

# 默認情況下合并執(zhí)行 INNER JOIN
pd.merge(df1, df2, on="key")

結(jié)果如下：

當(dāng)您希望將一個 DataFrame 的列與另一個 DataFrame 的索引連接時，merge() 還提供參數(shù)。

indexed_df2 = df2.set_index("key")
pd.merge(df1, indexed_df2, left_on="key", right_index=True)

結(jié)果如下：

② 左外連接

顯示 df1 中的所有記錄。

SELECT *
FROM df1
LEFT OUTER JOIN df2
  ON df1.key = df2.key;

在Pandas中：

pd.merge(df1, df2, on="key", how="left")

結(jié)果如下：

③ 右連接

顯示 df2 中的所有記錄。

SELECT *
FROM df1
RIGHT OUTER JOIN df2
  ON df1.key = df2.key;

在Pandas中：

pd.merge(df1, df2, on="key", how="right")

結(jié)果如下：

④ 全連接

pandas 還允許 FULL JOIN，它顯示數(shù)據(jù)集的兩側(cè)，無論連接的列是否找到匹配項。在撰寫本文時，并非所有 RDBMS (MySQL) 都支持 FULL JOIN。

顯示兩個表中的所有記錄。

# 這個不是MySQL寫法
SELECT *
FROM df1
FULL OUTER JOIN df2
  ON df1.key = df2.key;

在Pandas中：

pd.merge(df1, df2, on="key", how="outer")

結(jié)果如下：

⑤ 聯(lián)合

UNION ALL 可以使用 concat() 執(zhí)行。

SELECT city, rank
FROM df1
UNION ALL
SELECT city, rank
FROM df2;
/*
         city  rank
      Chicago     1
San Francisco     2
New York City     3
      Chicago     1
       Boston     4
  Los Angeles     5
*/

在Pandas中：

df1 = pd.DataFrame({"city": ["Chicago", "San Francisco", "New York City"], "rank": range(1, 4)})
df2 = pd.DataFrame({"city": ["Chicago", "Boston", "Los Angeles"], "rank": [1, 4, 5]})
pd.concat([df1, df2])

結(jié)果如下：

SQL UNION 類似于 UNION ALL，但是 UNION 會刪除重復(fù)的行。

SELECT city, rank
FROM df1
UNION
SELECT city, rank
FROM df2;
-- notice that there is only one Chicago record this time
/*
         city  rank
      Chicago     1
San Francisco     2
New York City     3
       Boston     4
  Los Angeles     5
*/

在 Pandas 中，您可以將 concat() 與 drop_duplicates() 結(jié)合使用，實現(xiàn)此操作。

pd.concat([df1, df2]).drop_duplicates()

結(jié)果如下：

6. limit關(guān)鍵字

SELECT * FROM tips
LIMIT 10;

在Pandas中：

tips.head(10)

結(jié)果如下：

7. SQL和pandas的一些等效操作

① 具有偏移量的前 n 行

-- MySQL
SELECT * FROM tips
ORDER BY tip DESC
LIMIT 10 OFFSET 5;

在Pandas中：

tips.nlargest(10 + 5, columns="tip").tail(10)

結(jié)果如下：

② 每組前 n 行

# 需要窗口函數(shù)
SELECT * FROM (
  SELECT
    t.*,
    ROW_NUMBER() OVER(PARTITION BY day ORDER BY total_bill DESC) AS rn
  FROM tips t
)
WHERE rn < 3
ORDER BY day, rn;

在Pandas中：

(
    tips.assign(
        rn=tips.sort_values(["total_bill"], ascending=False)
        .groupby(["day"])
        .cumcount()
        + 1
    )
    .query("rn < 3")
    .sort_values(["day", "rn"])
)

結(jié)果如下：

同樣可以使用 rank(method='first') 函數(shù)。

SELECT * FROM (
  SELECT
    t.*,
    RANK() OVER(PARTITION BY sex ORDER BY tip) AS rnk
  FROM tips t
  WHERE tip < 2
)
WHERE rnk < 3
ORDER BY sex, rnk;

在Pandas中：

(
    tips.assign(
        rn=tips.sort_values(["total_bill"].rank(method='first'), ascending=False)
        
    )
    .query("rnk < 3")
    .sort_values(["day", "rnk"])
)

結(jié)果如下：

讓我們找到每個性別組（提示 < 2）的提示（等級 < 3）。請注意，當(dāng)使用 rank(method='min') 函數(shù)時，rnk_min 對于相同的提示保持不變（如 Oracle 的 RANK() 函數(shù)）

(
    tips[tips["tip"] < 2]
    .assign(rnk_min=tips.groupby(["sex"])["tip"].rank(method="min"))
    .query("rnk_min < 3")
    .sort_values(["sex", "rnk_min"])
)

結(jié)果如下：

8. update關(guān)鍵字

UPDATE tips
SET tip = tip*2
WHERE tip < 2;

在Pandas中：

tips.loc[tips["tip"] < 2, "tip"] *= 2

9. delete關(guān)鍵字

DELETE FROM tips
WHERE tip > 9;

在Pandas中，我們選擇應(yīng)該保留的行，而不是刪除它們。

tips = tips.loc[tips["tip"] <= 9]

--END--
老表推薦
圖書介紹：《Python自動化測試實戰(zhàn)》本書從自動化測試理論入手，全面地闡述自動化測試的意義及實施過程。全文以Python語言驅(qū)動，結(jié)合真實案例分別對主流自動化測試工具Selenium、RobotFramework、Postman、Python Requests、Appium等進行系統(tǒng)講解。通過學(xué)習(xí)本書，讀者可以快速掌握主流自動化測試技術(shù)，并幫助讀者豐富測試思維，提高Python編碼能力。

掃碼即可加我微信
老表朋友圈經(jīng)常有贈書/紅包福利活動


學(xué)習(xí)更多：
整理了我開始分享學(xué)習(xí)筆記到現(xiàn)在超過250篇優(yōu)質(zhì)文章，涵蓋數(shù)據(jù)分析、爬蟲、機器學(xué)習(xí)等方面，別再說不知道該從哪開始，實戰(zhàn)哪里找了
優(yōu)秀的讀者都知道，“點贊”傳統(tǒng)美德不能丟