Skip to content
Advertisement

Formula that calculates year real variation in pandas?

I have a dataframe with this info:

DATA

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

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement