5 Best Ways to Create a BusinessHour Offset with Python Pandas

by

💡 Problem Formulation: In business data analysis, you may need to calculate offsets based on business hours. For instance, when scheduling appointments or deadlines, it is essential to consider only the working hours of a business. Let’s say we start with a timestamp ‘2023-03-15 09:00:00’ and we need to create an offset to know when it will be after 4 business hours, considering standard business hours to be from 9 AM to 5 PM.

Method 1: Using BusinessHour Class

This method utilizes the BusinessHour class from pandas.tseries.offsets module, which represents a timetable conforming to standard business day hours. The BusinessHour offset accounts for 9AM to 5PM business hours. By default, it aligns timestamps to 9 AM on the next business day if a timestamp is outside business hours.

Here’s an example:

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

start_date = pd.Timestamp('2023-03-15 09:00:00')
offset = BusinessHour(4)
new_date = start_date + offset
print(new_date)

Output of this code snippet:

2023-03-15 13:00:00

This code snippet generates a new timestamp that is 4 business hours ahead of the specified start date. The resulting time is 1:00 PM which is within the standard business hours on the same business day.

Method 2: Custom Business Hours

Here, a customized BusinessHour object is created to align with business hours that differ from the standard 9-5. The start and end times are arguments, bringing flexibility to the offset based on custom business hours operation.

Here’s an example:

start_date = pd.Timestamp('2023-03-15 16:00:00')
custom_business_hour = BusinessHour(start='10:00', end='18:00')
adjusted_date = start_date + custom_business_hour
print(adjusted_date)

Output of this code snippet:

2023-03-16 12:00:00

The example shows how to calculate a business hour offset when the business operates from 10 AM to 6 PM. Starting from 4 PM, one business hour later would normally be 5 PM, but since business hours end at 6 PM, the additional hour rolls over to the next business day, which starts at 10 AM. Therefore, the resulting time is 12 PM the following day.

Method 3: BusinessHour with Rollforward and Rollback

We may use the rollback and rollforward methods in conjunction with BusinessHour to control the alignment of the timestamp. The rollback method adjusts a timestamp to the previous business hour, while rollforward moves to the next business hour.

Here’s an example:

from pandas.tseries.offsets import BusinessHour

start_date = pd.Timestamp('2023-03-15 18:30:00')
bh = BusinessHour()

# Using rollforward
date_forward = bh.rollforward(start_date)
print('Rollforward:', date_forward)

# Using rollback
date_backward = bh.rollback(start_date)
print('Rollback:', date_backward)

Output of this code snippet:

Rollforward: 2023-03-16 09:00:00
Rollback: 2023-03-15 17:00:00

The rollforward method aligns the 6:30 PM timestamp to 9 AM the next business day. Conversely, rollback method adjusts it back to 5 PM of the current day, the last business hour before the timestamp.

Method 4: BusinessHour with Holidays

If you need to account for holidays, you can combine BusinessHour with a holiday calendar. The CustomBusinessHour class is ideal for adding holiday effects to the business hour offset calculations.

Here’s an example:

from pandas.tseries.holiday import USFederalHolidayCalendar
from pandas.tseries.offsets import CustomBusinessHour

start_date = pd.Timestamp('2023-11-22 16:00:00')  # The day before Thanksgiving
cbh = CustomBusinessHour(calendar=USFederalHolidayCalendar())

adjusted_date = start_date + cbh
print(adjusted_date)

Output of this code snippet:

2023-11-24 09:00:00

The code takes into account that the following day, Thanksgiving, is a federal holiday. Therefore, one business hour later from 4 PM on Nov 22 is 9 AM on Nov 24, since Nov 23 is skipped over.

Bonus One-Liner Method 5: Chain Offsets with Addition

Pandas allows chaining multiple offsets together using addition. This way, we can create complex offsets in a very concise manner.

Here’s an example:

from pandas.tseries.offsets import BusinessDay

start_date = pd.Timestamp('2023-03-15 17:05:00')
complex_offset = BusinessHour(start='09:00') + BusinessDay()
new_date = start_date + complex_offset

print(new_date)

Output of this code snippet:

2023-03-16 10:00:00

This one-liner combines a business hour offset with a business day offset. Starting from 5:05 PM, which is after business hours, it first moves to the next business day and then adds an additional business hour, resulting in 10 AM on the next business day.

Summary/Discussion

  • Method 1: Standard BusinessHour. Straightforward for standard 9-5 businesses. Not suitable for custom business hours.
  • Method 2: Custom Business Hours. Flexible for non-standard working hours. Complexity increases with custom rules.
  • Method 3: Rollforward and Rollback. Great for handling timestamps outside business hours. Requires additional handling for complex scenarios.
  • Method 4: BusinessHour with Holidays. Accounts for holidays. Requires more setup with holiday calendars.
  • Method 5: Chain Offsets with Addition. Quick and powerful for one-liners. Might be less readable for complex schedules.