如何反转总和? [英] How to Reverse Rolling Sum?

问题描述

我在一个分组的数据帧上计算了滚动总和，但是当我需要过去总和时，它的总和是错误的，它是未来的总和.

I have a rolling sum calculated on a grouped data frame but its adding up the wrong way, it is a sum of the future, when I need a sum of the past.

我在做什么错了?

我导入数据并按维度和日期排序(我已经尝试删除日期排序)

I import the data and sort by Dimension and Date (I have tried removing the date sort already)

df = pd.read_csv('Input.csv', parse_dates=True)
df.sort_values(['Dimension','Date'])
print(df)

然后我创建一个新列，该列是按滚动窗口分组的多索引

I then create a new column which is a multi index grouped by rolling window

new_column = df.groupby('Dimension').Value1.apply(lambda x: 
x.rolling(window=3).sum())

然后我将索引重置为原始索引

I then reset the index to be the same as the original

df['Sum_Value1'] = new_column.reset_index(level=0, drop=True)
print(df)

我还尝试在计算之前反转索引，但这也失败了.

I have also tried reversing the index before the calculation, but that also failed.

输入

Dimension,Date,Value1,Value2
1,4/30/2002,10,20
1,1/31/2002,10,20
1,10/31/2001,10,20
1,7/31/2001,10,20
1,4/30/2001,10,20
1,1/31/2001,10,20
1,10/31/2000,10,20
2,4/30/2002,10,20
2,1/31/2002,10,20
2,10/31/2001,10,20
2,7/31/2001,10,20
2,4/30/2001,10,20
2,1/31/2001,10,20
2,10/31/2000,10,20
3,4/30/2002,10,20
3,1/31/2002,10,20
3,10/31/2001,10,20
3,7/31/2001,10,20
3,1/31/2001,10,20
3,10/31/2000,10,20

输出:

    Dimension        Date  Value1  Value2  Sum_Value1
0           1   4/30/2002      10      20         NaN
1           1   1/31/2002      10      20         NaN
2           1  10/31/2001      10      20        30.0
3           1   7/31/2001      10      20        30.0
4           1   4/30/2001      10      20        30.0
5           1   1/31/2001      10      20        30.0
6           1  10/31/2000      10      20        30.0
7           2   4/30/2002      10      20         NaN
8           2   1/31/2002      10      20         NaN
9           2  10/31/2001      10      20        30.0
10          2   7/31/2001      10      20        30.0
11          2   4/30/2001      10      20        30.0
12          2   1/31/2001      10      20        30.0
13          2  10/31/2000      10      20        30.0

目标输出:

    Dimension        Date  Value1  Value2  Sum_Value1
0           1   4/30/2002      10      20        30.0
1           1   1/31/2002      10      20        30.0
2           1  10/31/2001      10      20        30.0
3           1   7/31/2001      10      20        30.0
4           1   4/30/2001      10      20        30.0
5           1   1/31/2001      10      20         NaN
6           1  10/31/2000      10      20         NaN
7           2   4/30/2002      10      20        30.0
8           2   1/31/2002      10      20        30.0
9           2  10/31/2001      10      20        30.0
10          2   7/31/2001      10      20        30.0
11          2   4/30/2001      10      20        30.0
12          2   1/31/2001      10      20         Nan
13          2  10/31/2000      10      20         NaN

推荐答案

您可以将结果移动window-1以获得左对齐的结果:

You can shift the result by window-1 to get the left aligned results:

df["sum_value1"] = (df.groupby('Dimension').Value1
                      .apply(lambda x: x.rolling(window=3).sum().shift(-2)))

这篇关于如何反转总和?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！