对比SQL，学习Pandas操作：groupby机制
公众号：尤而小屋
作者：Peter
编辑：Peter
大家，我是Peter~~ 祝大家国庆快乐🌹
在SQL或者MySQL的数据库查询操作中我们经常会使用group by关键词来表示根据某个字段分组，然后再进行后续的聚合统计操作。
在Pandas我们同样可以实现类似的功能，使用的关键字是：groupby（连在一起的）。以前写过一篇Pandas中的groupby机制详解的文章，请参考：
聚合函数-aggregation function
不管是SQL数据库还是Pandas中，分组之后的后续操作绝大部分情况下都是进行聚合统计，下面列出常用的聚合函数：
求和：sum 最大值：max 最小值：min 均值：avg 统计个数：count
SQL多表查询
现在我们有两张表：Student和Score表，二者通过s_id进行join关联
通过下面的SQL语句，我们查询出相关的4个字段：
姓名 性别 课程编号 成绩
select
s1.s_name -- 姓名
,s1.s_sex -- 性别
,s2.c_id -- 课程编号
,s2.s_score -- 成绩分数
from Student s1
join Score s2 on s1.s_id = s2.s_id;
我们将上面的结果作为临时表t，然后再进行后续的查询操作。
pymysql获取数据
为了能够使用Pandas进行数据分析，我们先连接到本地的数据库，读取到相关的数据，并且生成相应的DataFrame：
1、连接数据库
import pandas as pd
import numpy as np
import pymysql # 连接数据库
con1 = pymysql.connect(
host="127.0.0.1", # 数据库ip
port=3306, # 端口号
user="root", # 用户名
password="password", # 密码
charset="utf8", # 字符编码
db="test" # 数据库名称
)
cur1 = con1.cursor() # 建立游标
# 待执行的SQL语句
sql1 = """
select
s1.s_name -- 姓名
,s1.s_sex -- 性别
,s2.c_id -- 课程编号
,s2.s_score -- 成绩分数
from Student s1
join Score s2 on s1.s_id = s2.s_id;
"""
# 在游标中执行SQL语句
cur1.execute(sql1)
刚好是18条数据，就是我们上面临时表t的结果，也是18条
2、生成数据
data1 = []
for i in cur1.fetchall():
data1.append(i)
df = pd.DataFrame(data1,columns=["姓名","性别","课程id","分数"])
df
在后续的pandas中操作中就是对这个df数据进行操作。
单个聚合函数
1、求和sum：我们想知道每个学生的总成绩是多少
SQL实现：
select
t.s_name
,sum(t.s_score)
from(
select
s1.s_name -- 姓名
,s1.s_sex -- 性别
,s2.c_id -- 课程编号
,s2.s_score -- 成绩分数
from Student s1
join Score s2 on s1.s_id = s2.s_id
)t
group by t.s_name;
Pandas实现：
Pandas还有另一种写法：
2、求每个同学的平均成绩avg
SQL实现：
select
t.s_name
,avg(t.s_score) -- 改成均值函数
from(
select
s1.s_name -- 姓名
,s1.s_sex -- 性别
,s2.c_id -- 课程编号
,s2.s_score -- 成绩分数
from Student s1
join Score s2 on s1.s_id = s2.s_id
)t
group by t.s_name;
Pandas实现的两种不同写法：
3、求每个同学的分数最大值（最小值）的那门学科
SQL实现：
select
t.s_name
,max(t.s_score) -- 改成最大值函数
from(
select
s1.s_name -- 姓名
,s1.s_sex -- 性别
,s2.c_id -- 课程编号
,s2.s_score -- 成绩分数
from Student s1
join Score s2 on s1.s_id = s2.s_id
)t
group by t.s_name;
Pandas实现过程：
4、统计个数：求每个同学参加了多少门学科的考试
SQL实现：
select
t.s_name
,count(t.c_id) -- 改成count函数，同时字段是课程c_id
from(
select
s1.s_name -- 姓名
,s1.s_sex -- 性别
,s2.c_id -- 课程编号
,s2.s_score -- 成绩分数
from Student s1
join Score s2 on s1.s_id = s2.s_id
)t
group by t.s_name;
Pandas实现过程：
多个聚合函数
在查询数据的时候，我们是可以同时使用多个聚合函数的，比如：我们想查看不同性别同学的：总成绩、平均成绩、人数、成绩最大值
SQL实现过程：
select
t.s_sex
,sum(t.s_score) -- 总成绩
,avg(t.s_score) -- 平均成绩
,count(t.s_name) -- 统计不同性别人数
,max(t.s_score) -- 成绩最大值
from(
select
s1.s_name -- 姓名
,s1.s_sex -- 性别
,s2.c_id -- 课程编号
,s2.s_score -- 成绩分数
from Student s1
join Score s2 on s1.s_id = s2.s_id
)t
group by t.s_sex; -- 根据性别分组统计
Pandas实现过程：
修改字段别名
上面的数据结果中不管是SQL还是Pandas的结果，都是使用默认的字段名称，我们可以进行相应修改，使得名字更具有意义：
单个聚合函数
还是每个同学的总成绩为例
SQL实现：通过as来取别名，as可省略
select
t.s_name as "姓名" -- as可省略
,sum(t.s_score) as "总成绩" -- as可省略
from(
select
s1.s_name -- 姓名
,s1.s_sex -- 性别
,s2.c_id -- 课程编号
,s2.s_score -- 成绩分数
from Student s1
join Score s2 on s1.s_id = s2.s_id
)t
group by t.s_name;
Pandas实现过程：
方式1：pandas中取别名是通过rename函数来实现的
方式2：直接修改columns属性
多个聚合函数
1、SQL实现还是通过as取别名：
select
t.s_sex as "性别"
,sum(t.s_score) as "总成绩" -- as可省略
,avg(t.s_score) as "平均成绩"
,max(t.s_score) as "最高成绩"
,count(t.s_name) as "总人数"
from(
select
s1.s_name -- 姓名
,s1.s_sex -- 性别
,s2.c_id -- 课程编号
,s2.s_score -- 成绩分数
from Student s1
join Score s2 on s1.s_id = s2.s_id
)t
group by t.s_sex; -- 根据性别分组统计
2、Pandas实现
df2 = df.groupby("性别").agg(
总成绩 = pd.NamedAgg(column="分数", aggfunc="sum"),
平均成绩 = pd.NamedAgg(column="分数", aggfunc="mean"),
最高成绩 = pd.NamedAgg(column="分数", aggfunc="max"),
总人数 = pd.NamedAgg(column="课程id", aggfunc="count")
)
df2
经典图
最后奉上一张经典的图形。在这张图形中记录了我们如何：
选择分组字段month 相同的字段（duration）进行多个聚合操作：max、min、sum 对结果取别名（重新命名）：max_duration、min_duration、total_duration
推荐阅读
尤而小屋，一个温馨的小屋。小屋主人，一手代码谋求生存，一手掌勺享受生活，欢迎你的光临