Pandas Fiscal Year – Get Financial Year with Pandas

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(lambda x: str(x-1) + "-" + str(x))

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 pd

dates = {'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:

         Date
0  2021-01-01
1  2021-01-31
2  2021-02-01
3  2021-02-28
4  2021-03-01
5  2021-03-31
6  2021-04-01
7  2021-04-30
8  2021-05-01
9  2021-05-31
10 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.year
print(df)

This returns:

         Date  Year
0  2021-01-01  2021
1  2021-01-31  2021
2  2021-02-01  2021
3  2021-02-28  2021
4  2021-03-01  2021
5  2021-03-31  2021
6  2021-04-01  2021
7  2021-04-30  2021
8  2021-05-01  2021
9  2021-05-31  2021
10 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:

         Date  Year As Quarter
0  2021-01-01  2021     2021Q4
1  2021-01-31  2021     2021Q4
2  2021-02-01  2021     2021Q4
3  2021-02-28  2021     2021Q4
4  2021-03-01  2021     2021Q4
5  2021-03-31  2021     2021Q4
6  2021-04-01  2021     2022Q1
7  2021-04-30  2021     2022Q1
8  2021-05-01  2021     2022Q1
9  2021-05-31  2021     2022Q1
10 2021-06-01  2021     2022Q1

We’ve now created a new column (“Quarter”) that uses April as the first month of a year and aggregates values into quarters, with April-June being Quarter 1.

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.qyear
print(df)

This returns the following:

         Date  Year As Quarter  Fiscal Year
0  2021-01-01  2021     2021Q4         2021
1  2021-01-31  2021     2021Q4         2021
2  2021-02-01  2021     2021Q4         2021
3  2021-02-28  2021     2021Q4         2021
4  2021-03-01  2021     2021Q4         2021
5  2021-03-31  2021     2021Q4         2021
6  2021-04-01  2021     2022Q1         2022
7  2021-04-30  2021     2022Q1         2022
8  2021-05-01  2021     2022Q1         2022
9  2021-05-31  2021     2022Q1         2022
10 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 Range
0  2021-01-01  2021     2021Q4         2021         2020-2021
1  2021-01-31  2021     2021Q4         2021         2020-2021
2  2021-02-01  2021     2021Q4         2021         2020-2021
3  2021-02-28  2021     2021Q4         2021         2020-2021
4  2021-03-01  2021     2021Q4         2021         2020-2021
5  2021-03-31  2021     2021Q4         2021         2020-2021
6  2021-04-01  2021     2022Q1         2022         2021-2022
7  2021-04-30  2021     2022Q1         2022         2021-2022
8  2021-05-01  2021     2022Q1         2022         2021-2022
9  2021-05-31  2021     2022Q1         2022         2021-2022
10 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