对比SQL学习Pandas操作之groupby

对比SQL，学习Pandas：groupby

在SQL或者MySQL的数据库查询操作中我们经常会使用group by关键词来表示根据某个字段来分组，然后再进行后续的聚合统计操作。在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="rot",  # 用户名
    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