数据分析（二十二）

21.分组 聚合 后的 数据处理

21.1 先分组，再计算，添加前缀，merge关联

>>> dict_new = {
...    'data1':np.random.randint(5,15,8),
...    'data2':np.random.randint(5,15,8),
...    'key1':list('aabbccdd'),
...    'key2':['one','two','three','one','two','one','two','three'],
... }
>>> df1 = pd.DataFrame(dict_new,index=list('ADCBEFGH'))
>>> print(df1)
    data1  data2 key1   key2
A      6     10    a    one
D      5     12    a    two
C     13      9    b  three
B     13      7    b    one
E      7      7    c    two
F     13     11    c    one
G      8     10    d    two
H     14     10    d  three

>>> group1 = df1.groupby(df1['key1'])
>>> for name,obj in group1:
...    print(name)
...    print(obj)
....    print("################################")
a
   data1  data2 key1 key2
A      6     10    a  one
D      5     12    a  two
################################
b
   data1  data2 key1   key2
C     13      9    b  three
B     13      7    b    one
################################
c
   data1  data2 key1 key2
E      7      7    c  two
F     13     11    c  one
################################
d
   data1  data2 key1   key2
G      8     10    d    two
H     14     10    d  three
################################

# 分组后，进行求和操作
>>> sum_key1 = group1.sum().add_prefix('key1_sum_')
>>> sum_key1
    key1_sum_data1	key1_sum_data2
key1		
a		11				 22
b		26				 16
c		20				 18
d		22				 20

# 先用merge进行关联
>>> pd.merge(df1,sum_key1,left_on="key1",right_index=True)
	data1	data2	key1	key2	key1_sum_data1	key1_sum_data2
A	 6	      10	 a		one	        11	              22
D	 5	      12	 a		two	        11	              22
C	 13	      9		 b		three	    26	              16
B	 13	      7		 b		one	        26	              16
E	 7	      7		 c		two	        20	              18
F	 13	      11	 c		one	        20	              18
G	 8	      10	 d		two	        22	              20
H	 14	      10	 d		three	    22	              20

注意点：在当前案例中，指定的连接条件：左表的列名，右表的行索引名

21.2 transform

将函数作用于每个分组上

>>> for name,obj in group1:
...    print(name)
...    print(obj)
....    print("################################")
a
   data1  data2 key1 key2
A      6     10    a  one
D      5     12    a  two
################################
b
   data1  data2 key1   key2
C     13      9    b  three
B     13      7    b    one
################################
c
   data1  data2 key1 key2
E      7      7    c  two
F     13     11    c  one
################################
d
   data1  data2 key1   key2
G      8     10    d    two
H     14     10    d  three
################################

>>> group1.transform('sum')
data1	data2	key2
A	11	22	onetwo
D	11	22	onetwo
C	26	16	threeone
B	26	16	threeone
E	20	18	twoone
F	20	18	twoone
G	22	20	twothree
H	22	20	twothree

>>> print(df1)
    data1  data2 key1   key2
A      6     10    a    one
D      5     12    a    two
C     13      9    b  three
B     13      7    b    one
E      7      7    c    two
F     13     11    c    one
G      8     10    d    two
H     14     10    d  three
>>> df1[['data1','data2']].groupby(df1['key2']).transform('sum').add_prefix('聚合求和_')
	聚合求和_data1	聚合求和_data2
A		32			  28
D		20			  29
C		27			  19
B		32			  28
E		20			  29
F		32			  28
G		20			  29
H		27			  19

注意点：

1. transform来计算的时候，会维持原来的数据结构
2. df1.loc[['data1','data2']].groupby(df1['key1'])，选取数据其中的数据，按照原来数据中的列来进行分组