Pandas round DateTime to week

Being able to round a DateTime object in Python to a given weekday 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 nearest week in Python?

In order to round a DateTime object to the nearest week, you need to use a combination of the to_period and start_timeoperations from Pandas on the DateTime column. It’s not possible to use the round operation as is the case with seconds, minutes, hours, and days as weeks don’t follow the same logical rounding.

python code for rounding pandas datetime to week date

Pandas get week start date (Monday) from DateTime

To get the Monday date of a given week you need to first convert the DateTime object to a week period (Monday - Sunday) and then grab the start date of that period (Monday).

import pandas as pd

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

df["datetime_monday_week"] = df["datetime"].dt.to_period('W').dt.start_time

"""
Output:

datetime                datetime_monday_week
0 2022-01-08 09:00:00   2022-01-03
1 2022-01-09 09:00:00   2022-01-03
2 2022-01-10 09:00:00   2022-01-10
3 2022-01-11 09:00:00   2022-01-10
"""

Pandas get week end date (Sunday) from DateTime

If you want to return the Sunday date of the week then you need to use Timedelta, which will add a certain number of days to the start date of the week's period. Here we add 6 day units to the start date of the week to get the Sunday date.

This example assumes that a week starts on Monday and ends on Sunday.

import pandas as pd

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

df["datetime_sunday_week"] = df["datetime"].dt.to_period('W').dt.start_time + pd.Timedelta(6, unit='d')

"""
Output:

datetime                datetime_sunday_week
0 2022-01-08 09:00:00   2022-01-09
1 2022-01-09 09:00:00   2022-01-09
2 2022-01-10 09:00:00   2022-01-16
3 2022-01-11 09:00:00   2022-01-16
"""

Pandas get second day of the week (Tuesday) from DateTime

If you want to return the Tuesday date of the week then we follow the same procedure. Here we add 1 day unit to the start date of the week to get the Tuesday date.

This example assumes that a week starts on Monday and ends on Sunday.

import pandas as pd

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

df["datetime_tuesday_week"] = df["datetime"].dt.to_period('W').dt.start_time + pd.Timedelta(1, unit='d')

"""
Output:

datetime                 datetime_tuesday_week
0 2022-01-08 09:00:00    2022-01-04
1 2022-01-09 09:00:00    2022-01-04
2 2022-01-10 09:00:00    2022-01-11
3 2022-01-11 09:00:00    2022-01-11
"""

Pandas get third day of the week (Wednesday) from DateTime

If you want to return the Wednesday date of the week then we follow the same procedure. Here we add 2 days unit to the start date of the week to get the Wednesday date.

This example assumes that a week starts on Monday and ends on Sunday.

import pandas as pd

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

df["datetime_wednesday_week"] = df["datetime"].dt.to_period('W').dt.start_time + pd.Timedelta(2, unit='d')

"""
Output:

datetime                 datetime_wednesday_week
0 2022-01-08 09:00:00    2022-01-05
1 2022-01-09 09:00:00    2022-01-05
2 2022-01-10 09:00:00    2022-01-12
3 2022-01-11 09:00:00    2022-01-12
"""

Pandas get fourth day of the week (Thursday) from DateTime

If you want to return the Thursday date of the week then we follow the same procedure. Here we add 3 days unit to the start date of the week to get the Thursday date.

This example assumes that a week starts on Monday and ends on Sunday.

import pandas as pd

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

df["datetime_thursday_week"] = df["datetime"].dt.to_period('W').dt.start_time + pd.Timedelta(3, unit='d')

"""
Output:

datetime                 datetime_thursday_week
0 2022-01-08 09:00:00    2022-01-06
1 2022-01-09 09:00:00    2022-01-06
2 2022-01-10 09:00:00    2022-01-13
3 2022-01-11 09:00:00    2022-01-13
"""

Pandas get fifth day of the week (Friday) from DateTime

If you want to return the Friday date of the week then we follow the same procedure. Here we add 4 days unit to the start date of the week to get the Friday date.

This example assumes that a week starts on Monday and ends on Sunday.

import pandas as pd

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

df["datetime_friday_week"] = df["datetime"].dt.to_period('W').dt.start_time + pd.Timedelta(4, unit='d')

"""
Output:

datetime                 datetime_friday_week
0 2022-01-08 09:00:00    2022-01-07
1 2022-01-09 09:00:00    2022-01-07
2 2022-01-10 09:00:00    2022-01-14
3 2022-01-11 09:00:00    2022-01-14
"""

Pandas get sixth day of the week (Saturday) from DateTime

If you want to return the Saturday date of the week then we follow the same procedure. Here we add 5 days unit to the start date of the week to get the Saturday date.

This example assumes that a week starts on Monday and ends on Sunday.

import pandas as pd

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

df["datetime_saturday_week"] = df["datetime"].dt.to_period('W').dt.start_time + pd.Timedelta(5, unit='d')

"""
Output:

datetime                 datetime_saturday_week
0 2022-01-08 09:00:00    2022-01-08
1 2022-01-09 09:00:00    2022-01-08
2 2022-01-10 09:00:00    2022-01-15
3 2022-01-11 09:00:00    2022-01-15
"""

Python round DateTime to day
Python round DateTime to week number
Python round DateTime to month number
Python round DateTime to start or end of month
Python convert DateTime to date

References

Pandas round documentation
Pandas floor documentation
Pandas ceil 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