Pandas按id分组根据时间区间条件合并两个DataFrame的实现方法

推荐使用pandas.merge_asof实现，该方法性能远高于按ID关联后过滤的方案，尤其适合数据量较大的场景。

完整可运行代码如下：

import pandas as pd
import numpy as np

# 构造第一个DataFrame df1
data1 = {'process': ['buying','selling','searching','repairing', 'preparing', 'selling','buying', 'searching', 'selling','searching'],
        'type': ['in_progress','in_progress','end','in_progress', 'end', 'in_progress','in_progress', 'end', 'in_progress','end'],
        'country': ['usa',np.nan, 'usa','ghana', 'ghana','ghana','portugal', 'portugal', 'portugal','england'],
        'id': ['022','022','022', '011','011', '011','011', '011', '011','011'],
        'created': ['2021-07-01','2021-07-03','2021-07-04','2021-07-05','2021-07-09','2021-07-10','2021-07-15','2021-07-17','2021-07-19','2021-07-21']        
}
df1 = pd.DataFrame(data1, columns = ['process', 'type', 'country', 'id', 'created'])
df1['next_created'] = df1.sort_values('created').groupby('id')['created'].shift(-1)

# 构造第二个DataFrame df2
data2 = {'id': ['022','022','022', '011','011', '011','011', '011', '011','011'],
        'product': ['apple','orange','watermelon', 'qiwi','pear', 'cherry','apple', 'qiwi', 'cherry','orange'],
        'created': ['2021-07-02','2021-07-06','2021-07-07','2021-07-11','2021-07-12','2021-07-13','2021-07-16','2021-07-20','2021-07-25','2021-07-26']        
}
df2 = pd.DataFrame(data2, columns = ['product', 'id','created'])

# 1. 日期列转为datetime类型，避免字符串比较错误
df1['created'] = pd.to_datetime(df1['created'])
df1['next_created'] = pd.to_datetime(df1['next_created'])
df2['created'] = pd.to_datetime(df2['created'])

# 2. 重命名df1的时间列避免合并后冲突
df1 = df1.rename(columns={'created': 'created_start'})

# 3. 必须按关联ID和时间列排序，merge_asof要求输入数据有序
df1 = df1.sort_values(['id', 'created_start']).reset_index(drop=True)
df2 = df2.sort_values(['id', 'created']).reset_index(drop=True)

# 4. 按ID分组匹配满足created >= created_start的最近df1记录
res = pd.merge_asof(
    df2, 
    df1,
    by='id',
    left_on='created',
    right_on='created_start',
    direction='backward'
)

# 5. 过滤满足created < next_created的记录，即符合区间要求的结果
res = res[res['created'] < res['next_created']].reset_index(drop=True)

# 若需要调整列顺序可自行修改，示例：
res = res[['process', 'type', 'country', 'id', 'created_start', 'next_created', 'product', 'created']]

如果你的数据量很小，也可以用更简单的笛卡尔积过滤方案：

# 小数据量简化方案，不适合单ID对应记录超过1000条的场景
merged = df1.merge(df2, on='id', suffixes=('_df1', '_df2'))
res = merged[(merged['created_df1'] < merged['created_df2']) & (merged['created_df2'] < merged['next_created'])].reset_index(drop=True)

内容的提问来源于stack exchange，提问作者user15920209