Python-Pandas-Datetime- How to convert Financial Year and Financial Month to Calendar date

Question

Trying to convert financial year and month to calendar date. I have a dataframe as below. Each ID will have multiple records.

ID  Financial_Year  Financial_Month
1   2021             1
1   2022             2
2   2021             3
2   2023             1

Trying to convert financial year and month to calendar date. I have a dataframe as below. Each ID will have multiple records.

ID  Financial_Year  Financial_Month
1   2021             1
1   2022             2
2   2021             3
2   2023             1

Expected output:

Eg: If the financial year starts form July to June eg: FY 2022 means:

July -2021  - This is 1st month in the financial year, 
August- 2021 - This is 2nd month in the financial year
Sep -2021 - This is 3rd month in the financial year
Oct -2021 - This is 4th month in the financial year
Nov 2021 - - This is 5th month in the financial year
Dec 2021- - This is 6th month in the financial year
jan 2022- This is 7th month in the financial year
feb 2022- This is 8th month in the financial year
March 2022- This is 9th month in the financial year
April 2022- This is 10th month in the financial year
May 2022- This is 11th month in the financial year
June 2022- This is 12th month in the financial year

Calendar year:

Jan -1st of the year ,Feb,March,April,May,June,July,Aug,SEp,Oct,Nov,Dec - 12th of the year

Expected output: Convert financial year and Month to Calendar date

ID  Financial_Year  Financial_Month     Calendar_date
1   2021             1                   01-07-2021
1   2022             2                   01-08-2022
2   2021             3                   01-09-2021
2   2023             12                  01-06-2023 

               

Answer 1

The datetime and apply functions on a dataframe can get you the desired result:

import pandas as pd
import datetime

def calendar_year(yr, mnth):
    mnth = mnth + 6
    mnth = mnth % 12
    dt = datetime.datetime(yr, mnth, 1).strftime("%d-%m-%Y")
    return dt
df["calendar_month"] = df.apply(lambda x: calendar_year(x["Financial_Year"], x["Financial_Month"]), axis = 1)

As the year isn't changing in your example, I have just adjusted the month to reflect the calendar month.

You can adjust the number of months added (currently 6) to adjust to your financial year.