python - Pandas merge_asof 不想在 pd.Timedelta 上合并，给出错误 "must be compat with type int64"

Question

我正在尝试合并以下文件

df1

unix_time,hk1,hk2,val2,hint
1560752700,10,15,3,6:25am
1560753900,20,25,5,6:45am
1560756600,10,10,-1,7:30am

df2

unix_time,hk1,hk2,val,hint
1560751200,10,15,1,6am
1560754800,20,25,2,7am
1560758400,10,10,3,8am

unix_time

我正在尝试按如下方式执行此操作

merged = pd.merge_asof(df2.sort_values('unix_time'),
              df1.sort_values('unix_time'),
              by=['hk1', 'hk2'],
              on='unix_time',
              tolerance=pd.Timedelta(seconds=1800),
              direction='nearest')

从文档 merge_asof tolerance 可以指定为 pd.Timedelta。但是当我运行上面的代码时，我得到了

pandas.errors.MergeError: incompatible tolerance <class 'pandas._libs.tslibs.timedeltas.Timedelta'>, must be compat with type int64

我该如何解决？

谢谢

上述示例的预期连接值输出:

val | val2
1   | 3
2   | 5
3   | -1

Answer 1

使用 tolerance=1800:

merged = pd.merge_asof(df2.sort_values('unix_time'),
              df1.sort_values('unix_time'),
              by=['hk1', 'hk2'],
              on='unix_time',
              tolerance=1800,
              direction='nearest')
print (merged)
    unix_time  hk1  hk2  val hint_x  val2  hint_y
0  1560751200   10   15    1    6am     3  6:25am
1  1560754800   20   25    2    7am     5  6:45am
2  1560758400   10   10    3    8am    -1  7:30am

如果想使用您的解决方案，或者在 merge_asof 之前将两列都转换为日期时间:

df1['unix_time'] = pd.to_datetime(df1['unix_time'], unit='s')
df2['unix_time'] = pd.to_datetime(df2['unix_time'], unit='s')

merged = pd.merge_asof(df2.sort_values('unix_time'),
              df1.sort_values('unix_time'),
              by=['hk1', 'hk2'],
              on='unix_time',
              tolerance=pd.Timedelta(seconds=1800),
              direction='nearest')

print (merged)
            unix_time  hk1  hk2  val hint_x  val2  hint_y
0 2019-06-17 06:00:00   10   15    1    6am     3  6:25am
1 2019-06-17 07:00:00   20   25    2    7am     5  6:45am
2 2019-06-17 08:00:00   10   10    3    8am    -1  7:30am