只会Pandas？来学习这25种Pandas变SQL的方法，让你的数据分析更得心应手！

Original 数据应用学院大数据应用 2023-08-17

今日份知识你摄入了么？

图片来自Unsplash，作者James Yarema

为什么要学习这些？

毫无疑问，SQL和Pandas都是数据科学家处理数据的强大工具。

一般来说，SQL是一种用于管理和操作数据库中数据的语言，而Pandas是Python中的数据操作和分析库。

此外，SQL通常用于从数据库中提取数据，在Python中进行分析（主要使用Pandas）。它的工具和功能齐全，能很好地处理表格数据，如数据操作、数据分析和可视化等。

将SQL和Pandas一起使用，我们就能清理、变换和分析大型数据集，创建复杂的数据管道和模型，这对作为数据科学家大有裨益，也因此，我们必须精通它们。在本文中，我将带你一步一步走，一起将最常见的Pandas操作变为SQL查询。

我们开始吧！

数据集

出于演示目的，我使用Faker创建了一个虚拟数据集：

随机员工数据集(图片由作者提供)

#1读取CSV文件

Pandas

CSV是最常用的用于读取Pandas数据帧的文件格式，我们需要用好Pandas中的pd.read_csv()。

file = "data.csv"df = pd.read_csv(file)

SQL

要想在数据库中创建表，我们首先要创建一个空表，定义其框架。

%%sqldrop table if exists employee;CREATE TABLE employee( ID integer, first_name varchar(20), last_name varchar(20), gender varchar(2), salary integer, level integer, date_of_joining date);

然后，我们将CSV文件的内容（如果第一行是标题，则从第二行开始）转储到上面创建的表中。

%%sql

LOAD DATA INFILE 'data.csv'INTO TABLE employeeFIELDS TERMINATED BY ','LINES TERMINATED BY '\n'IGNORE 1 ROWS;

输出

完成此数据帧/表后，我们将获得以下输出：

读取CSV后的输出(图片由作者提供)

#2显示前X行

Pandas

我们可以在Pandas中使用df.head()。

df.head() ID first_name last_name gender salary level date_of_joining0 1 Peter Sanders M 10000 2 2020-10-031 2 Julie Miller F 12000 2 2020-01-142 3 Rachel Bray F 9000 1 2020-09-033 4 Priti Agarwal F 10000 2 2020-01-034 5 David Cox M 5000 1 2020-05-28

SQL

在MySQL语法中，我们可以在select之后使用limit，并指定要显示的记录数。

%%sqlselect * from employee limit 5; * sqlite://Done.ID first_name last_name gender salary level date_of_joining1 Peter Sanders M 10000 2 2020-03-102 Julie Miller F 12000 2 2020-01-143 Rachel Bray F 9000 1 2020-03-094 Priti Agarwal F 10000 2 2020-03-015 David Cox M 5000 1 2020-05-28

#3输出尺寸

Pandas

根据表的shape属性输出行数和列数。

df.shape(10, 7)

SQL

我们可以使用“count”来输出行数。

%%sqlselect count(1) from employee; * sqlite://Done.count(1)10

#4输出数据类型

Pandas

在这里，我们可以使用dtypes参数输出所有列的数据类型：

df.dtypesID int64first_name objectlast_name objectgender objectsalary int64level int64date_of_joining objectdtype: object

SQL

接着，我们可以按如下方式输出数据类型：

%%sqlDESCRIBE employee;

#5修改列的数据类型

Pandas

在这里，我们可以使用如下的astype()：

df.ID.astype(str)0 11 22 33 44 55 66 77 88 99 10Name: ID, dtype: object

SQL

使用ALTER COLUMN更改列的数据类型。

%%sqlALTER TABLE employeeALTER COLUMN ID varchar(5);

以上操作将永久修改表中列的数据类型。如果你只希望在过滤时执行此操作，请使用cast。

%%sqlselect cast(ID as string)from employee * sqlite://Done.cast(ID as string)12345678910

#6–11过滤数据

在Pandas中有多种过滤数据帧的方法。

#6：你可以按如下方式过滤一列：

print(df[df.gender == "M"]) ID first_name last_name gender salary level date_of_joining0 1 Peter Sanders M 10000 2 2020-10-034 5 David Cox M 5000 1 2020-05-286 7 John Frank M 13000 3 2019-12-117 8 Mark Franklin M 13500 3 2019-12-119 10 Brock Murray M 11000 2 2020-02-13

可以将上述内容变换为SQL，如下所示：

%%sql

select * from employee where gender = "M"; * sqlite://Done.ID first_name last_name gender salary level date_of_joining1 Peter Sanders M 10000 2 2020-03-105 David Cox M 5000 1 2020-05-287 John Frank M 13000 3 2019-11-128 Mark Franklin M 13500 3 2019-11-1210 Brock Murray M 11000 2 2020-02-13

#7：此外，你还可以在多个列上进行过滤：

print(df[(df.gender == "M") & (df.level == 2)]) ID first_name last_name gender salary level date_of_joining0 1 Peter Sanders M 10000 2 2020-10-039 10 Brock Murray M 11000 2 2020-02-13

同样地，我们变换为SQL：

%%sql

select * from employee where gender = "M" and level = 2; * sqlite://Done.ID first_name last_name gender salary level date_of_joining1 Peter Sanders M 10000 2 2020-03-1010 Brock Murray M 11000 2 2020-02-13

#8：你还可以使用isin()从值列表中筛选：

print(df[df.level.isin([3,4])]) ID first_name last_name gender salary level date_of_joining6 7 John Frank M 13000 3 2019-12-117 8 Mark Franklin M 13500 3 2019-12-118 9 Cristina Rel F 15000 4 2019-09-22

为了模拟上述情况，我们在SQL中使用了“in”：

%%sql

select * from employee where level in (3,4); * sqlite://Done.ID first_name last_name gender salary level date_of_joining7 John Frank M 13000 3 2019-11-128 Mark Franklin M 13500 3 2019-11-129 Cristina Rel F 15000 4 2019-09-22

#9：在Pandas中，你还可以使用点运算符选择特定的列。

df.first_name.head(3)0 Peter1 Julie2 RachelName: first_name, dtype: object

在SQL中，我们可以在select之后指定所需的列。

%%sql

select first_namefrom employee limit 3;* sqlite://Done.first_namePeterJulieRachel

#10：如果要在Pandas中选择多个列，可以执行以下操作：

df[["first_name", "date_of_joining"]].head(3) first_name date_of_joining0 Peter 2020-10-031 Julie 2020-01-142 Rachel 2020-09-03

在SQL中的select之后选择多个列，也同样如此：

%%sql

select first_name, date_of_joiningfrom employee limit 3; * sqlite://Done.first_name date_of_joiningPeter 2020-03-10Julie 2020-01-14Rachel 2020-03-09

#11你还可以根据Pandas中的NaN值进行过滤

df[df.first_name.isna()]Empty DataFrameColumns: [ID, first_name, last_name, gender, salary, level, date_of_joining]Index: []

我们没有NaN值，因此看不到行。

如果是SQL，如下所示：

%%sql

select *from employee where first_name is NULL;* sqlite://Done.ID first_name last_name gender salary level date_of_joining

#12我们还可以尝试一些复杂的字符串过滤

df[df.first_name.str.startswith("J")] ID first_name last_name gender salary level date_of_joining1 2 Julie Miller F 12000 2 2020-01-146 7 John Frank M 13000 3 2019-12-11

在SQL中，我们可以使用LIKE语句。

%%sql

select * from employee where first_name LIKE "J%"; * sqlite://Done.ID first_name last_name gender Employee_Salary level date_of_joining full_name2 Julie Miller F 12000 2 2020-01-14 Julie Miller7 John Frank M 13000 3 2019-11-12 John Frank

#13你还可以搜索其中的子字符串。假设现在我们要查找“last_name”包含子字符串“an”的所有记录

在Pandas中，我们可以执行以下操作：

print(df[df.last_name.str.contains("an")]) ID first_name last_name gender salary level date_of_joining0 1 Peter Sanders M 10000 2 2020-10-036 7 John Frank M 13000 3 2019-12-117 8 Mark Franklin M 13500 3 2019-12-11

在SQL中，我们可以再次使用LIKE语句。

%%sql

select * from employee where last_name LIKE "%an%"; * sqlite://Done.last_name gender Employee_Salary level date_of_joining full_name1 Peter Sanders M 10000 2 2020-03-10 Peter Sanders7 John Frank M 13000 3 2019-11-12 John Frank8 Mark Franklin M 13500 3 2019-11-12 Mark Franklin

#14–16排序数据

排序是数据科学家用来对数据分等级的另一个操作。

Pandas

使用df.sort_values()对数据帧进行排序。

df.sort_values("salary") ID first_name last_name gender salary level date_of_joining4 5 David Cox M 5000 1 2020-05-285 6 Helly Raval F 8000 1 2020-06-192 3 Rachel Bray F 9000 1 2020-09-030 1 Peter Sanders M 10000 2 2020-10-033 4 Priti Agarwal F 10000 2 2020-01-039 10 Brock Murray M 11000 2 2020-02-131 2 Julie Miller F 12000 2 2020-01-146 7 John Frank M 13000 3 2019-12-117 8 Mark Franklin M 13500 3 2019-12-118 9 Cristina Rel F 15000 4 2019-09-22

你还可以对多个列进行排序：

print(df.sort_values(["salary", "level"])) ID first_name last_name gender salary level date_of_joining4 5 David Cox M 5000 1 2020-05-285 6 Helly Raval F 8000 1 2020-06-192 3 Rachel Bray F 9000 1 2020-09-030 1 Peter Sanders M 10000 2 2020-10-033 4 Priti Agarwal F 10000 2 2020-01-039 10 Brock Murray M 11000 2 2020-02-131 2 Julie Miller F 12000 2 2020-01-146 7 John Frank M 13000 3 2019-12-117 8 Mark Franklin M 13500 3 2019-12-118 9 Cristina Rel F 15000 4 2019-09-22

最后，我们还可以使用ascending（升序）参数为不同的列指定不同的条件（升序/降序）。

df.sort_values(["last_name", "level"], ascending=[False, True]) ID first_name last_name gender salary level date_of_joining0 1 Peter Sanders M 10000 2 2020-10-038 9 Cristina Rel F 15000 4 2019-09-225 6 Helly Raval F 8000 1 2020-06-199 10 Brock Murray M 11000 2 2020-02-131 2 Julie Miller F 12000 2 2020-01-147 8 Mark Franklin M 13500 3 2019-12-116 7 John Frank M 13000 3 2019-12-114 5 David Cox M 5000 1 2020-05-282 3 Rachel Bray F 9000 1 2020-09-033 4 Priti Agarwal F 10000 2 2020-01-03

这里，对应ascending的列表指示last_name按降序排序，而level按升序排序。

SQL

在SQL中，我们可以使用order by字句来执行此操作。

%%sql

select *from employee order by salary;* sqlite://Done.ID first_name last_name gender salary level date_of_joining5 David Cox M 5000 1 2020-05-286 Helly Raval F 8000 1 2020-06-193 Rachel Bray F 9000 1 2020-03-091 Peter Sanders M 10000 2 2020-03-104 Priti Agarwal F 10000 2 2020-03-0110 Brock Murray M 11000 2 2020-02-132 Julie Miller F 12000 2 2020-01-147 John Frank M 13000 3 2019-11-128 Mark Franklin M 13500 3 2019-11-129 Cristina Rel F 15000 4 2019-09-22

此外，通过在order by字句中指定更多列，我们可以容纳更多有排序条件的列：

%%sql

select *from employee order by salary, level;

* sqlite://Done.ID first_name last_name gender salary level date_of_joining5 David Cox M 5000 1 2020-05-286 Helly Raval F 8000 1 2020-06-193 Rachel Bray F 9000 1 2020-03-091 Peter Sanders M 10000 2 2020-03-104 Priti Agarwal F 10000 2 2020-03-0110 Brock Murray M 11000 2 2020-02-132 Julie Miller F 12000 2 2020-01-147 John Frank M 13000 3 2019-11-128 Mark Franklin M 13500 3 2019-11-129 Cristina Rel F 15000 4 2019-09-22

我们可以为不同的列指定不同的排序顺序，如下所示：

%%sql

select *from employee order by last_name desc, level asc; * sqlite://Done.ID first_name last_name gender salary level date_of_joining1 Peter Sanders M 10000 2 2020-03-109 Cristina Rel F 15000 4 2019-09-226 Helly Raval F 8000 1 2020-06-1910 Brock Murray M 11000 2 2020-02-132 Julie Miller F 12000 2 2020-01-148 Mark Franklin M 13500 3 2019-11-127 John Frank M 13000 3 2019-11-125 David Cox M 5000 1 2020-05-283 Rachel Bray F 9000 1 2020-03-094 Priti Agarwal F 10000 2 2020-03-01

#17填充NaN值

在此，我删除了“薪水”列中的几个值。这是更新后的数据帧：

print(df.head()) ID first_name last_name gender salary level date_of_joining0 1 Peter Sanders M NaN 2 2020-10-031 2 Julie Miller F NaN 2 2020-01-142 3 Rachel Bray F 9000.0 1 2020-09-033 4 Priti Agarwal F 10000.0 2 2020-01-034 5 David Cox M 5000.0 1 2020-05-28

Pandas

在Pandas中，我们可以使用fillna()来填充NaN值：

df.salary.fillna(10000).head()0 10000.01 10000.02 9000.03 10000.04 5000.0Name: salary, dtype: float64

SQL

在SQL中，我们可以使用选择语句。

%%sql

select case when salary is NULL then 10000 else salary end as salary from employeelimit 5; * sqlite://Done.salary10000100009000100005000

#18–19连接数据

Pandas

如果要合并两个数据帧，可以使用pd.merge()：

df1 = ...

df2 = ...

print(df1)print(df2) col1 col2 col30 1 2 A1 3 4 B2 5 6 C col3 col40 A X1 B Y

pd.merge(df1, df2, on = "col3") col1 col2 col3 col40 1 2 A X1 3 4 B Y

SQL

%%sql

select * fromtable1 join table2 on (table1.col3 = table2.col3); * sqlite://Done.col1 col2 col3 col3_1 col41 2 A A X3 4 B B Y

另一种方法是连接它们。

Pandas

看下面的数据帧：

df1 = ...print(df1) col1 col2 col30 1 2 A1 3 4 B2 5 6 C

在Pandas中，你可以使用concat()，将其传给另一个数据帧，将其连接为列表/元组。

pd.concat((df1, df1)) col1 col2 col30 1 2 A1 3 4 B2 5 6 C0 1 2 A1 3 4 B2 5 6 C

SQL

使用SQL中的UNION（只保留特定行）和UNION ALL（保留所有行）也同样能做到。

%%sql

select * from table1union allselect * from table1; * sqlite://Done.col1 col2 col31 2 A3 4 B5 6 C1 2 A3 4 B5 6 C

#20分组数据

Pandas

要对数据帧进行分组并聚合，可以使用Pandas中的groupby()，如下所示：

df.groupby("level").salary.mean()level1 7333.3333332 10750.0000003 13250.0000004 15000.000000Name: salary, dtype: float64

SQL

在SQL中，可以使用GROUP BY字句在SELECT字句中指定聚合。

%%sql

select level, avg(salary)from employee group by level; * sqlite://Done.level avg(salary)1 7333.3333333333332 10750.03 13250.04 15000.0

结果都一样！

#21–22输出特定值

Pandas

要输出列中不同的值，使用unique()

df.level.unique()array([2, 1, 3, 4])

要输出不同值的数量，使用nunique()。

df.level.nunique()4

SQL

在SQL中，我们可以在select中使用DISTINCT，如下所示：

%%sql

select distinct levelfrom employee; * sqlite://Done.level2134

要计算SQL中不同值的数量，我们可以将COUNT aggregator装给distinct。

%%sql

select count(distinct level)from employee; * sqlite://Done.count(distinct level)4

#23重命名列

Pandas

我们可以使用df.rename()，如下所示：

df.rename(columns = {"salary":"Employee_Salary"}).head() ID first_name last_name gender Employee_Salary level date_of_joining0 1 Peter Sanders M 10000 2 2020-10-031 2 Julie Miller F 12000 2 2020-01-142 3 Rachel Bray F 9000 1 2020-09-033 4 Priti Agarwal F 10000 2 2020-01-034 5 David Cox M 5000 1 2020-05-28

SQL

我们可以使用ALTER TABLE来重命名列：

%%sql

ALTER TABLE employee RENAME COLUMN salary to Employee_Salary; * sqlite://Done.1

#24删除列

Pandas

使用df.drop()：

df.drop(columns = ["last_name"]).head() ID first_name gender salary level date_of_joining0 1 Peter M 10000 2 2020-10-031 2 Julie F 12000 2 2020-01-142 3 Rachel F 9000 1 2020-09-033 4 Priti F 10000 2 2020-01-034 5 David M 5000 1 2020-05-28

SQL

与重命名类似，我们可以使用ALTER TABLE和RENAME来删除。

%%sql

ALTER TABLE employee DROP COLUMN last_name;

#25创建新列

假设我们要创建一个新的列“full_name”，它是列first_name和last_name的连接，中间有一个空格。

Pandas

我们可以在Pandas中进行一个简单的赋值运算。

df["full_name"] = df["first_name"] + " " + df["last_name"] ID first_name last_name gender salary level date_of_joining full_name0 1 Peter Sanders M 10000 2 2020-10-03 Peter Sanders1 2 Julie Miller F 12000 2 2020-01-14 Julie Miller2 3 Rachel Bray F 9000 1 2020-09-03 Rachel Bray3 4 Priti Agarwal F 10000 2 2020-01-03 Priti Agarwal4 5 David Cox M 5000 1 2020-05-28 David Cox

SQL

在SQL中，我们首先需要添加新列：

%%sql

ALTER TABLE employeeADD full_name varchar(40); * sqlite://Done.[]

接下来，我们使用SQL中的SET来设置该值。

%%sql

UPDATE employeeSET full_name = first_name || " " || last_name; * sqlite://10 rows affected.[]

“||”在SQLite中用作连接运算符。点此链接深入了解：https://www.sqlitetutorial.net/sqlite-string-functions/sqlite-concat/

结语

祝贺你！你现在已经了解了Pandas中最常见的SQL变换方法。

我已经努力做到面面俱到了，不过，我应该还是没能罗列齐全。

请批评指正！

一如既往，感谢你的阅读！

图片来自作者

原文作者：Avi Chawla

翻译作者：高佑兮

美工编辑：过儿

校对审稿：Chuang

原文链接：https://towardsdatascience.com/pandas-isnt-enough-learn-these-25-pandas-to-sql-translations-to-upgrade-your-data-analysis-game-af8d0c26948d

2022 IDEAS全球人工智能大会扫码购票

往期精彩回顾

2022年IDEAS全球人工智能大会火热抢票中！

招募志愿者啦！2022 IDEAS全球人工智能大会，与你不见不散！

Python的自回归分布滞后模型简介

3步走方略——用Python为数据科学项目收集数据

担任数据科学经理的前半年，我学到了什么？

点「在看」的人都变好看了哦

点击“阅读原文”查看数据应用学院核心课程

桐城一派｜突发！湖南省财政厅厅长刘文杰坠楼身亡

因为地铁逃票，警察拔枪乱射，无辜乘客爆头

陈佩斯，这次真悬了！

不能返税、不能补贴，招商局长们怎么办？

大，无需多言，事实胜于雄辩

只会Pandas？来学习这25种Pandas变SQL的方法，让你的数据分析更得心应手！

您可能也对以下帖子感兴趣

桐城一派｜突发！湖南省财政厅厅长刘文杰坠楼身亡

因为地铁逃票，警察拔枪乱射，无辜乘客爆头

陈佩斯，这次真悬了！

不能返税、不能补贴，招商局长们怎么办？

大，无需多言，事实胜于雄辩

生成图片，分享到微信朋友圈

只会Pandas？来学习这25种Pandas变SQL的方法，让你的数据分析更得心应手！

您可能也对以下帖子感兴趣