create fiscal year dataframe

fiscal_year_dataframe.py

import numpy as np
import pandas as pd

def get_fy_df(start_date, end_date, fy_start=4):
    df = pd.DataFrame({"date":pd.date_range(start=start_date,end=end_date)})
    df["day_of_week"] = df["date"].dt.weekday
    df["day_name"] = df["date"].dt.day_name()
    df["month"] = df["date"].dt.month
    df["month_name"] = df["date"].dt.month_name()
    df["month_abbr"] = df["month_name"].str.slice(stop=3)
    df["quarter"] = df["date"].dt.quarter
    df["year"] = df["date"].dt.year
    df["fy_month"] = df["month"] 
    months = list(range(fy_start, 13)) + list(range(1, fy_start))
    fy_months = list(range(1, 13))
    fy_quarter = list(np.repeat(list(range(1, 5)),3)) 
    month_mapping = dict(zip(months, fy_months))
    df = df.replace({"fy_month": month_mapping})
    df["fy_quarter"] = df["fy_month"]
    fy_quarter_mapping = dict(zip(fy_months, fy_quarter))
    df = df.replace({"fy_quarter": fy_quarter_mapping})
    def get_fy(row, fy_start):
        # closure function to calculate fy
        if row["month"] >= fy_start:
            row["fy"] = str(row["year"])[2:4] + "/" + str(row["year"]+1)[2:4] 
        else:
            row["fy"] = str(row["year"]-1)[2:4] + "/" + str(row["year"])[2:4]
        return row["fy"]
    df["fy"] = df.apply(get_fy, fy_start=fy_start, axis=1)
    df["month-year"] = df.month_abbr + "-" + df.year.astype(str)
    df["order_id"] = (df.year + df.fy_month) -1
    df = df[["date", "day_of_week", "day_name", "month", "month_name", "quarter", "year", "fy_month", "fy_quarter", "fy", "month-year", ]]
    
    return df
  
# example
df = get_fy_df(start_date = "2018-04-01", end_date="2022-03-31", fy_start=4)
df