Pandas round datetime to month (simply explained)

Being able to round a DateTime object in Python to either the start or end of the month can be extremely helpful for feature engineering. In this post, I will example how to do this simply in multiple variations.

How does Pandas round to the start or end of the month in Python?

There is no universal method for rounding to either the start and end of the month in Pandas, as is the case with rounding to the nearest minute or hour. Therefore, we need to use different workaround methods for both rounding to the start and end of the month. Let’s look at some examples.

python code for rounding pandas datetime to month start

Pandas round datetime to month start date

In order to round the date to the start of the month, we can simply convert the column’s datatype to datetime with the month aggregation.

import pandas as pd

df = pd.DataFrame(
	columns=["datetime"],
	data=pd.date_range("30/8/2022 09:00:00", periods=4, freq="D"))

df["month_start_date"] = df["datetime"].astype("datetime64[M]")

"""
Output:

datetime                month_start_date
0 2022-08-30 09:00:00   2022-08-01
1 2022-08-31 09:00:00   2022-08-01
2 2022-09-01 09:00:00   2022-09-01
3 2022-09-02 09:00:00   2022-09-01
"""

Pandas get end date of month from datetime column

Rounding to the end of the month requires a different approach. We need to use the tseries.offsets.MonthEnd and add this to the date column.

import pandas as pd
from pandas.tseries.offsets import MonthEnd

df = pd.DataFrame(
	columns=["datetime"],
	data=pd.date_range("30/8/2022 09:00:00", periods=4, freq="D"))

df["month_end_date"] = df['datetime'].dt.date + MonthEnd(0)

"""
Output:

datetime                month_end_date
0 2022-08-30 09:00:00   2022-08-31
1 2022-08-31 09:00:00   2022-08-31
2 2022-09-01 09:00:00   2022-09-30
3 2022-09-02 09:00:00   2022-09-30
"""


Pandas round DateTime to day
Pandas round DateTime to week
Python get week number from datetime
Python get month from date
Pandas convert datetime64 to date

References

MonthEnd documentation
Pandas astype documentation

Stephen Allwright

Stephen Allwright

I'm a Data Scientist currently working for Oda, an online grocery retailer, in Oslo, Norway. These posts are my way of sharing some of the tips and tricks I've picked up along the way.
Oslo, Norway