写给 SQL 开发者看的 Pandas 教程-程序员充电站

Pandas

pandas 是 python 中用于进行数据处理的库。其数据的结构 Dataframe 与数据库中的表非常相似。当然由于是一个 python 库因此对数据的处理总的来说相比 SQL 会更加灵活。在一些 SQL 处理起来比较麻烦的场景可以使用 pandas 来进行处理，或者先用 SQL 获取初步结果，然后再使用 pandas 处理。本文通过如何使用 dataframe 来实现 SQL 中的各种操作来介绍一下 pandas。

基本操作

创建一个 dataframe

在 pandas 中创建一个 dataframe 非常简单,像下面的代码就创建了一个列为name, age, score的 dataframe。可以看出 dataframe 和一张表非常相似。

import pandas as pd df = pd.DataFrame({ "name": ["lily", "bob"], "age": [20, 21], "score": [95, 94] }) df

Select

sql 中最简单的操作就是select xxx from yyy在 pandas 中也非常简单。例如我们假设这张表名为 student，那么 dataframe 实现select name, age from student的方式为df[['name', 'age']]

这里要注意的是，df['name']与df[['name']]是完全不同的概念，虽然看起来结果相似:

但是df['name']返回的为Series类型，而df[['name']]为Dataframe类型。Series可以理解为 dataframe 中的一行或一列:

Where

过滤数据也是非常基础的操作，例如select * from student where name = 'lily'，使用 dataframe 就为df[df['name'] == 'lily']

这里 pandas 使用了 python 的运算符重载来重载==。因此多个条件组合时也不用使用内置的or, and ,not而是|, &, ~例如df[(~(df['name'] == 'lily')) & (df['score'] > 90)]注意有多个条件时，每个条条件必须包含在括号内。

条件中也可以使用运算符符号，比如df[df['age'] + 1 > 29]。但是对于字符串类型的列就需要.str来代理一下。因为如df['age']本身没有startswith这个函数，因为它是Series类型。Series中可以存放任意类型的值，如果在Series类型中实现关于字符串操作的函数就不太合理。因此使用字符串操作的函数时，必须显式的调用.str来获取字符串操作的函数。例如df[df['name'].str.startswith('li')]。

大部分时候 where 和 select 是联合使用的，比如:

select name from student where age > 20

那么使用刚刚的方法就无法达到这个效果，我们可以使用 pandas 提供的更方便的.loc方法来实现:

df.loc[df['age'] > 20, ['name']]

.loc[]接收两个参数，分别是行索引和列索引。而df[]只能接收行索引或列索引中的一个，比如:

df[['name']]: 列索引，只能选择列
df[df['name'] == 'lily']: 行索引，只能选择行

Group By

在 sql 中使用 group by 是一个高频的操作，其有三个重点:

group by 的列
聚合的列
聚合的方式

总的来说，group by 可以看作一个分组的操作。按照某一列将数据放在不同的组中，然后对每个组中选中的列进行一个聚合操作。让我们来新建一个 dataframe，假设就叫 order 表:

df = pd.DataFrame({ "item": ["mobile", "mobile", "computer", "computer", "computer",], "price": [1999, 2399, 4399, 5999, 8999,] })

先做一个最简单的聚合操作，比如计算每个 item 最高的售价，那么用 sql 写就为select max(price) from order group by item。那么使用 dataframe 就对应着:

df.groupby("item")[['price']].sum()

这里的代码不是这么直观，稍微解释一下可以分为 3 步来理解:

grouped = df.groupby("item") # 将 dataframe 按照 item 的不同值进行分组 grouped_columns = grouped[["price"]] # 从各个组中取出需要计算的列，注意这里是 groupd[["price"]]，而# 不是 groupd["price"]。 grouped_columns.sum() # 计算选中的列的和

我们可以看到，结果中的 item 和 price 并不在同一行，这是因为计算结果后，用于分组的列会被 datafrme 视为index而不是普通列。如果我们想将其作为普通列使用，那么可以:

df = pd.DataFrame({ "item": ["mobile", "mobile", "computer", "computer", "computer",], "price": [1999, 2399, 4399, 5999, 8999,] }) df.groupby("item")[['price']].sum().reset_index()

这里的 reset

如果我们想同时计算多种计算方式，可以采用更通用的 .agg 函数，例如:

df = pd.DataFrame({ "item": ["mobile", "mobile", "computer", "computer", "computer",], "price": [1999, 2399, 4399, 5999, 8999,], "sell": [1000, 100, 2000, 200, 300] }) df.groupby("item").agg({ 'price': ['sum', 'max'], 'sell': ['sum', 'max'] })

计算过程中也可以顺便重命名，比如:

df = pd.DataFrame({ "item": ["mobile", "mobile", "computer", "computer", "computer",], "price": [1999, 2399, 4399, 5999, 8999,], "sell": [1000, 100, 2000, 200, 300] }) df.groupby("item").agg( mean_price=('price', 'mean'), mean_sell=('sell', 'mean') )

Window

在 SQL 中，window 操作也是常见的操作，比如像按找到不同的 item 售价最高的行，写法为:

select * from ( select *, row_number() over (partition by item order by price desc) as rank from table ) where rank = 1

实际上，rank 的整个操作可以分为两个步骤:

分组，将数据按 item 分组并按 price 排序
按照排序顺序进行过滤

因此对于 pandas 来说，第一步就是:

df = pd.DataFrame({ "item": ["mobile", "mobile", "computer", "computer", "computer",], "price": [1999, 2399, 4399, 5999, 8999,], "sell": [1000, 100, 2000, 200, 300] }) rank = df.groupby("item")['price'].rank(method='min', ascending=False) rank

这里的结果实际上就是一个 series，其中第一列 0 到 4 分别表示第几行，而第二列则表示 rank 的结果。我们先不深究 rank 函数的参数含义是什么，继续完成第二步:

可以看出 pandas 的 window 和 group by 操作非常统一。因为终究来说，rank 就是一个组内排序然后过滤的操作。再来看刚刚提到的rank函数的几个参数？ascending=False非常好理解，就是降序排列。而method='min'代表什么意思？我们知道 sql 中的窗口函数有几个常用的:

row_number: 遇到相同的值也取不同的 rank
rank: 遇到相同的值就取相同的 rank，但是下一个编号会'跳跃'
dense_rank: 遇到相同的值就取相同的 rank，但是下一个编号不会'跳跃'

我举个例子:

value: 1 2 2 7 9 10 row_number: 1, 2, 3, 4, 5, 6 // 不管是不是相同，都进行不同的连续编号 rank: 1, 2, 2, 4, 5, 6 // 2, 2 之后跟的 4，编号进行了跳跃 dense_rank: 1, 2, 2, 3, 4, 5 // 2, 2 之后跟的 3，编号不进行跳跃

这里有一个记忆小技巧，dense_rank 中的 dense 是稠密的意思。稠密就意味着没有空洞，那当然编号也就不会进行跳跃了。那再回到 pandas 的 rank 函数上，rank 函数的 method 参数正是用来实现相同的作用，我们来对比一下:

method	说明（对同一组同值）	下一步同值是否跳号	对应 SQL 窗口函数
average（默认）	取“平均排名”	是	无直接对应
min	取最小排名	是	`RANK()`
max	取最大排名	是	无直接对应
first	按数据出现顺序给不同序号	是	`ROW_NUMBER()`
dense	紧凑排名，同值同号	否	`DENSE_RANK()`

这里用取最小排名作为例子:

value: 1 2 2 7 9 10 # 这里的 2 2 如果直接编号分别是 2 3 直接编号: 1 2 3 4 5 6 min: 1 2 2 4 5 6 # 由于是取同值得最小编号，因此 2 2 最终编号都是 2

Join

对于 pandas join 可以通过 merge 函数实现，比如:

df1 = pd.DataFrame({ "item": ["mobile", "mobile", "computer", "computer", "computer",], "price": [1999, 2399, 4399, 5999, 8999,], "sell": [1000, 100, 2000, 200, 300], "id": [11, 22, 33, 44, 77] }) df2 = pd.DataFrame({ "id": [11, 22, 33, 44, 55], "name": ["app", "mimi", "vivi", "oopp", "kuku"] }) df1.merge(df2, how='left', on='id')

这个 sql 区别并不大，只不过变成了函数调用的方式，这里再列几个常用的参数:

how: 顾名思义，join 的方式，left/right/inner/outer/cross
on: join 的键, 如果左右的键不同，那就用 left_on/right_on
suffixes：如果有相同的列会加上对应的前缀

列操作

在 sql 中进行列操作是一个非常简单的事情，比如将价格单位从元转换为角:

select price * 100 as new_price

用 pandas 可以如下完成：

df = pd.DataFrame({ "item": ["mobile", "mobile", "computer", "computer", "computer",], "price": [1999, 2399, 4399, 5999, 8999,], "sell": [1000, 100, 2000, 200, 300], "id": [11, 22, 33, 44, 77] }) df['price'] = df['price'] * 100 df

大部分时候，我们需要先 filter 再进行列操作，比如

select price * 100 as new_price where item = 'computer'

那么直觉上来看，我们可以在 pandas 中这样操作:

虽然确实达到了最终的效果，但是 pandas 对我们抛出了一个警告。大概的意思是，我们对某个 copy 的 dataframe 进行了操作，虽然现在会生效，但是不保证以后还会生效，因此尽量不要依赖这种操作。为了解决这个问题，pandas 提供了.assign方法来实现这种场景，比如:

assign 的作用就是产生一个新的 dataframe 且不对原 dataframe 进行修改。这样就能避免修改一个 copy 的情况产生。