两个时间戳系列之间的营业时间，不包括周末和节假日

2022-01-11 00:00:00 python pandas datetime time-series calendar

问题描述

我有一个看起来像这样的 pandas DataFrame(示例):

I have a pandas DataFrame that looks like this (sample):

data = { 
    'start': ['2018-10-29 18:48:46.697000',
              '2018-10-29 19:01:10.887000',
              '2018-10-22 17:42:24.467000'], 
    'end': ['2018-10-31 17:56:38.830000',
            '2018-11-27 09:31:39.967000',
            '2018-11-28 18:33:35.243000' ]   
}
df = pd.DataFrame(data)
df['start'] = pd.to_datetime(df['start'])
df['end'] = pd.to_datetime(df['end'])

我的目标是计算 start 和 end 之间的(美国)营业时间，不包括周末和节假日.为此，我正在使用 pandas 的 CustomBusinessDay 功能，如下所示:

My goal is to calculate the (US) business hours between start and end, excluding weekends and holidays. For that I am using the CustomBusinessDay functionality of pandas as follows:

from pandas.tseries.holiday import USFederalHolidayCalendar
from pandas.tseries.offsets import CustomBusinessDay
us_bd = CustomBusinessDay(calendar=USFederalHolidayCalendar())

len(pd.bdate_range(start=df['start'][2], end=df['end'][2], freq=us_bd))
>> 26

这在工作日方面是正确的(它不包括周末、感恩节和黑色星期五的假期)，但我真正想要的是两个时间戳之间的工作时间数.所以当我尝试原生 BH:

This is correct in terms of business days (it does exclude weekends, and the holidays of Thanksgiving and Black Friday), but what I really want is the number of business hours between the two timestamps. So when I try the native BH:

len(pd.bdate_range(start=df['start'][2], end=df['end'][2], freq='BH'))
>> 216

这是不正确的，因为它考虑了周末，但不考虑了节假日.所以，我有两个问题:

which is incorrect, because it accounts for weekends, but not for the holidays. So, I have two questions:

  1. 如何通过排除周末和节假日来正确计算两个时间戳之间的营业时间
  2. 如何在 Pandas 系列中传播此计算以在 DataFrame 中生成新列?

当我尝试类似:

df['diff'] = pd.bdate_range(start=df['start'], end=df['end'], freq='BH')

结果是:

TypeError: Can not convert input [...] of type to Timestamp

TypeError: Can not convert input [...] of type to Timestamp

错误消息还包括数组中的整个系列.

The error message also included the whole series in the array.

解决方案

你应该使用 CustomBusinessHour 和 pd.date_range 而不是 pd.bdate_range.

You should use CustomBusinessHour and pd.date_range instead of pd.bdate_range.

第二行的小时数应该是 145，因为结束时间是 09:31:39.967.

The number of hours for your second row should be 145 because endtime is 09:31:39.967. 

us_bh = CustomBusinessHour(calendar=USFederalHolidayCalendar())
df['count'] = df.apply(lambda x: len(pd.date_range(start=x.start, end=x.end, freq= us_bh)),axis=1)
df['diff'] = df.apply(lambda x: pd.date_range(start=x.start, end=x.end, freq= us_bh),axis=1)
print(df)


    start                     end                  count                                               diff
0 2018-10-29 18:48:46.697 2018-10-31 17:56:38.830     16  DatetimeIndex(['2018-10-30 09:00:00', '2018-10...
1 2018-10-29 19:01:10.887 2018-11-27 09:31:39.967    145  DatetimeIndex(['2018-10-30 09:00:00', '2018-10...
2 2018-10-22 17:42:24.467 2018-11-28 18:33:35.243    200  DatetimeIndex(['2018-10-23 09:00:00', '2018-10...

当您使用 pd.bdate_range 时，diff 列的开始营业时间将 '2018-10-29 09:00:00'.

And diff columns start business hour will '2018-10-29 09:00:00' when you use pd.bdate_range.

us_bh = CustomBusinessHour(calendar=USFederalHolidayCalendar())
df['count'] = df.apply(lambda x: len(pd.bdate_range(start=x.start, end=x.end, freq= us_bh)),axis=1)
df['diff'] = df.apply(lambda x: pd.bdate_range(start=x.start, end=x.end, freq= us_bh),axis=1)
print(df)

                    start                     end  count                                               diff
0 2018-10-29 18:48:46.697 2018-10-31 17:56:38.830     16  DatetimeIndex(['2018-10-29 09:00:00', '2018-10...
1 2018-10-29 19:01:10.887 2018-11-27 09:31:39.967    152  DatetimeIndex(['2018-10-29 09:00:00', '2018-10...
2 2018-10-22 17:42:24.467 2018-11-28 18:33:35.243    200  DatetimeIndex(['2018-10-22 09:00:00', '2018-10...

相关文章