[FIXED] Calculate Return on average asset for a company

Issue

I need to calculate return on average asset for company, how do i do this in a pandas dataframe that looks like this?

data = {2012: {'Total Asset': 1000000, 'Net Income': 100000},
 2013: {'Total Asset': 2000000, 'Net Income': 300000},
 2014: {'Total Asset': 3000000, 'Net Income': 350000},
 2015: {'Total Asset': 4000000, 'Net Income': 260000},
 2016: {'Total Asset': 3000000, 'Net Income': 300000}}

df = pd.DataFrame(data)
print(df)

                2012     2013     2014     2015     2016
Total Asset  1000000  2000000  3000000  4000000  3000000
Net Income    100000   300000   350000   260000   300000

I intend to achieve the following:

• (2013 net income / average of 2012 and 2013 total assets),
• (2014 net income / average of 2013 and 2014 total assets), etc.

Solution

Use df.rolling:

df = pd.DataFrame(data)

df.loc['Returns'] = df.loc['Net Income']/df.loc['Total Asset'].rolling(2).mean()
print(df)

                  2012       2013        2014          2015          2016
Total Asset  1000000.0  2000000.0  3000000.00  4.000000e+06  3.000000e+06
Net Income    100000.0   300000.0   350000.00  2.600000e+05  3.000000e+05
Returns            NaN        0.2        0.14  7.428571e-02  8.571429e-02

It would perhaps make more sense to transpose your df:

df_t = pd.DataFrame(data).T
df_t['Returns'] = df_t['Net Income']/df_t['Total Asset'].rolling(2).mean()

print(df_t)

      Total Asset  Net Income   Returns
2012      1000000      100000       NaN
2013      2000000      300000  0.200000
2014      3000000      350000  0.140000
2015      4000000      260000  0.074286
2016      3000000      300000  0.085714

Answered By - ouroboros1