Find closest point in Pandas DataFrames

Question

I am quite new to Python. I have the following table in Postgres. These are Polygon values with four coordinates with same Id with ZONE name I have stored this data in Python dataframe called df1

Id  Order   Lat              Lon            Zone
00001   1   50.6373473  3.075029928          A
00001   2   50.63740441 3.075068636          A
00001   3   50.63744285 3.074951754          A 
00001   4   50.63737839 3.074913884          A 
00002   1   50.6376054  3.0750528            B
00002   2   50.6375896  3.0751209            B
00002   3   50.6374239  3.0750246            B
00002   4   50.6374404  3.0749554            B

I have Json data with Lon and Lat values and I have stored them is python dataframe called df2.

Lat                  Lon
50.6375524099   3.07507914474
50.6375714407   3.07508201591

My task is to compare df2 Lat and Lon values with four coordinates of each zone in df1 to extract the zone name and add it to df2.

For instance (50.637552409 3.07507914474) belongs to Zone B.

#This is ID with Zone
df1 = pd.read_sql_query("""SELECT * from "zmap" """,con=engine)
#This is with lat,lon values
df2 = pd.read_sql_query("""SELECT * from "E1" """,con=engine)
df2['latlon'] = zip(df2.lat, df2.lon)
zones = [
["A", [[50.637347297, 3.075029928], [50.637404408, 3.075068636], [50.637442847, 3.074951754],[50.637378390, 3.074913884]]]]
for i in range(0, len(zones)):  # for each zone points
    X = mplPath.Path(np.array(zones[i][1]))
    # find if points are Zones
    Y= X.contains_points(df2.latlon.values.tolist())
    # Label points that are in the current zone
    df2[Y, 'zone'] = zones[i][0]

Currently I have done it manually for Zone 'A'. I need to generate the "Zones" for the coordinates in df2.

Sorry, SO is not a tutorial or free code writing site. Please show your attempts first. — Julien
– Julien, Commented Aug 16, 2016 at 2:28

Community · Accepted Answer · 2017-04-13 12:40:36Z

14

This sounds like a good use case for scipy cdist, also discussed here.

import pandas as pd
from scipy.spatial.distance import cdist


data1 = {'Lat': pd.Series([50.6373473,50.63740441,50.63744285,50.63737839,50.6376054,50.6375896,50.6374239,50.6374404]),
         'Lon': pd.Series([3.075029928,3.075068636,3.074951754,3.074913884,3.0750528,3.0751209,3.0750246,3.0749554]),
         'Zone': pd.Series(['A','A','A','A','B','B','B','B'])}

data2 = {'Lat': pd.Series([50.6375524099,50.6375714407]),
         'Lon': pd.Series([3.07507914474,3.07508201591])}


def closest_point(point, points):
    """ Find closest point from a list of points. """
    return points[cdist([point], points).argmin()]

def match_value(df, col1, x, col2):
    """ Match value x from col1 row to value in col2. """
    return df[df[col1] == x][col2].values[0]


df1 = pd.DataFrame(data1)
df2 = pd.DataFrame(data2)

df1['point'] = [(x, y) for x,y in zip(df1['Lat'], df1['Lon'])]
df2['point'] = [(x, y) for x,y in zip(df2['Lat'], df2['Lon'])]

df2['closest'] = [closest_point(x, list(df1['point'])) for x in df2['point']]
df2['zone'] = [match_value(df1, 'point', x, 'Zone') for x in df2['closest']]

print(df2)
#    Lat        Lon       point                           closest                  zone
# 0  50.637552  3.075079  (50.6375524099, 3.07507914474)  (50.6375896, 3.0751209)  B
# 1  50.637571  3.075082  (50.6375714407, 3.07508201591)  (50.6375896, 3.0751209)  B

edited Apr 13, 2017 at 12:40

CommunityBot

11 silver badge

answered Sep 4, 2016 at 15:57

brennan

3,50328 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Pad Over a year ago

Thanks for this excellent answer - it's really helped me :)

Chris Decker Over a year ago

The logic for match_value is very expensive. More efficient to use merge, i.e. df2.merge(df1[['point','zone']].rename(columns={'point':'closest'})

Seyed Omid Nabavi Over a year ago

That is fabulous

padu Over a year ago

why doesn't anyone get confused by the fact that cdist defaults to a euclidian metric which is not suitable for longitude and width coordinates? And this implementation is extremely inefficient

Haleemur Ali · Accepted Answer · 2022-11-29 17:08:17Z

note that the current title of the post Find closest point in Pandas DataFrames but OP's attempt shows that they are looking for the zone within which a point is found.

It is possible to leverage the geopandas library to do this operation elegantly & efficiently.

Convert the DataFrame into a GeoDataFrame.

Then aggregate the points in df1 to create a polygon. The aggregation operation is called dissolve.

Finally, use a spatial join sjoin with the predicate such that points in df2 are covered by the polygon representing a Zone in zones and output the Lat, Lon&Zone` columns.

# set up
import pandas as pd
import geopandas as gpd

df1 = pd.DataFrame({
  'Id': [1, 1, 1, 1, 2, 2, 2, 2],
  'Order': [1, 2, 3, 4, 1, 2, 3, 4],
  'Lat': [50.6373473, 50.63740441, 50.63744285, 50.63737839, 50.6376054, 50.6375896, 50.6374239, 50.6374404], 
  'Lon': [3.075029928, 3.075068636, 3.074951754, 3.074913884, 3.0750528, 3.0751209, 3.0750246, 3.0749554],
 'Zone': ['A', 'A', 'A', 'A', 'B', 'B', 'B', 'B']
})

df2 = pd.DataFrame({
  'Lat': [50.6375524099, 50.6375714407],
  'Lon': [3.07507914474, 3.07508201591] 
})

# convert to GeoDataFrame
df1 = gpd.GeoDataFrame(df1, geometry=gpd.points_from_xy(df1.Lon, df1.Lat))
df2 = gpd.GeoDataFrame(df2, geometry=gpd.points_from_xy(df2.Lon, df2.Lat))

# aggregate & merge
zones = df1.dissolve(by='Zone').convex_hull.rename('geometry').reset_index()
merged = df2.sjoin(zones, how='left', predicate='covered_by')

# output
output_columns = ['Lat', 'Lon', 'Zone']
merged[output_columns]

this outputs:

         Lat       Lon Zone
0  50.637552  3.075079    B
1  50.637571  3.075082    B

Collectives™ on Stack Overflow

Find closest point in Pandas DataFrames

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related