I have a Pandas DataFrame consisting of asset price returns and want to create a new DataFrame that will convert those returns into price series. How do I do that?
The original returns dataframe is:
0 1 2
0 0.01 0.02 0.04
1 0.02 0.05 0.04
I want to index the original price for all series at 100.00 so that I get the following dataframe:
0 1 2
0 100.00 100.00 100.00
1 101.00 102.00 104.00
2 103.02 107.10 108.16
The easiest way would be to calculate the interest relative to the starting value. This is done easily using cumprod():
df = pd.DataFrame({0: [0.01, 0.02], 1: [0.02,0.05], 2:[0.04, 0.04]})
relative_returns = (df+1).cumprod()
# 0 1 2
# 0 1.0100 1.020 1.0400
# 1 1.0302 1.071 1.0816
To calculate the final value, we can multiply by the initial value:
final_value = 100*relative_returns
# 0 1 2
# 0 101.00 102.0 104.00
# 1 103.02 107.1 108.16
Rearranging the table will result in the exact form you require.
The rearranging can be done before the process above as follows:
df = df.append(
pd.Series(np.nan, index=df.columns, name=df.index.max()+1)
).shift().fillna(0.0)
# 0 1 2
# 0 0.00 0.00 0.00
# 1 0.01 0.02 0.04
# 2 0.02 0.05 0.04