最近的记录和两个数据帧中每个记录之间的对应距离

Question

假设我有两个DataFrame：XA和XB，例如每个都有3行和2列：

import pandas as pd

XA = pd.DataFrame({
    'x1': [1, 2, 3],
    'x2': [4, 5, 6]
})

XB = pd.DataFrame({
    'x1': [8, 7, 6],
    'x2': [5, 4, 3]
})

对于XA中的每个记录，我希望在XB中找到最近的记录（例如基于欧几里德距离），以及相应的距离。例如，这可能返回一个在id_A上索引的DataFrame，并带有id_B和distance列

我如何才能最有效地做到这一点

Answer 1

修改this answer以避免使用全距离矩阵，您可以在XA（nearest_record1()）中找到每一行最近的记录和距离，然后调用apply在每一行（nearest_record()）上遍历它。这在test中将运行时间缩短了约85%

from scipy.spatial.distance import cdist

def nearest_record1(XA1, XB):
    """Get the nearest record between XA1 and XB.

    Args:
        XA: Series.
        XB: DataFrame.

    Returns:
        DataFrame with columns for id_B (from XB) and dist.
    """
    dist = cdist(XA1.values.reshape(1, -1), XB)[0]
    return pd.Series({'dist': np.amin(dist), 'id_B': np.argmin(dist)})

def nearest_record(XA, XB):
    """Get the nearest record in XA for each record in XB.

    Args:
        XA: DataFrame. Each record is matched against the nearest in XB.
        XB: DataFrame.

    Returns:
        DataFrame with columns for id_A (from XA), id_B (from XB), and dist.
        Each id_A maps to a single id_B, which is the nearest record from XB.
    """
    res = XA.apply(lambda x: nearest_record1(x, XB), axis=1)
    res['id_A'] = XA.index
    # id_B is sometimes returned as an object.
    res['id_B'] = res.id_B.astype(int)
    # Reorder columns.
    return res[['id_A', 'id_B', 'dist']]

这也会返回正确的结果：

nearest_record(XA, XB)
    id_A    id_B        dist
0      0       2    5.099020
1      1       2    4.472136
2      2       2    4.242641