如何在 Pandas 中使用 SQL

如果您考虑 Pandas DataFrame 的结构和 SQL 数据库中表的结构，它们的结构非常相似。它们都由数据点或值组成，每一行都有一个唯一的索引，每一列都有一个唯一的名称。因此，SQL 允许您快速访问您正在处理的任何项目所需的特定信息。但是，可以使用 Pandas 进行非常相似的查询！在这篇博文中，我将向您展示如何做到这一点，同时解释您需要哪个库来实现它。

.query()

在使用 SQL 时，获取我们需要的信息称为查询数据。在 Pandas 中，有一个内置的查询方法可以让您做完全相同的事情，称为.query()。这既节省了时间，又使代码中的查询更加连贯，因为您不必使用切片语法。例如，使用.query()方法在 Pandas 中查询数据的简短示例是：

query_df = df.query("Col_1 > Col_2")

否则，如果您不使用此方法获取数据而是使用切片语法，则它看起来像这样：

query_df = df[df[df['Col_1'] > df['Col_2']]]

就像我说的，.query()方法让你的代码看起来更专业、更高效。我要注意的一件重要事情是，如果/当您决定在 Pandas 查询中使用“and”或“or”时，您实际上不能使用“and”或“or”——您必须使用符号代替“和”（&）和“或”（|）。下面是一个使用“&”来帮助澄清的例子：

query_df = df.query("Col_1 > Col_2 & Col_2 <= Col_3")

pandasql 库

众所周知，使用 SQL 和/或其所有变体的能力是市场上数据科学家最需要的工作技能之一——即使在大流行期间也是如此。幸运的是，Python 中有一个名为pandasql的库，它允许您编写 SQL 风格的语法来从 Pandas DataFrames 收集数据！这对于想要练习 SQL 技能的有抱负的数据科学家和习惯于使用 SQL 样式语法收集数据的经验丰富的数据科学家来说都非常有用。要将其安装到您的计算机上，只需使用 !pip install：

pip install pandasql

然后，要将其导入您的笔记本，您需要从pandasql导入一个sqldf对象：

from pandasql import sqldf

导入所有内容后，最好编写一个快速的 lambda 函数，以便更轻松地编写查询。这样做的原因是您不必在每次使用对象时都传入全局变量。以下是我教过并成功使用的 lambda 函数：

pysqldf = lambda q: sqldf(q, globals())

现在，每当您将查询传递到pysqldf 时，全局变量将在 lambda 中传递，这样您就不必为使用的每个对象一遍又一遍地执行此操作。

现在您已设置好一切并准备就绪，您可以使用与 SQL 相同的语法在 DataFrame 中查询数据！这是一个例子——这个查询将从 df 返回前 10 个名称：

q = """SELECT Name 
       FROM df 
       LIMIT 10;"""

names = pysqldf(q)
names

您查询的复杂性取决于您的需求和您作为数据科学家的技能。因此，如果您习惯于使用 SQL 风格的语法，或者希望提高您的 SQL 语法技能，使用pandasql可能是继续组织数据和练习技能的好方法。

Demo

merge_data_sql = sqldf(""" SELECT * 
                            FROM apm_data_df 
                            LEFT OUTER JOIN pingips_data_df
                            ON apm_data_df.pingip = pingips_data_df.pingip 
                            WHERE apm_data_df IS null
                            """)


merge_result = sqldf(merge_data_sql, globals())

SQL(Structured Query Language, 结构化查询语言)是用于访问和处理数据库的标准的计算机语言，也是数据清洗的神器。

日常的数据统计分析工作中，80%的时间是在做数据清洗，只有20%的时间在优化模型、分析统计结果等。数据清洗的工作的重要性不言而喻，今天先简单介绍下数据清洗中最重要的工具--SQL。

1. sqldf包简介

通过sqldf包，可直接在R中的数据框(data.frame)（类似数据库中的表）上进行SQL操作，R中数据清洗常用的dplyr包的许多函数操作也是将相应的命令转化为SQL语句来执行。sqldf包支持SQLite(默认)， H2，MySQL及PostgreSQL作为后台来执行SQL语句。SQLite及H2是两个无服务器端，无需配置的轻量级数据库管理系统，在R中安装好并加载sqldf包就可以直接使用SQLite数据库来操作数据，H2同时还需要安装并加载RH2包即可使用（MySQL和PostgreSQL需要繁琐的服务器端客户端的配置）。SQLite灵活轻便，应用非常广泛，集成到了许多IOS及Android的app中。SQLite 没有一个单独的用于存储日期和/或时间的存储类，但 SQLite 能够把日期和时间存储为文本或数值类型（在用SQLite处理日期数据的时候要非常小心，以后再细说）。

2.R中的简单SQL语句

主要介绍使用SQLite为后台，通过sqldf中执行SQL语句

library(sqldf)

df2 <- sqldf('select * from df'，drv = 'SQLite')

##这里的drv不写默认就是SQLite。选择df数据库所有变量（* 代表所有变量）

Examples

在R中不使用SQL与使用SQL比较

2.1 head

> df1 <- head(warpbreaks, 5)

> df2 <- sqldf('select * from warpbreaks limit 5;')

> identical(df1, df2)

[1] TRUE

2.2 subset

> data(farms, package = 'MASS')

> df1 <- subset(farms, Manag %in% c('SF', 'BF'))

> df2 <- sqldf("select * from farms where Manag in ('SF', 'BF')")

> row.names(df1) <- NULL

> identical(df1, df2)

[1] TRUE

> df1 <- subset(warpbreaks, breaks >= 20 & breaks <= 30)

> df2 <- sqldf('select * from warpbreaks where breaks between 20 and 30;')

> row.names(df1) <- NULL

> identical(df1, df2)

[1] TRUE

> df1 <- subset(farms, Mois == 'M1')

> df2 <- sqldf('select * from farms where Mois = "M1"', row.names = T)

> identical(df1, df2)

[1] TRUE

2.3 rbind

> df1 <- subset(farms, Mois == 'M1')

> df2 <- subset(farms, Mois == 'M2')

> df3 <- sqldf('select * from farms where Mois = "M1"', row.names = T)

> df4 <- sqldf('select * from farms where Mois = "M2"', row.names = T)

> df12 <- rbind(df1, df2)

> df34 <- sqldf('select * from df3 union all select * from df4', row.names = T)

> identical(df12, df34)

[1] TRUE

2.4 aggregate

> df1 <- aggregate(iris[1:2], iris[5], mean)

> df2 <- sqldf('select Species, avg("Sepal.Length") as "Sepal.Length",

+ avg("Sepal.Width") as "Sepal.Width" from iris group by Species;')

> all.equal(df1, df2)

[1] TRUE

2.5 order

> df1 <- head(warpbreaks[order(warpbreaks$breaks, decreasing = T), ], 3)

> df2 <- sqldf('select * from warpbreaks order by breaks desc limit 3;')

> row.names(df1) <- NULL

> identical(df1, df2)

[1] TRUE

作者：wangli_5e2f
链接：https://www.jianshu.com/p/699c2ff06a87
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

继续介绍如何在R中通过sqldf包执行SQL语句

3.SQLite-JOIN

sqldf包默认以SQLite为后台执行SQL语句。

SQLite支持inner join, left (outer) join, full (outer) join和cross join, 并不支持right join。

sqldf包中的SQLite并没有对full (outer) join提供支持，故我们在sqldf中使用SQLite只支持inner join， left (outer) join和cross join，尽管如此，也能满足我们数据清洗中的大多数要求了。

先简单介绍一下各种连接。

3.1 inner join--产生两张表（数据框）的交集

df_x

df_y

df_x left join df_y

3.2 cross join

交叉连接的操作，它们都返回被连接的两个表所有数据行的笛卡尔积，返回到的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。在这里跟inner join效果是一样的。

3.3 left join--左连接

产生左边数据框的完全集，而右边中匹配的则有值，没有匹配的则以NA值取代

虽然SQLite不支持right (outer) join，但是可以通过交换表（数据框）的位置用left (outer) join实现右连接的功能。

3.4 group by 分组统计， order by 排序

按gender, year分组统计value均值,并按性别，年份排序。

Example: 根据疾病报卡数据统计每周发病人数

报卡数据样式如下，下面的日期为每个病例的发病日期，统计每周发病例数（每行为一个病例）

思路大概如下：

1.每一个日期代表一个病例，日期数据可能有缺失，需要生成一个从开始到结束的日期向量。

2.以从开始到结束的日期向量构成左表，与报卡日期进行左连接，没匹配上的日期发病数即为0。

3.根据日期生成星期变量，判断每个星期的结束和开始，并给每个星期顺序编号，以便根据星期编号分组统计发病人数。

具体实现方法可以参考下面的函数：

week_num_f <- function(dates){

##dates为报卡的日期向量，一个日期代表一个病例

df <- data.frame(date = dates)

df$date <- as.character(ymd(df$date))

df$dnum <- 1##发病例数，一行即代表1个病例

##根据报卡数据,判断起止日期

date_max <- max(ymd(df$date))

date_min <- min(ymd(df$date))

##星期天，第一天

gen_con_dates <- function(date_start, date_end){

##生成连续日期向量

date_start <- ymd(date_start)

date_end <- ymd(date_end)

return (as.character((date_start + days(0: (interval(date_start, date_end) / ddays(1))))))

}

##生成从开始到结束的日期数据框

dates_df <- data.frame(date = gen_con_dates(date_min, date_max))

dates_df$date <- as.character(dates_df$date)

##日期顺序编号

dates_df$date_num <- 1:nrow(dates_df)

##星期编号

dates_df$wday <- wday(dates_df$date)

##年份

dates_df$year <- year(dates_df$date)

##星期按照年份排序

wnums_by_year <- c(1)

##星期连续排序

wnums_conti <- c(1)

wnum_conti <- 1

for (r in 2:nrow(dates_df)){

##连续周数

if (dates_df$wday[r] == 1){##如果是周日，周数加1

wnum_conti <- wnum_conti + 1

}

wnums_conti <- c(wnums_conti, wnum_conti)

dates_df$wnum_conti <- wnums_conti

df <- sqldf('select dates_df.*, df.dnum from

dates_df left join df on

dates_df.date == df.date;')

df$dnum <- ifelse(is.na(df$dnum), 0, df$dnum)##如果日期没有匹配，说明当天没有病例，及病例数为0

df_wnum_conti <- sqldf('select *, sum(dnum) as count_dnum

from df group by wnum_conti;')##按星期分组统计每周发病例数

df_wnum_conti[, c('date_num', 'dnum', 'wday')] <- NULL

return (data_wnum_conti = df_wnum_conti)

}

作者：wangli_5e2f
链接：https://www.jianshu.com/p/9e28e35a711e
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

posted @ 2021-11-14 11:49 DaisyLinux 阅读(707) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

DaisyLinux

如何在 Pandas 中使用 SQL

.query()

pandasql 库

1. sqldf包简介

2.R中的简单SQL语句

Examples

在R中不使用SQL与使用SQL比较

2.1 head

2.2 subset

2.3 rbind

2.4 aggregate

2.5 order

继续介绍如何在R中通过sqldf包执行SQL语句

3.SQLite-JOIN

sqldf包默认以SQLite为后台执行SQL语句。

SQLite支持inner join, left (outer) join, full (outer) join和cross join, 并不支持right join。

sqldf包中的SQLite并没有对full (outer) join提供支持，故我们在sqldf中使用SQLite只支持inner join， left (outer) join和cross join，尽管如此，也能满足我们数据清洗中的大多数要求了。

先简单介绍一下各种连接。

3.1 inner join--产生两张表（数据框）的交集

df_x

df_y

df_x left join df_y

3.2 cross join

交叉连接的操作，它们都返回被连接的两个表所有数据行的笛卡尔积，返回到的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。在这里跟inner join效果是一样的。

3.3 left join--左连接

产生左边数据框的完全集，而右边中匹配的则有值，没有匹配的则以NA值取代

虽然SQLite不支持right (outer) join，但是可以通过交换表（数据框）的位置用left (outer) join实现右连接的功能。

3.4 group by 分组统计， order by 排序

按gender, year分组统计value均值,并按性别，年份排序。

Example: 根据疾病报卡数据统计每周发病人数

报卡数据样式如下，下面的日期为每个病例的发病日期， 统计每周发病例数（每行为一个病例）

思路大概如下：

1.每一个日期代表一个病例，日期数据可能有缺失， 需要生成一个从开始到结束的日期向量。

2.以从开始到结束的日期向量构成左表，与报卡日期进行左连接， 没匹配上的日期发病数即为0。

3.根据日期生成星期变量， 判断每个星期的结束和开始，并给每个星期顺序编号，以便根据星期编号分组统计发病人数。

具体实现方法可以参考下面的函数：

week_num_f <- function(dates){

##dates为报卡的日期向量，一个日期代表一个病例

df <- data.frame(date = dates)

df$date <- as.character(ymd(df$date))

df$dnum <- 1##发病例数， 一行即代表1个病例

##根据报卡数据,判断起止日期

date_max <- max(ymd(df$date))

date_min <- min(ymd(df$date))

##星期天，第一天

gen_con_dates <- function(date_start, date_end){

##生成连续日期向量

date_start <- ymd(date_start)

date_end <- ymd(date_end)

return (as.character((date_start + days(0: (interval(date_start, date_end) / ddays(1))))))

}

##生成从开始到结束的日期数据框

dates_df <- data.frame(date = gen_con_dates(date_min, date_max))

dates_df$date <- as.character(dates_df$date)

##日期顺序编号

dates_df$date_num <- 1:nrow(dates_df)

##星期编号

dates_df$wday <- wday(dates_df$date)

##年份

dates_df$year <- year(dates_df$date)

##星期按照年份排序

wnums_by_year <- c(1)

##星期连续排序

wnums_conti <- c(1)

wnum_conti <- 1

for (r in 2:nrow(dates_df)){

##连续周数

if (dates_df$wday[r] == 1){##如果是周日， 周数加1

wnum_conti <- wnum_conti + 1

}

wnums_conti <- c(wnums_conti, wnum_conti)

dates_df$wnum_conti <- wnums_conti

df <- sqldf('select dates_df.*, df.dnum from

dates_df left join df on

dates_df.date == df.date;')

df$dnum <- ifelse(is.na(df$dnum), 0, df$dnum)##如果日期没有匹配，说明当天没有病例，及病例数为0

df_wnum_conti <- sqldf('select *, sum(dnum) as count_dnum

from df group by wnum_conti;')##按星期分组统计每周发病例数

df_wnum_conti[, c('date_num', 'dnum', 'wday')] <- NULL

报卡数据样式如下，下面的日期为每个病例的发病日期，统计每周发病例数（每行为一个病例）

1.每一个日期代表一个病例，日期数据可能有缺失，需要生成一个从开始到结束的日期向量。

2.以从开始到结束的日期向量构成左表，与报卡日期进行左连接，没匹配上的日期发病数即为0。

3.根据日期生成星期变量，判断每个星期的结束和开始，并给每个星期顺序编号，以便根据星期编号分组统计发病人数。

df$dnum <- 1##发病例数，一行即代表1个病例

if (dates_df$wday[r] == 1){##如果是周日，周数加1