我正在尝试检查以下数据框中,以time_from和time_to表示的持续时间相同的file_id中的两个时间段是否重叠:
df1
id,file_id,time_from,time_to
1,10,00:00:19,00:00:25
2,12,00:02:39,00:02:49
3,12,00:04:18,00:04:30
4,12,00:05:30,00:05:55
5,15,00:01:35,00:01:38
6,18,00:07:35,00:07:48
df2
id,file_id,time_from,time_to
1,10,00:00:18,00:00:26
2,12,00:02:30,00:02:49
3,12,00:05:28,00:05:56
4,15,00:01:40,00:01:50
基本上,我想要计算两个数据框中在相同的file_id下,time_from和time_to之间允许一定距离(比如+/-4秒)的片段重叠的次数。
输出应该类似于:
file_id, number_of_overlapping_segments
10, 1
12, 2
15, 1
18, 0
这是唯一不重叠的时间段,算法应该返回0(假设距离>4秒)。其他情况都应该返回1:
[_____]
[________] [______]
到目前为止,我的方法是使用pandas将给定时间段的左右(限制)扩展4秒,并创建临时数据框来保存具有重叠时间段的行。例如:
import pandas as pd
left_overlap = []
right_overlap = []
for f in list(set(df1.file_id) & set(df2.file_id)):
for t_from, t_from_lim in list(zip(df2[df2.file_id==f]['time_from'],
df1[df1file_id==f]['time_from_limit'])):
if t_from > t_from_lim:
left_overlap.append(df_2[(df2.file_id==audio) & \
(df2.time_from==t_from)])
df_left_overlap = pd.concat(left_overlap).reset_index(drop=True)
for f in list(set(df1.file_id) & set(df2.file_id)):
for t_to, t_to_lim in list(zip(df_left_overlap[df_left_overlap.file_id==f]['time_to'],
df1[df1.file_id==f]['time_to_limit'])):
if t_to < t_to_lim:
right_overlap.append(
df_left_overlap[(df_left_overlap.file_id==f) & \
(df_left_overlap.time_to==t_to)])
overlap = pd.concat(right_overlap)
我认为这个解决方案不够高效,希望能找到更加健壮的方法来处理它。
提前感谢!