Using pandas to join on multiple soft keys and multiple hard keys with different names

user_3849781 0
Viewed 28

Is it possible to use pandas to join on multiple soft keys e.g when we allow tolerance range for a match and multiple hard keys that are named differently in both tables?

It seems that pandas.merge_asof allows only to join on one soft key and does not allow to specify hard key names separately for left and right tables (in case they are differently named and renaming isn't easy to process).

Consider the following two datasets

table1:

soft keys: sk1, sk2

hard keys: x, y

sk1,sk2,x,y,val1
10,100,10,15,1
20,200,20,25,2
30,300,10,10,3

table2:

soft keys: sk1,sk2

hard keys: k1,k2

sk1,sk2,k1,k2,val2,x,y
15,110,10,15,3,1,1
23,230,20,25,5,2,2
34,330,10,10,-1,3,3

I would need something equivalent to 

soft_merge(t1, t2, left_by=["x","y"], right_by=["k1","k2"], on=[sk1, sk2], tolerance=[5,15])

to get output (showed vals only for clarity):

val1 | val2
1    | 3

I understand that instead of left_by and right_by for hard keys we can just use by and rename columns, but this might not be easily supportable by a system since other system components might rely on old namings. Is there any clean and nice way of achieving it without multiple naming-renaming? But the problem of joining on multiple soft keys still remain unclear ...

pandas
1 Answers

Implement the tolerances after an exact merge:

m = df1.merge(df2, left_on=["x","y"], right_on=["k1","k2"])
mask = (m.sk1_x - m.sk1_y).abs().le(5) & (m.sk2_x - m.sk2_y).abs().le(15)

m.loc[mask, ['val1', 'val2']]
#   val1  val2
#0     1     3

This doesn't ensure a 1:1 merge, and will give all combinations that achieve that tolerance. If you need the "nearest" match you will need to specify some distance formula and keep only the closest. Here I use the total absolute distance. Assuming val1 is a unique key:

m['dist'] = (m.sk1_x - m.sk1_y).abs() + (m.sk2_x - m.sk2_y).abs()
m.sort_values('dist').loc[mask].drop_duplicates('val1')
user_43333590
Related