Search code examples
pythondatetimefiscal

Assign Fiscal Year based on data ranges

I am trying to assign the appropriate fiscal year based on date ranges. set fiscal year parameters My data frame contains records of claims where each record shows the 'Date Generated' , 'Claim Amount', and 'Amount Paid'. dataframe The data types for each of those columns is:

  • Date Generated = datetime64[ns] (ex: '2018-10-03')
  • Claim Amount = float64 (ex: 2,948.35)
  • Amount Paid = float64 (ex: 2,948.35)

I want to create a new column 'Fiscal Year' that classifies each record into the correct fiscal year based on the 'Date Generated'.

I've tried a variety of solutions using 'pandas cut', 'lambda apply', 'pandas category' but I keep running into issues with the date range condition. Can someone help me with this? Thank you!

Solution

  • Looks like your fiscal year begins two months before the calendar year, so I'll add two months to the current date and take the year property

    offset = pandas.DateOffset(months=2)
df['Fiscal Year'] = (df['Date Generated'] + pandas.DateOffset(months=2)).dt.year