需求

• 有两份数据A和B，都包含cuid和时间戳
• A和B都是用户的操作行为相关数据，希望通过时间维度左右5s或者更短，对A和B做拼接

数据

用户行为表

检索日志

思路

• 以用户行为表为左表，检索日志作为右表进行join操作。
• join过程中，以5s中为最大时间间隔，找右表中最近的记录进行拼接

代码实现

    real_data = pd.DataFrame(real_raw)
    real_data.columns = ['cuid', 'tm', 'info', 'real_tm', 'busid', 'o_order', 'd_order', 'order_diff', 'min_dis', 'dist_2_first', 'speed', 'real_format_time']
    real_data[['real_tm', 'val']] = real_data[['real_tm', 'val']].astype('int')
    real_data['datetime'] = pd.to_datetime(real_data['real_tm'], unit='s')
    real_data.sort_values(by=['datetime'], inplace=True)

    sample_data = pd.DataFrame(sample_raw)
    sample_data.columns = ['cuid', 'tm', 'info']
    sample_data['datetime'] = pd.to_datetime(sample_data.tm, origin='unix', unit='s')
    sample_data.sort_values(by=['datetime'], inplace=True)

    # converting this to the index so we can preserve the date_start_time columns so you can validate the merging logic
    real_data.index = real_data['datetime']
    sample_data.index = sample_data['datetime']

    # the magic happens below, check the direction and tolerance arguments
    tol = pd.Timedelta(seconds=300)
    data = pd.merge_asof(left=sample_data, right=real_data, right_index=True,
            left_index=True, direction='nearest', tolerance=tol, by=['o', 'd'])

效果