Aggregation over Partition - pandas Dataframe

Question

I am looking for the best way to aggregate values based on a particular partition , an equivalent of

SUM(TotalCost) OVER(PARTITION BY ShopName) Earnings  ( SQL server) 

I am able to do this by the following steps in Pandas , but looking for a native approach which I am sure should exist

TempDF= DF.groupby(by=['ShopName'])['TotalCost'].sum()  TempDF= TempDF.reset_index()   NewDF=pd.merge(DF , TempDF, how='inner', on='ShopName') 

Thanks a lot for reading through !

Answer 1

You can use pandas transform() method for within group aggregations like "OVER(partition by ...)" in SQL:

import pandas as pd import numpy as np  #create dataframe with sample data df = pd.DataFrame({'group':['A','A','A','B','B','B'],'value':[1,2,3,4,5,6]})  #calculate AVG(value) OVER (PARTITION BY group) df['mean_value'] = df.groupby('group').value.transform(np.mean)  df: group   value   mean_value A       1       2 A       2       2 A       3       2 B       4       5 B       5       5 B       6       5