Python pandas - 把绝对时期变成相对时期 [英] Python Pandas - turn absolute periods into relative periods

问题描述

我有一个数据框，我想用来计算相对于事件日期的滚动和。每个列的事件日期不同，并由每列中的值的最新日期表示。

I have a dataframe that I want to use to calculate rolling sums relative to an event date. The event date is different for each column and is represented by the latest date in which there is a value in each column.

这是一个玩具示例：

rng = pd.date_range('1/1/2011', periods=8, freq='D')
df = pd.DataFrame({
            '1' : [56, 2, 3, 4, 5, None, None, None],
            '2' : [51, 2, 3, 4, 5, 6, None, None],
            '3' : [51, 2, 3, 4, 5, 6, 0, None]}, index = rng)

pd.rolling_sum(df,3)

它生成的数据框如下所示：

The dataframe it produces looks like this:

            1       2       3
2011-01-01  NaN     NaN     NaN
2011-01-02  NaN     NaN     NaN
2011-01-03  61      56      56
2011-01-04  9       9       9
2011-01-05  12      12      12
2011-01-06  NaN     15      15
2011-01-07  NaN     NaN     11
2011-01-08  NaN     NaN     NaN

我现在想对齐最后一个事件数据es在数据帧的最后一行，并将索引设置为0，每个前面的行索引为-1，-2，-3等等。期间不再是绝对但相对于事件日期。

I now want to align the last event dates on the final row of the dataframe and set the index to 0 with each preceding row index -1,-2,-3 and so on. The periods no longer being absolute but relative to the event date.

所需的数据框将如下所示：

The desired dataframe would look like this:

    1   2   3
-7.00   NaN NaN NaN
-6.00   NaN NaN NaN
-5.00   NaN NaN NaN
-4.00   NaN NaN 56
-3.00   NaN 56  9
-2.00   61  9   12
-1.00   9   12  15
0.00    12  15  11

感谢任何指导。

推荐答案

我没有看到任何容易这样做的方法以下将会起作用，但有点混乱。

I don't see any easy ways to do this. The following will work, but a bit messy.

In [37]: def f(x):
   ....:     y = x.dropna()
   ....:     return Series(y.values,x.index[len(x)-len(y):])
   ....: 

In [40]: roller = pd.rolling_sum(df,3).reset_index(drop=True)

In [41]: roller
Out[41]: 
    1   2   3
0 NaN NaN NaN
1 NaN NaN NaN
2  61  56  56
3   9   9   9
4  12  12  12
5 NaN  15  15
6 NaN NaN  11
7 NaN NaN NaN

[8 rows x 3 columns]

In [43]: roller.apply(f).reindex_like(roller)
Out[43]: 
    1   2   3
0 NaN NaN NaN
1 NaN NaN NaN
2 NaN NaN NaN
3 NaN NaN  56
4 NaN  56   9
5  61   9  12
6   9  12  15
7  12  15  11

[8 rows x 3 columns]

In [44]: result = roller.apply(f).reindex_like(roller)

In [49]: result.index = result.index.values-len(result.index)+1

In [50]: result
Out[50]: 
     1   2   3
-7 NaN NaN NaN
-6 NaN NaN NaN
-5 NaN NaN NaN
-4 NaN NaN  56
-3 NaN  56   9
-2  61   9  12
-1   9  12  15
 0  12  15  11

[8 rows x 3 columns]

这篇关于Python pandas - 把绝对时期变成相对时期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！