pandas分组聚合、表格操作

一、聚合函数

（1）通过numpy或者pandas中统计分析方法；
（2）agg([np.sum,np.mean])
或agg({‘columns’:np.sum,np.mean],‘columns2’:np.sum,np.mean]})
（3）transform：转换只有一个参数func

transform聚合方法：
在pandas或者numpy中没有现成的函数可以使用，可以通过transform使用自定义的函数

data=pd.read_excel(r'meal_order_detail.xlsx')
print(data.info())

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 2779 entries, 0 to 2778
Data columns (total 19 columns):
 #   Column             Non-Null Count  Dtype         
---  ------             --------------  -----         
 0   detail_id          2779 non-null   int64         
 1   order_id           2779 non-null   int64         
 2   dishes_id          2779 non-null   int64         
 3   logicprn_name      0 non-null      float64       
 4   parent_class_name  0 non-null      float64       
 5   dishes_name        2779 non-null   object        
 6   itemis_add         2779 non-null   int64         
 7   counts             2779 non-null   int64         
 8   amounts            2779 non-null   int64         
 9   cost               0 non-null      float64       
 10  place_order_time   2779 non-null   datetime64[ns]
 11  discount_amt       0 non-null      float64       
 12  discount_reason    0 non-null      float64       
 13  kick_back          0 non-null      float64       
 14  add_inprice        2779 non-null   int64         
 15  add_info           0 non-null      float64       
 16  bar_code           0 non-null      float64       
 17  picture_file       2779 non-null   object        
 18  emp_id             2779 non-null   int64         
dtypes: datetime64[ns](1), float64(8), int64(8), object(2)
memory usage: 412.6+ KB
None

# 写法一：
data['counts'].transform(lambda x:x*2)

0       2
1       2
2       2
3       2
4       2
       ..
2774    2
2775    2
2776    2
2777    2
2778    2
Name: counts, Length: 2779, dtype: int64

# 写法二：
def transform1(value):
    values=value*2
    return values
data['counts'].transform(transform1)

0       2
1       2
2       2
3       2
4       2
       ..
2774    2
2775    2
2776    2
2777    2
2778    2
Name: counts, Length: 2779, dtype: int64

二、分组：
groupby(by=‘columns1’)

三、透视表

index：行分组键，分完组以后，分组键的取值在行索引的位置；
aggfunc：聚合函数==>和agg方法一致；
values：指定想要进行聚合的列；
columns：列分组键，分完组之后，分组键的取值，在列索引的位置；
fill_value：将数据为np.nan的值填充为对应的值；
margins：表示汇总开关，默认是False；
margins_name:‘ALL’，汇总的列或者行的columns，index的索引；

pd.pivot_table(data,
               index=['order_id','amounts'],
               aggfunc=[np.mean,np.sum],
               values=['counts','dishes_name']).head()

		mean	sum
		counts	counts
order_id	amounts
137	1	4.0	4
	6	1.0	1
	26	1.0	1
	27	1.0	1
	35	1.0	1

pd.pivot_table(data,
               columns=['order_id'],
               aggfunc=[np.mean,np.sum],
               values=['counts','add_inprice']).head()

	mean										...	sum
order_id	137	165	166	171	177	193	201	203	239	242	...	1290	1293	1298	1302	1303	1309	1314	1317	1319	1323
add_inprice	0.0	0.000000	0.0	0.000000	0.0	0.0	0.00	0.0	0.0	0.000000	...	0	0	0	0	0	0	0	0	0	0
counts	1.5	1.166667	1.4	1.428571	1.0	1.0	1.25	1.0	1.0	1.833333	...	15	5	9	22	9	15	12	18	9	15

2 rows × 556 columns

pd.pivot_table(data,
               columns=['order_id'],
               index='dishes_name',
               values='counts',
               aggfunc=np.sum,
               fill_value=0,
               margins=True
              ).head()

order_id	137	165	166	171	177	193	201	203	239	242	...	1293	1298	1302	1303	1309	1314	1317	1319	1323	All
dishes_name
42度海之蓝	0	0	0	0	0	0	0	0	0	0	...	0	0	0	0	0	0	0	0	0	5
北冰洋汽水	0	0	0	0	0	0	0	0	0	0	...	0	0	0	3	0	0	0	0	0	45
38度剑南春	0	1	0	0	0	0	0	0	0	0	...	0	0	0	0	0	1	0	0	1	6
50度古井贡酒	0	0	0	0	0	0	0	0	0	0	...	0	0	0	0	0	0	0	0	0	5
52度泸州老窖	0	0	0	0	0	0	0	0	0	0	...	0	0	0	0	0	0	0	0	0	8

5 rows × 279 columns

四、交叉表

这里面不传表，因此行索引，列索引，值都得从表开始
这个方法必须行列都传

pd.crosstab(index=data['order_id'],
            columns=data['dishes_name'],
            values=data['counts'],
            aggfunc=np.sum).head()

dishes_name	42度海之蓝	北冰洋汽水	38度剑南春	50度古井贡酒	52度泸州老窖	53度茅台	一品香酥藕	三丝鳝鱼	三色凉拌手撕兔	不加一滴油的酸奶蛋糕	...	香辣腐乳炒虾	香酥两吃大虾	鱼香肉丝拌面	鲜美鳝鱼	鸡蛋、肉末肠粉	麻辣小龙虾	黄尾袋鼠西拉子红葡萄酒	黄油曲奇饼干	黄花菜炒木耳	黑米恋上葡萄
order_id
137	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	1.0	NaN	NaN	NaN	NaN
165	NaN	NaN	1.0	NaN	NaN	NaN	1.0	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	1.0	NaN
166	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
171	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
177	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN

5 rows × 156 columns

五、表格合并方法

left = pd.DataFrame({'key1': ['K0', 'K0', 'K1', 'K2'],
                     'key2': ['K0', 'K1', 'K0', 'K1'],
                         'A': ['A0', 'A1', 'A2', 'A3'],
                        'B': ['B0', 'B1', 'B2', 'B3']})

right = pd.DataFrame({'key1': ['K0', 'K1', 'K1', 'K2'],
                      'key2': ['K0', 'K0', 'K0', 'K0'],
                         'C': ['C0', 'C1', 'C2', 'C3'],
                         'D': ['D0', 'D1', 'D2', 'D3']})

print(left)

  key1 key2   A   B
0   K0   K0  A0  B0
1   K0   K1  A1  B1
2   K1   K0  A2  B2
3   K2   K1  A3  B3

print(right)

  key1 key2   C   D
0   K0   K0  C0  D0
1   K1   K0  C1  D1
2   K1   K0  C2  D2
3   K2   K0  C3  D3

concat参数：
join='outer’外连接，求并集，默认
join='inner’内连接，求交集

# 表格纵向拼接
pd.concat((left,right),axis=0,join='outer')

	key1	key2	A	B	C	D
0	K0	K0	A0	B0	NaN	NaN
1	K0	K1	A1	B1	NaN	NaN
2	K1	K0	A2	B2	NaN	NaN
3	K2	K1	A3	B3	NaN	NaN
0	K0	K0	NaN	NaN	C0	D0
1	K1	K0	NaN	NaN	C1	D1
2	K1	K0	NaN	NaN	C2	D2
3	K2	K0	NaN	NaN	C3	D3

# 表格横向拼接,一般不用，因为是找索引，索引乱的话合并是乱的
pd.concat((left,right),axis=1)

	key1	key2	A	B	key1	key2	C	D
0	K0	K0	A0	B0	K0	K0	C0	D0
1	K0	K1	A1	B1	K1	K0	C1	D1
2	K1	K0	A2	B2	K1	K0	C2	D2
3	K2	K1	A3	B3	K2	K0	C3	D3

merge：主键合并方法 横向拼接
解决行索引没有意义情况下，数据行不匹配问题(解决concat中横向拼接问题)
on：必须是两张表公共的字段，才能作为主键
how：连接方式，默认是inner 内连接，取交集
how=‘outer’，外连接，取并集
how=‘left’，左连接，左表为主，只关心左表的主键，不管右表
how=‘right’，右连接，右表为主，只关心右表的主键，不管左表

pd.merge(left,right,on='key1')

	key1	key2_x	A	B	key2_y	C	D
0	K0	K0	A0	B0	K0	C0	D0
1	K0	K1	A1	B1	K0	C0	D0
2	K1	K0	A2	B2	K0	C1	D1
3	K1	K0	A2	B2	K0	C2	D2
4	K2	K1	A3	B3	K0	C3	D3

多个主键on=[ , ]

pd.merge(left,right,on=['key1','key2'],how='outer')

	key1	key2	A	B	C	D
0	K0	K0	A0	B0	C0	D0
1	K0	K1	A1	B1	NaN	NaN
2	K1	K0	A2	B2	C1	D1
3	K1	K0	A2	B2	C2	D2
4	K2	K1	A3	B3	NaN	NaN
5	K2	K0	NaN	NaN	C3	D3

当两个表中主键名字不一样的时候，不能使用on值
left_on：指定左表中的主键；
right_on：指定右表中的主键；
左表key1，右表中key2，作为主键
也就是左表的key1 找右表的key2

pd.merge(left,right,left_on='key1',right_on='key2',how='right')

	key1_x	key2_x	A	B	key1_y	key2_y	C	D
0	K0	K0	A0	B0	K0	K0	C0	D0
1	K0	K1	A1	B1	K0	K0	C0	D0
2	K0	K0	A0	B0	K1	K0	C1	D1
3	K0	K1	A1	B1	K1	K0	C1	D1
4	K0	K0	A0	B0	K1	K0	C2	D2
5	K0	K1	A1	B1	K1	K0	C2	D2
6	K0	K0	A0	B0	K2	K0	C3	D3
7	K0	K1	A1	B1	K2	K0	C3	D3

更改表格名称的方法
inplace=True对原表进行修改，默认为False，不修改原表
df.rename(columns={‘old_column’:‘new_column’})

left.rename(columns={'key1':'键1'},inplace=False)

	键1	key2	A	B
0	K0	K0	A0	B0
1	K0	K1	A1	B1
2	K1	K0	A2	B2
3	K2	K1	A3	B3

六、重叠合并，将残缺的表合并成完整的

dict1 = {'ID':[1,2,3,4,5,6,7,8,9],
  'System':['W10','w10',np.nan,'w10',np.nan,np.nan,'w7','w7','w8']}

dict2 = {'ID':[1,2,3,4,5,6,7,8,9],
'System':[np.nan,np.nan,'w7','w7','w7','w7','w8',np.nan,np.nan]}
df1 = pd.DataFrame(dict1)
df2 = pd.DataFrame(dict2)

print(df1)

   ID System
0   1    W10
1   2    w10
2   3    NaN
3   4    w10
4   5    NaN
5   6    NaN
6   7     w7
7   8     w7
8   9     w8

print(df2)

   ID System
0   1    NaN
1   2    NaN
2   3     w7
3   4     w7
4   5     w7
5   6     w7
6   7     w8
7   8    NaN
8   9    NaN

# 用df2表补全 df1，df1缺少4、5行，就将df2的4、5行给df1
# 谁写到前面，谁为主，只补主表空缺，当df2数据与df1不一致时，以df1为主
df1.combine_first(df2)

	ID	System
0	1	W10
1	2	w10
2	3	w7
3	4	w10
4	5	w7
5	6	w7
6	7	w7
7	8	w7
8	9	w8