Calculation of floating weights based on weight and return DataFrame

Question

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!

Shubham Sharma · Accepted Answer · 2021-12-12T09:48:59.133

3

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

edited Dec 12 '21 at 09:48

answered Dec 12 '21 at 09:05

Shubham Sharma

68,127
6
24
53

1

This is a good solution! +1 from me – nikeros Dec 12 '21 at 09:10
Thanks a lot for your solution! Is there an easy way to make sure that we always start with the initial weight also in column "01K W"? So that the cumprod() starts when we get a weight assigned and ends when the weight is zero again. – fjurt Dec 12 '21 at 09:12
@fjurt Not sure if I understand, Can you please elaborate? – Shubham Sharma Dec 12 '21 at 09:16
@Shubham Sharma yes sure, so for "01K W" on "2021-01-03" you get a weight of 0.505 which should still be 0.5 for that day as we only start to invest that weight on that day, without having a return on it. For the following days your solution perfectly works until I assign the weight equal to zero again for example for "04K W". In case I reassign a weight for "04K W" in a following day (not in the sample dataset), I would expect to start with cumprod() at one again. I hope that makes sense. – fjurt Dec 12 '21 at 09:26
@Shubham Sharma I added two more dates to make it more clear. – fjurt Dec 12 '21 at 09:47
@Shubham Sharma thank you very much! Thats exactly what I was trying to achieve:) – fjurt Dec 12 '21 at 09:55
1

@fjurt happy coding! Can you please edit your question to make the `Dates` column as default index in both the dataframes? – Shubham Sharma Dec 12 '21 at 09:59
@ShubhamSharma I just realized with the data, that the first weights (0.5 for 2021-01-03/01K W and 0.3 for 2021-01-05/04K W were coincidentally the same as the initial weights of 0.5 and 0.3 because both of the returns one day before were 0. When these returns are not zero anymore, then the first weights are not the same as the initial weights. I changed it in my question (2021-01-02/01K W = -0.02 and 2021-01-04/04K W = 0.01 instead of 0). Would you know how to deal with that change? Thanks a lot! – fjurt Dec 14 '21 at 16:05

Calculation of floating weights based on weight and return DataFrame

1 Answers1