Group by one columns and find sum and max value for another in pandas
The most (pandas) native way to do this, is to use the .agg()
method that allows you to specify the aggregation function you want to apply per column (just like you would do in SQL).
Sample from the documentation:
df.groupby('A').agg({'B': ['min', 'max'], 'C': 'sum'})
You can use groupby/transform to creat the required columns
df[['col1_sum', 'col4_sum']]=df.groupby('id')['col1', 'cl4'].transform('sum')
df[['col2_max', 'col3_max']]=df.groupby('id')['col1', 'cl4'].transform('max')
Name id col1 col2 col3 cl4 col1_sum col4_sum col2_max col3_max
0 PL 252 0 747 3 53 5 101 4 53
1 PL2 252 1 24 2 35 5 101 4 53
2 PL3 252 4 75 24 13 5 101 4 53
3 AD 889 53 24 0 95 76 114 53 95
4 AD2 889 23 2 0 13 76 114 53 95
5 AD3 889 0 24 3 6 76 114 53 95
6 BG 24 12 89 53 66 60 70 43 66
7 BG1 24 43 16 13 0 60 70 43 66
8 BG2 24 5 32 101 4 60 70 43 66