Home > Net >  calculate growth rates over columns by iteration in pandas
calculate growth rates over columns by iteration in pandas

Time:07-25

I have the following dataframe

#Example for Stackoverflow
df = pd.DataFrame({'Ticker': ['AAPL', 'MSFT', 'IBM'],
                   'totalRevenue_ttm': [386017000000, 192557000000, 50608000000],
                   'revenues_2021': [365817000000, 168088000000, 57351000000],
                   'revenues_2020': [274515000000, 143015000000, 55179000000],
                   'revenues_2019': [260174000000, 125843000000, 57714000000],
                   'revenues_2018': [265595000000, 110360000000, 79591000000]})

I would like to calculate the growth rates of sales for 2021, 2020 and 2019.

If I use the following code, I get the growth rate for 2021

col=2
df[str(revenues.columns[col])   "_Growth"]=(df.iloc[:, col]-revenues.iloc[:, col 1])/df.iloc[:, col 1]
df

The result looks like: enter image description here

How can I iterate over the columns, so I can calculate also revenues_2020_Growth and revenues_2019_Growth

thank your help, I very much appreciate it.

CodePudding user response:

You can use pct_change on columns:

out = (df.filter(regex='revenues_\d ').sort_index(axis=1, ascending=False)
         .pct_change(-1, axis=1).round(2)
         .iloc[:,1:].add_suffix('_Growth')
       )

output:

   revenues_2021_Growth  revenues_2020_Growth  revenues_2019_Growth
0                  0.33                  0.06                 -0.02
1                  0.18                  0.14                  0.14
2                  0.04                 -0.04                 -0.27

combining with input:

out = df.join(df
 .filter(regex='revenues_\d ').sort_index(axis=1, ascending=False)
 .pct_change(-1, axis=1).round(2)
 .iloc[:,:-1].add_suffix('_Growth')
)

output:

  Ticker  totalRevenue_ttm  revenues_2021  revenues_2020  revenues_2019  revenues_2018  revenues_2021_Growth  revenues_2020_Growth  revenues_2019_Growth
0   AAPL      386017000000   365817000000   274515000000   260174000000   265595000000                  0.33                  0.06                 -0.02
1   MSFT      192557000000   168088000000   143015000000   125843000000   110360000000                  0.18                  0.14                  0.14
2    IBM       50608000000    57351000000    55179000000    57714000000    79591000000                  0.04                 -0.04                 -0.27
  • Related