Distance between points in two data frames

Question

I'm new to python and I tried searching but could not find a solution.

I have two dataframes with Cartesian coordinates.

node    x      y    value
abc    645    714   8 
def    187    754   11 

location  x    y    value
ijk      621  744   1 
lmn      202  720   -5

I want to find the minimum distance of each location in df2 with nodes in df1 and update "value" in df1.

I have seen cdist to calculate the minimum distance, but how do I link that back to the corresponding? node

Since ijk is close to abc and lmn to def, the final answer should be

node    x      y    value
abc    645    714   9 
def    187    754   6

Something with `scipy.spatial.distance_matrix`. What is your expected output? — Quang Hoang, Apr 13 '20 at 16:33
@QuangHoang ijk is close to abc. In the end I need to add the value of ijk to that of abc, so that it becomes 9 — User8547236, Apr 13 '20 at 16:40

score 0 · Answer 1 · answered Apr 13 '20 at 19:04

Here's a way to do it from scratch:

import numpy as np
import pandas as pd

df1 = pd.DataFrame({'node': ['abc', 'def'], 
                    'x': [645, 187], 
                    'y': [714, 754], 
                    'value': [8, 11]}) 
df2 = pd.DataFrame({'location': ['ijk', 'lmn'], 
                    'x': [621,  202], 
                    'y': [744, 720], 
                    'value': [1, -5]}) 

# compute Euclidean distances (using Pythagoras' theorem)
df2['distances'] = [[((df1['x'][j] - df2['x'][i]) ** 2 
                    + (df1['y'][j] - df2['y'][i]) ** 2) ** 0.5 
                     for j in range(len(df1))] 
                    for i in range(len(df2))]
df2

    location  x     y      value  distances
0   ijk       621   744     1     [38.41874542459709, 434.11519208615584]
1   lmn       202   720    -5     [443.0406301909566, 37.16180835212409]

df1['new_value'] = [df1['value'][row] + df2['value'][np.argmin(df2['distances'][row])] 
                    for row in range(len(df1))] 
df1

    node    x       y      value  new_value
0   abc     645     714     8     9
1   def     187     754    11     6

Distance between points in two data frames

1 Answers1