课程
SQL 中的数据处理
4 小时
338.4K
在本教程中，我们将讨论在 pandas 框架中何时以及如何（以及何时不能）使用 SQL 功能。此外，我们还将查看多种实现该方法的示例，并将结果与等效的纯 pandas 代码进行比较。
为何在 pandas 中使用 SQL？
当 pandas 已经是一个面向数据分析的全能型包时，为什么还要将它与 SQL 结合使用？
答案是，在某些情况下，尤其是对于复杂程序，SQL 查询相比相应的 pandas 代码更直观、可读性更强。对于先用 SQL 处理数据、后来才学习 pandas 的人来说，这一点尤为明显。
为了直观感受 SQL 的可读性，假设我们有一张名为 penguins 的表（一个 dataframe），其中包含关于企鹅的各类信息（本教程稍后会使用该表）。要提取所有“性别为雄性且鳍长超过 210 毫米”的企鹅物种的唯一值，在 pandas 中需要如下代码：
penguins[(penguins['sex'] == 'Male') & (penguins['flipper_length_mm'] > 210)]['species'].unique()而使用 SQL 获取相同信息，只需运行以下代码：
SELECT DISTINCT species FROM penguins WHERE sex = 'Male' AND flipper_length_mm > 210第二段 SQL 代码几乎像自然英语句子一样，因此更直观。我们还可以把它分成多行以进一步提升可读性：
SELECT DISTINCT species
FROM penguins
WHERE sex = 'Male'
AND flipper_length_mm > 210既然我们已经认识到了在 pandas 中使用 SQL 的优势，接下来看看如何在技术上将二者结合。
如何使用 pandasql
pandasql 是一个 Python 库，它允许您在无需连接任何 SQL 服务器的情况下，通过运行 SQL 命令来查询 pandas 的 dataframe。在底层，它使用 SQLite 语法，可自动检测任意 pandas dataframe，并将其视作常规的 SQL 表。
环境准备
首先，安装 pandasql：
pip install pandasql然后，导入所需的包：
from pandasql import sqldf
import pandas as pd如上，我们直接从 pandasql 导入了 sqldf() 函数，它几乎是该库唯一有意义的函数。顾名思义，它用于以 SQL 语法查询 dataframe。除此之外，pandasql 还带有两个简单的内置数据集，可通过不言自明的函数 load_births() 和 load_meat() 加载。
pandasql 语法
sqldf() 函数的语法非常简单：
sqldf(query, env=None)其中，query 是必需参数，接收字符串形式的 SQL 查询；env 是可选参数（且很少有用），可为 locals() 或 globals()，允许 sqldf() 访问您 Python 环境中的相应变量集合。
sqldf() 函数会将查询结果以 pandas dataframe 的形式返回。
可以使用 pandasql 的场景
pandasql 允许使用 SQL 的数据查询语言（DQL）来处理数据。换句话说，利用 pandasql，我们可以对存储在数据库中的数据运行查询，从中检索所需信息。具体而言，我们可以对数据进行访问、提取、筛选、排序、分组、连接、聚合，并执行数学或逻辑运算。
不适用 pandasql 的场景
除了 DQL 之外，pandasql 不支持使用 SQL 的其他子集。这意味着我们无法使用 pandasql 去修改（update、truncate、insert 等）表，或更改表中的数据（update、delete 或 insert）。
此外，由于该库基于 SQL 语法，我们还应注意 SQLite 的一些已知特性。
pandasql 的使用示例
现在，我们将更细致地了解如何使用 pandasql 的 sqldf() 函数在 pandas 的 dataframe 上运行 SQL 查询。为便于练习，我们加载 seaborn 库的一个内置数据集——penguins：
import seaborn as sns
penguins = sns.load_dataset('penguins')
print(penguins.head())输出：
species island bill_length_mm bill_depth_mm flipper_length_mm \
0 Adelie Torgersen 39.1 18.7 181.0
1 Adelie Torgersen 39.5 17.4 186.0
2 Adelie Torgersen 40.3 18.0 195.0
3 Adelie Torgersen NaN NaN NaN
4 Adelie Torgersen 36.7 19.3 193.0
body_mass_g sex
0 3750.0 Male
1 3800.0 Female
2 3250.0 Female
3 NaN NaN
4 3450.0 Female如果您需要温习 SQL，我们的 SQL Fundamentals 技能路径是不错的参考。
使用 pandasql 提取数据
print(sqldf('''SELECT species, island
FROM penguins
LIMIT 5'''))输出：
species island
0 Adelie Torgersen
1 Adelie Torgersen
2 Adelie Torgersen
3 Adelie Torgersen
4 Adelie Torgersen
以上，我们从 penguins dataframe 中提取了前五只企鹅的物种和所在岛屿信息。注意，运行 sqldf() 会返回一个 pandas 的 dataframe：
print(type(sqldf('''SELECT species, island
FROM penguins
LIMIT 5''')))输出：
<class 'pandas.core.frame.DataFrame'>等效的纯 pandas 写法为：
print(penguins[['species', 'island']].head())输出：
species island
0 Adelie Torgersen
1 Adelie Torgersen
2 Adelie Torgersen
3 Adelie Torgersen
4 Adelie Torgersen
另一个示例是从某一列提取唯一值：
print(sqldf('''SELECT DISTINCT species
FROM penguins'''))输出：
species
0 Adelie
1 Chinstrap
2 Gentoo在 pandas 中，可以这样写：
print(penguins['species'].unique())输出：
['Adelie' 'Chinstrap' 'Gentoo']使用 pandasql 排序数据
print(sqldf('''SELECT body_mass_g
FROM penguins
ORDER BY body_mass_g DESC
LIMIT 5'''))输出：
body_mass_g
0 6300.0
1 6050.0
2 6000.0
3 6000.0
4 5950.0上面我们按体重对企鹅进行降序排序，并展示了体重的前五个数值。
在 pandas 中，可以这样写：
print(penguins['body_mass_g'].sort_values(ascending=False,
ignore_index=True).head())输出：
0 6300.0
1 6050.0
2 6000.0
3 6000.0
4 5950.0
Name: body_mass_g, dtype: float64使用 pandasql 筛选数据
让我们尝试在“为何在 pandas 中使用 SQL”一节中提到的同一示例：提取“性别为雄性且鳍长超过 210 毫米”的企鹅物种的唯一值：
print(sqldf('''SELECT DISTINCT species
FROM penguins
WHERE sex = 'Male'
AND flipper_length_mm > 210'''))输出：
species
0 Chinstrap
1 Gentoo在上述查询中，我们基于两个条件对数据进行了筛选：sex = 'Male' 和 flipper_length_mm > 210。
等效的 pandas 代码会显得稍微繁琐一些：
print(penguins[(penguins['sex'] == 'Male') & (penguins['flipper_length_mm'] > 210)]['species'].unique())输出：
['Chinstrap' 'Gentoo']使用 pandasql 分组与聚合
现在，我们进行分组与聚合，找出每个物种在 dataframe 中最长的喙长度：
print(sqldf('''SELECT species, MAX(bill_length_mm)
FROM penguins
GROUP BY species'''))输出：
species MAX(bill_length_mm)
0 Adelie 46.0
1 Chinstrap 58.0
2 Gentoo 59.6对应的 pandas 代码：
print(penguins[['species', 'bill_length_mm']].groupby('species', as_index=False).max())输出：
species bill_length_mm
0 Adelie 46.0
1 Chinstrap 58.0
2 Gentoo 59.6使用 pandasql 进行数学运算
借助 pandasql，我们可以方便地对数据执行数学或逻辑运算。假设我们要计算每只企鹅的“喙长与喙深之比”，并展示该指标的前五个值：
print(sqldf('''SELECT bill_length_mm / bill_depth_mm AS length_to_depth
FROM penguins
ORDER BY length_to_depth DESC
LIMIT 5'''))输出：
length_to_depth
0 3.612676
1 3.510490
2 3.505882
3 3.492424
4 3.458599请注意，这里我们为比值列使用了别名 length_to_depth。否则，该列名将会是冗长难看的 bill_length_mm / bill_depth_mm。
在 pandas 中，我们需要先创建一个新的比值列：
penguins['length_to_depth'] = penguins['bill_length_mm'] / penguins['bill_depth_mm']
print(penguins['length_to_depth'].sort_values(ascending=False, ignore_index=True).head())输出：
0 3.612676
1 3.510490
2 3.505882
3 3.492424
4 3.458599
Name: length_to_depth, dtype: float64总结
总而言之，在本教程中，我们探讨了为何以及何时可以将 SQL 的功能与 pandas 结合，以编写更好、更高效的代码。我们讨论了为此如何设置并使用 pandasql 库，以及该包的局限性。最后，我们给出了多个 pandasql 实战的常见示例，并在每种情况下都与其对应的 pandas 代码进行了对比。
现在，您已经具备在真实项目中将 SQL 应用于 pandas 的一切所需。一个很好的练习场所是 DataLab——DataCamp 的 AI 加持数据笔记本，具备出色的 SQL 支持。