How to extract mean and fluctuation by equal index?

user_10920639 0
Viewed 148

I have a CSV file like the below (after sorted the dataframe by iy):

iy,u
1,80
1,90
1,70
1,50
1,60
2,20
2,30
2,35
2,15
2,25

I'm trying to compute the mean and the fluctuation when iy are equal. For example, for the CSV above, what I want is something like this:

iy,u,U,u'
1,80,70,10
1,90,70,20
1,70,70,0
1,50,70,-20
1,60,70,-10
2,20,25,-5
2,30,25,5
2,35,25,10
2,15,25,-10
2,25,25,0

Where U is the average of u when iy are equal, and u' is simply u-U, the fluctuation. I know that there's a function called groupby.mean() in pandas, but I don't want to group the dataframe, just take the mean, put the values in a new column, and then calculate the fluctuation.

How can I proceed?

pandas python
1 Answers

Use groupby with transform to calculate a mean for each group and assign that value to a new column 'U', then pandas to subtract two columns:

df['U'] = df.groupby('iy').transform('mean')
df["u'"] = df['u'] - df['U']
df

Output:

   iy   u   U  u'
0   1  80  70  10
1   1  90  70  20
2   1  70  70   0
3   1  50  70 -20
4   1  60  70 -10
5   2  20  25  -5
6   2  30  25   5
7   2  35  25  10
8   2  15  25 -10
9   2  25  25   0

You could get fancy and do it in one line:

df.assign(U=df.groupby('iy').transform('mean')).eval("u_prime = u-U")
user_63615310
Related