对比sql,学习pandas操作
简说Python
共 10722字,需浏览 22分钟
·
2021-08-13 14:32
↑↑↑关注后"星标"简说Python
人人都可以简单入门Python、爬虫、数据分析 简说Python推荐 译者:黄伟呢 来源:数据分析与统计学之美
阅读须知
import pandas as pd
import numpy as np
url = ("https://raw.github.com/pandas-dev/pandas/master/pandas/tests/io/data/csv/tips.csv")
tips = pd.read_csv(url)
tips
1. 副本与就地操作
sorted_df = df.sort_values("col1")
df = df.sort_values("col1")
df.sort_values("col1", inplace=True)
2. select关键字
SELECT total_bill, tip, smoker, time
FROM tips;
tips[["total_bill", "tip", "smoker", "time"]]
![](https://filescdn.proginn.com/07cb443f63c0f3f469b5347e4e45d52b/cd9559bbfb0d0757d3768a2d9ceabf59.webp)
SELECT *, tip/total_bill as tip_rate
FROM tips;
tips.assign(tip_rate=tips["tip"] / tips["total_bill"])
![](https://filescdn.proginn.com/3f98f668a9a8f3c6e2ef036547a67858/ddf0c374dbc1079e281a05f2b4a695fc.webp)
3. where关键字
SELECT *
FROM tips
WHERE time = 'Dinner';
布尔索引
。tips[tips["total_bill"] > 10]
![](https://filescdn.proginn.com/d617b75591dff2fddfac5badc7ac1a11/a825ba33cdc79ceb1090b1a1b7abde52.webp)
is_dinner = tips["time"] == "Dinner"
is_dinner.value_counts()
tips[is_dinner]
![](https://filescdn.proginn.com/3f20cdb2e1ac46827484ed0c7deb8533/f154514093758445cb63dae3e7dcc334.webp)
SELECT *
FROM tips
WHERE time = 'Dinner' AND tip > 5.00;
tips[(tips["time"] == "Dinner") & (tips["tip"] > 5.00)]
![](https://filescdn.proginn.com/c56dad5be516dfa779022c62ec1c6370/45b9b6e4b245185f7d7acd58a1cedb21.webp)
SELECT *
FROM tips
WHERE size >= 5 OR total_bill > 45;
tips[(tips["size"] >= 5) | (tips["total_bill"] > 45)]
![](https://filescdn.proginn.com/cb0366983cb015c036b3e4df92e4706e/56799d1e169148ee91d29ef90f9f146f.webp)
frame = pd.DataFrame({"col1": ["A", "B", np.NaN, "C", "D"], "col2": ["F", np.NaN, "G", "H", "I"]})
frame
![](https://filescdn.proginn.com/537e72e67f9787e666ab0df0b158746e/8184cf062b85d14064fb3e3806eba7ec.webp)
SELECT *
FROM frame
WHERE col2 IS NULL;
frame[frame["col2"].isna()]
![](https://filescdn.proginn.com/6213fcaa80ef835cc3a2f7ced869c961/184fae00ea5c598df7d9963553fb730f.webp)
SELECT *
FROM frame
WHERE col1 IS NOT NULL;
frame[frame["col1"].notna()]
![](https://filescdn.proginn.com/3025ac0db1041890adcf993769032761/675959a080ebe9b0723b160af4b792b8.webp)
4. group by关键字
SELECT sex, count(*)
FROM tips
GROUP BY sex;
/*
Female 87
Male 157
*/
tips.groupby("sex").size()
![](https://filescdn.proginn.com/82cc761719b2ec85bd702a5a081be663/e5a1aa998a2652f601da2b13db2b2b0f.webp)
tips.groupby("sex").count()
![](https://filescdn.proginn.com/512f81b2f106081fcebb929eed70288c/adf4f71fa14c9174d4f6a6cef6f84a0e.webp)
tips.groupby("sex")["total_bill"].count()
![](https://filescdn.proginn.com/5dbd6c8433e0e7428438ef8b1e90351d/f455c94b337d0f2ab4bf0f41d22b1744.webp)
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
*/
tips.groupby("day").agg({"tip": np.mean, "day": np.size})
![](https://filescdn.proginn.com/c64779ab4229c89cb296e9c9589a8570/4a81568ef5cd74a4f4ec16c04cdd59ca.webp)
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
*/
tips.groupby(["smoker", "day"]).agg({"tip": [np.size, np.mean]})
![](https://filescdn.proginn.com/bdf5747d68473849de6c0e510b424115/c1110e896e530c94700c4384754942b3.webp)
5. JOIN关键字
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)})
① 内连接
SELECT *
FROM df1
INNER JOIN df2
ON df1.key = df2.key;
# 默认情况下合并执行 INNER JOIN
pd.merge(df1, df2, on="key")
![](https://filescdn.proginn.com/7a4fe2640564b0be6f055bcb7d09de95/5f2aeda120e144929c122e94ca7229d5.webp)
indexed_df2 = df2.set_index("key")
pd.merge(df1, indexed_df2, left_on="key", right_index=True)
![](https://filescdn.proginn.com/6ed36efdda2c6d9e1c569d7402561e0b/16f203c9a58f91ca13684d4b61ca7270.webp)
② 左外连接
SELECT *
FROM df1
LEFT OUTER JOIN df2
ON df1.key = df2.key;
pd.merge(df1, df2, on="key", how="left")
![](https://filescdn.proginn.com/91acdf7e745133797725aeb473dbbcbf/2afa9e24ee49884282d87b24ee2856f8.webp)
③ 右连接
SELECT *
FROM df1
RIGHT OUTER JOIN df2
ON df1.key = df2.key;
pd.merge(df1, df2, on="key", how="right")
![](https://filescdn.proginn.com/9992d2655d590469409b590e62da792f/25a5ed8b0390ce200d3b7bde3d008554.webp)
④ 全连接
# 这个不是MySQL写法
SELECT *
FROM df1
FULL OUTER JOIN df2
ON df1.key = df2.key;
pd.merge(df1, df2, on="key", how="outer")
![](https://filescdn.proginn.com/bd91b41716e84cc6435bdbdecd0f8943/f9e87c8c05e8d42ebc0a6fc4602fc00f.webp)
⑤ 联合
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
*/
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])
![](https://filescdn.proginn.com/05c73525cd0c6e3bdfb66c3d51ffad21/6c9022ae118bc343dfe18c56c51bc181.webp)
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
*/
pd.concat([df1, df2]).drop_duplicates()
![](https://filescdn.proginn.com/f44860cadfcd7c32d83acf0108fc788a/0a1edae35a16176e62c9a0f23c5a794a.webp)
6. limit关键字
SELECT * FROM tips
LIMIT 10;
tips.head(10)
![](https://filescdn.proginn.com/ae23839ca1d0102a089a58ce9545e232/13cda06e2292cf9c959286435acf6518.webp)
7. SQL和pandas的一些等效操作
① 具有偏移量的前 n 行
-- MySQL
SELECT * FROM tips
ORDER BY tip DESC
LIMIT 10 OFFSET 5;
tips.nlargest(10 + 5, columns="tip").tail(10)
![](https://filescdn.proginn.com/87105285a1eadce11dca80f4dcbf6534/1fadc3fd6c8754e890366fa7a9bc4a6e.webp)
② 每组前 n 行
# 需要窗口函数
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;
(
tips.assign(
rn=tips.sort_values(["total_bill"], ascending=False)
.groupby(["day"])
.cumcount()
+ 1
)
.query("rn < 3")
.sort_values(["day", "rn"])
)
![](https://filescdn.proginn.com/2fb3ea269c1cb218d38f3c1c3d5ee827/6f00853769140299517b32509cda4589.webp)
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;
(
tips.assign(
rn=tips.sort_values(["total_bill"].rank(method='first'), ascending=False)
)
.query("rnk < 3")
.sort_values(["day", "rnk"])
)
![](https://filescdn.proginn.com/b11a5fc62b47e2a1dd6456f902dca760/0a6b4dce5bad646df30d40ca6fb4029d.webp)
(
tips[tips["tip"] < 2]
.assign(rnk_min=tips.groupby(["sex"])["tip"].rank(method="min"))
.query("rnk_min < 3")
.sort_values(["sex", "rnk_min"])
)
![](https://filescdn.proginn.com/facd94453980a057a9bb4ea8555aa3aa/ebe9b794806e02ca83394a4bd0bf9f66.webp)
8. update关键字
UPDATE tips
SET tip = tip*2
WHERE tip < 2;
tips.loc[tips["tip"] < 2, "tip"] *= 2
9. delete关键字
DELETE FROM tips
WHERE tip > 9;
tips = tips.loc[tips["tip"] <= 9]
--END--
老表推荐
图书介绍:《Python自动化测试实战》本书从自动化测试理论入手,全面地阐述自动化测试的意义及实施过程。全文以Python语言驱动,结合真实案例分别对主流自动化测试工具Selenium、RobotFramework、Postman、Python Requests、Appium等进行系统讲解。通过学习本书,读者可以快速掌握主流自动化测试技术,并帮助读者丰富测试思维,提高Python编码能力。
扫码即可加我微信
老表朋友圈经常有赠书/红包福利活动
学习更多: 整理了我开始分享学习笔记到现在超过250篇优质文章,涵盖数据分析、爬虫、机器学习等方面,别再说不知道该从哪开始,实战哪里找了 优秀的读者都知道,“点赞”传统美德不能丢
评论