Pandas Fiscal Year - Get Financial Year with Pandas • datagy (2024)

Learn all about the Pandas fiscal year! Pandas makes getting date information quite straightforward – getting a financial date, however, is a bit less intuitive. In this post, I hope to explain the process in a straightforward way.

At the end of this post, you’ll learn how to:

Use Pandas .to_period() to resample dates to different time periods,
Use Pandas .qyear() to calculate years based on different start months, and
Format your Pandas fiscal years in different ways.

In short, you can write: df['Date'].dt.to_period('Q-MAR').dt.qyear.apply(lambdax:str(x-1)+"-"+str(x))

Table of Contents

Loading a Sample Dataset for Pandas Fiscal Year

Let’s begin by loading a dataset with different time periods that we can use. We’ll import Pandas and print out the first five records, to get a sense of what the data contains:

import pandas as pddates = {'Date': ['1/1/2021', '1/31/2021', '2/1/2021', '2/28/2021', '3/1/2021', '3/31/2021', '4/1/2021', '4/30/2021', '5/1/2021', '5/31/2021', '6/1/2021']}df = pd.DataFrame(dates)df['Date'] = pd.to_datetime(df['Date'])print(df)

This returns:

 Date0 2021-01-011 2021-01-312 2021-02-013 2021-02-284 2021-03-015 2021-03-316 2021-04-017 2021-04-308 2021-05-019 2021-05-3110 2021-06-01

By default, we can access the calendar year using the .dt.year accessor. Let’s take a look at how to do this to pull the calendar year into its own column. We’ll create a new column called Year and assign the year value.

df['Year'] = df['Date'].dt.yearprint(df)

This returns:

 Date Year0 2021-01-01 20211 2021-01-31 20212 2021-02-01 20213 2021-02-28 20214 2021-03-01 20215 2021-03-31 20216 2021-04-01 20217 2021-04-30 20218 2021-05-01 20219 2021-05-31 202110 2021-06-01 2021

Check out some other Python tutorials on datagy, including our complete guide to styling Pandas and our comprehensive overview of Pivot Tables in Pandas!

Pandas .to_period() to resample dates to different time periods

Pandas allows us to easily resample datetime data into different periods. With the Pandas .to_period() method, you can resample datetime values into different periods. Let’s take a look with an example. We’ll resample values to quarters, with the first month of the year being in April (meaning, we’ll write Q-(for quarter) and MAR (for a year ending in March)).

df['As Quarter'] = df['Date'].dt.to_period('Q-MAR')print(df)

This returns:

Pandas .qyear() to calculate years on different start months

Now that we’ve resampled our data with the .to_period() method, let’s get only the year. You may be inclined to use the dt.year accessor, but that would continue to pull the calendar year. Pandas has a convient .qyear() method that pulls years based on a given starting quarter. Had we not resample the data, the .year and .qyear would be the same.

Note! Pandas will pull the ending year.

Let’s take a look with an example:

df['Fiscal Year'] = df['As Quarter'].dt.qyearprint(df)

This returns the following:

 Date Year As Quarter Fiscal Year0 2021-01-01 2021 2021Q4 20211 2021-01-31 2021 2021Q4 20212 2021-02-01 2021 2021Q4 20213 2021-02-28 2021 2021Q4 20214 2021-03-01 2021 2021Q4 20215 2021-03-31 2021 2021Q4 20216 2021-04-01 2021 2022Q1 20227 2021-04-30 2021 2022Q1 20228 2021-05-01 2021 2022Q1 20229 2021-05-31 2021 2022Q1 202210 2021-06-01 2021 2022Q1 2022

Note, we can write this all in one line by chaining it all together:

df['Fiscal Year'] = df['Date'].dt.to_period('Q-MAR').dt.qyear

Format Pandas Fiscal Year

By default, Pandas will only pull the first year in the fiscal year. Unless your fiscal year goes from January to December, your fiscal year will always span two years. It may be helpful to include both years in your column to be able to better identify them. Let’s create a new column where years are formatted like this: 2021-2022:

df['Fiscal Year Range'] = df['Date'].dt.to_period('Q-APR').dt.qyear.apply(lambda x: str(x-1) + "-" + str(x))print(df)

This returns the following:

 Date Year As Quarter Fiscal Year Fiscal Year Range0 2021-01-01 2021 2021Q4 2021 2020-20211 2021-01-31 2021 2021Q4 2021 2020-20212 2021-02-01 2021 2021Q4 2021 2020-20213 2021-02-28 2021 2021Q4 2021 2020-20214 2021-03-01 2021 2021Q4 2021 2020-20215 2021-03-31 2021 2021Q4 2021 2020-20216 2021-04-01 2021 2022Q1 2022 2021-20227 2021-04-30 2021 2022Q1 2022 2021-20228 2021-05-01 2021 2022Q1 2022 2021-20229 2021-05-31 2021 2022Q1 2022 2021-202210 2021-06-01 2021 2022Q1 2022 2021-2022

Conclusion

In this post, you learned how to calculate custom fiscal years by resample datetime series, using the .qyear() method, and how to format years in a different formats.

Check out the official documentation for .to_period() and .qyear() to learn more.

Additional Resources

To learn more about related topics, check out the resources below:

Pandas to_datetime: Convert a Pandas String Column to Date Time
DateTime in Pandas and Python
Pandas Datetime to Date Parts (Month, Year, etc.)
Pandas: Add Days to a Date Column