如何计算DataFrame两日期列间自定义营业时间并生成新列用于图表展示

你遇到的报错是因为businessDuration库仅支持传入单个datetime对象计算时长，不支持直接传入pandas Series（整列数据），库内部的逻辑判断无法处理数组类型的输入，因此抛出真值歧义错误。

可以通过逐行处理（pandas apply方法，本质为行遍历）实现自定义规则的时长计算，完整实现步骤如下：

1. 前置依赖导入与基础配置 #

import pandas as pd
from datetime import time, datetime, timedelta
from business_duration import businessDuration
import holidays as pyholidays

# 基础配置
BIZ_OPEN = time(9,0,0)
BIZ_CLOSE = time(17,0,0) # 按需求修正为17点，原参考代码16点为错误值
CA_HOLIDAYS = pyholidays.US(state='CA')
# 提前维护John和James的周日轮值日期集合，可根据实际规则动态生成
JOHN_DUTY_SUNDAYS = set([datetime(2024,5,12).date(), datetime(2024,5,19).date()]) # 示例日期
JAMES_DUTY_SUNDAYS = set([datetime(2024,5,5).date(), datetime(2024,5,26).date()]) # 示例日期

2. 自定义营业时长计算函数 #

def calc_business_sla(row):
    analyst = row['analyst_name']
    start = row['assigned_at']
    end = row['completed_at']
    
    # 处理周五16点后分配的特殊规则
    if start.weekday() == 4 and start.hour >= 16:
        # 当日仅计算1小时，剩余开始时间调整为下周一9点
        friday_credit = 1
        next_monday = start + timedelta(days= (7 - start.weekday()))
        start = datetime.combine(next_monday.date(), BIZ_OPEN)
    else:
        friday_credit = 0
    
    # 生成对应当前分析师的周末排除规则
    weekend_list = []
    if analyst == 'Jason':
        # 仅排除周日
        weekend_list = [6]
    elif analyst in ['John', 'James']:
        # 周日如果是轮值日则不排除，否则排除周六周日
        has_duty_sun = False
        current_date = start.date()
        end_date = end.date()
        while current_date <= end_date:
            if current_date.weekday() == 6:
                if (analyst == 'John' and current_date in JOHN_DUTY_SUNDAYS) or (analyst == 'James' and current_date in JAMES_DUTY_SUNDAYS):
                    has_duty_sun = True
                    break
            current_date += timedelta(days=1)
        weekend_list = [5] if has_duty_sun else [5,6]
    else:
        # 其他人员排除周六周日
        weekend_list = [5,6]
    
    # 计算有效营业时长
    try:
        biz_hours = businessDuration(
            startdate=start,
            enddate=end,
            starttime=BIZ_OPEN,
            endtime=BIZ_CLOSE,
            holidaylist=CA_HOLIDAYS,
            weekendlist=weekend_list,
            unit='hour'
        )
        return round(biz_hours + friday_credit, 2)
    except:
        # 异常情况返回0或空值，可根据需求调整
        return 0

3. 执行计算生成新列 #

# 先确保日期字段为datetime类型
df['assigned_at'] = pd.to_datetime(df['assigned_at'])
df['completed_at'] = pd.to_datetime(df['completed_at'])

# 逐行计算生成新列
df['business_hours_sla'] = df.apply(calc_business_sla, axis=1)

• 如果数据量超过10万行，apply方法性能较低，可将规则拆解为向量化逻辑计算提升效率
• John和James的轮值规则可根据实际逻辑替换JOHN_DUTY_SUNDAYS和JAMES_DUTY_SUNDAYS的生成方式，无需手动维护日期
• 若出现跨节假日的时长计算异常，可检查holidays库的版本是否为最新，确保加州节假日规则正确

内容的提问来源于stack exchange，提问作者Jon Oneill