Pandas加速数据框应用（）计算时差

Question

我有使用df.apply()计算两个日期之间营业时间的工作代码。然而，考虑到我的df是~40k行，它是非常慢的，有没有办法我可以加快这一点通过矢量化？你知道吗

原始代码：

import datetime
import pytz
import businesstimedelta
import holidays as pyholidays

workday = businesstimedelta.WorkDayRule(
    start_time=datetime.time(9),
    end_time=datetime.time(17),
    working_days=[0, 1, 2, 3, 4])


vic_holidays = pyholidays.AU(prov='VIC')
holidays = businesstimedelta.HolidayRule(vic_holidays)
businesshrs = businesstimedelta.Rules([workday, holidays])

def BusHrs(start, end):
    return businesshrs.difference(start,end).hours+float(businesshrs.difference(start,end).seconds)/float(3600)

df['Diff Hrs'] = df.apply(lambda row: BusHrs(row['Updated Date'], row['Current Date']), axis=1)

提供：

Index   Created Date        Updated Date        Diff Hrs    Current Date
10086   2016-11-04 16:00:00 2016-11-11 11:38:00 35.633333   2018-05-29 10:09:11.291391
10087   2016-11-04 16:03:00 2016-11-29 12:54:00 132.850000  2018-05-29 10:09:11.291391
10088   2016-11-04 16:05:00 2016-11-16 08:05:00 56.916667   2018-05-29 10:09:11.291391
10089   2016-11-04 16:17:00 2016-11-08 11:37:00 11.333333   2018-05-29 10:09:11.291391
10090   2016-11-04 16:20:00 2016-11-16 09:58:00 57.633333   2018-05-29 10:09:11.291391
10091   2016-11-04 16:32:00 2016-11-08 11:10:00 10.633333   2018-05-29 10:09:11.291391

我可以看到它嘎吱作响，看起来可能需要5个多小时才能完成。你知道吗

奇怪的是，我有预感，两个日期越接近，计算速度就越快。 例如df['Time Since Last Update'] = df.apply(lambda row: BusHrs(row['Updated Date'], row['Current Date']), axis=1)比

df['Time Since Last Update'] = df.apply(lambda row: BusHrs(row['Created Date'], row['Updated Date']), axis=1)

像这样的优化是一个步骤以上，我习惯了，所以任何帮助是感激的。你知道吗

Answer 1

如果要加快代码速度，可以首先重新定义函数：

def BusHrs(start, end):
    diff_hours = businesshrs.difference(start,end)
    return diff_hours.hours+float(diff_hours.seconds)/float(3600)

然后，我想你可以通过计算两个连续更新日期之间的小时数，然后将这些部分计算相加，直到当前日期，来加快速度。您需要两个临时列，一个带有更改的更新日期，另一个带有部分工作时间

# sort from more recent date 
df = df.sort_values('Updated Date',ascending=False)
#create a column with shift of 1 and set the Nan to be now
df['Shift Date'] = df['Updated Date'].shift(1).fillna(pd.datetime.now())
#calculate partial business hours between two successives update date
df['BsnHrs Partial'] = df.apply(lambda row: BusHrs(row['Updated Date'], row['Shift Date']), axis=1)
# with this order, you can use cumsum() to add the necessary value of partial business hours
df['Time Since Last Update'] = df['BsnHrs Partial'].cumsum()
#drop column not usefull anymore and sort_index to return original order
df = df.drop(['Shift Date','BsnHrs Partial'],1).sort_index()