Pandas: calculate difference from grouped mean
I have sensor data for multiple sensors by month and year:
import pandas as pd
df = pd.DataFrame([
['A', 'Jan', 2015, 13],
['A', 'Feb', 2015, 10],
['A', 'Jan', 2016, 12],
['A', 'Feb', 2016, 11],
['B', 'Jan', 2015, 7],
['B', 'Feb', 2015, 8],
['B', 'Jan', 2016, 4],
['B', 'Feb', 2016, 9]
], columns = ['sensor', 'month', 'year', 'value'])
In [2]: df
Out[2]:
sensor month year value
0 A Jan 2015 13
1 A Feb 2015 10
2 A Jan 2016 12
3 A Feb 2016 11
4 B Jan 2015 7
5 B Feb 2015 8
6 B Jan 2016 4
7 B Feb 2016 9
I calculated the average for each gauge and month using a group:
month_avg = df.groupby(['sensor', 'month']).mean()['value']
In [3]: month_avg
Out[3]:
sensor month
A Feb 10.5
Jan 12.5
B Feb 8.5
Jan 5.5
Now I want to add a column in df
with a difference from the monthly averages, for example:
sensor month year value diff_from_avg
0 A Jan 2015 13 1.5
1 A Feb 2015 10 2.5
2 A Jan 2016 12 0.5
3 A Feb 2016 11 0.5
4 B Jan 2015 7 2.5
5 B Feb 2015 8 0.5
6 B Jan 2016 4 -1.5
7 B Feb 2016 9 -0.5
I tried multi-indexing df
and avgs_by_month
similar and tried simple subtraction but no good:
df = df.set_index(['sensor', 'month'])
df['diff_from_avg'] = month_avg - df.value
Thanks for any advice.
+3
source to share
3 answers
assign
new column with transform
diff_from_avg=df.value - df.groupby(['sensor', 'month']).value.transform('mean')
df.assign(diff_from_avg=diff_from_avg)
sensor month year value diff_from_avg
0 A Jan 2015 13 0.5
1 A Feb 2015 10 -0.5
2 A Jan 2016 12 -0.5
3 A Feb 2016 11 0.5
4 B Jan 2015 7 1.5
5 B Feb 2015 8 -0.5
6 B Jan 2016 4 -1.5
7 B Feb 2016 9 0.5
+4
source to share
Try:
df['diff_from_avg']=df.groupby(['sensor','month'])['value'].apply(lambda x: x-x.mean())
Out[18]:
sensor month year value diff_from_avg
0 A Jan 2015 13 0.5
1 A Feb 2015 10 -0.5
2 A Jan 2016 12 -0.5
3 A Feb 2016 11 0.5
4 B Jan 2015 7 1.5
5 B Feb 2015 8 -0.5
6 B Jan 2016 4 -1.5
7 B Feb 2016 9 0.5
+2
source to share