Merge two dataframes on multiple keys with tolerance using merge

We wish to find the best matches out of multiple keys in two dataframes. From the documentation the method merge_asof seemed to be a reasonable choice. Testing it for one column it worked as expected.

import pandas as pd
import numpy as np
data_key_1 = np.arange(10).astype(float)
data_key_2 = np.arange(10).astype(float)
data_key_1_noisy = data_key_1-0.25*np.random.rand(10)
data_key_2_noisy = data_key_2-0.1*np.random.rand(10)
data_target = list('abcdefghij')

# one key approach
df_1 = pd.DataFrame(zip(data_key_1[::2], ), columns=['key_1',])
df_2 = pd.DataFrame(zip(data_key_1_noisy, data_target), columns=['key_1', 'target',])
df_result_1 = pd.merge_asof(df_1, df_2, on='key_1', direction='nearest', tolerance=0.5)
print(df_result_1)

With console output as follow.

   key_1 target
0    0.0      a
1    2.0      c
2    4.0      e
3    6.0      g
4    8.0      i

When trying to use two keys, it failed. We tried different combinations of keyword settings but didn't manage to get it running.

# two keys approach
df_1 = pd.DataFrame(zip(data_key_1[::2], data_key_2[::2]), columns=['key_1', 'key2'])
df_2 = pd.DataFrame(zip(data_key_1_noisy, data_key_2_noisy, data_target), columns=['key_1', 'key2', 'target'])
df_result_2 = pd.merge_asof(df_1, df_2, on=['key_1', 'key_2'], direction='nearest', tolerance=0.5)
print(df_result_2)

It will throw an error:

pandas.errors.MergeError: can only asof on a key for left

Expected console output would have been.

   key_1  key2 target
0    0.0   0.0      a
1    2.0   2.0      c
2    4.0   4.0      e
3    6.0   6.0      g
4    8.0   8.0      i

So we questioned ourselves whether we try to apply this method in an inappropriate context, or if it is an applicable use-case and we just messed up with the keywords.

CodePudding user response：

merge_asof is always one key only (think why it requires key to be sorted). You can merge on one key and filter the other key.

(pd.merge_asof(df_1, df_2, on='key_1', direction='nearest', tolerance=0.5, suffixes=['','_'])
   # may need to drop duplicate on `key_1, key2` here based on `abs` as well
   .loc[lambda x: x['key2'].sub(x['key2_']).abs() < 0.5]
   .drop(columns=['key2_'])  
)

Output:

   key_1  key2 target
0    0.0   0.0      a
1    2.0   2.0      c
2    4.0   4.0      e
3    6.0   6.0      g
4    8.0   8.0      i

CodePudding user response：

I believe you can also use KDTree to do this

from scipy.spatial import cKDTree
tree1 = cKDTree(df_1[["key_1", "key2"]])
tree2 = cKDTree(df_2[["key_1", "key2"]])

df_1['target'] = [ df_2.loc[i[0], 'target'] if i else np.nan for i in tree1.query_ball_tree (tree2, r=0.5)]

   key_1  key2 target
0    0.0   0.0      a
1    2.0   2.0      c
2    4.0   4.0      e
3    6.0   6.0      g
4    8.0   8.0      i