1

I have the following dataframe containing financial data for a company which has its fiscal year from 1 March to the last day of February. The real dataframe covers multiple fiscal years.

date fcf debt_service cash_or_overdraft cash_generated
2017Q1 38 0 -36.0 14.5
2017Q2 -24 0 -101.4 -65.3
2017Q3 21 0 -92.8 8.5
2017Q4 89 -145 -115.0 15.3
2018Q1 17 -150 -140.4 -25.8
2018Q2 36 -130 -59.5 80.8
2018Q3 50 -162 -179.9 -120.4
2018Q4 29 -147 -142.0 -6.3

I want to aggregate the data for each fiscal year. I tried the following method. However, it seems to aggregate the data based on calendar year, not fiscal year. This throws out my totals, because the totals don't reconcile with the actual fiscal year.

debt_service = df2.groupby(df2['index'].dt.year)['debt_service'].agg(['sum', 'mean', 'max'])
Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Bandit King
  • 183
  • 10

1 Answers1

2

You can try extracting the year of the period object.

data = [ {"period" : pd.Period('2018Q1', freq='Q-FEB'), "value" : 1000},
        {"period" : pd.Period('2018Q2', freq='Q-FEB'), "value" : 1000},
        {"period" : pd.Period('2018Q3', freq='Q-FEB'), "value" : 1000},
        {"period" : pd.Period('2018Q4', freq='Q-FEB'), "value" : 1000},
        {"period" : pd.Period('2019Q1', freq='Q-FEB'), "value" : 2000},
        {"period" : pd.Period('2019Q2', freq='Q-FEB'), "value" : 2000},
        {"period" : pd.Period('2019Q3', freq='Q-FEB'), "value" : 2000},
        {"period" : pd.Period('2019Q4', freq='Q-FEB'), "value" : 2000}]

df = pd.DataFrame(data)

With list comprehension

df["fiscal_year"] = [x.qyear for x in df["period"].values]

Or with apply

def get_fiscal_year(row):
  row["fiscal_year"] = row["period"].qyear
  return row

df["fiscal_year"] = None
df = df.apply(get_fiscal_year, axis=1)

And then, make the groupby:

df.groupby('fiscal_year')['debt_service'].agg(['sum','mean','max'])
Ángel Igualada
  • 891
  • 9
  • 13