写给 SQL 开发者看的 Pandas 教程

l1n3x
0 阅读9分钟

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

image.png

Select

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

image.png

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

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

image.png

Where

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

image.png

这里 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']]

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

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

Group By

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

  1. group by 的列
  2. 聚合的列
  3. 聚合的方式

总的来说，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()

image.png

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

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

image.png 我们可以看到，结果中的 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

image.png 如果我们想同时计算多种计算方式，可以采用更通用的 .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']
})

image.png

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

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')
)

image.png

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 的整个操作可以分为两个步骤:

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

因此对于 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

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

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)
df['rank'] = rank # 将 rank 作为一列
df[df["rank"] == 1] 过滤 rank = 1 的行

image.png

可以看出 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

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

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

那么直觉上来看，我们可以在 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]
})
filterd = df[df['item'] == 'computer']
filterd['price'] = filterd['price'] * 100
filterd

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

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[df['item'] == 'computer'].assign(price=lambda x: x['price'] * 100)

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

avatar
文章
阅读
粉丝