- 微信
- 微博
  
  分享文章到微博
- 复制链接
  
  复制链接到剪贴板

我通宵翻译了Pandas官方文档，又写了这一份Mysql万字肝货操作！

数据分析与统计学之美发表于 2021/09/28 11:13:49 2021/09/28

【摘要】对比系列第十三课

前面，我写了一篇文章，讲述Pandas实现Excel电子表格相关操作，深受大家的喜欢。

今天，我继续为大家讲述Pandas如何实现MySQL中的相关操作。

由于许多潜在的 Pandas 用户对SQL有一定的了解，因此本页旨在提供一些示例，说明如何使用 Pandas 执行各种 SQL 操作。

如果您不熟悉 Pandas，您可能需要先阅读 10 Minutes的官方文档，以熟悉该库。
按照惯例，我们按如下方式，导入 pandas 和 NumPy：

import pandas as pd
import numpy as np

大多数示例将使用tips在 Pandas 测试中找到的数据集。我们将数据读入一个名为 DataFrame 的数据框，tips并假设我们有一个同名和结构相同的数据库表。

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

1. 副本与就地操作

大多数 Pandas 操作返回 Series/DataFrame 的副本。要使更改“保持不变”，您需要分配给一个新变量。

sorted_df = df.sort_values("col1")

或覆盖原来的。

df = df.sort_values("col1")

如果您想就地操作，您将看到某些方法可用的 inplace=True 关键字参数。

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

2. select关键字

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

SELECT total_bill, tip, smoker, time
FROM tips;

使用 Pandas，列选择是通过将列名列表，传递给您的 DataFrame 来完成的：

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

结果如下：

在没有列名列表的情况下，调用 DataFrame 将显示所有列（类似于 SQL 的 *）。

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

SELECT *, tip/total_bill as tip_rate
FROM tips;

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

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

结果如下：

3. where关键字

SQL 中的过滤是通过 WHERE 子句完成的。

SELECT *
FROM tips
WHERE time = 'Dinner';

可以通过多种方式过滤数据帧；其中最直观的是使用布尔索引。

tips[tips["total_bill"] > 10]

结果如下：

上面的语句只是将一个True/False对象传递给 DataFrame，返回所有带有True的行。

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

结果如下：

就像 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)]

结果如下：

例子：至少 5 人的派对小费或账单总额超过 45 美元；

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

在Pandas中：

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

结果如下：

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

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

结果如下：

假设我们有一个与上面的 DataFrame 结构相同的表。col2通过以下查询，我们只能看到IS NULL的记录：

SELECT *
FROM frame
WHERE col2 IS NULL;

在Pandas中：

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

结果如下：

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

SELECT *
FROM frame
WHERE col1 IS NOT NULL;

在Pandas中：

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

结果如下：

4. group by关键字

在 Pandas 中，SQL 的 GROUP BY 操作是使用类似命名的 groupby() 方法执行的。 groupby() 通常是指我们希望将数据集拆分为多个组，应用一些函数（通常是聚合），然后将这些组组合在一起的过程。

一个常见的 SQL 操作是获取整个数据集中每个组中的记录数。例如，一个查询让我们知道性别留下的小费数量：

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

在Pandas中：

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

结果如下：

请注意，在 Pandas 代码中，我们使用了 size() 而不是 count()。这是因为 count() 将函数应用于每一列，返回每一列中 NOT NULL 记录的数量。

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

结果如下：

或者，我们可以将 count() 方法应用于单个列：

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

结果如下：

也可以同时应用多个功能。例如，假设我们想查看小费金额在一周中的某天有何不同 - agg() 允许您将字典传递给分组的 DataFrame，指示将哪些函数应用于特定列。

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

结果如下：

通过将列列表传递给 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]})

结果如下：

5. JOIN关键字

可以使用 join() 或 merge() 执行 JOIN。默认情况下，join() 将在其索引上加入 DataFrame。每个方法都有参数，允许您指定要执行的连接类型（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)})

假设我们有两个与 DataFrame 名称和结构相同的数据库表。

现在让我们来看看各种类型的 JOIN。

① 内连接

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

在Pandas中：

# 默认情况下合并执行 INNER JOIN
pd.merge(df1, df2, on="key")

结果如下：

当您希望将一个 DataFrame 的列与另一个 DataFrame 的索引连接时，merge() 还提供参数。

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

结果如下：

② 左外连接

显示 df1 中的所有记录。

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

在Pandas中：

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

结果如下：

③ 右连接

显示 df2 中的所有记录。

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

在Pandas中：

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

结果如下：

④ 全连接

pandas 还允许 FULL JOIN，它显示数据集的两侧，无论连接的列是否找到匹配项。在撰写本文时，并非所有 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")

结果如下：

⑤ 联合

UNION ALL 可以使用 concat() 执行。

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

结果如下：

SQL UNION 类似于 UNION ALL，但是 UNION 会删除重复的行。

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() 结合使用，实现此操作。

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

结果如下：

6. limit关键字

SELECT * FROM tips
LIMIT 10;

在Pandas中：

tips.head(10)

结果如下：

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)
Out[37]:

结果如下：

② 每组前 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;

在Pandas中：

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

结果如下：

同样可以使用 rank(method=‘first’) 函数。

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

结果如下：

让我们找到每个性别组（提示 < 2）的提示（等级 < 3）。请注意，当使用 rank(method=‘min’) 函数时，rnk_min 对于相同的提示保持不变（如 Oracle 的 RANK() 函数）

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

结果如下：

8. update关键字

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

在Pandas中：

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

9. delete关键字

DELETE FROM tips
WHERE tip > 9;

在 Pandas 中，我们选择应该保留的行而不是删除它们。

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

点赞
收藏
关注作者

0/1000

抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认取消

加入云驻计划，成为创作者

华为云周边好礼
免费体验产品
特殊身份标识
线下官方门票
内部专家零距离
与10000+优质创作者共同成长

立即加入

我通宵翻译了Pandas官方文档，又写了这一份Mysql万字肝货操作！

1. 副本与就地操作

2. select关键字

3. where关键字

4. group by关键字

5. JOIN关键字

① 内连接

② 左外连接

③ 右连接

④ 全连接

⑤ 联合

6. limit关键字

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

① 具有偏移量的前 n 行

② 每组前 n 行

8. update关键字

9. delete关键字

全部回复

设置昵称

关于作者

目录

加入云驻计划，成为创作者

我通宵翻译了Pandas官方文档，又写了这一份Mysql万字肝货操作！

1. 副本与就地操作

2. select关键字

3. where关键字

4. group by关键字

5. JOIN关键字

① 内连接

② 左外连接

③ 右连接

④ 全连接

⑤ 联合

6. limit关键字

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

① 具有偏移量的前 n 行

② 每组前 n 行

8. update关键字

9. delete关键字

全部回复

设置昵称

关于作者

目录

加入云驻计划，成为创作者

推荐阅读

相关产品