I have a dataframe that looks like the below. You can see that, to start, the SMA column is a duplicate of the Index Value column intentionally. I’m trying to adjust the SMA column so that it will be the result of a simple calculation, something to the effect of the below, which I believe would require a loop of some kind:
for i in range(len(df)):
df['SMA'].loc[i,"SMA"]= df['SMA'].loc[i-1,"SMA"] + df['SMA'].loc[i,"ER"] * (df['SMA'].loc[i,"Index Value"] - df['SMA'].loc[i-1,"SMA"])
In words, I’m attempting to take the current row of ‘SMA’ and have it be the output of the prior row of “SMA” + the current row of “ER” multiplied by the difference between the current row of “Index Value” and prior row of “SMA”
| Date | Index Value | ER | SMA |
|---|---|---|---|
| 9/5/2023 | 4496.83 | 0.15066 | 4496.83 |
| 9/6/2023 | 4465.48 | 0.157105 | 4465.48 |
| 9/7/2023 | 4451.14 | 0.218561 | 4451.14 |
| 9/8/2023 | 4457.49 | 0.233893 | 4457.49 |
| 9/11/2023 | 4487.46 | 0.233709 | 4487.46 |
| 9/12/2023 | 4461.9 | 0.191352 | 4461.9 |
| 9/13/2023 | 4467.44 | 0.090935 | 4467.44 |
| 9/14/2023 | 4505.1 | 0.398004 | 4505.1 |
| 9/15/2023 | 4450.32 | 0.127833 | 4450.32 |
| 9/18/2023 | 4453.53 | 0.062296 | 4453.53 |
| 9/19/2023 | 4443.95 | 0.198933 | 4443.95 |
| 9/20/2023 | 4402.2 | 0.382776 | 4402.2 |
| 9/21/2023 | 4330 | 0.494406 | 4330 |
| 9/22/2023 | 4320.06 | 0.541878 | 4320.06 |
| 9/25/2023 | 4337.44 | 0.44323 | 4337.44 |
| 9/26/2023 | 4273.53 | 0.489456 | 4273.53 |
| 9/27/2023 | 4274.51 | 0.466276 | 4274.51 |
| 9/28/2023 | 4299.7 | 0.396806 | 4299.7 |
| 9/29/2023 | 4288.05 | 0.52569 | 4288.05 |
In an effort to avoid a loop since I’m not great with loop construction, I’ve tried something like the below, which is close, but not exactly what I want.
df['SMA']=df['SMA'].shift(1) + df['ER'] * (df['Index Value'] - df['SMA'].shift(1))
The reason why it’s not exactly what I want is because I want the first number of the Index Value column to match the first value of SMA, and have the formula apply only after that point (which is why it seems a loop would be necessary).
All of that being said and given the goal, can someone please advise what the best way might be to accomplish this task?
1