合并Pandas中的两个时间间隔序列（交叉点）

Question

我有多个时间间隔列表，我需要找到所有这些时间间隔的公共时间间隔（交叉点）

例如

a = [['2018-02-03 15:06:30', '2018-02-03 17:06:30'], # each line is read as [start, end]
     ['2018-02-05 10:30:30', '2018-02-05 10:36:30'],
     ['2018-02-05 11:30:30', '2018-02-05 11:42:32']]

b = [['2018-02-03 15:16:30', '2018-02-03 18:06:30'],
     ['2018-02-04 10:30:30', '2018-02-05 10:32:30']]

c = [['2018-02-01 15:00:30', '2018-02-05 18:06:30']]

结果将是

common_intv = [['2018-02-03 15:16:30','2018-02-03 17:06:30'],
               ['2018-02-05 10:30:30','2018-02-05 10:32:30']]

我发现this解决方案应该也适用于时间间隔，但我想知道是否有一种更有效的方法在熊猫身上实现这一点

链接中建议的解决方案将一次处理两个列表，即首先查找a和b之间的公共间隔，然后将这些公共间隔放入变量common中，然后查找common和c之间的公共间隔，依此类推

当然，全局解决方案（同时考虑所有时间间隔）会更好

Answer 1

您可以在两个方向上使用pandas.merge_asof来获得第一个选择，然后仔细清理结果行。代码可以是：

# build the dataframes and ensure Timestamp types
dfa = pd.DataFrame(a, columns=['start', 'end']).astype('datetime64[ns]')
dfb = pd.DataFrame(b, columns=['start', 'end']).astype('datetime64[ns]')
dfc = pd.DataFrame(c, columns=['start', 'end']).astype('datetime64[ns]')

# merge a and b
tmp = pd.concat([pd.merge_asof(dfa, dfb, on='start'),
                 pd.merge_asof(dfb, dfa, on='start')]
                ).sort_values('start').dropna()

# keep the minimum end and ensure end <= start
tmp = tmp.assign(end=np.minimum(tmp.end_x, tmp.end_y))[['start', 'end']]
tmp = tmp[tmp['start'] <= tmp['end']]

# merge c
tmp = pd.concat([pd.merge_asof(tmp, dfc, on='start'),
                 pd.merge_asof(dfc, tmp, on='start')]
                ).sort_values('start').dropna()

tmp = tmp.assign(end=np.minimum(tmp.end_x, tmp.end_y))[['start', 'end']]
tmp = tmp[tmp['start'] <= tmp['end']]

正如预期的那样：

                start                 end
0 2018-02-03 15:16:30 2018-02-03 17:06:30
1 2018-02-05 10:30:30 2018-02-05 10:32:30