I have a dataframe with this info:
I need to find a formula that calculates, for each of the 4 months of 2023, the real variation of column A against the same months of 2022. For example, in the case of 2023-04, the calculation is
x = 140 (value of 2022-04) * 1,66 (accumulated inflation from 2022-04 to 2023-04)
x= 232,27
Real variation 2023-04= (150 (value of 2023-04) – x)/x
Real variation 2023-04 = -0,35
The value 1,66, that is the accumulated inflation from 2022-04 to 2023-04, comes from this calculation: starting from the number 1 in 2022-04, for every month until 2023-04, apply the formula = previous row value*(1+inflation column value). For example, in the case 2023-04 the value 1,66 is the the last one of the calculation (the accumulated inflation of the 12 months) 1 1,06 1,09 1,15 1,19 1,28 1,35 1,39 1,46 1,58 1,64 1,66.
Thanks
Advertisement
Answer
your data is realy bad. You have missing values. ColumnB is in [%] I think.
here is my suggestion
Dataframe:
Time columnA columnB 0 2022-01-31 100 0.3 1 2022-02-28 120 0.5 2 2022-03-31 150 0.4 3 2022-04-30 140 0.7
Code of your calculations
df['vals'] = np.nan df.loc[3, 'vals'] = 1 k = 1 arr = [] for i in df['columnB'].loc[4:].values: k = k*(1+i/10) arr.append(k) df.loc[4:, 'vals'] = arr df['Month'] = df['Time'].dt.month df['Year'] = df['Time'].dt.year year = 2023 for month in range(1,13): v1 = df['vals'].loc[(df['Month'] == month)&(df['Year'] == year)].values[0] v2 = df['columnA'].loc[(df['Month'] == month)&(df['Year'] == year-1)].values[0] x = v1['vals']*v2 print(f'{year}-{month}', (v1['columnA']-x)/x)
Output would be:
2023-4 -0.354193
The code could be perhabs optimized, but I am not sure, if your input is correct.
cheers