I have this dataframe:
date amount
2018/01 100
2018/02 105
2018/03 110.25
2018/04 200
As you can see, every month, the amount is increasing by 5% of the previous value. However, every the 4th month (2018/04), this rule does not apply. Instead, it should only past the constant value of 200 for example.
How do I program this in pandas dataframe?
@Lroy_12374 It's not clear what would happen in month's 5-8 and beyond, which would affect how to write the logic. For example: a) Should month 5 be 5% higher than month 3? OR b) should it be 5% higher than every fourth month (i.e. April 2018, August 2018, December 2018, April 2019, August 2019, December 2019, etc.)? OR c) Should it be 5% higher than Month 4 had month 4 not been a constant, which means that Month 5 is 1.05^2*(Month 3).
Also, the definition of a constant is not clear. Literally, will it be 200 or something for every fourth month? Or, will it be a different number that does not follow the pattern of the other 3 months.
I have written some code for scenario c) above:
import pandas as pd
import numpy as np
df = pd.DataFrame({'date' : ['2018/01','2018/02','2018/03',
'2018/04','2018/05','2018/06','2018/07', '2018/08']})
start_amount = 100
constant=200
growth=.05
df['amount'] = np.where((df.index+1)%4 != 0,
start_amount * (1+growth) ** df.index, constant)
df
The key here is to use np.where and implement logic based on the row number, which you can get with df.index. What I am doing in the code above is adding 1 to the row (df.index+1), since python starts counting at 0 and you want logic based on the fourth month. Then, I am using the % symbol, which returns the remainder after dividing, which you want to equal zero if it is the fourth row (i.e. 4/4 = remainder 0). So, basically, where something is not every fourth row you want to multiply by 1.05 (5% increase) RAISED according to the row number, and where it is the fourth row you want to return a constant.
I hope this helps.