python - Pandas :计算与分组平均值的差异

Question

我有多个传感器的按月和按年的传感器数据:

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

我使用 groupby 计算了每个传感器和月份的平均值:

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

现在我想在 df 中添加一个与月平均值不同的列，如下所示:

    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

我类似地尝试了多索引 df 和 avgs_by_month 并尝试了简单的减法，但效果不佳:

df = df.set_index(['sensor', 'month'])
df['diff_from_avg'] = month_avg - df.value

感谢您的任何建议。

Answer 1

assign 使用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