I have a table like below, using this dummy data:

data = [['Jane', 10,10.5,11,13,45,41,66,21,88,99,77,84,66,8,77,22,11,44,69,85,36,4,
         87,74,56,88,23,6,9,8,55,12,4,58,36,44,89,81,7,98,52,11,45,87,96,32,58,76],
        ['John',11,22,55,23,6,9,8,41,12,4,58,66,99,36,44,89,81,7,98,52,33,11,45,87,
         96,32,58,55,12,4,58,13,36,52,89,81,7,98,34,44,11,25,22,44,55,88,56,47],
        ['Tom',23,32,43,12,10,10.5,11,46,45,45,46,66,21,88,99,77,8,77,22,11,44,69,8511,44,77,85,99,45,63,
        89,81,7,98,3,52,89,81,7,98,13,58,36,44,89,81,7,98,52]]

df = pd.DataFrame(data, columns = ['Name','January-19','February-19','March-19','April-19','May-19','June-19',
                                   'July-19','August-19', 'September-19', 'October-19','November-19', 'December-19',
                                   'January-20','February-20','March-20','April-20','May-20','June-20',
                                   'July-20','August-20', 'September-20', 'October-20','November-20','December-20',
                                   'January-21','February-21','March-21','April-21','May-21','June-21',
                                   'July-21','August-21', 'September-21', 'October-21','November-21',
                                   'December-21','Q1 2019',
                                   'Q2 2019', 'Q3 2019', 'Q4 2019','Q1 2020',
                                   'Q2 2020', 'Q3 2020', 'Q4 2020','Q1 2021',
                                   'Q2 2021', 'Q3 2021', 'Q4 2021'])

This results in the follow dataframe:

Each entry indicates a growth rate.

I need to calculate 2Y and 3Y stacked growth rates using a specific formula for each month and quarter in 2021.

Formula example:

3Y Jan 21 = ((1 Jan 2021 rate) * (1 Jan 2020 rate) * (1 Jan 2019 rate)-1

2Y Jan 21 = ((1 Jan 2021 rate) * (1 Jan 2020 rate))-1

I had initially done the following, which was to transpose the data and try to use the percentage change method in Pandas, indicating a time period of 24 months for the 2 year stacked column:

test1 = df.T
new_header = test1.iloc[0] #grab the first row for the header
test1 = test1[1:] #take the data less the header row
test1.columns = new_header #
test1 = test1.reset_index()
test1.rename(columns={'index': 'Date'}, inplace = True)
test1['Jane_2Y_Growth'] = test1['Jane'].pct_change(24)
test1.tail(15)

However I don't think its correct because I get a different answer when I manually do the formula.

Does anyone know how to apply this formula to all of my periods in 2021? 12 months and 4 quarters.

So, the 2Y stacked growth column for Q4 2021 for example, would use the Q4 figures from 2021 and 2020.

Thanks

CodePudding user response：

Based on the formula example you have given :

3Y Jan 21 = ((1 Jan 2021 rate) * (1 Jan 2020 rate) * (1 Jan 2019 rate)-1
2Y Jan 21 = ((1 Jan 2021 rate) * (1 Jan 2020 rate))-1

Transposing and filtering values of interest from dataframe

NDF = df.T
NDF, NDF.columns = NDF[1:] , NDF.iloc[0]
result_df = NDF.loc[[x for x in NDF.index if '21' in x ]]

Creating placeholder columns for the resultant dataframe

for col in NDF.columns:
    result_df[col   '_2Y Growth'] = 0
    result_df[col   '_3Y Growth'] = 0

The above gives us

result_df
Name         Jane John Tom  ...  John_3Y Growth  Tom_2Y Growth  Tom_3Y Growth
January-21     56   96  77  ...               0              0              0
February-21    88   32  85  ...               0              0              0
March-21       23   58  99  ...               0              0              0
April-21        6   55  45  ...               0              0              0
May-21          9   12  63  ...               0              0              0
June-21         8    4  89  ...               0              0              0
July-21        55   58  81  ...               0              0              0
August-21      12   13   7  ...               0              0              0
September-21    4   36  98  ...               0              0              0
October-21     58   52   3  ...               0              0              0
November-21    36   89  52  ...               0              0              0
December-21    44   81  89  ...               0              0              0
Q1 2021        96   55  81  ...               0              0              0
Q2 2021        32   88   7  ...               0              0              0
Q3 2021        58   56  98  ...               0              0              0
Q4 2021        76   47  52  ...               0              0              0

Applying the formula :

for idx in NDF.index:
    if('21' in idx):
        idx20 = idx[:-2]   '20'
        idx19 = idx[:-2]   '19'
        for col in NDF.columns:
            result_df.loc[idx, col   '_3Y Growth'] = (1   NDF.loc[idx, col]) * (1   NDF.loc[idx20, col]) * (1   NDF.loc[idx19, col]) - 1
            result_df.loc[idx, col   '_2Y Growth'] = (1   NDF.loc[idx, col]) * (1   NDF.loc[idx20, col]) - 1

Output :

This gives us the expected output based on the formula

EDIT : As OP asked,

Is there a way to add the new columns to the bottom instead and instead of labelling them 'Jane_2Y Growth' for example, label then 'January-21 2Y growth'. This way they are added to the index and do not have any names in them, just the date and the growth period.

Instead of adding the result to results dataframe we can add it to NDF datafrmae in the appropriate cell. The result would be stored in NDF

for idx in NDF.index:
    if('21' in idx):
        idx20 = idx[:-2]   '20'
        idx19 = idx[:-2]   '19'
        for col in NDF.columns:
            NDF.loc[idx   ' 3Y Growth', col] = (1   NDF.loc[idx, col]) * (1   NDF.loc[idx20, col]) * (1   NDF.loc[idx19, col]) - 1
            NDF.loc[idx   ' 2Y Growth', col] = (1   NDF.loc[idx, col]) * (1   NDF.loc[idx20, col]) - 1

This gives us the expected output :