I have a pandas multi indexed dataframe that I would like to compute a compound growth on. But some of the values in the column are negative. If the first or the last values are negative, then the compound formula can fail to compute. What is the best way to get a compound growth over, say 3,5,7,10 years, in a case like this? I am thinking maybe shift my start point away from any negative values to the first positive value, and adjust my endpoint from any negative number, to an average of the last n positive values, but I am open to other suggestions.
I am stumped on how actually to implement a solution. I just know I've heard iterating a dataframe is anti pattern and I should be able to accomplish this without having to resort to iteration.
import pandas as pd
from pandas import Timestamp
stocks =pd.DataFrame({
'Ticker': {83: 'AAPL', 84: 'AAPL', 85: 'AAPL', 86: 'AAPL', 87: 'AAPL', 88: 'AAPL', 89: 'AAPL', 90: 'AAPL', 91: 'AAPL', 92: 'AAPL', 93: 'AAPL', 94: 'AAPL', 95: 'AAPL', 96: 'AAPL', 97: 'AAPL', 98: 'AAPL', 99: 'AAPL', 100: 'AAPL', 101: 'AAPL', 102: 'AAPL', 103: 'AAPL'},
'Report Date': {83: Timestamp('2000-09-30 00:00:00'), 84: Timestamp('2001-09-30 00:00:00'), 85: Timestamp('2002-09-30 00:00:00'), 86: Timestamp('2003-09-30 00:00:00'), 87: Timestamp('2004-09-30 00:00:00'), 88: Timestamp('2005-09-30 00:00:00'), 89: Timestamp('2006-09-30 00:00:00'), 90: Timestamp('2007-09-30 00:00:00'), 91: Timestamp('2008-09-30 00:00:00'), 92: Timestamp('2009-09-30 00:00:00'), 93: Timestamp('2010-09-30 00:00:00'), 94: Timestamp('2011-09-30 00:00:00'), 95: Timestamp('2012-09-30 00:00:00'), 96: Timestamp('2013-09-30 00:00:00'), 97: Timestamp('2014-09-30 00:00:00'), 98: Timestamp('2015-09-30 00:00:00'), 99: Timestamp('2016-09-30 00:00:00'), 100: Timestamp('2017-09-30 00:00:00'), 101: Timestamp('2018-09-30 00:00:00'), 102: Timestamp('2019-09-30 00:00:00'), 103: Timestamp('2020-09-30 00:00:00')},
'Fiscal Year': {83: 2000, 84: 2001, 85: 2002, 86: 2003, 87: 2004, 88: 2005, 89: 2006, 90: 2007, 91: 2008, 92: 2009, 93: 2010, 94: 2011, 95: 2012, 96: 2013, 97: 2014, 98: 2015, 99: 2016, 100: 2017, 101: 2018, 102: 2019, 103: 2020},
'Earnings Per Share': {83: 0, 84: -1.031721589159224, 85: -3.6019417475728153, 86: 0.06343283582089554, 87: 2.856140350877193, 88: 3.991810737033667, 89: 0.4976303317535544, 90: 0.7576679649464462, 91: 0.716570874593172, 92: 0.3285731574488686, 93: 0.6708872290034611, 94: 0.8202220647295062, 95: 0.591763590076374, 96: -0.10342513437741085, 97: 0.1354399121364962, 98: 0.4294251740496582, 99: -0.10014223524848076, 100: 0.10974920488945084, 101: 0.2962851063278793, 102: -0.003962242162918184, 103: 0.10601547744404893},
})
_
Fiscal Year Earnings Per Share
Ticker Report Date
A ... ... ...
AAPL 2000-09-30 2000 <NA>
2001-09-30 2001 -1.031722
2002-09-30 2002 -3.601942
2003-09-30 2003 0.063433
2004-09-30 2004 2.85614
2005-09-30 2005 3.991811
2006-09-30 2006 0.49763
2007-09-30 2007 0.757668
2008-09-30 2008 0.716571
2009-09-30 2009 0.328573
2010-09-30 2010 0.670887
2011-09-30 2011 0.820222
2012-09-30 2012 0.591764
2013-09-30 2013 -0.103425
2014-09-30 2014 0.13544
2015-09-30 2015 0.429425
2016-09-30 2016 -0.100142
2017-09-30 2017 0.109749
2018-09-30 2018 0.296285
2019-09-30 2019 -0.003962
2020-09-30 2020 0.106015
AAWW 2010-12-31 2010 0.125432
... ... ...
This is what I would like the result to look like(with made up values):
Fiscal Year Earnings Per Share 10y Compounded Growth
Ticker Report Date
... ... ... ...
A 2020-12-31 2020 0.49253 0.132
AAPL 2000-09-30 2000 <NA> <NA>
2001-09-30 2001 -1.031722 <NA>
2002-09-30 2002 -3.601942 <NA>
2003-09-30 2003 0.063433 <NA>
2004-09-30 2004 2.85614 <NA>
2005-09-30 2005 3.991811 <NA>
2006-09-30 2006 0.49763 <NA>
2007-09-30 2007 0.757668 <NA>
2008-09-30 2008 0.716571 <NA>
2009-09-30 2009 0.328573 0.234
2010-09-30 2010 0.670887 0.234
2011-09-30 2011 0.820222 0.234
2012-09-30 2012 0.591764 0.234
2013-09-30 2013 -0.103425 0.234
2014-09-30 2014 0.13544 0.234
2015-09-30 2015 0.429425 0.234
2016-09-30 2016 -0.100142 0.234
2017-09-30 2017 0.109749 0.234
2018-09-30 2018 0.296285 0.234
2019-09-30 2019 -0.003962 0.234
2020-09-30 2020 0.106015 0.234
AAWW 2010-12-31 2010 0.125432 0.234
... ... ... ...
