Get Weighted Average Summary Data Column In New Pandas Dataframe From Existing Dataframe Based On Other Column-ID

Somewhat similar question to an earlier question I had here: Get summary data columns in new pandas dataframe from existing dataframe based on other column-ID However, instead of j

Solution 1:

This should do the trick:

data.groupby('ID').apply(lambda g: (g['U-value']*g['Surface']).sum() / g['Surface'].sum())

To add to original dataframe, don't reset the index first:

df = data[cols].mask(m1 & m2).groupby(data["ID"]).sum()
df['U-value'] = data.groupby('ID').apply(
    lambda g: (g['U-value'] * g['Surface']).sum() / g['Surface'].sum())
df.reset_index(inplace=True)

The result:

   ID  Surface   U-value
0   2     54.0  0.777778
1   4     84.0  0.800000
2  52     96.0  1.000000
3  95     68.0  1.470588