Python Pandas: Counting Custom Business Hour Offsets

by

💡 Problem Formulation: Analysts working with time series data often need to calculate the number of custom business hours between timestamps. Consider a DataFrame that includes timestamps and the requirement to count how many increments of a CustomBusinessHour offset have been applied. For example, given the start and end timestamps, we want a count of business hours respecting a custom schedule and holidays.

Method 1: Using CustomBusinessHour with apply

This method involves creating a CustomBusinessHour object reflecting the custom business schedule and holidays. Then, we can apply a function across the DataFrame to calculate the increments between each pair of timestamps.

Here’s an example:

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

# Define custom business hours and holidays
cbh = CustomBusinessHour(start='09:00', end='17:00', holidays=['2023-01-01'])

# Timestamps to calculate increments between
start_time = pd.Timestamp('2023-01-02 09:00')
end_time = pd.Timestamp('2023-01-03 11:00')

# Applying the offset to get the count of increments
increments = cbh.rollforward(start_time)
count = 0
while increments < end_time:
    increments += cbh
    count += 1

print(count)

Output:

10

In this snippet, CustomBusinessHour is set up with a start and end time, along with defined holidays. The rollforward method aligns the starting point to the business hour window if necessary. The while loop iterates, adding the custom business hour increment until it reaches the end time, thereby counting the number of increments.

Method 2: CustomBusinessHours with date_range

Another approach is to generate a date range between the start and end timestamps with the CustomBusinessHour frequency, and then simply count the number of entries.

Here’s an example:

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

# Define custom business hours and holidays
cbh = CustomBusinessHour(start='09:00', end='17:00', holidays=['2023-01-01'])

# Generate a date range
date_range = pd.date_range(start='2023-01-02 09:00', end='2023-01-03 11:00', freq=cbh)

print(len(date_range) - 1)

Output:

10

This code creates a date range using the CustomBusinessHour object as the frequency, which respects the custom business hours and holidays. The length of the date range, minus one to exclude the starting point, gives the count of CBH increments between the two timestamps.

Method 3: Looping through a Timestamp series

For datasets where each row contains a pair of timestamps, one can loop through each row, applying the CustomBusinessHour offset to calculate the increment counts.

Here’s an example:

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

# Define custom business hours and holidays
cbh = CustomBusinessHour(start='09:00', end='17:00', holidays=['2023-01-01'])

# DataFrame with timestamp pairs
df = pd.DataFrame({'start': [pd.Timestamp('2023-01-02 09:00')], 
                   'end': [pd.Timestamp('2023-01-03 11:00')]})

# Function to count increments
def count_increments(row):
    increments = cbh.rollforward(row['start'])
    count = 0
    while increments < row['end']:
        increments += cbh
        count += 1
    return count

# Apply function to DataFrame
df['increments'] = df.apply(count_increments, axis=1)

print(df)

Output:

                start                 end  increments
0 2023-01-02 09:00:00 2023-01-03 11:00:00          10

This method loops through each row in a DataFrame, applying the count_increments() function to calculate the increments for each row. The loop adds the custom business hour to the start time until it reaches the end time for each pair, counting the increments in the process.

Bonus One-Liner Method 5: Using list comprehension

A fast, Pythonic way to accomplish the count using list comprehension.

Here’s an example:

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

# Define custom business hours and holidays
cbh = CustomBusinessHour(start='09:00', end='17:00', holidays=['2023-01-01'])

# Timestamp pairs
timestamps = [(pd.Timestamp('2023-01-02 09:00'), pd.Timestamp('2023-01-03 11:00'))]

# Counts as list comprehension
count_list = [sum(1 for _ in pd.date_range(start, end, freq=cbh, closed='left')) for start, end in timestamps]

print(count_list)

Output:

[10]

Using list comprehension paired with pd.date_range and the closed='left' parameter, this method quickly computes the count of CBH increments for each pair in a list, making it an efficient one-liner approach for multiple pairs of timestamps.

Summary/Discussion

  • Method 1: Using CustomBusinessHour with apply. Strengths: Flexible with direct manipulation of timestamps. Weaknesses: May be slower with large datasets.
  • Method 2: CustomBusinessHours with date_range. Strengths: Simplifies the counting to a date range length. Weaknesses: Not suited for row-wise operations on DataFrames.
  • Method 3: Looping through a Timestamp series. Strengths: Good for DataFrames with timestamp pairs in rows. Weaknesses: Less efficient for large datasets.
  • Bonus Method 5: Using list comprehension. Strengths: Compact and Pythonic. Weaknesses: Less readable for those unfamiliar with list comprehensions.