0

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!

Niki
  • 13
  • 4
  • `df['Date Generated'].dt.year` will give you the calendar year, which may or may not be the same as your fiscal year. To be able to help better please provide some (obfuscated if necessary) example data and an example output. – el_oso Mar 29 '22 at 16:12
  • Sorry, it's hard for me to copy over the data because it's in a closed environment but I can add screenshots of the data. I already have the calendar date. I just want to delineate which fiscal year it falls under. – Niki Mar 29 '22 at 16:17
  • @el_oso I added pictures linked in my question above. – Niki Mar 29 '22 at 16:23
  • What is the relationship between Fiscal Year, Claim Amount and Amount Paid? – pyaj Mar 29 '22 at 23:52

1 Answers1

0

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
el_oso
  • 1,021
  • 6
  • 10
  • It's assigning the wrong fiscal year to the dates. For example the date '2018-10-03' is being assigned fiscal year 2018 when it should be 2019. Can you help? – Niki Apr 04 '22 at 14:52
  • change the months=2 to months=3? – el_oso Apr 04 '22 at 16:01