I'm trying to aggregate certain variables in my dataframe as a function of the categories/labels in one column. Example df:
id side level x y
1 left up 12 23
1 right up 32 21
1 left down 43 54
1 right down 33 22
2 left up 99 88
...
I need to aggregate x and y values as a function of either side or level. For example, if I do it as a function of level and drop the side variable, the df should look like this:
id level x y
1 up 44 44
1 down 76 76
2 up .. ..
...
Here I aggregate all values of up and down and sum the values across side.
I've tried this approach but the values don't seem correct and I haven't been able to remove repeated values
df['new_x'] = df.groupby('level')['x'].transform('sum')