Matching IDs Between Pandas DataFrames and Applying Function

If ID isn't the index, make it so.

df_B.set_index('ID', inplace=True)df_A.set_index('ID', inplace=True)df_B['dist'] = ((df_A - df_B) ** 2).sum(1) ** .5

Since index and columns are already aligned, simply doing the math should just work.

Solution which uses sklearn.metrics.pairwise.paired_distances method:

In [73]: AOut[73]:    x  yIDa   0  0c   3  2b   2  5In [74]: BOut[74]:    x  yIDa   2  1c   3  5b   1  2In [75]: from sklearn.metrics.pairwise import paired_distancesIn [76]: B['dist'] = paired_distances(B, A)In [77]: BOut[77]:    x  y      distIDa   2  1  2.236068c   3  5  3.000000b   1  2  3.162278

For performance, you might want to work with NumPy arrays and for euclidean distance computations between corresponding rows, np.einsum would be do it pretty efficiently.

Incorporating the fixing of rows to make them aligned, here's an implementation -

# Get sorted row indices for dataframe-Asidx = df_A.index.argsort()idx = sidx[df_A.index.searchsorted(df_B.index,sorter=sidx)]# Sort A rows accordingly and get the elementwise differences against Bs = df_A.values[idx] - df_B.values# Use einsum to square and sum each row and finally sqrt for distancesdf_B['dist'] = np.sqrt(np.einsum('ij,ij->i',s,s))

Sample input, output -

In [121]: df_AOut[121]:    0  1a  0  0c  3  2b  2  5In [122]: df_BOut[122]:    0  1c  3  5a  2  1b  1  2In [124]: df_B  # After code runOut[124]:    0  1      distc  3  5  3.000000a  2  1  2.236068b  1  2  3.162278

Here's a runtime test comparing einsum against few other counterparts.