Calculate value based on previous value and multiplication

Question

I am trying to do something which is very simple in excel, but I cant seem to find the way the way to do it in python. I want to calculate the next value in a dataframe, based on the current value + percentage, similar to the excel version:

Answer 1

You can use cumprod for this type of operations. Add a shift and fillna to make the first value remains unchanged:

df = pd.DataFrame({"Date": pd.date_range("1-1-1980", "1-12-1980", freq="D")})
df["Value"] = 105 # or df["Value].ffill() for your current df
df["mul"] = 1+1/12
df["Value"] *= df.pop("mul").cumprod().shift(1).fillna(1)
print (df)

Date       Value
0  1980-01-01  105.000000
1  1980-01-02  113.750000
2  1980-01-03  123.229167
3  1980-01-04  133.498264
4  1980-01-05  144.623119
5  1980-01-06  156.675046
6  1980-01-07  169.731300
7  1980-01-08  183.875575
8  1980-01-09  199.198539
9  1980-01-10  215.798417
10 1980-01-11  233.781619
11 1980-01-12  253.263420

Answer 2

I was surprised there are not tons of answers about such a question in the internet. Not the most elegant way but you could do it like this:

I created a little example df (for the future: you should copy your data from your editor and paste it to your question as text, not as picture)

df = pd.DataFrame(
    {"Date": [1, 2, 3, 4, 5], "Value": [105, np.nan, np.nan, np.nan, np.nan]}
)
df["factor"] = 1 + 1 / 12
print(df)

   Date  Value    factor
0     1  105.0  1.083333
1     2    NaN  1.083333
2     3    NaN  1.083333
3     4    NaN  1.083333
4     5    NaN  1.083333

start_value = df["Value"][0]
df["Value"] = df["factor"].cumprod() * start_value
df = df.drop("factor", axis=1)

print(df)

   Date       Value
0     1  113.750000
1     2  123.229167
2     3  133.498264
3     4  144.623119
4     5  156.675046

Answer 3

Yeap.

It is simple.

After importing pandas, and converting your excel in a dataframe you only need one more step:

your_dataframe[new_column_name]=your_dataframe[value]*(1+1/12)

Saying “thanks” is appreciated, but it doesn’t answer the question. Instead, vote up the answers that helped you the most! If these answers were helpful to you, please consider saying thank you in a more constructive way – by contributing your own answers to questions your peers have asked here.