Pandas :最近的合并

Question

我有两个数据框。我想在最近的时间点将第二个数据帧合并到第一个。

Example: (df1)

user    t   
Ajay    2020-05-05 18:00:00
Ajay    2020-05-05 20:00:00
Ajay    2020-05-06 17:15:00
Ajay    2020-05-06 20:30:00
Ajay    2020-05-06 21:15:00

Example: (df2)

user    t   flag
Ajay    2020-05-05 18:04:00 10
Ajay    2020-05-05 18:12:00 7
Ajay    2020-05-05 20:02:00 9
Ajay    2020-05-06 17:13:00 2
Ajay    2020-05-06 17:45:00 5
Ajay    2020-05-06 20:30:00 8
Ajay    2020-05-06 21:12:00 9

df2 应该在最近的时间戳上合并到 df1 上。根据我有限的理解，pandas 仅在精确值上合并。是否可以？

预期输出:

user    t    flag
Ajay    2020-05-05 18:00:00   10
Ajay    2020-05-05 20:00:00   9
Ajay    2020-05-06 17:15:00   2
Ajay    2020-05-06 20:30:00   8  
Ajay    2020-05-06 21:15:00   9

Answer 1

使用 merge_asof 与 direction='nearest'范围:

df1['t'] = pd.to_datetime(df1['t'])
df2['t'] = pd.to_datetime(df2['t'])

df = pd.merge_asof(df1, df2, on='t', direction='nearest')
print (df)
  user_x                   t user_y  flag
0   Ajay 2020-05-05 18:00:00   Ajay    10
1   Ajay 2020-05-05 20:00:00   Ajay     9
2   Ajay 2020-05-06 17:15:00   Ajay     2
3   Ajay 2020-05-06 20:30:00   Ajay     8
4   Ajay 2020-05-06 21:15:00   Ajay     9

如果需要也可以通过 User 合并添加参数 by :

df1['t'] = pd.to_datetime(df1['t'])
df2['t'] = pd.to_datetime(df2['t'])

df = pd.merge_asof(df1, df2, on='t', by='user', direction='nearest')
print (df)
   user                   t  flag
0  Ajay 2020-05-05 18:00:00    10
1  Ajay 2020-05-05 20:00:00     9
2  Ajay 2020-05-06 17:15:00     2
3  Ajay 2020-05-06 20:30:00     8
4  Ajay 2020-05-06 21:15:00     9