Formula that calculates year real variation in pandas?

user_19088124 0
Viewed 45

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

pandas python
2 Answers

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_129368810

Here is a completely vectorized solution using pure pandas (in other words: it is fast).

It is relatively straightforward if you have a DataFrame with the proper index. Also, your value "INFLATION" is in undefined units. In order to match your example, I have to divide it by 10 (so it is neither fraction nor percentage).

Step 1: sample data as reproducible example

df = pd.DataFrame({
    'TIME': ['2022-01', '2022-02', '2022-03', '2022-04', '2022-05', '2022-06', '2022-07', '2022-08',
             '2022-09', '2022-10', '2022-11', '2023-01', '2023-02', '2023-03', '2023-04'],
    'A': [100, 120, 150, 140, 180, 200, 100, 120, 150, 140, 180, 200, 100, 120, 150],
    'INFLATION': [0.3, 0.5, 0.4, 0.7, 0.6, 0.3, 0.5, 0.4, 0.7, 0.6, 0.3, 0.5, 0.8, 0.4, 0.1],
})

Step 2: Your calculation

  1. Convert the time column into a PeriodIndex
df = df.assign(TIME=df['TIME'].apply(pd.Period)).set_index('TIME')
  1. Solution
ci = (1 + df['INFLATION']/10).cumprod()  # cumulative inflation
ci12 = ci / ci.shift(12, 'M')  # 12-month variation
x = df['A'].shift(12, 'M') * ci12
real_var = (df['A'] - x) / x  # your "real variation"

# putting it all together in a new df
res = df.assign(ci12=ci12, x=x, real_var=real_var)
>>> res
           A  INFLATION      ci12           x  real_var
TIME                                                   
2022-01  100        0.3       NaN         NaN       NaN
2022-02  120        0.5       NaN         NaN       NaN
2022-03  150        0.4       NaN         NaN       NaN
2022-04  140        0.7       NaN         NaN       NaN
2022-05  180        0.6       NaN         NaN       NaN
2022-06  200        0.3       NaN         NaN       NaN
2022-07  100        0.5       NaN         NaN       NaN
2022-08  120        0.4       NaN         NaN       NaN
2022-09  150        0.7       NaN         NaN       NaN
2022-10  140        0.6       NaN         NaN       NaN
2022-11  180        0.3       NaN         NaN       NaN
2023-01  200        0.5  1.708788  170.878849  0.170420
2023-02  100        0.8  1.757611  210.913323 -0.525872
2023-03  120        0.4  1.757611  263.641653 -0.544837
2023-04  150        0.1  1.659053  232.267475 -0.354193
user_7581740
Related