当pandas用的像sql一样，是否还有必要继续坚持？

最新推荐文章于 2023-10-06 16:38:59 发布

皇家茶壶

最新推荐文章于 2023-10-06 16:38:59 发布

阅读量817

点赞数 4

分类专栏： python sql 文章标签： pandas

本文链接：https://blog.csdn.net/breavo_raw/article/details/108307600

版权

python 同时被 2 个专栏收录

9 篇文章 2 订阅

订阅专栏

sql

2 篇文章 0 订阅

订阅专栏

前言

本篇立眼于公司项目中数据分析与报表生成的技术方案，主要是针对pandas数据处理方面与SQL的异同发表一点心得见解。

目前项目中使用的BI报表功能，其底层数据支撑来自于经过pandas处理生成的H5文件。报表展示的是聚合之后的数据，这就涉及到对数据的筛选，分类以及聚合。

那么pandas的操作与常见的SQL有什么异同呢，下面就一起来看看～：）

什么是pandas

网上针对pandas的解释已经非常多了，这里就不做过多的介绍，简单提两句：
1. pandas是python的数据分析库
2. pandas特别适合结构化数据，经常被数据分析师们用来处理各种各样的文本数据
3. SQL能够做到的事情，它基本都能做到

某些场景下，我们完全可以把pandas当成数据库来使用，虽然pandas的api和sql在用法上存在很大的差异，但是目的却不尽相同。

像使用SQL一样使用pandas

这里以MySQL作为对比，列举相同数据操作下pandas和sql处理的逻辑
给出一列结构化数据

id	student_name	Math	English	Language
1	vivian	70	100	85
2	joke	100	100	100
3	facker	88	60	100

在MySQL以及pandas当中，数据结构是基本以一致的，至少看起来是这样的（都是大宽表）。

简单介绍下pandas的数据结构，其结构化数据分为了三类：

Series，1维序列，可视作为没有column名的、只有一个column的DataFrame；类似于数据库表中的“一列”数据
DataFrame，同Spark SQL中的DataFrame一样，其概念来自于R语言，为多column并schema化的2维结构化数据，可视作为Series的容器（container）；**相当于数据库中的一张表。**是pandas中常用的数据结构
Panel，为3维的结构化数据，可视作为DataFrame的容器；（这个不是很常见）

常用命令

要像使用SQL一样使用pandas，最简单的方式直接直接将SQL与pandas api进行对照，一对比就都出来了，下面来看一看

新增数据

CREATE TABLE `class_demo`.`score` (
  `id` INT NOT NULL,
  `student_name` VARCHAR(45) NULL,
  `Math` INT NULL,
  `English` INT NULL,
  `Language` INT NULL,
  PRIMARY KEY (`id`));

MySQL首先需要新建一个schema，pandas没有schema这个概念,我们需要新建一个dataframe

INSERT

MySQL

INSERT INTO `class_demo`.`score`
(`id`, `student_name`, `Math`, `English`, `Language`)
VALUES
(1, "vivian", 70, 100, 85),
(2, "joke", 100, 100, 100),
(3, "facker", 88, 60, 100);

pandas

import pandas as pd
# 创建dataframe
df = pd.DataFrame({
    'id': [1, 2, 3],
    'student_name': ['vivian', 'joke', 'facker'],
    'Math': [70, 100, 88],
    'English': [100, 100, 60],
    'Language': [85, 100, 100]
})

pandas没有插入的概念，可以创建一个包含新纪录的dataframe，然后合并两个dataframe

df2 = pd.DataFrame({
    'id': [4],
    'student_name': ['mike'],
    'Math': [66],
    'English': [77],
    'Language': [73]
})
df_new = pd.concat([df, df2]).reset_index(drop=True)
print(df_new)

>>> id student_name  Math  English  Language
0    1       vivian    70      100        85
1    2         joke   100      100       100
2    3       facker    88       60       100
3    4         mike    66       77        73

查询数据

MySQL中一个通用的查询结构通常长这样：

# 筛选
SELECT ... FROM ... WHERE ...
# 分组
GROUP BY ... HAVING ...
# 排序
ORDER BY ...
# 按条件读取
LIMIT ... OFFSET ...

上面这些是我们查询时最常用的。(df来源参考插入数据小节)

SQL	pandas
SELECT * FROM score	df
SELECT * FROM score limit 5	df.head(5)
select id from score where student_name = ‘vivian’	df[df.student_name == ‘vivian’].id
select distinct English from score	df.English.unique()
select * from score where English = 100 and student_name = ‘joke’	df[(df.English == 100) & (df.student_name == ‘joke’ )]
select id, student_name from score where English = 100 and student_name = ‘joke’	df[(df.English == 100) & (df.student_name == ‘joke’ )][[‘id’, ‘student_name’]]
	df.query(“English == 100 & student_name == ‘joke’”)[[‘id’, ‘student_name’]]

除了df[df[...]]的语法之外，也可以使用df.query(...)的方式进行查询
另外提醒一下pandas api 筛选指定column注意是双中括号呦～

IN…NOT IN

SQL	pandas
select * from score where student_name in (‘mike’, ‘facker’)	df[df.student_name.isin([‘mike’, ‘facker’])]
select * from score where student_name not in (‘mike’, ‘facker’)	df[~df.student_name.isin([‘mike’, ‘facker’])]

ORDER BY

SQL	pandas
select id from score order by English	df.sort_values(‘English’)[[‘id’]]
select id from score order by English desc	df.sort_values(‘English’, ascending=False)[[‘id’]]

GROUP BY, COUNT, (分组下的)ORDER BY

pandas分组操作采用内置的groupby()方法

SQL	pandas
select English, count(*) from score group by English	df.groupby([‘English’]).size()
select English, student_name, count() from score group by English, student_name order by English, count() desc	df.groupby([‘English’, ‘student_name’]).size().to_frame(‘size’).reset_index().sort_values([‘English’, ‘size’], ascending=[True, False])

注意，pandas中也存在count()方法，其返回只包含非空和非NAN的值，这一点和SQL的COUNT不太相同，要获得与SQL COUNT 相同的结果，需要使用size()

在表格第二个示例中，我们想要通过count(*)的计算结果进行降序排序
首先，在pandas中执行df.groupby([‘English’, ‘student_name’]).size()聚合之后，我们会得到一个pandas的Series（Series解释上文已提及）

English  student_name
60       facker          1
         mike            2
100      joke            1
         vivian          1
dtype: int64

<class 'pandas.core.series.Series'>

后面一列 [1, 2, 1, 1]是groupby聚合后的结果，我们通过to_frame('size') 将结果保存成Dataframe并命名为size

                      size
English student_name      
60      facker           1
        mike             2
100     joke             1
        vivian           1
        
<class 'pandas.core.frame.DataFrame'>

此时的size，已经出现在了Dataframe当中，由于索引编号不一致，展示出来的Dataframe比较丑，这里再使用 .reset_index()，重新进行数据帧的行编号，最后进行排序操作，结果如下

   English student_name  size
1       60         mike     2
0       60       facker     1
2      100         joke     1
3      100       vivian     1

HAVING

数据分组后如果需要进一步过滤，需要使用HAVING语句，对应到pandas的API则是filter()

SQL	pandas
select English, count() from score group by English having count() > 1	df.groupby([‘English’]).filter(lambda x: len(x) > 1).groupby([‘English’]).size().sort_values(ascending=False)

LIMIT…OFFSET

SQL	pandas
select * from score order by Math desc limit 3	df.nlargest(3, columns=‘Math’)
select * from score order by Math desc limit 3 offset 1	df.nlargest(4, columns=‘Math’).tail(1)

第一条是获取Math分数前三的记录，
第二条是获取Math分数前三之后的一条（即第四条）记录；

聚合函数（MIN， MAX， MEAN）

SQL	pandas
select min(English), max(English), median(English), avg(English) from score	df.agg({‘English’: [‘min’, ‘max’, ‘median’, ‘mean’]}).T

这里加.T的作用是将数据转置，将结果转换为一列数据

多表联查（JOIN）

多表联查与pandas中的merge类似，我们继续通过对比观察两者的查询结果
首先新建一张表score2并往里面插入一些数据

# 创建schema
CREATE TABLE `class_demo`.`score2` 
(   
	`id` INT NOT NULL,   
	`student_name` VARCHAR(45) NULL,   
	`Math` INT NULL,   
	`English` INT NULL,   
	`Language` INT NULL,   PRIMARY KEY (`id`)
)

# 插入数据
INSERT INTO `class_demo`.`score2` 
(`id`, `student_name`, `Math`, `English`, `Language`) 
VALUES 
(1, "mike", 66, 60, 73), 
(2, "mike", 67, 60, 73)

再新建一个score2用于实验pandas中联查的API

score2 = pd.DataFrame({
    'id': [4, 5],
    'student_name': ['mike', 'mike'],
    'Math': [66, 67],
    'English': [60, 60],
    'Language': [73, 73]
})

SQL	pandas
select score.student_name, score2.student_name, score.English from score left join score2 on score.English = score2.English	df1.merge(df2[df2.English == 60], left_on=[‘English’], right_on=[‘English’], how=‘left’)[[‘student_name_x’, ‘student_name_y’, ‘English’]]

SQL输出

student_name  student_name  English
    'facker'        'mike'     '60'
    'facker'        'mike'     '60'
    'vivian'          NULL    '100'
      'joke'          NULL    '100'

pandas输出
由于通过English进行了联结，而两表中出现了相同的字段，合并之后pandas针对相同字段之间添加了后缀作为区分

>>>    id_x student_name_x  Math_x  ...  student_name_y  Math_y  Language_y
	0     1         vivian      70  ...             NaN     NaN         NaN
	1     2           joke     100  ...             NaN     NaN         NaN
	2     3         facker      88  ...            mike    66.0        73.0
	3     3         facker      88  ...            mike    67.0        73.0

pandas中的参数left_on 和 right_on用来填写用于联结的列，how表示连接类型，其默认为inner对应SQL中的INNER， left对应 SQL中的 LEFT OUTER，right对应RIGHT OUTER

UNION ALL…UNION

pandas使用concat来实现类似UNION的效果

SQL	pandas
select student_name, English from score where English = 100 union all select student_name, English from score2 where English = 60	pd.concat([df1[df.English == 100], df2[df2.English == 60]])[[‘student_name’, ‘English’]]

如果想要删除合并后重复的数据，可以在表中pandas命令最后再添加.drop_duplicates()

修改数据

UPDATE

SQL	pandas
update score set English = 99 where student_name = ‘facker’	df.loc[df.student_name == ‘facker’, ‘English’] = 99

删除数据

DELETE

pandas删除数据有两种方案：

将dataframe种需要保留的数据提取出来（简单，易于理解，推荐）
获取行数据索引，通过.drop()方法进行删除

SQL	pandas
delete from score where English < 60	df = df[df.English > 60]
	df.drop(df[df.English <= 60].index)

dataframe的不可变性

大多数的情况下，对dataframe的操作会返回的一个全新的对象，如果我们需要进行原地操作，部分API的提供了inplace参数，通过设置inplace=True，我们可以对dataframe进行原地修改。

比如重置索引，使用loc更新、直接更新某一列值或者添加新的一列：

pandas	原地修改
df.reset_index(drop=True, inplace=True)	true
df[‘English’] = 99	true
df[‘new_column’] = 1	true

读者可自行尝试文中pandas API命令并查看执行效果。

关于使用pandas进行数据聚合的一些看法

目前项目中使用到了pandas作为BI报表的数据源，BI图标生成时会从pandas生成的H5文件（pd.to_hdf(...)）读取原始数据进行上述聚合，并最终返回给前端结果。这里面存在一个非常重要的问题。

所有的操作都是基于将 整个H5文件 加载到内存dataframe当中才可进行之后的操作，小数据量下问题不大，但是如果数据量比较大的情况下，对内存的消耗就会非常严重（尽管pandas支持列式存储），且数据文件生成较多后管理也比较麻烦。

pandas相较于SQL，有着更多丰富的数据处理功能，在某些数据分析场景下可以大有作为，但是其生成的文件在作为底层数据源为BI报表展示提供数据的技术可行性还有待商榷。

参考

Pandas与SQL对比
 Pandas数据分析

在这里插入图片描述

皇家茶壶

关注

4
点赞
踩
10

收藏

觉得还不错? 一键收藏
0
评论
当pandas用的像sql一样，是否还有必要继续坚持？

文章目录前言什么是pandas像使用sql一样使用pandas常用命令对照新增数据插入查询数据IN...NOT IN排序GROUP BY, COUNT, (分组下的)ORDER BY修改数据删除数据前言本篇立眼于公司项目中数据分析与报表生成的技术方案，主要是针对pandas数据处理方面与SQL的异同发表一些心得见解。目前项目中使用的BI报表功能，其底层数据支撑来自于经过pandas处理生成的H5文件。报表展示的是聚合之后的数据，这就涉及到对数据的筛选，分类以及聚合。什么是pandas网上针对pan
复制链接

扫一扫