CODEWITHSUNDEEP
pythonpandas
chippycentra
chippycentra

Reputation: 3432

Add a new column with sum of values divided by unique value within groups in python

I have a dataframe such as ::

Group COL1 COL2 
G1    30   500
G1    21   500 
G1    43   500 
G2    89   677
G2    78   900 
G3    32   322
G3    90   200

and I would like to add a new column called mean_group where I calculate for each Group, the sum of COL1/unique value of COL2.For instance, (30+21+43)/500 = 0.188

I should then get:  
Group COL1 COL2 mean_group
G1    30   500  0.188
G1    21   500  0.188
G1    43   500  0.188
G2    89   677  0.2466765
G2    78   677  0.2466765
G3    32   322  0.09937888

Upvotes: 1

Views: 47

Answers (2)

gilf0yle
gilf0yle

Reputation: 1102

do this

df['mean_group']=df.groupby('Group')['COL1'].transform('sum')/df['COL2']

output:

  Group  COL1  COL2  mean_group
0    G1    30   500    0.188000
1    G1    21   500    0.188000
2    G1    43   500    0.188000
3    G2    89   677    0.246677
4    G2    78   900    0.185556
5    G3    32   322    0.378882
6    G3    90   200    0.610000

Upvotes: 2

mozway
mozway

Reputation: 260745

Use:

df['mean_group'] = df.groupby('Group')['COL1'].transform('mean')/df['COL2']

Upvotes: 1

Related Questions