3
$\begingroup$

Given a main left dataset, how can I merge with right dataset with smallest Euclidean distance (d = sqrt(a^2 + b^2)) on specified columns?

Details:

  • if both of key1 and key2 from left exist in right, merge with row that matches key1 and key2, and has smallest value from sqrt((aux1r - aux1l)^2 + (aux2r - aux2l)^2)
  • if both of key1 and key2 are not NaN and either of key1 and key2 from left does not exist in right, merge with row that has smallest value from sqrt((aux1r - aux1l)^2 + (aux2r - aux2l)^2)
  • if one of key1 or key2 from left is NaN, merge with row from right that matches the non-NaN key1 or key2, and has smallest value from sqrt((aux1r - aux1l)^2 + (aux2r - aux2l)^2)
  • if both of key1 and key2 from left is NaN, merge with row from right that has smallest value from sqrt((aux1r - aux1l)^2 + (aux2r - aux2l)^2)

Example of input dataframes, and wanted dataframe after merge:

import pandas as pd
import numpy as np

left = pd.DataFrame([
    #  "key1"      "key2"        "aux1l" "aux2l" "left1"        "left2"
    [np.nan,       np.nan,       1.00,   1.00,   "unimportant", "unimportant"],  # left[0]
    ["key1",       np.nan,       1.00,   1.00,   "unimportant", "unimportant"],  # left[1]
    [np.nan,       "key2",       1.00,   1.00,   "unimportant", "unimportant"],  # left[2]
    ["key1",       "key2",       1.00,   1.00,   "unimportant", "unimportant"],  # left[3]
    ["key1unique", "key2unique", 1.00,   1.00,   "unimportant", "unimportant"],  # left[4]
    ["key1repeat", "key2repeat", 1.00,   1.00,   "unimportant", "unimportant"],  # left[5]
    ["key1repeat", "key2repeat", 1.00,   1.00,   "unimportant", "unimportant"],  # left[6]
], columns=["key1", "key2", "aux1l", "aux2l", "left1", "left2"])

right = pd.DataFrame([
    # "key1"       "key2"        "aux1r"  "aux2r"  "right1"       "right2"
    [np.nan,       "key2",       0.99,    0.97,    "unimportant", "unimportant"],
    ["key1",       "key2",       0.99,    0.96,    "unimportant", "unimportant"],
    ["key1repeat", "key2repeat", 1.85,    1.56,    "unimportant", "unimportant"],
    ["key1repeat", "key2repeat", 0.99,    0.99,    "unimportant", "unimportant"],
], columns=["key1", "key2", "aux1r", "aux2r", "right1", "right2"])

# what to do here?
# left.merge(right) discards left with no matches (left[4] discarded, but want to fill with closest match with aux1l/aux2l with aux1r/aux2r)

# it does not matter if aux1r and aux2r is included
wanted = pd.DataFrame([
    # "key1"       "key2"        "auxl1"  "aux2l" "left1"       "left2"        "right1"       "right2"
    [np.nan,       np.nan,       1.00,   1.00,   "unimportant", "unimportant", "unimportant", "unimportant"],
    ["key1",       np.nan,       1.00,   1.00,   "unimportant", "unimportant", "unimportant", "unimportant"],
    [np.nan,       "key2",       1.00,   1.00,   "unimportant", "unimportant", "unimportant", "unimportant"],
    ["key1",       "key2",       1.00,   1.00,   "unimportant", "unimportant", "unimportant", "unimportant"],
    ["key1unique", "key2unique", 1.00,   1.00,   "unimportant", "unimportant", "unimportant", "unimportant"],
    ["key1repeat", "key2repeat", 1.00,   1.00,   "unimportant", "unimportant", "unimportant", "unimportant"],
    ["key1repeat", "key2repeat", 1.00,   1.00,   "unimportant", "unimportant", "unimportant", "unimportant"],
], columns=["key1", "key2", "aux1l", "aux2l", "left1", "left2", "right1", "right2"])
$\endgroup$

1 Answer 1

1
$\begingroup$

Let's simplify the requirements

A wanted row from the right dataframe is a row that

  1. has the least amount of unmatched keys
  2. has the minimum euclidean distance
def merge_left_and_right(left, right):
    # save original input from modification
    left = left.copy()
    right = right.copy()
    
    # numerate rows in the right dataframe
    right['row_num'] = range(len(right))

    # find the best matchings for left dataframe
    left['row_num'] = [
        find_the_best_matching(right,
                               key1=row['key1'],
                               key2=row['key2'],
                               aux1l=row['aux1l'],
                               aux2l=row['aux2l'])
        for index, row in left.iterrows()
    ]
    
    # merge dataframe by row number
    right = right.drop(['key1', 'key2'], axis=1)
    merged = left.merge(right, on='row_num', how='left')
    merged = merged.drop('row_num', axis=1)
    
    return merged

def find_the_best_matching(right, key1, key2, aux1l, aux2l):
    right = right.copy()

    # keys match only when they aren't NaN and equal ("np.nan != np.nan" is True)
    right['unmatched_key_count'] = 0
    right['unmatched_key_count'] += (right['key1'] != key1).astype(int)
    right['unmatched_key_count'] += (right['key2'] != key2).astype(int)

    right['euclidean_distance'] = np.sqrt((right['aux1r'] - aux1l) ** 2 + (right['aux2r'] - aux2l) ** 2)
    
    # Sort by unmatched amount, then by distance. The first row will be best
    return right.sort_values(['unmatched_key_count', 'euclidean_distance']).iloc[0]['row_num']

enter image description here

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.