Python: calculate rolling returns over different frequencies-CodePudding

I have the following DataFrame:

data = [[99330,12,122],[1123,1230,1287],[123,101,812739],[1143,1230123,252],[234,342,4546],[2445,3453,3457],[7897,8657,5675],[46,5675,453],[76,484,3735],[363,93,4568],[385,568,367],[458,846,4847],[574,45747,658468],[57457,46534,4675]]
df1 = pd.DataFrame(data, index=['2022-01-01', '2022-01-02', '2022-01-03', '2022-01-04',
                       '2022-01-05', '2022-01-06', '2022-01-07', '2022-01-08',
                       '2022-01-09', '2022-01-10', '2022-01-11', '2022-01-12',
                       '2022-01-13', '2022-01-14'], 
          columns=['col_A', 'col_B', 'col_C'])
df1.index = pd.to_datetime(df1.index)
df1: 
            col_A   col_B   col_C
2022-01-01  99330   12      122
2022-01-02  1123    1230    1287
2022-01-03  123     101     812739
2022-01-04  1143    1230123 252
2022-01-05  234     342     4546
2022-01-06  2445    3453    3457
2022-01-07  7897    8657    5675
2022-01-08  46      5675    453
2022-01-09  76      484     3735
2022-01-10  363     93      4568
2022-01-11  385     568     367
2022-01-12  458     846     4847
2022-01-13  574     45747   658468
2022-01-14  57457   46534   4675

I am applying the following to get rolling returns:

periodicity_dict = {1:'daily', 7:'weekly'}
df_columns = df1.columns
for key in periodicity_dict:
    for col in df_columns:
        df1[col '_rolling']= np.nan
        for i in range(key, len(df1[col][df1[col].first_valid_index():df1[col].last_valid_index()])):
            df1[col '_rolling'].iloc[i] = (df1[col].iloc[i] - df[col].iloc[i-key])/df[col].iloc[i-key]

But I am getting the following error: KeyError: 'col_A'.

What am I doing wrong? And is there a better way to do this with less loops?

CodePudding user response：

I think you are looking for something like the shift method (no for-loop is needed):

df1['col_A_rolling'] = (df1['col_A'] - df1['col_A'].shift(7)) / df1['col_A'].shift(7)

OUTPUT:

            col_A    col_B   col_C  col_A_rolling
2022-01-01  99330       12     122            NaN
2022-01-02   1123     1230    1287            NaN
2022-01-03    123      101  812739            NaN
2022-01-04   1143  1230123     252            NaN
2022-01-05    234      342    4546            NaN
2022-01-06   2445     3453    3457            NaN
2022-01-07   7897     8657    5675            NaN
2022-01-08     46     5675     453      -0.999537
2022-01-09     76      484    3735      -0.932324
2022-01-10    363       93    4568       1.951220
2022-01-11    385      568     367      -0.663167
2022-01-12    458      846    4847       0.957265
2022-01-13    574    45747  658468      -0.765235
2022-01-14  57457    46534    4675       6.275801