将事件日志映射到中的间隔日志

Question

我有两个表，一个事件日志和一个间隔日志。你知道吗

import pandas as pd
interval_log = {'interval_id':['AG21234', 'AG21236', 'AS21234'], 
           'start_utc':['2016-01-01 10:30:50', '2016-03-20 09:31:50', '2016-06-20 21:12:30'], 
           'end_utc':['2016-01-02 12:41:00', '2016-03-20 10:12:32', '2016-06-20 23:32:21']}
interval_table = pd.DataFrame(interval_log)

event_log = {'event_id':[12334,13242, 43251, 32123, 43213, 43215, 34221],
         'timestamp':['2016-01-01 10:30:51', '2016-01-01 10:31:51', '2016-01-01 10:35:51',
                      '2016-03-20 09:35:12', '2016-03-20 09:40:23', 
                      '2016-06-20 22:10:30', '2016-06-20 22:21:12'],
        'event_class':['A', 'B', 'A', 'C', 'B', 'D', 'A'],
        'event_cause':['A1', 'B1', 'A2', 'C4', 'B34', 'D32', 'A12']    }
event_table = pd.DataFrame(event_log)

interval_table.start_utc = pd.to_datetime(interval_table.start_utc)
interval_table.end_utc = pd.to_datetime(interval_table.end_utc)
event_table.timestamp = pd.to_datetime(event_table.timestamp)

我想做的是将interval_id添加到event_log中的每个条目中 当前的工作代码是：

result = []
for counter, interval in interval_table.iterrows():
    subset = event_table[(event_table.timestamp > interval.start_utc) & 
                   (event_table.timestamp < interval.end_utc) ]
    subset['interval_id'] = interval.interval_id
    result.append([subset])

它适用于这种简单的情况，但在实际的数据集上，事件日志约为900万行，间隔约为300万行，它的运行效率非常低。你知道吗

最好的解决方案是什么？你知道吗

Answer 1

我刚刚了解了merge_asof，而from the docs就是这样做的：

For each row in the left DataFrame, we select the last row in the right DataFrame whose ‘on’ key is less than or equal to the left’s key. Both DataFrames must be sorted by the key.

两个数据帧都必须按其键排序。你知道吗

pd.merge_asof(event_table, interval_table, left_on='timestamp', right_on='start_utc')

Answer 2

使用0.19^{}

pd.merge_asof(
    event_table, interval_table,
    left_on='timestamp', right_on='start_utc'
).drop(['end_utc', 'start_utc'], 1)