DataAnalysis-Pandas分组聚合

• 2020 年 4 月 15 日
• 笔记
• DataAnalysis

title: Pandas分组聚合
tags:

• 数据分析
• python
  categories:
• DataAnalysis
  toc: true
  date: 2020-02-10 16:28:49

Description：Pandas分组聚合

一、分组

GroupBy对象

· groupedby函数中的参数：

as_index的作用：控制聚合输出是否以组标签为索引值，默认为True，就是分层次的索引，若为False多加一列默认索引索引，相当于非其他数据排序好了。
但是这两组标签索引值不同有什么作用呢？=== 作用就是，根据的一列是否为索引列。
sort_values的作用：对选定的一列数值数据从上往下从小到大进行排序（如果传值没成功===设置本体覆盖，传值覆盖）

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import matplotlib.pyplot 
%matplotlib inline

dict_obj = {'key1' : ['a', 'b', 'a', 'b', 
                      'a', 'b', 'a', 'a'],
            'key2' : ['one', 'one', 'two', 'three',
                      'two', 'two', 'one', 'three'],
            'data1': np.random.randn(8),
            'data2': np.random.randn(8)}
df_obj = pd.DataFrame(dict_obj)
print (df_obj)

  key1   key2     data1     data2
0    a    one -0.147612 -0.348087
1    b    one -0.992986  0.902458
2    a    two  0.547541 -0.310040
3    b  three  0.458871 -1.895392
4    a    two  1.224041  0.220150
5    b    two -0.200124 -1.562237
6    a    one  1.539144 -0.758716
7    a  three  0.385845  0.074309

'''1. dataframe根据key2进行分组'''
print(df_obj.groupby('key2')['key1'].count())

print (type(df_obj.groupby('key1')))
#没有可视化的输出 

key2
one      3
three    2
two      3
Name: key1, dtype: int64
<class 'pandas.core.groupby.generic.DataFrameGroupBy'>

'''2. 指定列根据key1进行分组'''
print (type(df_obj['data1'].groupby(df_obj['key1'])))

<class 'pandas.core.groupby.generic.SeriesGroupBy'>

# 分组运算
grouped1 = df_obj.groupby('key1',as_index=False)
print (grouped1.mean())

grouped2 = df_obj['data1'].groupby(df_obj['key1'])#指定某一列的数据在该索引下进行分组并且加以聚合
print (grouped2.mean())

  key1     data1     data2
0    a  0.709792 -0.224477
1    b -0.244746 -0.851723
key1
a    0.709792
b   -0.244746
Name: data1, dtype: float64

'''3. 按自定义key分组，列表'''
self_def_key = [1, 1, 2, 2, 2, 1, 1, 1]
df_obj.groupby(self_def_key).mean()

df_obj

'''4. 按多个列多层分组 = = = 通过列表'''
grouped2 = df_obj.groupby(['key1', 'key2'],as_index=False)
print (grouped2.mean())
print('--------比较asindex的差异-------')
grouped2 = df_obj.groupby(['key1', 'key2'],as_index=True)
print (grouped2.mean())

  key1   key2     data1     data2
0    a    one  0.695766 -0.553401
1    a  three  0.385845  0.074309
2    a    two  0.885791 -0.044945
3    b    one -0.992986  0.902458
4    b  three  0.458871 -1.895392
5    b    two -0.200124 -1.562237
--------比较asindex的差异-------
               data1     data2
key1 key2                     
a    one    0.695766 -0.553401
     three  0.385845  0.074309
     two    0.885791 -0.044945
b    one   -0.992986  0.902458
     three  0.458871 -1.895392
     two   -0.200124 -1.562237

# 多层分组按key的顺序进行===和上面的asindex作用一样，把所选取的列数据当成索引，这才是区别之处
grouped3 = df_obj.groupby(['key2', 'key1'])
print (grouped3.mean())
print ('=============================================')
'''PS：如果想按照列进行分组聚合运算 === unstack===也可以通过转置'''
print (grouped3.mean().unstack())

               data1     data2
key2  key1                    
one   a     0.695766 -0.553401
      b    -0.992986  0.902458
three a     0.385845  0.074309
      b     0.458871 -1.895392
two   a     0.885791 -0.044945
      b    -0.200124 -1.562237
=============================================
          data1               data2          
key1          a         b         a         b
key2                                         
one    0.695766 -0.992986 -0.553401  0.902458
three  0.385845  0.458871  0.074309 -1.895392
two    0.885791 -0.200124 -0.044945 -1.562237

GroupBy对象遍历迭代

grouped1

<pandas.core.groupby.generic.DataFrameGroupBy object at 0x000001AF5B5F9088>

# 单层分组
print(grouped1.head(5))
print("------------------------------------分割线------------------------------------------")
for group_name, group_data in grouped1:
    print (group_name)
    print (group_data['data1'])

  key1   key2     data1     data2
0    a    one -0.147612 -0.348087
1    b    one -0.992986  0.902458
2    a    two  0.547541 -0.310040
3    b  three  0.458871 -1.895392
4    a    two  1.224041  0.220150
5    b    two -0.200124 -1.562237
6    a    one  1.539144 -0.758716
7    a  three  0.385845  0.074309
------------------------------------分割线------------------------------------------
a
0   -0.147612
2    0.547541
4    1.224041
6    1.539144
7    0.385845
Name: data1, dtype: float64
b
1   -0.992986
3    0.458871
5   -0.200124
Name: data1, dtype: float64

# 多层分组
for group_name, group_data in grouped2:
    print (group_name)
    print (group_data)

('a', 'one')
  key1 key2     data1     data2
0    a  one -0.147612 -0.348087
6    a  one  1.539144 -0.758716
('a', 'three')
  key1   key2     data1     data2
7    a  three  0.385845  0.074309
('a', 'two')
  key1 key2     data1    data2
2    a  two  0.547541 -0.31004
4    a  two  1.224041  0.22015
('b', 'one')
  key1 key2     data1     data2
1    b  one -0.992986  0.902458
('b', 'three')
  key1   key2     data1     data2
3    b  three  0.458871 -1.895392
('b', 'two')
  key1 key2     data1     data2
5    b  two -0.200124 -1.562237

# GroupBy对象转换list
print(grouped1.mean())
list(grouped1)

  key1     data1     data2
0    a  0.709792 -0.224477
1    b -0.244746 -0.851723





[('a',   key1   key2     data1     data2
  0    a    one -0.147612 -0.348087
  2    a    two  0.547541 -0.310040
  4    a    two  1.224041  0.220150
  6    a    one  1.539144 -0.758716
  7    a  three  0.385845  0.074309), ('b',   key1   key2     data1     data2
  1    b    one -0.992986  0.902458
  3    b  three  0.458871 -1.895392
  5    b    two -0.200124 -1.562237)]

# GroupBy对象转换dict
dict(list(grouped1))

{'a':   key1   key2     data1     data2
 0    a    one -0.147612 -0.348087
 2    a    two  0.547541 -0.310040
 4    a    two  1.224041  0.220150
 6    a    one  1.539144 -0.758716
 7    a  three  0.385845  0.074309, 'b':   key1   key2     data1     data2
 1    b    one -0.992986  0.902458
 3    b  three  0.458871 -1.895392
 5    b    two -0.200124 -1.562237}

# 按列分组
print (df_obj.dtypes)

# 按数据类型分组
df_obj.groupby(df_obj.dtypes, axis=1).size()
df_obj.groupby(df_obj.dtypes, axis=1).sum()

key1      object
key2      object
data1    float64
data2    float64
dtype: object

其他分组方法

0. 其实列表也是分组的一种方式
   ===用到列表时候，一般都是多层索引了

df_obj2 = pd.DataFrame(np.random.randint(1, 10, (5,5)),
                       columns=['a', 'b', 'c', 'd', 'e'],
                       index=['A', 'B', 'C', 'D', 'E'])
df_obj2.ix[1, 1:4] = np.NaN
df_obj2

C:\Users\wztli\Anaconda3\lib\site-packages\ipykernel_launcher.py:4: FutureWarning: 
.ix is deprecated. Please use
.loc for label based indexing or
.iloc for positional indexing

See the documentation here:
//pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#ix-indexer-is-deprecated
  after removing the cwd from sys.path.

1. 通过字典分组

# 通过字典分组
mapping_dict = {'A':'python', 'B':'python', 'C':'java', 'D':'C', 'E':'java'}
#df_obj2.groupby(mapping_dict, axis=1).size()
#df_obj2.groupby(mapping_dict, axis=1).count() # 非NaN的个数
print(df_obj2.groupby(mapping_dict, axis=0).sum())

        a    b     c    d   e
C       9  5.0   6.0  5.0   9
java    6  4.0  14.0  8.0   4
python  9  2.0   6.0  5.0  15

2. 通过函数分组

# 通过函数分组
df_obj3 = pd.DataFrame(np.random.randint(1, 10, (5,5)),
                       columns=['a', 'b', 'c', 'd', 'e'],
                       index=['AA', 'BBB', 'CC', 'D', 'EE'])
#df_obj3

def group_key(idx):
    """
        idx 为列索引或行索引
    """
    #return idx
    return len(idx)

df_obj3.groupby(group_key).size()

# 以上自定义函数等价于
#df_obj3.groupby(len).size()

1    1
2    3
3    1
dtype: int64

3. 通过层级索引级别分组

# 通过索引级别分组
columns = pd.MultiIndex.from_arrays([['Python', 'Java', 'Python', 'Java', 'Python'],
                                     ['A', 'A', 'B', 'C', 'B']], names=['language', 'index'])
df_obj4 = pd.DataFrame(np.random.randint(1, 10, (5, 5)), columns=columns)
df_obj4

# 根据language进行分组
df_obj4.groupby(level='language', axis=1).sum()
df_obj4.groupby(level='index', axis=1).sum()

二、聚合

dict_obj = {'key1' : ['a', 'b', 'a', 'b', 
                      'a', 'b', 'a', 'a'],
            'key2' : ['one', 'one', 'two', 'three',
                      'two', 'two', 'one', 'three'],
            'data1': np.random.randint(1,10, 8),
            'data2': np.random.randint(1,10, 8)}
df_obj5 = pd.DataFrame(dict_obj)
print (df_obj5)

  key1   key2  data1  data2
0    a    one      9      4
1    b    one      6      7
2    a    two      9      4
3    b  three      9      6
4    a    two      6      2
5    b    two      3      3
6    a    one      1      1
7    a  three      2      6

内置的聚合函数

df_obj5

# 内置的聚合函数
#print (df_obj5.groupby('key1').sum())
#print (df_obj5.groupby('key1').max())
#print (df_obj5.groupby('key1').min())
print (df_obj5.groupby('key1').mean())
#print (df_obj5.groupby('key1').size())
#print (df_obj5.groupby('key1').count())
#print (df_obj5.groupby('key1').describe())
'''
count：分组中非NA的值
std：标准差
var：方差
median：非NA中的中位数
mean：非NA的平均值
25%||50%||75%是什么意思==不造？
'''

      data1     data2
key1                 
a       5.4  3.400000
b       6.0  5.333333





'\ncount：分组中非NA的值\nstd：标准差\nvar：方差\nmedian：非NA中的中位数\nmean：非NA的平均值\n25%||50%||75%是什么意思==不造？\n'

自定义聚合函数

# 自定义聚合函数
def peak_range(df):
    """
        返回数值范围
    """
    #print type(df) #参数为索引所对应的记录
    return df.max() - df.min()

print (df_obj5.groupby('key1').agg(peak_range))
#print df_obj.groupby('key1').agg(lambda df : df.max() - df.min())
#默认列名就是函数名。

      data1  data2
key1              
a         8      5
b         6      4

3. 同时应用多个聚合函数：agg

# 同时应用多个聚合函数：agg
print (df_obj.groupby('key1').agg(['mean', 'std', 'count'])) 

         data1                     data2                
          mean       std count      mean       std count
key1                                                    
a     0.709792  0.674293     5 -0.224477  0.385674     5
b    -0.244746  0.726957     3 -0.851723  1.528271     3

print (df_obj.groupby('key1').agg(['mean', 'std', 'count', ('range', peak_range)])) # 通过元组提供新的列名

         data1                               data2                          
          mean       std count     range      mean       std count     range
key1                                                                        
a     0.709792  0.674293     5  1.686756 -0.224477  0.385674     5  0.978865
b    -0.244746  0.726957     3  1.451857 -0.851723  1.528271     3  2.797850

# 每列作用不同的聚合函数
dict_mapping = {'data1':'mean',
                'data2':'sum'}
print (df_obj.groupby('key1').agg(dict_mapping))

         data1     data2
key1                    
a     0.709792 -1.122384
b    -0.244746 -2.555170

dict_mapping = {'data1':['mean','max'],
                'data2':'sum'}
print (df_obj.groupby('key1').agg(dict_mapping))

         data1               data2
          mean       max       sum
key1                              
a     0.709792  1.539144 -1.122384
b    -0.244746  0.458871 -2.555170

三、分组运算

import pandas as pd
import numpy as np

分组和对齐

s1 = pd.Series(range(10, 20), index = range(10))
s2 = pd.Series(range(20, 25), index = range(5))
print ('s1: ' )
print (s1)
print('===========================') 
print ('s2: ')
print (s2)

s1: 
0    10
1    11
2    12
3    13
4    14
5    15
6    16
7    17
8    18
9    19
dtype: int64
===========================
s2: 
0    20
1    21
2    22
3    23
4    24
dtype: int64

# Series 对齐运算
s1 + s2
print(s1+s2)

0    30.0
1    32.0
2    34.0
3    36.0
4    38.0
5     NaN
6     NaN
7     NaN
8     NaN
9     NaN
dtype: float64

df1 = pd.DataFrame(np.ones((2,2)), columns = ['a', 'b'])
df2 = pd.DataFrame(np.ones((3,3)), columns = ['a', 'b', 'c'])

print ('df1: ')
print (df1)
print ('=================')
print ('df2: ')
print (df2)

df1: 
     a    b
0  1.0  1.0
1  1.0  1.0
=================
df2: 
     a    b    c
0  1.0  1.0  1.0
1  1.0  1.0  1.0
2  1.0  1.0  1.0

# DataFrame对齐操作
print(df1 + df2)

     a    b   c
0  2.0  2.0 NaN
1  2.0  2.0 NaN
2  NaN  NaN NaN

① 常用运算函数

# 填充未对齐的数据进行运算
print(s1.add(s2, fill_value = -1))

0    30.0
1    32.0
2    34.0
3    36.0
4    38.0
5    14.0
6    15.0
7    16.0
8    17.0
9    18.0
dtype: float64

df1.sub(df2, fill_value = 2.)
#sub函数

# 填充NaN
s3 = s1 + s2
print (s3)

0    30.0
1    32.0
2    34.0
3    36.0
4    38.0
5     NaN
6     NaN
7     NaN
8     NaN
9     NaN
dtype: float64

s3_filled = s3.fillna(-1)
print (s3)

0    30.0
1    32.0
2    34.0
3    36.0
4    38.0
5     NaN
6     NaN
7     NaN
8     NaN
9     NaN
dtype: float64

df3 = df1 + df2
print (df3)

     a    b   c
0  2.0  2.0 NaN
1  2.0  2.0 NaN
2  NaN  NaN NaN

df3.fillna(100, inplace = True)
print (df3)

       a      b      c
0    2.0    2.0  100.0
1    2.0    2.0  100.0
2  100.0  100.0  100.0

统计计算VS聚合运算

df_obj1 = pd.DataFrame(np.random.randn(5,4), columns = ['a', 'b', 'c', 'd'])
print(df_obj1)

          a         b         c         d
0 -0.542708  0.201376  1.111431  1.784324
1  0.583422  0.231096 -2.801967  0.568497
2 -0.577329 -1.668581 -0.842126  1.803080
3 -0.128431 -1.769619  2.089983  0.209761
4  0.493981 -1.571405  0.690019 -0.215292

print(df_obj1.sum(axis=1))
print('=====================================')
print(df_obj1.max())
print('=====================================')
print(df_obj1.min(axis=1))

0    2.554423
1   -1.418952
2   -1.284956
3    0.401694
4   -0.602698
dtype: float64
=====================================
a    0.583422
b    0.231096
c    2.089983
d    1.803080
dtype: float64
=====================================
0   -0.542708
1   -2.801967
2   -1.668581
3   -1.769619
4   -1.571405
dtype: float64

数据分组运算

# 分组运算后保持shape
dict_obj = {'key1' : ['a', 'b', 'a', 'b', 
                      'a', 'b', 'a', 'a'],
            'key2' : ['one', 'one', 'two', 'three',
                      'two', 'two', 'one', 'three'],
            'data1': np.random.randint(1, 10, 8),
            'data2': np.random.randint(1, 10, 8)}
df_obj = pd.DataFrame(dict_obj)
df_obj

# 按key1分组后，计算data1，data2的统计信息======并附加到原始表格中
k1_sum = df_obj.groupby('key1').sum().add_prefix('sum_')
print(k1_sum)
print('================================')
print(df_obj)

      sum_data1  sum_data2
key1                      
a            22         15
b            18          8
================================
  key1   key2  data1  data2
0    a    one      4      3
1    b    one      4      4
2    a    two      9      6
3    b  three      8      2
4    a    two      3      3
5    b    two      6      2
6    a    one      4      1
7    a  three      2      2

1. merge方法

# 方法1，使用merge
pd.merge(df_obj, k1_sum, left_on='key1', right_index=True)

2. transform方法

# 方法2，使用transform
k1_sum_tf = df_obj.groupby('key1').transform(np.sum).add_prefix('sum_')
df_obj[k1_sum_tf.columns] = k1_sum_tf
df_obj

3. 自定义函数

# 自定义函数传入transform
def diff_mean(s):
    """
        返回数据与均值的差值
    """
    return s - s.mean()

df_obj.groupby('key1').transform(diff_mean)

dataset_path = './data/starcraft.csv'
df_data = pd.read_csv(dataset_path, usecols=['LeagueIndex', 'Age', 'HoursPerWeek', 
                                             'TotalHours', 'APM'])

• apply

def top_n(df, n=3, column='APM'):
    """
        返回每个分组按 column 的 top n 数据
    """
    return df.sort_values(by=column, ascending=False)[:n]

df_data.groupby('LeagueIndex').apply(top_n)

# apply函数接收的参数会传入自定义的函数中
df_data.groupby('LeagueIndex').apply(top_n, n=2, column='Age')

• 禁止分组 group_keys=False

df_data.groupby('LeagueIndex', group_keys=False).apply(top_n)