Pandas merge_asof does not want to merge on pd.Timedelta giving error "must be compat with type int64"

user_3849781 0
Viewed 2081

I am trying to merge the following files

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

on unix_time

I am trying to do this as follows

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')

From docs merge_asof tolerance can be specified as pd.Timedelta. But when I am running the above piece of code I get

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

How do I fix it?

Thank you

the expected join vals output for the above example:

val | val2
1   | 3
2   | 5
3   | -1
pandas python
1 Answers

Use 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

Or convert both columns to datetimes before merge_asof if want use your solution:

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
user_29010020
Related