How to do a recursive calculation in a pandas DataFrame?

Question

I have a DataFrame with the buy and sell operations resulting from a quant investing algorithm. Then I tried to do a simulation with an initial value invested, in order to calculate the resulting capital after each of the operations.

For that, I created a new column called 'money' where I try to do a recursive calculation. Where the current value for the 'money' column is the previous value multiplied by the profit/loss percentage of the operation.

ops_df['money'] = list( repeat(TOTAL_INVESTED, len(ops_df)) )
ops_df['money'] = (1 + ops_df.profit_perc.shift(1)) * ops_df.money.shift(1)
ops_df.head(10)

However, the expected recursive calculation did not occur. I'm suspicious because the right hand side of the attribution is calculated in full before the attribution.

I managed to solve this calculation using common loop, but I kept that in my head. There must be a better, more performative way of doing this calculation. I would like to know if there is, what would it be like?

TOTAL_INVESTED = 1000
money = [TOTAL_INVESTED, ]

for i in range(1, len(ops_df)):
    curr_money = round( money[i-1] * (1 + ops_df.profit_perc.iloc[i]), 2 )
    money.append(curr_money)

ops_df['money'] = money

Data:

{'profit_perc': [-0.039548, 0.490518, 0.127511, -0.019439]}

Answer 1

You could use cumprod. The idea is to find the yield in each row and multiply it by the initial investment:

TOTAL_INVESTED = 1000
df = pd.DataFrame({'profit_perc': [-0.039548, 0.490518, 0.127511, -0.019439]})
df['money'] = df['profit_perc'].shift(-1).add(1).cumprod().mul(TOTAL_INVESTED).shift().fillna(TOTAL_INVESTED)

Output:

   profit_perc        money
0    -0.039548  1000.000000
1     0.490518  1490.518000
2     0.127511  1680.575441
3    -0.019439  1647.906735