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

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

CodePudding user response：

You can convert financial year and month to calendar date using Python's datetime module:

import datetime
def financial_to_calendar(row):
    financial_year = row['Financial_Year']
    financial_month = row['Financial_Month']
    if financial_month not in range(1, 13):
        return None
    if financial_month <= 6:
        calendar_year = financial_year - 1
        calendar_month = 7   financial_month
    else:
        calendar_year = financial_year
        calendar_month = financial_month - 6
    calendar_date = datetime.datetime(calendar_year, calendar_month, 1)
    return calendar_date.strftime('%d-%m-%Y')

df['Calendar_date'] = df.apply(financial_to_calendar, axis=1)

The code assumes that the financial year starts from July to June, as described in your example. You can modify the function financial_to_calendar to match your specific financial year and month convention.

Edit:

As mention in comment "expecting that every ID will have all the 12 months because it wouldnt be that case .It may have just 1 record or multiple records."

If that is the case, and not every ID has data for all 12 months, then you may want to consider using groupby to convert the financial months to calendar months for each ID separately. You can do something like this:

def financial_to_calendar(group):
    financial_year = group['Financial_Year'].iloc[0]
    financial_month = group['Financial_Month']
    calendar_dates = []
    for month in financial_month:
        if month <= 6:
            calendar_year = financial_year - 1
            calendar_month = 7   month
        else:
            calendar_year = financial_year
            calendar_month = month - 6
        calendar_date = datetime.datetime(calendar_year, calendar_month, 1)
        calendar_dates.append(calendar_date.strftime('%d-%m-%Y'))
    return pd.Series(calendar_dates, index=group.index)

df['Calendar_date'] = df.groupby('ID').apply(financial_to_calendar)

CodePudding user response：

The datetime and apply function on dataframe can get 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 calendar month.

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