Pandas vs SQL

1、資料查詢

首先,讀取資料

import pandas as pd
import numpy as np
tips = pd.read_csv('tips.csv')
tips
tips[["total_bill", "tip"]]
select total_bill, tip
from tips;
tips['tip_rate'] = tips["tip"] / tips["total_bill"]
select *, tip/total_bill as tip_rate
from tips;
tips[(tips["time"] == "Dinner") & (tips["tip"] > 5.00)]
select *
from tips
where time = 'Dinner' and tip > 5.00;

2、分組聚合

按照某列分組計數

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

'''
sex
Female 87
Male 157
dtype: int64
'''
select sex, count(*)
from tips
group by sex;
tips.groupby(["smoker", "day"]).agg({"tip": [np.size, np.mean]})
select smoker, day, count(*), avg(tip)
from tips
group by smoker, day;

3. join

構造兩個臨時DataFrame

# inner join
pd.merge(df1, df2, on="key")

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

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

# inner join
pd.merge(df1, df2, on="key", how="outer")
# inner join
select *
from df1 inner join df2
on df1.key = df2.key;
# left join
select *
from df1 left join df2
on df1.key = df2.key;
# right join
select *
from df1 right join df2
on df1.key = df2.key;
# full join
select *
from df1 full join df2
on df1.key = df2.key;

4. union

將兩個表縱向堆疊

pd.concat([df1, df2])
select *
from df1

union all

SELECT *
from df2;
pd.concat([df1, df2]).drop_duplicates()
select *
from df1

union

SELECT *
from df2;

5. 開窗

對tips中day列取值相同的記錄按照total_bill排序。

(tips.assign(
rn=tips.sort_values(["total_bill"], ascending=False)
.groupby(["day"])
.cumcount()
+ 1
)
.sort_values(["day", "rn"])
)
select
*,
row_number() over(partition by day order by total_bill desc) as rn
from tips t

文章推薦

餅圖變形記,肝了3000字,收藏就是學會!

--

--

這是一個專注於數據分析職場的內容部落格,聚焦一批數據分析愛好者,在這裡,我會分享數據分析相關知識點推送、(工具/書籍)等推薦、職場心得、熱點資訊剖析以及資源大盤點,希望同樣熱愛數據的我們一同進步! 臉書會有更多互動喔:https://www.facebook.com/shujvfenxi/

Love podcasts or audiobooks? Learn on the go with our new app.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
數據分析那些事

數據分析那些事

這是一個專注於數據分析職場的內容部落格,聚焦一批數據分析愛好者,在這裡,我會分享數據分析相關知識點推送、(工具/書籍)等推薦、職場心得、熱點資訊剖析以及資源大盤點,希望同樣熱愛數據的我們一同進步! 臉書會有更多互動喔:https://www.facebook.com/shujvfenxi/