Pandas-高级处理（四）：分组与聚合【分组：groupby、聚合统计：max/min/mean...、分组转换：transform、一般化Groupby方法：apply】【抛开聚合只谈分组没意义】
2023-09-27 14:20:40 时间
df.groupby(by=None, axis=0, level=None, as_index=True, sort=True, group_keys=True, squeeze=False, **kwargs)
分组统计 - groupby功能
① 根据某些条件将数据拆分成组
② 对每个组独立应用函数
③ 将结果合并到一个数据结构中
应用groupby和聚合函数实现数据的分组与聚合
一、只分组，不聚合
直接分组得到一个groupby对象（可迭代对象），是一个中间数据，没有进行计算。
import numpy as np
import pandas as pd
# 分组
df = pd.DataFrame({'A': ['foo', 'bar', 'foo', 'bar', 'foo', 'bar', 'foo', 'foo'],
'B': ['one', 'one', 'two', 'three', 'two', 'two', 'one', 'three'],
'C': np.random.randn(8),
'D': np.random.randn(8)})
print("df = \n", df)
print('-' * 200)
# 直接分组得到一个groupby对象，是一个中间数据，没有进行计算
data1 = df.groupby('A')
print("data1 = \n{0} \ntype(data1) = {1}".format(data1, type(data1)))
print('-' * 200)
打印结果：
df =
A B C D
0 foo one 0.482379 0.462419
1 bar one -1.318749 -0.216995
2 foo two -0.603411 0.738953
3 bar three -0.042360 1.492691
4 foo two 0.277921 -0.763043
5 bar two -0.675156 0.605158
6 foo one 1.357539 -0.907503
7 foo three 0.462997 -1.774442
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
data1 =
<pandas.core.groupby.generic.DataFrameGroupBy object at 0x000002B3D9F82408>
type(data1) = <class 'pandas.core.groupby.generic.DataFrameGroupBy'>
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
二、分组 & 聚合
import numpy as np
import pandas as pd
# 分组 & 聚合
df = pd.DataFrame({'A': ['foo', 'bar', 'foo', 'bar', 'foo', 'bar', 'foo', 'foo'],
'B': ['one', 'one', 'two', 'three', 'two', 'two', 'one', 'three'],
'C': np.random.randn(8),
'D': np.random.randn(8)})
print("df = \n", df)
print('-' * 200)
# 通过分组后的计算，得到一个新的dataframe
# 默认axis = 0，以行来分组
# 可单个或多个（[]）列分组
a = df.groupby('A').mean()
b = df.groupby(['A', 'B']).mean()
c = df.groupby(['A'])['D'].mean() # 以A分组，算D的平均值
print("a = \n{0} \ntype(a) = {1}".format(a, type(a)))
print("a.columns = ", a.columns)
print('-' * 50)
print("b = \n{0} \ntype(b) = {1}".format(b, type(b)))
print("b.columns = ", b.columns)
print('-' * 50)
print("c = \n{0} \ntype(c) = {1}".format(c, type(c)))
print('-' * 200)
打印结果：
df =
A B C D
0 foo one -0.945193 -0.256331
1 bar one 0.584281 0.054737
2 foo two -1.486370 0.697265
3 bar three 0.911407 0.227388
4 foo two 0.228599 -0.076100
5 bar two -0.291184 -0.143679
6 foo one 1.716099 -1.411537
7 foo three 0.766290 -1.278755
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
a =
C D
A
bar 0.401501 0.046149
foo 0.055885 -0.465092
type(a) = <class 'pandas.core.frame.DataFrame'>
a.columns = Index(['C', 'D'], dtype='object')
--------------------------------------------------
b =
C D
A B
bar one 0.584281 0.054737
three 0.911407 0.227388
two -0.291184 -0.143679
foo one 0.385453 -0.833934
three 0.766290 -1.278755
two -0.628885 0.310582
type(b) = <class 'pandas.core.frame.DataFrame'>
b.columns = Index(['C', 'D'], dtype='object')
--------------------------------------------------
c =
A
bar 0.046149
foo -0.465092
Name: D, dtype: float64
type(c) = <class 'pandas.core.series.Series'>
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Process finished with exit code 0
三、分组计算函数方法
import pandas as pd
# 分组计算函数方法
s = pd.Series([1, 2, 3, 10, 20, 30],
index=[1, 2, 3, 1, 2, 3])
result = s.groupby(level=0) # 唯一索引用.groupby(level=0)，将同一个index的分为一组
print("result = ", result)
print('-' * 200)
print("first：非NaN的第一个值: result.first() = \n", result.first())
print('-' * 200)
print("last：非NaN的最后一个值: result.last() = \n", result.last())
print('-' * 200)
print("sum：非NaN的和: result.sum() = \n", result.sum())
print('-' * 200)
print("mean：非NaN的平均值: result.mean() = \n", result.mean())
print('-' * 200)
print("median：非NaN的算术中位数: result.median() = \n", result.median())
print('-' * 200)
print("count：非NaN的值: result.count() = \n", result.count())
print('-' * 200)
print("min、max：非NaN的最小值、最大值: result.min() = \n", result.min())
print('-' * 200)
print("first：非NaN的第一个值: result.first() = \n", result.first())
print('-' * 200)
print("std，var：非NaN的标准差和方差: result.std() = \n", result.std())
print('-' * 200)
print("prod：非NaN的积: result.prod() = \n", result.prod())
print('-' * 200)
打印结果：
result = <pandas.core.groupby.generic.SeriesGroupBy object at 0x0000023728D7C788>
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
first：非NaN的第一个值: result.first() =
1 1
2 2
3 3
dtype: int64
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
last：非NaN的最后一个值: result.last() =
1 10
2 20
3 30
dtype: int64
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
sum：非NaN的和: result.sum() =
1 11
2 22
3 33
dtype: int64
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
mean：非NaN的平均值: result.mean() =
1 5.5
2 11.0
3 16.5
dtype: float64
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
median：非NaN的算术中位数: result.median() =
1 5.5
2 11.0
3 16.5
dtype: float64
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
count：非NaN的值: result.count() =
1 2
2 2
3 2
dtype: int64
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
min、max：非NaN的最小值、最大值: result.min() =
1 1
2 2
3 3
dtype: int64
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
first：非NaN的第一个值: result.first() =
1 1
2 2
3 3
dtype: int64
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
std，var：非NaN的标准差和方差: result.std() =
1 6.363961
2 12.727922
3 19.091883
dtype: float64
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
prod：非NaN的积: result.prod() =
1 10
2 40
3 90
dtype: int64
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Process finished with exit code 0
四、可迭代对象（只进行分组后生成可迭代对象）
import numpy as np
import pandas as pd
# 分组 - 可迭代对象
df = pd.DataFrame({'X': ['A', 'B', 'A', 'B'],
'Y': [1, 4, 3, 2]})
print("df = \n", df)
x1 = df.groupby('X')
print("x1 = {0} \ntype(x1) = {1}".format(x1, type(x1)))
print('-' * 200)
x2 = list(df.groupby('X'))
print("可迭代对象，直接生成list(元素是元祖): x2 = \n", x2)
print('-' * 50)
x3 = list(df.groupby('X'))[0]
print("元素可以元祖形式显示: x3 = \n", x3)
print('-' * 200)
# n是组名，g是分组后的Dataframe
for n, g in df.groupby('X'):
print("n = {0} \ng = \n{1}".format(n, g))
print('-' * 50)
print('-' * 200)
x4 = df.groupby(['X']).get_group('A')
print("x4 = \n", x4)
print('-' * 50)
# .get_group()提取分组后的组
x5 = df.groupby(['X']).get_group('B')
print("x5 = \n", x5)
print('-' * 200)
# .groups：将分组后的groups转为dict
# 可以字典索引方法来查看groups里的元素
grouped = df.groupby(['X'])
x6 = grouped.groups
print("x6 = ", x6)
print('-' * 50)
# 也可写：df.groupby('X').groups['A']
x7 = grouped.groups['A']
print("x7 = ", x7)
print('-' * 50)
# .size()：查看分组后的长度
sz = grouped.size()
print("sz = \n{0}\ntype(sz) = {1}".format(sz, type(sz)))
print('-' * 200)
# 按照两个列进行分组
df = pd.DataFrame({'A': ['foo', 'bar', 'foo', 'bar', 'foo', 'bar', 'foo', 'foo'],
'B': ['one', 'one', 'two', 'three', 'two', 'two', 'one', 'three'],
'C': np.random.randn(8),
'D': np.random.randn(8)})
grouped = df.groupby(['A', 'B']).groups
print("df = \n", df)
print("grouped = \n", grouped)
print('-' * 50)
x8 = grouped[('foo', 'three')]
print("x8 = ", x8)
print('-' * 200)
打印结果：
df =
X Y
0 A 1
1 B 4
2 A 3
3 B 2
x1 = <pandas.core.groupby.generic.DataFrameGroupBy object at 0x000001EE03FD0E08>
type(x1) = <class 'pandas.core.groupby.generic.DataFrameGroupBy'>
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
可迭代对象，直接生成list(元素是元祖): x2 =
[('A', X Y
0 A 1
2 A 3), ('B', X Y
1 B 4
3 B 2)]
--------------------------------------------------
元素可以元祖形式显示: x3 =
('A', X Y
0 A 1
2 A 3)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
n = A
g =
X Y
0 A 1
2 A 3
--------------------------------------------------
n = B
g =
X Y
1 B 4
3 B 2
--------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
x4 =
X Y
0 A 1
2 A 3
--------------------------------------------------
x5 =
X Y
1 B 4
3 B 2
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
x6 = {'A': [0, 2], 'B': [1, 3]}
--------------------------------------------------
x7 = Int64Index([0, 2], dtype='int64')
--------------------------------------------------
sz =
X
A 2
B 2
dtype: int64
type(sz) = <class 'pandas.core.series.Series'>
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
df =
A B C D
0 foo one -0.233579 1.086187
1 bar one -1.510359 -0.728268
2 foo two -1.020681 -0.023260
3 bar three 0.283932 -0.012469
4 foo two -0.280528 1.607814
5 bar two 0.146867 -0.233957
6 foo one -1.305016 -1.287504
7 foo three 0.907516 -0.622065
grouped =
{('bar', 'one'): [1], ('bar', 'three'): [3], ('bar', 'two'): [5], ('foo', 'one'): [0, 6], ('foo', 'three'): [7], ('foo', 'two'): [2, 4]}
--------------------------------------------------
x8 = Int64Index([7], dtype='int64')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Process finished with exit code 0
五、其他轴上的分组
import numpy as np
import pandas as pd
# 其他轴上的分组
df = pd.DataFrame({'data1': np.random.rand(2),
'data2': np.random.rand(2),
'key1': ['a', 'b'],
'key2': ['one', 'two']})
print("df = \n", df)
print('-' * 50)
print("df.dtypes = \n", df.dtypes)
print('-' * 200)
# 按照值类型分列
for n, g in df.groupby(df.dtypes, axis=1):
print("n = {0} \ng = \n{1}".format(n, g))
print('-' * 50)
打印结果：
df =
data1 data2 key1 key2
0 0.036959 0.561923 a one
1 0.903778 0.160286 b two
--------------------------------------------------
df.dtypes =
data1 float64
data2 float64
key1 object
key2 object
dtype: object
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
n = float64
g =
data1 data2
0 0.036959 0.561923
1 0.903778 0.160286
--------------------------------------------------
n = object
g =
key1 key2
0 a one
1 b two
--------------------------------------------------
Process finished with exit code 0
六、通过字典或者Series分组
import numpy as np
import pandas as pd
# 通过字典或者Series分组
df = pd.DataFrame(np.arange(16).reshape(4, 4),
columns=['a', 'b', 'c', 'd'])
print("df = \n", df)
print('-' * 200)
# mapping中，a、b列对应的为one，c、d列对应的为two，以字典来分组
mapping = {'a': 'one', 'b': 'one', 'c': 'two', 'd': 'two', 'e': 'three'}
by_column = df.groupby(mapping, axis=1)
x1 = by_column.sum()
print("x1 = \n", x1)
print('-' * 200)
# s中，index中a、b对应的为one，c、d对应的为two，以Series来分组
s = pd.Series(mapping)
print("s = \n", s)
print('-' * 50)
x2 = s.groupby(s).count()
print("x2 = \n", x2)
print('-' * 200)
打印结果：
df =
a b c d
0 0 1 2 3
1 4 5 6 7
2 8 9 10 11
3 12 13 14 15
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
x1 =
one two
0 1 5
1 9 13
2 17 21
3 25 29
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
s =
a one
b one
c two
d two
e three
dtype: object
--------------------------------------------------
x2 =
one 2
three 1
two 2
dtype: int64
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Process finished with exit code 0
七、通过函数分组
import numpy as np
import pandas as pd
# 通过函数分组
df = pd.DataFrame(np.arange(16).reshape(4, 4),
columns=['a', 'b', 'c', 'd'],
index=['abc', 'bcd', 'aa', 'b'])
print("df = \n", df)
print('-' * 200)
# 按照字母长度分组
x = df.groupby(len).sum()
print("x = df.groupby(len).sum() = \n", x)
print('-' * 200)
打印结果：
df =
a b c d
abc 0 1 2 3
bcd 4 5 6 7
aa 8 9 10 11
b 12 13 14 15
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
x = df.groupby(len).sum() =
a b c d
1 12 13 14 15
2 8 9 10 11
3 4 6 8 10
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Process finished with exit code 0
八、多函数计算：agg()
import numpy as np
import pandas as pd
# 多函数计算：agg()
df = pd.DataFrame({'a': [1, 1, 2, 2],
'b': np.random.rand(4),
'c': np.random.rand(4),
'd': np.random.rand(4), })
print("df = \n", df)
print('-' * 200)
# 函数写法可以用str，或者np.方法
x1 = df.groupby('a').agg(['mean', np.sum])
print("x1 = \n", x1)
print('-' * 200)
打印结果：
df =
a b c d
0 1 0.338574 0.805788 0.916617
1 1 0.739710 0.547594 0.285460
2 2 0.171160 0.119593 0.063185
3 2 0.609000 0.733188 0.219303
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
x1 =
b c d
mean sum mean sum mean sum
a
1 0.539142 1.078285 0.676691 1.353383 0.601039 1.202077
2 0.390080 0.780160 0.426391 0.852781 0.141244 0.282488
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Process finished with exit code 0
九、分组转换：transform
import numpy as np
import pandas as pd
# 数据分组转换,transform
df = pd.DataFrame({'data1': np.random.rand(5),
'data2': np.random.rand(5),
'key1': list('aabba'),
'key2': ['one', 'two', 'one', 'two', 'one']})
print("df = \n", df)
print('-' * 200)
# data1、data2每个位置元素取对应分组列的均值
# 字符串不能进行计算
k_mean = df.groupby('key1').mean()
print("k_mean = df.groupby('key1').mean() = \n", k_mean)
print('-' * 200)
# 通过分组、合并，得到一个包含均值的Dataframe
x1 = pd.merge(df, k_mean, left_on='key1', right_index=True)
print("x1= pd.merge(df, k_mean, left_on='key1', right_index=True) = \n", x1)
print('-' * 200)
# .add_prefix('mean_')：添加前缀
x2 = x1.add_prefix('mean_')
print("x2 = data1.add_prefix('mean_') = \n", x2)
print('-' * 200)
# 按照key2分组求均值
x3 = df.groupby('key2').mean()
print("x3 = df.groupby('key2').mean() = \n", x3)
print('-' * 200)
x4 = df.groupby('key2').transform(np.mean)
print("x4 = df.groupby('key2').transform(np.mean) = \n", x4)
print('-' * 200)
打印结果：
df =
data1 data2 key1 key2
0 0.970811 0.469705 a one
1 0.065935 0.881523 a two
2 0.290174 0.491378 b one
3 0.417073 0.990027 b two
4 0.147860 0.570658 a one
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
k_mean = df.groupby('key1').mean() =
data1 data2
key1
a 0.394869 0.640629
b 0.353624 0.740702
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
x1= pd.merge(df, k_mean, left_on='key1', right_index=True) =
data1_x data2_x key1 key2 data1_y data2_y
0 0.970811 0.469705 a one 0.394869 0.640629
1 0.065935 0.881523 a two 0.394869 0.640629
4 0.147860 0.570658 a one 0.394869 0.640629
2 0.290174 0.491378 b one 0.353624 0.740702
3 0.417073 0.990027 b two 0.353624 0.740702
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
x2 = data1.add_prefix('mean_') =
mean_data1_x mean_data2_x mean_key1 mean_key2 mean_data1_y mean_data2_y
0 0.970811 0.469705 a one 0.394869 0.640629
1 0.065935 0.881523 a two 0.394869 0.640629
4 0.147860 0.570658 a one 0.394869 0.640629
2 0.290174 0.491378 b one 0.353624 0.740702
3 0.417073 0.990027 b two 0.353624 0.740702
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
x3 = df.groupby('key2').mean() =
data1 data2
key2
one 0.469615 0.510580
two 0.241504 0.935775
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
x4 = df.groupby('key2').transform(np.mean) =
data1 data2
0 0.469615 0.510580
1 0.241504 0.935775
2 0.469615 0.510580
3 0.241504 0.935775
4 0.469615 0.510580
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Process finished with exit code 0
十一、案例分析
分组与聚合通常是分析数据的一种方式，通常与一些统计函数一起使用，查看数据的分组情况
想一想其实刚才的交叉表与透视表也有分组的功能，所以算是分组的一种形式，只不过他们主要是计算次数或者计算比例！！看其中的效果：
1 什么分组与聚合
2 分组API
- DataFrame.groupby(key, as_index=False)
- key:分组的列数据，可以多个
- 案例:不同颜色的不同笔的价格数据
col =pd.DataFrame({'color': ['white','red','green','red','green'], 'object': ['pen','pencil','pencil','ashtray','pen'],'price1':[5.56,4.20,1.30,0.56,2.75],'price2':[4.75,4.12,1.60,0.75,3.15]})
color object price1 price2
0 white pen 5.56 4.75
1 red pencil 4.20 4.12
2 green pencil 1.30 1.60
3 red ashtray 0.56 0.75
4 green pen 2.75 3.15
- 进行分组，对颜色分组，price进行聚合
# 分组，求平均值
col.groupby(['color'])['price1'].mean()
col['price1'].groupby(col['color']).mean()
color
green 2.025
red 2.380
white 5.560
Name: price1, dtype: float64
# 分组，数据的结构不变
col.groupby(['color'], as_index=False)['price1'].mean()
color price1
0 green 2.025
1 red 2.380
2 white 5.560
3 星巴克零售店铺数据
现在我们有一组关于全球星巴克店铺的统计数据，如果我想知道美国的星巴克数量和中国的哪个多，或者我想知道中国每个省份星巴克的数量的情况，那么应该怎么办？
数据来源：https://www.kaggle.com/starbucks/store-locations/data
3.1 数据获取
从文件中读取星巴克店铺数据
# 导入星巴克店的数据
starbucks = pd.read_csv("./data/starbucks/directory.csv")
3.2 进行分组聚合
# 按照国家分组，求出每个国家的星巴克零售店数量
count = starbucks.groupby(['Country']).count()
画图显示结果
count['Brand'].plot(kind='bar', figsize=(20, 8))
plt.show()
假设我们加入省市一起进行分组
# 设置多个索引，set_index()
starbucks.groupby(['Country', 'State/Province']).count()
仔细观察这个结构，与我们前面讲的哪个结构类似？？
与前面的MultiIndex结构类似
4 小结
- groupby进行数据的分组【知道】
- pandas中，抛开聚合谈分组，无意义
相关文章
- 使用位运算方法实现十进制数字转换为十六进制数
- ros1的数据包和ROS2的数据包播放或转换的方法
- 【图像处理与医学图像处理】YUV与RGB格式转换速度几种方法对比
- 【转】给DataTable和DataRow扩展方法,直接转换为对象集合或对象
- 怎样把颜色转换为字符串
- java-BigDecimal百位取整和科学记数法转换
- Python时间，日期，时间戳之间转换
- BGRA与BGR的相互转换
- MyBatis使用自定义TypeHandler转换类型的实现方法
- python2.x脚本转换为python3.x脚本的方法
- Android平台Camera实时滤镜实现方法探讨(三)--通过Shader实现YUV转换RBG
- Java精选笔记_IO流(转换流、常用流、流操作规律、字符编码)
- iOS项目开发实战——网页源码实现二进制和HTML的转换
- 时间戳 和 日期 转换的方法 (含获取当前时间戳的方法)
- 【Python基础】列表的基本操作：列表的数据统计、排序、遍历 || 关键字、函数、方法 || 列表的应用场景 || 元组的定义、循环遍历、应用场景 || 格式化字符 || 元组和列表之间的转换
- C语言中将字符串转换为数字的方法
- 《挑战30天C++入门极限》C++运算符重载转换运算符
- Full Convert相互转换器,转换数据库格式简单的方法
- java 字节流和字符流转换类InputStreamReader，OutPutStreamReader
- JS转换HTML转义符
- ASP.NET Core(C#) 字符串常用处理转换的扩展方法工具类
- IP地址转换函数
- Python数据类型 列表、元组、集合、字典的区别和相互转换
- 例2-6 字母转换
- 实现RGB,CMY(K),YUV,YIQ,YCbCr颜色的转换算法
- C++多字节字符转换为宽字符的两种方法
- Linux-iconv命令、并批处理将所有GBK文件转换为UTF8(18)
- C# 使用转换语义版本号