Python - Get policy year from datetime dataframe

user_14803010 0
Viewed 91

I have a dataframe (df) with a column in datetime format YYYY-MM-DD ('date'). I am trying to create a new column that returns the policy year, which always starts on April 1st and thus the policy year for January through March will always be the prior calander year. There are dates that are rather old so setting up individual date ranges for the sample size below wouldn't be ideal

The dataframe would look like this

df['date']

    2020-12-10
    2021-02-10 
    2019-03-31

and output should look like this

2020 
2020 
2018

I now know how to get the year using df['date'].dt.year. However, I am having trouble getting the dataframe to convert each year to the respective policy year so that if df['date'].dt.month >= 4 then df['date'].dt.year, else df['date'].dt.year - 1

I am not quite sure how to set this up exactly. I have been trying to avoid setting up multiple columns to do a bool for month >= 4 and then setting up different columns. I've gone so far as to set up this but get ValueError stating the series is too ambiguous

def PolYear(x):
  y = x.dt.month
  if y >= 4:
    x.dt.year
  else:
    x.dt.year - 1

df['Pol_Year'] = PolYear(df['date'])

I'm wasn't sure if this was the right way to go about it so I also tried a df.loc format for >= and < 4 but len key and value are not equal. Definitely think I'm missing something super simple.

I previously had mentioned 'fiscal year', but this is incorrect.

pandas dataframe python
2 Answers

Quang Hoand had the right idea but used the incorrect frequency in the call to to_period(self, freq). For your purposes you want to use the following code:

df.date.dt.to_period('Q-MAR').dt.qyear

This will give you:

0    2021
1    2021
2    2019
Name: date, dtype: int64

Q-MAR defines fiscal year end in March

These values are the correct fiscal years (fiscal years use the year in which they end, not where they begin[reference]). If you you want to have the output using the year in which they begin, it's simple:

df.date.dt.to_period('Q-MAR').dt.qyear - 1

Giving you

0    2020
1    2020
2    2018
Name: date, dtype: int64

qyear docs

user_24139830

This is qyear:

df.date.dt.to_period('Q').dt.qyear

Output:

0    2020
1    2021
2    2019
Name: date, dtype: int64
user_42384080
Related