时间戳

首页 > 解决方案 > Pandas merge_asof 不想在 pd.Timedelta 上合并，给出错误“必须与 int64 类型兼容”

python - Pandas merge_asof 不想在 pd.Timedelta 上合并，给出错误“必须与 int64 类型兼容”

问题描述

我正在尝试合并以下文件

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 容差可以指定为 pd.Timedelta。但是当我运行上面的代码时，我得到了

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

我如何解决它？

谢谢

上述示例的预期连接 vals 输出：

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

标签： pythonpandas

解决方案

使用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

推荐阅读