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

本文說(shuō)明

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

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

本文大綱

引入相關(guān)庫(kù)和數(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中，選擇是使用您要選擇的列（用逗號(hào)分隔）或（*選擇所有列）來(lái)完成的。

SELECT?'總費(fèi)用',?'小費(fèi)',?'是否吸煙',?'吃飯時(shí)間'
FROM?df
LIMIT?5;

對(duì)于pandas，通過(guò)將列名列表傳遞給DataFrame來(lái)完成列選擇。

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

結(jié)果如下：

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

在SQL中，您可以添加一個(gè)計(jì)算列：

SELECT?*,?"小費(fèi)"/"總費(fèi)用"?as?"小費(fèi)占比"
FROM?df
LIMIT?5;

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

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

結(jié)果如下：

2. Where按條件查詢

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

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

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

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

結(jié)果如下：

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

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

結(jié)果如下：

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

SELECT?*
FROM?df
WHERE?吃飯時(shí)間?=?'晚餐'?AND?小費(fèi)?>?5.00;

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

df[(df['吃飯時(shí)間']?==?'晚餐')?&?(df['小費(fèi)']?>?5.00)]

結(jié)果如下：

3. in和not in條件查詢

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

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

對(duì)比到DataFrame中，我們?cè)倏纯丛趺醋觯?/span>

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

結(jié)果如下：

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

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

結(jié)果如下：

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

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

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

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

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

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

結(jié)果如下：

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

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

結(jié)果如下：

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

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

結(jié)果如下：

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

在SQL中：

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

在Dataframe中：

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

結(jié)果如下：

通過(guò)將一列列傳遞給方法，來(lái)完成按多個(gè)列分組groupby()。在SQL中：

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

在Dataframe中：

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

結(jié)果如下：

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

可以使用join()或merge()執(zhí)行JOIN。默認(rèn)情況下，join()將在其索引上聯(lián)接DataFrame。每個(gè)方法都有參數(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è)我們有兩個(gè)數(shù)據(jù)庫(kù)表，它們的名稱和結(jié)構(gòu)與我們的DataFrames相同?，F(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完成這個(gè)操作的，這將在下面一個(gè)知識(shí)點(diǎn)中體現(xiàn)。

在Dataframe中：

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

結(jié)果如下：

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

UNION（ALL）操作在Dataframe中可以使用concat()來(lái)執(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可能有些費(fèi)勁，以前的文章中已經(jīng)提到過(guò)，這里也就沒(méi)有多余的敘述。

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

df?=?pd.DataFrame({"name":["張三","王五","李四","張三","王五","張三","李四","李四","王五"],
???????????????????"subject":["語(yǔ)文","英語(yǔ)","數(shù)學(xué)","數(shù)學(xué)","語(yǔ)文","英語(yǔ)","語(yǔ)文","英語(yǔ)","數(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é)果如下：

??