Joining two Pandas DataFrames based on lat, long as fields

Question

I have two Pandas DataFrames containing "lat" and "long" coordinates. I'd like to do a spatial join and merge columns from one DataFrame into another.

import pandas as pd

df1 = pd.DataFrame(data={
       'name': ['post', 'sutter', 'oak'],
       'Lat': [37.788151, 37.789551, 37.815730],
       'Long': [-122.407570, -122.408302, -122.288810]
      })


df2 = pd.DataFrame(data={
       'id': [0, 1, 2],
       'col1': ['xx','yy','zz'],
       'Lat': [37.787994, 37.789575, 37.813122],
       'Long': [-122.407419, -122.408312, -122.288810]
      })

When a match is found based on the "lat", "long" coordinates, the join / output would look like this:

name   Lat        Long         col1
post   37.788151  -122.407570  xx
sutter 37.789551  -122.408302  NaN
oak    37.815730  -122.288810  NaN

Open to ideas on how to implement this solution? Spatial joins or maybe using reverse geocoding API to get addresses from "Lat" "Long" and then join on them?

Answer 1

1. Create Pandas DataFrame
2. Create GeoPandas DataFrame using #1
3. Create Buffer for Points
4. sjoin both GeoDataFrame

df1 = pd.DataFrame({
       'name': ['post', 'sutter', 'oak'],
       'Lat': [37.788151, 37.789551, 37.815730],
       'Long': [-122.407570, -122.408302, -122.288810]
      })
df2 = pd.DataFrame({
       'id': [0, 1, 2],
       'col1': ['xx','yy','zz'],
       'Lat': [37.787994, 37.789575, 37.813122],
       'Long': [-122.407419, -122.408312, -122.288810]
      })
gdf1 = gpd.GeoDataFrame(
    df1, geometry=gpd.points_from_xy(df1['Long'], df1['Lat']))

gdf2 = gpd.GeoDataFrame(
    df2, geometry=gpd.points_from_xy(df2['Long'], df2['Lat']))

gdf2['geometry'] = gdf2.geometry.buffer(0.001)

gdf1.sjoin(gdf2, how="left") 

Note that the join is completely dependent on your buffer size. Make sure to tune according to your needs.

Working copy can be found here

Answer 2

Another solution that was already mentioned by OP is to use the reverse geocoding. There might be a problem about that, the result quality will be strongly dependable on the decoder.

Here the Nominatim geocoder (free to choose) from the GeoPy geocoding Python library was used, for more details, please check the documentation. Also, coordinates of point features should be transmitted as a pair, otherwise, you may get this error ValueError: Must be a coordinate pair or Point. Therefore from geopy.point import Point was additionally imported.

When using this code:

import numpy as np
import pandas as pd
from geopy.geocoders import Nominatim
from geopy.point import Point

geolocator = Nominatim(user_agent="test")

def reverse_geocoding(lat, lon):
    try:
        location = geolocator.reverse(Point(lat, lon))
        return location.raw['place_id']
    except:
        return None

df1 = pd.DataFrame(data={
       'name': ['post', 'sutter', 'oak'],
       'Lat': [37.788151, 37.789551, 37.815730],
       'Long': [-122.407570, -122.408302, -122.288810]
      })


df2 = pd.DataFrame(data={
       'id': [0, 1, 2],
       'col1': ['xx','yy','zz'],
       'Lat': [37.787994, 37.789575, 37.813122],
       'Long': [-122.407419, -122.408312, -122.288810]
      })

df1['address'] = np.vectorize(reverse_geocoding)(df1['Lat'], df1['Long'])

df2['address'] = np.vectorize(reverse_geocoding)(df2['Lat'], df2['Long'])

result = pd.merge(df1, df2, how='left', left_on='address', right_on='address')

print(result)

it will result in this

     name      Lat_x      Long_x    address   id col1      Lat_y      Long_y
0    post  37.788151 -122.407570  127113751  NaN  NaN        NaN         NaN
1  sutter  37.789551 -122.408302  110481100  1.0   yy  37.789575 -122.408312
2     oak  37.815730 -122.288810  114898877  NaN  NaN        NaN         NaN

Note: the join was done by "place_id" attribute which is different to "osm_id".