Dataframe: adding a column with mean by other column group

user_14098361 0
Viewed 67

Say I have the following DataFrame:

data = pd.DataFrame({'id' : ['1','2','3','4','5'], 'group' : ['1','1','2','1','2'], 
      'state' : ['True','False','False','True','True'], 'value' : [11,12,5,8,3]})

I would like to add to the previous dataframe, a new column with the average 'state', which would be

pd.DataFrame({'id' : ['1','2','3','4','5'], 'group' : ['1','1','2','1','2'],
      'state' : ['True','False','False','True','True'], 'avg_state' : [0.66,0.66,0.5,0.66,0.5] ,value' : [11,12,5,8,3]})
pandas-groupby pandas mean dataframe python
2 Answers

IIUC change state column back to boolean so you can sum, then groupby and transform:

df["avg_state"] = (df.assign(state=df["state"].map({"True":True, "False":False}))
                     .groupby("group")["state"]
                     .transform(lambda d: d.sum()/d.count()))
  
print (df)
  
  id group  state  value  avg_state
0  1     1   True     11   0.666667
1  2     1  False     12   0.666667
2  3     2  False      5   0.500000
3  4     1   True      8   0.666667
4  5     2   True      3   0.500000
user_92844230

Another alternative with pd.eval and transform with mean

data['av_state'] = (data.assign(state=pd.eval(data['state']).astype(int))
                       .groupby("group")['state'].transform('mean'))

print(data)

  id group  state  value  av_state
0  1     1   True     11  0.666667
1  2     1  False     12  0.666667
2  3     2  False      5  0.500000
3  4     1   True      8  0.666667
4  5     2   True      3  0.500000
user_98406370
Related