How to multiply a column by a shorter list of "weights", on a rolling basis, and then perform calculations on the output and store the value

user_13748857 0
Viewed 48

I have a set up like this - but with different numbers:

df1 =  {'Date': ['2020-01-06', '2020-01-07', '2020-01-08','2020-01-09', '2020-01-10', '2020-01-13','2020-01-14','2020-01-15','2020-01-16', '2020-01-17', '2020-01-20'],
        'Return': '0.02', '0.004','0.006', '0.001','0.005', '0.01','0.015', '0.001','0.0014',
'0.04', '0.037'}

weights = [1,2,3]

What I need to do is multiply the last 3 Returns by the weights column, sum them, then sqrt the answer - . Then store that against 2020-01-20. I then need to multiply the last 3 returns EXCEPT last line (so shifted 1 row) by weights,sum, sqrt and store answer against 2020-01-17 and so on. So my output column would be blank for the first 2 rows, then have 9 populated entries from 2020-01-08 onwards.

So I need to do the calc, shift the column 1 row, and then repeat, but saving the summed sqrt'd return each time.

I can calculate the one off (just for last 3 rows) correctly using :

df_last_3 = df.iloc[-3:].reset_index()
df_last_3['return*weights'] = df_last_3 * weights
sqrt_return= (np.sqrt((df_last_3['return*weights']).sum()))

But I then need to perform the same calculation on the shifted column rows - and store the result.

I'm new to Python and not practised enough with loops to really figure it out. I've had a go but didnt get the results I was after.

I've looked all round for examples of this and still can't get the solution. Any help would be appreciated.

pandas iteration python loops
1 Answers

Use, Series.rolling with a window size of 3, then use .apply to process the rolling window according to the requirements:

df1['Result'] = df1['Return'].rolling(3).apply(
    lambda s: np.sqrt((s * weights[::-1]).sum()))

# print(df1)
          Date  Return    Result
0   2020-01-06    0.02       NaN
1   2020-01-07   0.004       NaN
2   2020-01-08   0.006  0.272029
3   2020-01-09   0.001  0.158114
4   2020-01-10   0.005  0.158114
5   2020-01-13    0.01  0.151658
6   2020-01-14   0.015  0.223607
7   2020-01-15   0.001  0.246982
8   2020-01-16  0.0014  0.220000
9   2020-01-17    0.04  0.214009
10  2020-01-20   0.037  0.348138

# Calculations:
# 0.348138 = sqrt(1 * 0.037 + 2 * 0.04 + 3 * 0.0014)
# 0.214009 = sqrt(1 * 0.04 + 2 * 0.0014 + 3 * 0.001)
# ...
user_128331660
Related