CODEWITHSUNDEEP
python-3.xpandas
Kumar P
Kumar P

Reputation: 300

Year On Year Growth Using Pandas - Traverse N rows Back

I have a lot of parameters on which I have to calculate the year on year growth. 

Type                    2006-Q1 2006-Q2 2006-Q3 2006-Q4 2007-Q1 2007-Q2 2007-Q3 2007-Q4 2008-Q1 2008-Q2 2008-Q3 2008-Q4
MonMkt_IntRt            3.44    3.60    3.99    4.40    4.61    4.73    5.11    4.97    4.92    4.89    5.29    4.51
RtlVol                  97.08   97.94   98.25   99.15   99.63   100.29  100.71  101.18  102.04  101.56  101.05  99.49
IntRt                   4.44    5.60    6.99    7.40    8.61    9.73    9.11    9.97    9.92    9.89    7.29    9.51
GMR                     9.08    9.94    9.25    9.15    9.63    10.29   10.71   10.18   10.04   10.56   10.05   9.49

I need to calculate the growth, i.e in column 2007-Q1 i need to find the growth from 2006-Q1. The formula is (2007-Q1/2006-Q1) - 1

I have gone through the link below and tried to code Calculating year over year growth by group in Pandas

df = pd.read_csv('c:/Econometric/EconoModel.csv')
df.set_index('Type',inplace=True)
df.sort_index(axis=1, inplace=True)
df_t = df.T
df_output=(df_cd_americas_t/df_cd_americas_t.shift(4)) -1

The output is as below

Type                    2006-Q1 2006-Q2 2006-Q3 2006-Q4 2007-Q1 2007-Q2 2007-Q3 2007-Q4 2008-Q1 2008-Q2 2008-Q3 2008-Q4
MonMkt_IntRt                                            0.3398  0.3159  0.2806  0.1285  0.0661  0.0340  0.0363  -0.0912
RtlVol                                                  0.0261  0.0240  0.0249  0.0204  0.0242  0.0126  0.0033  -0.0166
IntRt                                                   0.6666  0.5375  0.3919  0.2310  0.1579  0.0195  0.0856  -0.2688
GMR                                                     0.0077  -0.031  0.1124  0.1704  0.0571  -0.024  -0.014  -0.0127

Upvotes: 1

Views: 110

Answers (2)

Chinmay
Chinmay

Reputation: 930

I could not find any issue with your code.
Simply added axis=1 to the dataframe.shift() method as you are trying to do the column comparison
I have executed the following code it is giving the result you expected.

def getSampleDataframe():
    df_economy_model = pd.DataFrame(
        {
        'Type':['MonMkt_IntRt', 'RtlVol', 'IntRt', 'GMR'],
        '2006-Q1':[3.44, 97.08, 4.44, 9.08],
        '2006-Q2':[3.6, 97.94, 5.6, 9.94],
        '2006-Q3':[3.99, 98.25, 6.99, 9.25],
        '2006-Q4':[4.4, 99.15, 7.4, 9.15],
        '2007-Q1':[4.61, 99.63, 8.61, 9.63],
        '2007-Q2':[4.73, 100.29, 9.73, 10.29],
        '2007-Q3':[5.11, 100.71, 9.11, 10.71],
        '2007-Q4':[4.97, 101.18, 9.97, 10.18],
        '2008-Q1':[4.92, 102.04, 9.92, 10.04],
        '2008-Q2':[4.89, 101.56, 9.89, 10.56],
        '2008-Q3':[5.29, 101.05, 7.29, 10.05],
        '2008-Q4':[4.51, 99.49, 9.51, 9.49]
        })  # Your data
    return df_economy_model> 

df_cd_americas = getSampleDataframe()
df_cd_americas.set_index('Type', inplace=True)
df_yearly_growth = (df/df.shift(4, axis=1))-1
print (df_cd_americas)
print (df_yearly_growth)

Upvotes: 1

AT_asks
AT_asks

Reputation: 132

Use iloc to shift data slices. See an example on test df.

df= pd.DataFrame({i:[0+i,1+i,2+i] for i in range(0,12)})
print(df)
    0   1   2   3   4   5   6   7   8   9  10  11
0   0   1   2   3   4   5   6   7   8   9  10  11
1   1   2   3   4   5   6   7   8   9  10  11  12
2   2   3   4   5   6   7   8   9  10  11  12  13


df.iloc[:,list(range(3,12))] = df.iloc[:,list(range(3,12))].values/ df.iloc[:,list(range(0,9))].values - 1
print(df)
    0   1   2  3    4    5     6     7     8         9         10   
0   0   1   2  inf  3.0  1.50  1.00  0.75  0.600000  0.500000  0.428571   
1   1   2   3  3.0  1.5  1.00  0.75  0.60  0.500000  0.428571  0.375000   
2   2   3   4  1.5  1.0  0.75  0.60  0.50  0.428571  0.375000  0.333333   

   11  
0  0.375000  
1  0.333333  
2  0.300000

Upvotes: 1

Related Questions