python - Pandas new column from groupby averages -
i have dataframe
>>> df = pd.dataframe({'a':[1,1,1,2,2,2], ... 'b':[10,20,20,10,20,20], ... 'result':[100,200,300,400,500,600]}) ... >>> df b result 0 1 10 100 1 1 20 200 2 1 20 300 3 2 10 400 4 2 20 500 5 2 20 600
and want create new column average result corresponding values 'a' , 'b'. can values groupby:
>>> df.groupby(['a','b'])['result'].mean() b 1 10 100 20 250 2 10 400 20 550 name: result, dtype: int64
but can not figure out how turn new column in original dataframe. final result should this,
>>> df b result avg_result 0 1 10 100 100 1 1 20 200 250 2 1 20 300 250 3 2 10 400 400 4 2 20 500 550 5 2 20 600 550
i looping through combinations of 'a' , 'b' slow , unwieldy larger sets of data. there simpler , faster way go.
you need transform
:
df['avg_result'] = df.groupby(['a','b'])['result'].transform('mean')
this generates correctly indexed column of groupby values you:
b result avg_result 0 1 10 100 100 1 1 20 200 250 2 1 20 300 250 3 2 10 400 400 4 2 20 500 550 5 2 20 600 550
Comments
Post a Comment