Pandas怎样实现对数据的分组统计?

Pandas怎样实现groupby分组统计

类似SQL:
select city,max(temperature) from city_weather group by city;

groupby:先对数据分组,然后在每个分组上应用聚合函数、转换函数

本次演示:
一、分组使用聚合函数做数据统计
二、遍历groupby的结果理解执行流程
三、实例分组探索天气数据

import pandas as pd
import numpy as np
# 加上这一句,能在jupyter notebook展示matplot图表
%matplotlib inline
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)})
df
ABCD
0fooone0.5429030.788896
1barone-0.375789-0.345869
2footwo-0.9034070.428031
3barthree-1.5647480.081163
4footwo-1.0936020.837348
5bartwo-0.2024030.701301
6fooone-0.665189-1.505290
7foothree-0.4983390.534438

一、分组使用聚合函数做数据统计

1、单个列groupby,查询所有数据列的统计

df.groupby('A').sum()
CD
A
bar-2.1429400.436595
foo-2.6176331.083423

我们看到:
1. groupby中的’A’变成了数据的索引列
2. 因为要统计sum,但B列不是数字,所以被自动忽略掉

2、多个列groupby,查询所有数据列的统计

df.groupby(['A','B']).mean()
CD
AB
barone-0.375789-0.345869
three-1.5647480.081163
two-0.2024030.701301
fooone-0.061143-0.358197
three-0.4983390.534438
two-0.9985040.632690

我们看到:(‘A’,’B’)成对变成了二级索引

df.groupby(['A','B'], as_index=False).mean()
ABCD
0barone-0.375789-0.345869
1barthree-1.5647480.081163
2bartwo-0.2024030.701301
3fooone-0.061143-0.358197
4foothree-0.4983390.534438
5footwo-0.9985040.632690

3、同时查看多种数据统计

df.groupby('A').agg([np.sum, np.mean, np.std])
CD
summeanstdsummeanstd
A
bar-2.142940-0.7143130.7415830.4365950.1455320.526544
foo-2.617633-0.5235270.6378221.0834230.2166850.977686

我们看到:列变成了多级索引

4、查看单列的结果数据统计

# 方法1:预过滤,性能更好
df.groupby('A')['C'].agg([np.sum, np.mean, np.std])
summeanstd
A
bar-2.142940-0.7143130.741583
foo-2.617633-0.5235270.637822
# 方法2
df.groupby('A').agg([np.sum, np.mean, np.std])['C']
summeanstd
A
bar-2.142940-0.7143130.741583
foo-2.617633-0.5235270.637822

5、不同列使用不同的聚合函数

df.groupby('A').agg({"C":np.sum, "D":np.mean})
CD
A
bar-2.1429400.145532
foo-2.6176330.216685

二、遍历groupby的结果理解执行流程

for循环可以直接遍历每个group

1、遍历单个列聚合的分组
g = df.groupby('A')
g
<pandas.core.groupby.generic.DataFrameGroupBy object at 0x00000123B250E548>
for name,group in g:
    print(name)
    print(group)
    print()
bar
     A      B         C         D
1  bar    one -0.375789 -0.345869
3  bar  three -1.564748  0.081163
5  bar    two -0.202403  0.701301

foo
     A      B         C         D
0  foo    one  0.542903  0.788896
2  foo    two -0.903407  0.428031
4  foo    two -1.093602  0.837348
6  foo    one -0.665189 -1.505290
7  foo  three -0.498339  0.534438

可以获取单个分组的数据

g.get_group('bar')
ABCD
1barone-0.375789-0.345869
3barthree-1.5647480.081163
5bartwo-0.2024030.701301
2、遍历多个列聚合的分组
g = df.groupby(['A', 'B'])
for name,group in g:
    print(name)
    print(group)
    print()
('bar', 'one')
     A    B         C         D
1  bar  one -0.375789 -0.345869

('bar', 'three')
     A      B         C         D
3  bar  three -1.564748  0.081163

('bar', 'two')
     A    B         C         D
5  bar  two -0.202403  0.701301

('foo', 'one')
     A    B         C         D
0  foo  one  0.542903  0.788896
6  foo  one -0.665189 -1.505290

('foo', 'three')
     A      B         C         D
7  foo  three -0.498339  0.534438

('foo', 'two')
     A    B         C         D
2  foo  two -0.903407  0.428031
4  foo  two -1.093602  0.837348

可以看到,name是一个2个元素的tuple,代表不同的列

g.get_group(('foo', 'one'))
ABCD
0fooone0.5429030.788896
6fooone-0.665189-1.505290

可以直接查询group后的某几列,生成Series或者子DataFrame

g['C']
<pandas.core.groupby.generic.SeriesGroupBy object at 0x00000123C33F64C8>
for name, group in g['C']:
    print(name)
    print(group)
    print(type(group))
    print()
('bar', 'one')
1   -0.375789
Name: C, dtype: float64
<class 'pandas.core.series.Series'>

('bar', 'three')
3   -1.564748
Name: C, dtype: float64
<class 'pandas.core.series.Series'>

('bar', 'two')
5   -0.202403
Name: C, dtype: float64
<class 'pandas.core.series.Series'>

('foo', 'one')
0    0.542903
6   -0.665189
Name: C, dtype: float64
<class 'pandas.core.series.Series'>

('foo', 'three')
7   -0.498339
Name: C, dtype: float64
<class 'pandas.core.series.Series'>

('foo', 'two')
2   -0.903407
4   -1.093602
Name: C, dtype: float64
<class 'pandas.core.series.Series'>

其实所有的聚合统计,都是在dataframe和series上进行的;

三、实例分组探索天气数据

fpath = "./datas/beijing_tianqi/beijing_tianqi_2018.csv"
df = pd.read_csv(fpath)
# 替换掉温度的后缀℃
df.loc[:, "bWendu"] = df["bWendu"].str.replace("℃", "").astype('int32')
df.loc[:, "yWendu"] = df["yWendu"].str.replace("℃", "").astype('int32')
df.head()
ymdbWenduyWendutianqifengxiangfengliaqiaqiInfoaqiLevel
02018-01-013-6晴~多云东北风1-2级592
12018-01-022-5阴~多云东北风1-2级491
22018-01-032-5多云北风1-2级281
32018-01-040-8东北风1-2级281
42018-01-053-6多云~晴西北风1-2级501
# 新增一列为月份
df['month'] = df['ymd'].str[:7]
df.head()
ymdbWenduyWendutianqifengxiangfengliaqiaqiInfoaqiLevelmonth
02018-01-013-6晴~多云东北风1-2级5922018-01
12018-01-022-5阴~多云东北风1-2级4912018-01
22018-01-032-5多云北风1-2级2812018-01
32018-01-040-8东北风1-2级2812018-01
42018-01-053-6多云~晴西北风1-2级5012018-01

1、查看每个月的最高温度

data = df.groupby('month')['bWendu'].max()
data
month
2018-01     7
2018-02    12
2018-03    27
2018-04    30
2018-05    35
2018-06    38
2018-07    37
2018-08    36
2018-09    31
2018-10    25
2018-11    18
2018-12    10
Name: bWendu, dtype: int32
type(data)
pandas.core.series.Series
data.plot()
<matplotlib.axes._subplots.AxesSubplot at 0x123c344b308>

2、查看每个月的最高温度、最低温度、平均空气质量指数

df.head()
ymdbWenduyWendutianqifengxiangfengliaqiaqiInfoaqiLevelmonth
02018-01-013-6晴~多云东北风1-2级5922018-01
12018-01-022-5阴~多云东北风1-2级4912018-01
22018-01-032-5多云北风1-2级2812018-01
32018-01-040-8东北风1-2级2812018-01
42018-01-053-6多云~晴西北风1-2级5012018-01
group_data = df.groupby('month').agg({"bWendu":np.max, "yWendu":np.min, "aqi":np.mean})
group_data
bWenduyWenduaqi
month
2018-017-1260.677419
2018-0212-1078.857143
2018-0327-4130.322581
2018-04301102.866667
2018-05351099.064516
2018-06381782.300000
2018-07372272.677419
2018-08362059.516129
2018-09311150.433333
2018-1025167.096774
2018-1118-4105.100000
2018-1210-1277.354839
group_data.plot()
<matplotlib.axes._subplots.AxesSubplot at 0x123c5502d48>


相关推荐

发表评论

电子邮件地址不会被公开。 必填项已用*标注