我正在查看时间段的 Pandas DataFrame,试图将每个时间段与当天的其他时间段进行比较,以找到重复预订。
脚本需要一段时间才能运行。有更快的方法吗?
df_temp = pd.DataFrame()
for date in df_cal["date"].unique():
df_date = df_cal[df_cal["date"]==date]
for current in range(len(df_date)):
for comp in range(current+1,df_date[df_date["Start"]<df_date.iloc[current]["End"]]["Start"].idxmax()+1):
df_date.loc[comp,"Double booked"] = True
df_date.loc[current,"Double booked"] = True
df_date.loc[comp,"Time_removed"] = max(df_date.loc[comp,"Time_removed"],pd.Timedelta(min(df_date.iloc[current]["End"] - df_date.iloc[comp]["Start"],\
df_date.iloc[comp]["End"] - df_date.iloc[comp]["Start"])))
df_temp = pd.concat([df_temp,df_date])
列是 [["MEET_ID","date","Start","End","double_booked","Time_removed"]]
[[1943,
Timestamp('2017-05-01 00:00:00'),
Timestamp('2017-05-01 09:00:00'),
Timestamp('2017-05-01 09:30:00'),
False,
Timedelta('0 days 00:00:00')],
[1907,
Timestamp('2017-05-01 00:00:00'),
Timestamp('2017-05-01 10:00:00'),
Timestamp('2017-05-01 11:00:00'),
False,
Timedelta('0 days 00:00:00')],
[1913,
Timestamp('2017-05-01 00:00:00'),
Timestamp('2017-05-01 11:00:00'),
Timestamp('2017-05-01 12:00:00'),
False,
Timedelta('0 days 00:00:00')],
[1956,
Timestamp('2017-05-01 00:00:00'),
Timestamp('2017-05-01 12:00:00'),
Timestamp('2017-05-01 12:30:00'),
False,
Timedelta('0 days 00:00:00')],
[1905,
Timestamp('2017-05-01 00:00:00'),
Timestamp('2017-05-01 12:30:00'),
Timestamp('2017-05-01 13:00:00'),
False,
Timedelta('0 days 00:00:00')],
[1914,
Timestamp('2017-05-01 00:00:00'),
Timestamp('2017-05-01 12:30:00'),
Timestamp('2017-05-01 13:00:00'),
False,
Timedelta('0 days 00:00:00')],
[1940,
Timestamp('2017-05-01 00:00:00'),
Timestamp('2017-05-01 13:00:00'),
Timestamp('2017-05-01 16:00:00'),
False,
Timedelta('0 days 00:00:00')],
[1958,
Timestamp('2017-05-01 00:00:00'),
Timestamp('2017-05-01 14:30:00'),
Timestamp('2017-05-01 15:30:00'),
False,
Timedelta('0 days 00:00:00')],
[1892,
Timestamp('2017-05-01 00:00:00'),
Timestamp('2017-05-01 16:00:00'),
Timestamp('2017-05-01 16:30:00'),
False,
Timedelta('0 days 00:00:00')],
[1929,
Timestamp('2017-05-01 00:00:00'),
Timestamp('2017-05-01 16:30:00'),
Timestamp('2017-05-01 17:00:00'),
False,
Timedelta('0 days 00:00:00')],
[1887,
Timestamp('2017-05-01 00:00:00'),
Timestamp('2017-05-01 17:30:00'),
Timestamp('2017-05-01 18:00:00'),
False,
Timedelta('0 days 00:00:00')]]
然后应该会产生类似这样的结果,其中重复预订的 session 被标记为这样,并且重叠时间从其中一个 session 中删除(此处从第二个 session 中删除) 列是 [["MEET_ID","Start","End","Time_removed","double_booked"]]
[[1943,
Timestamp('2017-05-01 09:00:00'),
Timestamp('2017-05-01 09:30:00'),
Timedelta('0 days 00:00:00'),
False],
[1907,
Timestamp('2017-05-01 10:00:00'),
Timestamp('2017-05-01 11:00:00'),
Timedelta('0 days 00:00:00'),
False],
[1913,
Timestamp('2017-05-01 11:00:00'),
Timestamp('2017-05-01 12:00:00'),
Timedelta('0 days 00:00:00'),
False],
[1956,
Timestamp('2017-05-01 12:00:00'),
Timestamp('2017-05-01 12:30:00'),
Timedelta('0 days 00:00:00'),
False],
[1905,
Timestamp('2017-05-01 12:30:00'),
Timestamp('2017-05-01 13:00:00'),
Timedelta('0 days 00:00:00'),
False],
[1914,
Timestamp('2017-05-01 12:30:00'),
Timestamp('2017-05-01 13:00:00'),
Timedelta('0 days 00:30:00'),
True],
[1940,
Timestamp('2017-05-01 13:00:00'),
Timestamp('2017-05-01 16:00:00'),
Timedelta('0 days 00:00:00'),
True],
[1958,
Timestamp('2017-05-01 14:30:00'),
Timestamp('2017-05-01 15:30:00'),
Timedelta('0 days 01:00:00'),
True],
[1892,
Timestamp('2017-05-01 16:00:00'),
Timestamp('2017-05-01 16:30:00'),
Timedelta('0 days 00:00:00'),
False],
[1929,
Timestamp('2017-05-01 16:30:00'),
Timestamp('2017-05-01 17:00:00'),
Timedelta('0 days 00:00:00'),
False],
[1887,
Timestamp('2017-05-01 17:30:00'),
Timestamp('2017-05-01 18:00:00'),
Timedelta('0 days 00:00:00'),
False]]
编辑新数据 09/07/2018:
Start End Time_removed Double booked
77 2018-07-02 00:00:00 2018-07-02 10:00:00 00:00:00 True
78 2018-07-02 03:00:00 2018-07-02 08:00:00 05:00:00 True
79 2018-07-02 03:00:00 2018-07-02 08:00:00 05:00:00 True
80 2018-07-02 04:30:00 2018-07-02 09:30:00 03:30:00 True
81 2018-07-02 05:00:00 2018-07-02 10:00:00 04:30:00 True
82 2018-07-02 05:00:00 2018-07-02 10:00:00 05:00:00 True
第 80 行应该删除 5 小时,但只删除 3:30,因为它与前一行相比。它之前必须在第 77 行和第 80 行之间计算了 Time_removed,但随后它被一个更小的时间差所取代。
请您参考如下方法:
看起来像是 DataFrame.groupby 的工作.您也可以使用 numpy's outer product用于消除内部双 for 循环。
def process_data(df):
pos = np.argwhere(np.less.outer(df['start'], df['end']))
indices = df.index[pos]
unique = indices.ravel().unique()
date_diff = np.subtract.outer(df['end'], df['start']).max(axis=0)
return pd.DataFrame(
data=np.asarray([
[True]*len(indices),
np.where(
np.isin(unique, indices[:, 1]),
date_diff,
np.NaN
)
]).T,
columns=['Double booked', 'Time_removed'],
index=unique
)
df_cal.groupby('date').apply(process_data)
无论如何,这只是基于 OP 的片段,没有任何示例数据框和示例输出(即某种单元测试),很难说它是否真的解决了问题。
此外,您还必须确保不要混淆 index 和 position。在您的问题中,您似乎混合了 .loc 和 .iloc 以及 range 的用法。我不确定这是否给出了您想要的结果。
编辑
从添加到 OP 的数据来看,'Date' 变量实际上依赖于 'Start' 变量(即只是 的日期>'开始' 日期时间值)。鉴于此成立,我们可以省去 groupby 的应用并直接应用外积以获得重叠项:
overlapping = np.less_equal.outer(df['Start'], df['Start']) & np.greater.outer(df['End'], df['Start'])
overlapping &= ~np.identity(len(df), dtype=bool) # Meetings are overlapping with themselves; need to remove.
overlapping_indices = df.index[np.argwhere(overlapping)].values
df.loc[
np.unique(overlapping_indices.ravel()),
'double_booked'
] = True
df.loc[
overlapping_indices[:, 1],
'Time_removed'
] = (
np.minimum(df.loc[overlapping_indices[:, 0], 'End'], df.loc[overlapping_indices[:, 1], 'End'])
- np.maximum(df.loc[overlapping_indices[:, 0], 'Start'], df.loc[overlapping_indices[:, 1], 'Start'])
).values
但是,从示例数据来看,您并不清楚您希望如何将重叠 session 标记为重复预订。对于 12:30:00 - 13:00:00 session ,您只标记了第二个,而对于 13:00:00 - 16:00:00 和14:30:00 - 15:30:00 您将两者标记为双重预订。
编辑2
为了考虑多个(> 3)重叠 session ,我们需要计算所有 session 对的重叠时间,然后考虑那些实际有(正)重叠的 session 的最大重叠。以下解决方案要求数据按开始时间排序:
# This requires the data frame to be sorted by `Start` time.
start_times = np.tile(df['Start'].values, (len(df), 1))
end_times = np.tile(df['End'].values, (len(df), 1))
overlap_times = np.triu(np.minimum(end_times, end_times.T) - np.maximum(start_times, start_times.T))
overlap_times[np.diag_indices(len(overlap_times))] = np.timedelta64(0)
overlap_indices = df.index[np.argwhere(overlap_times > np.timedelta64(0))]
overlaps_others_indices = np.unique(overlap_indices[:, 1])
df.loc[
np.unique(overlap_indices.ravel()),
'double_booked'
] = True
df.loc[
overlaps_others_indices,
'Time_removed'
] = pd.Series(overlap_times.max(axis=0), index=df.index)[overlaps_others_indices]
