0

I have data (dataframe called returns) that looks like this

DATE         TICKER    RETURN_DATA
2010-01-01    xxx       0.05
2010-01-01    yyy       0.01
2010-01-02    xxx       0.02
2010-01-02    yyy       0.08
.....
2010-01-29    xxx       0.11
2010-01-29    yyy       0.01

what I try to do is to calculate 4(n)-weeks rolling returns.

I implemented this

def rolling_fct(returns, window_len):
    return returns.groupby('TICKER')['RETURN_DATA'].rolling(window=window_len).apply(lambda x: np.prod(1+x)-1)

where window_len = 28 days, this works but I just discovered I need to roll this over a time delta rather than having a window_len = integer. The problems is that I am dealing with public holidays etc, so my window length is not fixed.

I am looking for the same rolling logic just on a time delta rather than a length.

cs95
  • 379,657
  • 97
  • 704
  • 746
ThatQuantDude
  • 759
  • 1
  • 9
  • 26
  • so instead of specifying a `window_len = some_integer`, you'd want something like `window_len = '28D'` ? – Uvar Sep 18 '17 at 15:11
  • Yes, instead of having it run on the number of data points, I would like it to roll over a time window, irrespective of the number of data points I have in that time period. Example 4 weeks should equal 28 days, 20 working days but if I have a public holiday I might end up with 19 entries. I am looking for a robust solution. – ThatQuantDude Sep 18 '17 at 15:15
  • I know what you mean, but did you try running it with `28D`? It should produce a rolling window based on 4 weeks worth of time in your index. That also includes robustness to missing entries due to holidays. As for the date info, it was present from the start, I think you'll need to specify "as_index=False" explicitly to retain the dates though. – Uvar Sep 18 '17 at 15:18
  • Oh ok, did know about that one, will try it. But where does my current code get the date information from? I am not referencing date in the function, the window_len accesses the return series. – ThatQuantDude Sep 18 '17 at 15:18
  • can you try `returns.groupby('TICKER', as_index=False)['RETURN_DATA'].rolling(window='28D').apply(lambda x: np.prod(1+x)-1).T` ? – Uvar Sep 18 '17 at 15:32
  • Does this work for you I get a syntax error, 'ValueError: window must be an integer' – ThatQuantDude Sep 18 '17 at 16:07
  • If I copy-paste I actually also get a syntax error because the lambda is no longer recognized as lambda. Retyping that part works for me. – Uvar Sep 18 '17 at 16:14
  • Got it working, now get the valueerror – ThatQuantDude Sep 18 '17 at 16:14

1 Answers1

0

As mentioned by @Uvar, pandas dataframe supports an offset in window declaration. You need to create the dataframe and convert the index into datetime format. Then use the rolling function

a
        DATE TICKER  RETURN_DATA
0 2010-01-01    xxx         0.05
1 2010-01-01    yyy         0.01
2 2010-01-02    xxx         0.02
3 2010-01-02    yyy         0.08
5 2010-01-29    xxx         0.11
6 2010-01-29    yyy         0.01

a.DATE = pd.to_datetime(a.DATE)
a.set_index('DATE', inplace=True)

a.rolling('2D').mean() 

     TICKER  RETURN_DATA
DATE                          
2010-01-01    xxx     0.050000
2010-01-01    yyy     0.030000
2010-01-02    xxx     0.026667
2010-01-02    yyy     0.040000
2010-01-29    xxx     0.110000
2010-01-29    yyy     0.060000
chrisckwong821
  • 1,133
  • 12
  • 24