3

I have two pandas DataFrames with Address, Lat and Long as columns. I'd like to find rows that are duplicates between the 2 dataframes based on the intersection of their coordinates. I'll define the logic for intersection as two points that within 50ft of each other.

import pandas as pd

df1 = pd.DataFrame({'addr': ['NBC Tower, 455 N Cityfront Plaza Dr #2700, Chicago, IL 60611, United States','340 E North Water St, Chicago, IL 60611, United States'],
                    'Lat': [41.890100, 41.889680],
                    'Lon': [-87.621150, -87.618790]
                   })

df1 = pd.DataFrame({'addr': ['455 N Cityfront Plaza Dr #2900, Chicago, IL 60611, United States','255 E Grand Ave, Chicago, IL 60611, United States'],
                    'Lat': [41.890100, 41.891392],
                    'Lon': [-87.621150, -87.621323] 
                   })

If the intersection between the coordinates is less than 50ft, then insert match, otherwise insert No match found.

Would prefer to have a function that find the intersection and use .apply. Something like below:

def find_geomatch(args...):

    if match:

      txt = 'match'
 
    else:
   
      txt = 'No match found'

    return txt

df1['geomatch'] = df1.apply(lambda x: geomatch(x), axis=1)
1
  • You probably meant df2 = ... for '455 N Cityfront Plaza Dr #2900, Chicago, IL 60611, United States','255 E Grand Ave, Chicago, IL 60611, United States' ?
    – Taras
    Mar 21 at 7:19

1 Answer 1

3

You can add geometry columns with units in meters (or feet), cross join the two data frames, and find the rows where distance is less than some threshold:

import pandas as pd
import geopandas as gpd

df1 = pd.DataFrame({'addr': ['NBC Tower, 455 N Cityfront Plaza Dr #2700, Chicago, IL 60611, United States','340 E North Water St, Chicago, IL 60611, United States'],
                    'Lat': [41.890100, 41.889680],
                    'Lon': [-87.621150, -87.618790],
                    'id' : [1,2]
                   })

df2 = pd.DataFrame({'addr': ['455 N Cityfront Plaza Dr #2900, Chicago, IL 60611, United States','255 E Grand Ave, Chicago, IL 60611, United States'],
                    'Lat': [41.890100, 41.891392],
                    'Lon': [-87.621150, -87.621323],
                    'id' : ['a','b']
                   })

gdf1 = gpd.GeoDataFrame(df1, geometry=gpd.points_from_xy(x=df1.Lon, y=df1.Lat)) #You should be able to set crs here, but I cant get it to work
gdf2 = gpd.GeoDataFrame(df2, geometry=gpd.points_from_xy(x=df2.Lon, y=df2.Lat))
gdf1.crs = "EPSG:4326"
gdf2.crs = "EPSG:4326"

gdf1['geometry_in_m'] = gdf1.geometry.to_crs("EPSG:26916") #Create an additional geometry column with crs in meters
gdf2['geometry_in_m'] = gdf2.geometry.to_crs("EPSG:26916")

#Cross join the dataframes https://stackoverflow.com/questions/34161978/pandas-two-dataframe-cross-join
gdf1['key']=0
df2['key']=0
gdf3 = gdf1.merge(gdf2, on='key', how='outer')

def givedist(row):
   """ Returns the distance between two geometries """
    return row['geometry_in_m_x'].distance(row['geometry_in_m_y'])

gdf3['distance'] = gdf3.apply(givedist, axis=1)

gdf3.loc[gdf3.distance<1][['id_x','id_y']] #Select the rows where distance is less than 1 m

#   id_x id_y
#0     1    a

#So id 1 in gdf1 is a duplicate to "a" in gdf2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.