對比MySQL，學(xué)會在Pandas中實現(xiàn)SQL的常用操作

本文說明

我相信你如果學(xué)習(xí)了Pandas，就一定是想從事數(shù)據(jù)分析這樣一個行業(yè)。既然你想從事數(shù)據(jù)分析行業(yè)，那我就默認(rèn)你肯定是會Sql，即使你現(xiàn)在不會，你以后也要會。

本文初步對比Sql，說明如何使用Pandas中執(zhí)行各種SQL操作。真的！好像對比起來，學(xué)習(xí)什么都快了。

本文大綱

引入相關(guān)庫和數(shù)據(jù)讀取

import?numpy?as?np
import?pandas?as?pd

df?=?pd.read_csv("tips.csv",encoding="gbk")
df.head()

結(jié)果如下：

1. Select數(shù)據(jù)查詢

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

SELECT?'總費用',?'小費',?'是否吸煙',?'吃飯時間'
FROM?df
LIMIT?5;

對于pandas，通過將列名列表傳遞給DataFrame來完成列選擇。

df[['總費用',?'小費',?'是否吸煙',?'吃飯時間']].head(5)

結(jié)果如下：

注意：調(diào)用不帶列名列表的DataFrame將顯示所有列（稱為SQL的*）。

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

SELECT?*,?"小費"/"總費用"?as?"小費占比"
FROM?df
LIMIT?5;

對于pandas，可以使用DataFrame.assign()的方法追加新列。

df.assign(小費占比=df['小費']?/?df['總費用']).head(5)

結(jié)果如下：

2. Where按條件查詢

通過WHERE子句在SQL中進(jìn)行過濾。

SELECT?*
FROM?df
WHERE?吃飯時間?=?'晚餐'
LIMIT?5;

DataFrame可以通過多種方式進(jìn)行過濾。最直觀的方法是使用布爾索引。

df[df['吃飯時間']?==?'晚餐'].head(5)

結(jié)果如下：

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

is_dinner?=?df['吃飯時間']?==?'晚餐'
is_dinner.value_counts()
df[is_dinner].head(5)

結(jié)果如下：

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

SELECT?*
FROM?df
WHERE?吃飯時間?=?'晚餐'?AND?小費?>?5.00;

那么，在DataFrame代碼應(yīng)該怎么寫呢？

df[(df['吃飯時間']?==?'晚餐')?&?(df['小費']?>?5.00)]

結(jié)果如下：

3. in和not in條件查詢

我們先來看看在SQL中應(yīng)該怎么做。

SELECT?*
FROM?df
WHERE?星期幾?in?(周四，周五)
LIMIT?5;

對比到DataFrame中，我們再看看怎么做？

df[df["星期幾"].isin(['周四','周五'])].head(5)

結(jié)果如下：

如果是not in，對比到DataFrame中，直接使用取反操作（~）。

df[~df["星期幾"].isin(['周四','周五'])].head(5)

結(jié)果如下：

4.group by分組統(tǒng)計

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

常見的SQL操作是獲取整個數(shù)據(jù)集中每個組中的記錄數(shù)。例如，通過查詢可以了解性別留下的提示數(shù)量。

SELECT?"性別",?count(*)
FROM?df
GROUP?BY?性別;

對比到DataFrame中，應(yīng)該是這樣的。

df.groupby('性別').size()

結(jié)果如下：

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

df.groupby('性別').count()

結(jié)果如下：

如果想要使用count()方法應(yīng)用于單個列的話，應(yīng)該這樣做。（后面需要隨意選擇一列）

df.groupby('性別')["總費用"].count()

結(jié)果如下：

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

在SQL中：

SELECT?星期幾,?AVG(小費),?COUNT(*)
FROM?df
GROUP?BY?星期幾;

在Dataframe中：

df.groupby('星期幾').agg({'小費':?np.mean,?'星期幾':?np.size})

結(jié)果如下：

通過將一列列傳遞給方法，來完成按多個列分組groupby()。在SQL中：

SELECT?是否吸煙,?星期幾,?COUNT(*),?AVG(小費)
FROM?tips
GROUP?BY?是否吸煙,?星期幾;

在Dataframe中：

df.groupby(['是否吸煙',?'星期幾']).agg({'小費':?[np.size,?np.mean]})

結(jié)果如下：

5.join數(shù)據(jù)關(guān)聯(lián)

可以使用join()或merge()執(zhí)行JOIN。默認(rèn)情況下，join()將在其索引上聯(lián)接DataFrame。每個方法都有參數(shù)，可讓您指定要執(zhí)行的聯(lián)接類型（LEFT，RIGHT，INNER，F(xiàn)ULL）或要聯(lián)接的列（列名或索引）。但是還是推薦使用merge()函數(shù)。

1）數(shù)據(jù)準(zhǔn)備

df1?=?pd.DataFrame({'key':?['A',?'B',?'C',?'D'],
????????????????????'value':?[1,?3,?5,?7]})

df2?=?pd.DataFrame({'key':?['B',?'D',?'D',?'E'],
????????????????????'value':[2,?4,?6,?8]})

結(jié)果如下：

假設(shè)我們有兩個數(shù)據(jù)庫表，它們的名稱和結(jié)構(gòu)與我們的DataFrames相同。現(xiàn)在讓我們看一下各種類型的JOIN。

2）inner join內(nèi)連接

在SQL中：

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

在Dataframe中：

pd.merge(df1,?df2,?on='key')

結(jié)果如下：

3）left outer join左連接

在SQL中：

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

在Dataframe中：

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

結(jié)果如下：

4）right join右連接

在SQL中：

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

在Dataframe中：

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

結(jié)果如下：

5）full join全連接

注意在MySQL中是不支持全連接的，一般是使用union完成這個操作的，這將在下面一個知識點中體現(xiàn)。

在Dataframe中：

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

結(jié)果如下：

6. union數(shù)據(jù)合并

UNION（ALL）操作在Dataframe中可以使用concat()來執(zhí)行。

1）數(shù)據(jù)準(zhǔn)備

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]})

結(jié)果如下：

2）union all不去重合并

在SQL中：

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
"""

在Dataframe中：

#?默認(rèn)就是axis=0
pd.concat([df1,?df2],axis=0)

結(jié)果如下：

3）union去重合并

在SQL中：

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
"""

在Dataframe中：

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

結(jié)果如下：

7.取group分組后的Topn

在MySQL8.0以前的版本，可能是不支持窗口函數(shù)，因此求Topn可能有些費勁，以前的文章中已經(jīng)提到過，這里也就沒有多余的敘述。

有下面一堆數(shù)據(jù)，怎么求出Topn呢？

df?=?pd.DataFrame({"name":["張三","王五","李四","張三","王五","張三","李四","李四","王五"],
???????????????????"subject":["語文","英語","數(shù)學(xué)","數(shù)學(xué)","語文","英語","語文","英語","數(shù)學(xué)"],
???????????????????"score":[95,80,83,80,90,71,88,70,78]})
df

結(jié)果如下：

在Dataframe中：

df.groupby(["subject"]).apply(lambda?df:df.sort_values("score",ascending=True))

結(jié)果如下：

??