Calculating monthly return from a dataframe that has cumulative return down the column

I have the following dataframe. How do I create a new column that has the monthly return?

Exhibit A:

Exhibit B:

Where row n is (n-1 / n)-1 for each change in time, stopping at the end of each scenario.

The end goal is to do this iteratively for cumulative indeces, then create a linearly combination from the individual returns, then RE roll to a new cumulative number.

I am doing this in pandas.

So far I have this code:

  for idx,wght, i in zip( indeces,fundweights , range(len(indeces)-1)):
        df_temp = pd.read_csv(filepath + file_names[idx],header=None)
        df_temp = df_temp.stack()
        df_temp.index.names=['Scen','TIME']
        df_temp= df_temp.to_frame(name='CumGrowth_idx')
        df_temp.reset_index(inplace=True)
        df_temp['Scen'] = df_temp['Scen'] + 1 
        df_temp[idx] = idx
        df_temp = df_temp.rename(columns={'TIME':'Month'})
        df_temp["Monthly_Return"]= 

I want to create the monthly return dataframe, then append these all to a dataframe, take a linear combination. Can anyone offer some input?

Thanks

While there is probably a much more elegant way to accomplish this with some of Pandas' merge and group methods, here is a method that will work.

First of all define a function to split the dataframe by Scenario Indexs, then update the cumReturn column using the shift function, finally concat the separate frames back into 1 frame as follows:

import pandas as pd

def computeDifference(df: pd.DataFrame)-> pd.DataFrame:
    scnslist = df['Scenario'].unique()
    rslt = df[df['Scenario'] == scnslist[0]].copy()
    rslt['CumReturn'] = (rslt['CumReturn'].shift(-1)/rslt['CumReturn'])-1
    for i in range(1, len(scnslist)):
        dx = df[df['Scenario'] == scnslist[i]].copy()
        dx['CumReturn'] = (dx['CumReturn'].shift(-1)/dx['CumReturn'])-1
        rslt = pd.concat([rslt, dx], axis=0, ignore_index=True)
    return rslt

When given the dataframe in Exhibit 1, defined as df
you can execute computeDifference(df) which yields:

	Scenario	TimeStep	CumReturn
0	1	0	0.050000
1	1	1	0.019048
2	1	2	NaN
3	2	0	0.040000
4	2	1	-0.019231
5	2	2	NaN

 df_temp = pd.read_csv(filepath + file_names_equity[idx],header=None)
        df_temp = df_temp.stack()
        df_temp.index.names=['Scen','TIME']
        df_temp= df_temp.to_frame(name='CumGrowth_idx')
        df_temp.reset_index(inplace=True)
        df_temp['Scen'] = df_temp['Scen'] + 1 
        df_temp[idx] = idx
        df_temp = df_temp.rename(columns={'TIME':'Month'})
        
        df_temp["Monthly_Return"]= (df_temp["CumGrowth_idx"].div(df_temp["CumGrowth_idx"].shift(1)) - 1).shift(-1)
        df_temp["Monthly_Return"] = np.where(df_temp['Month'] == 600,0, df_temp['Monthly_Return'] )