I have two DataFrames; returns and weights and I try to combine them to a floating_weights DataFrame. The idea behind this is that I want to dynamically adjust the weights in period t with the return in period t-1. So the weights increase if there are positive returns and vice versa.
Here I created a simple example:
weights:
Dates 01K W 02K W 03K W 04K W
0 2021-01-01 0.0 0.2 0.3 0.5
1 2021-01-02 0.0 0.2 0.3 0.5
2 2021-01-03 0.5 0.2 0.3 0.0
3 2021-01-04 0.5 0.2 0.3 0.0
4 2021-01-05 0.5 0.0 0.2 0.3
5 2021-01-06 0.5 0.0 0.2 0.3
returns:
Dates 01K W 02K W 03K W 04K W
0 2021-01-01 0.01 0.01 -0.03 0.05
1 2021-01-02 -0.02 0.02 0.04 -0.02
2 2021-01-03 0.03 -0.03 0.01 -0.02
3 2021-01-04 -0.03 0.01 0.02 0.01
4 2021-01-05 0.02 0.02 0.01 0.01
5 2021-01-06 0.01 -0.01 0.03 0.02
The floating_weights DataFrame is based on the normal weights adjusted with the returns from the previous period:
floating_weights (2021-01-01, 02K W): 0.2 (start with normal weight)
floating_weights (2021-01-02, 02K W): 0.202 = 0.2 * (1+0.01)
floating_weights (2021-01-03, 02K W): 0.206 = 0.2 * (1+0.01) * (1+0.02)
floating_weights (2021-01-04, 02K W): 0.19986 = 0.2 * (1+0.01) * (1+0.02) * (1-0.03)
The floating_weights would look like this.
Dates 01K W 02K W 03K W 04K W
0 2021-01-01 0.0000 0.20000 0.30000 0.500
1 2021-01-02 0.0000 0.20200 0.29100 0.525
2 2021-01-03 0.5000 0.20604 0.30264 0.000
3 2021-01-04 0.5150 0.19986 0.30567 0.000
4 2021-01-05 0.4995 0.00000 0.20785 0.300
5 2021-01-06 0.5095 0.00000 0.20993 0.303
For reproducibility:
import pandas as pd
returns = pd.DataFrame({
'Dates':['2021-01-01', '2021-01-02', '2021-01-03', '2021-01-04', '2021-01-05', '2021-01-06'],
'01K W':[0.01, -0.2, 0.03, -0.03, 0.02, 0.01],
'02K W':[0.01, 0.02, -0.03, 0.01, 0.02, -0.01],
'03K W':[-0.03, 0.04, 0.01, 0.02, 0.01, 0.03],
'04K W':[0.05, -0.02, -0.02, 0.01, 0.01, 0.02]})
returns = returns.set_index('Dates')
weights = pd.DataFrame({
'Dates':['2021-01-01', '2021-01-02', '2021-01-03', '2021-01-04', '2021-01-05', '2021-01-06'],
'01K W':[0, 0, 0.5, 0.5, 0.5, 0.5],
'02K W':[0.2, 0.2, 0.2, 0.2, 0, 0],
'03K W':[0.3, 0.3, 0.3, 0.3, 0.2, 0.2],
'04K W':[0.5, 0.5, 0, 0, 0.3, 0.3]})
weights = weights.set_index('Dates')
Thank you very much for the help!
We can use cumprod to calculate the cumulative returns, then shift and multiply the cumulative returns with the weights dataframe to get the desired result
r = returns.add(1).cumprod().shift()
floating_weights = weights.mul(r, fill_value=1)
If you want to reset the cumprod every time the weight is assigned to zero, in such case we have to consider each column separately
floating_weights = weights.copy()
for col in weights:
g = weights[col].eq(0).cumsum()
r = returns[col].add(1).groupby(g).cumprod()
floating_weights[col] = weights[col].mul(r.shift(1), fill_value=1)
>>> floating_weights
01K W 02K W 03K W 04K W
Dates
2021-01-01 0.000000 0.200000 0.300000 0.500
2021-01-02 0.000000 0.202000 0.291000 0.525
2021-01-03 0.500000 0.206040 0.302640 0.000
2021-01-04 0.515000 0.199859 0.305666 0.000
2021-01-05 0.499550 0.000000 0.207853 0.300
2021-01-06 0.509541 0.000000 0.209932 0.303