Trying to compare two data frames based on difference between latitude and longitude in each data frame

Question

I am trying to compare lat & long coordinates in two data frames. If the difference in latitude_fuze is < .01 latitude_air and if the difference in longitude_fuze is < .01 longitude_air, then I want to update the field df_result['Type'] to read 'Airport'. Basically, I have a DF with airport lat & long coordinates, and if these coordinates are very similar to the lat & long coordinates that I have in my business DF, I want to add a flag to the business DF to indicate that this is an airport.

Here is the code that I am testing.

lat1 = df_result['latitude_fuze']
lon1 = df_result['longitude_fuze']
lat2 = df_airports['latitude_air']
lon2 = df_airports['longitude_air']

fuze_rows=range(df_result.shape[0])
air_rows=range(df_airports.shape[0])

for r in fuze_rows:
    lat = df_result.loc[r,lat1]
    max_lat = lat + .01
    min_lat = lat - .01
    lon = df_result.loc[r,lon1]
    max_lon = lon + .01
    min_lon = lon - .01
    for a in air_rows:
        if (min_lat <= df_airports.loc[a,lat2] <= max_lat) and (min_lon <= df_airports.loc[a,lon2] <= max_lon):
            df_result['Type'] = 'Airport'

Here are two sample data frames:

# Import pandas library 
import pandas as pd 
  
# initialize list of lists 
data = [['NY', 'Uniondale', 'Nassau', '40.72', '-73.59'], 
        ['NY', 'Uniondale', 'Nassau', '40.72', '-73.59'],
        ['NY', 'Uniondale', 'Nassau', '40.72', '-73.59'],
        ['NY', 'NY', 'New York', '40.76', '73.98'],
        ['NY', 'NY', 'New York', '40.76', '73.98']] 
  
# Create the pandas DataFrame 
df_result = pd.DataFrame(data, columns = ['state', 'city', 'county','latitude_fuze','longitude_fuze']) 
# print dataframe. 
df_result

And...

data = [['New York', 'JFK', '40.64', '-73.78'], 
        ['New York', 'JFK', '40.64', '-73.78'],
        ['Los Angeles', 'LAX', '33.94', '-118.41'],
        ['Chicago', 'ORD', '41.98', '-87.90'],
        ['San Francisco', 'SFO', '37.62', '-122.38']] 
  
# Create the pandas DataFrame 
df_airports = pd.DataFrame(data, columns = ['municipality_name', 'airport_code', 'latitude_air','longitude_air']) 
# print dataframe. 
df_airports

When running this code, I get this error:

KeyError: "None of [Float64Index([40.719515, 40.719515, 40.719515,  40.75682,  40.75682,  40.75682,\n               40.75682,  40.75682,  40.75682,   40.7646,\n              ...\n                40.0006,   40.0006,   40.0006,   40.0006,   40.0006,   40.0006,\n                40.0006, 39.742417, 39.742417, 39.742417],\n             dtype='float64', length=1720)] are in the [index]"

If using KNN or the Haversine method to do the calculation is better, I'm open to that. I'm not looking for distances here, but rather similarities in lat & long numbers. If I do need to calculate the distance to make this work correctly, please let me know. Thanks everyone.

Answer 1

I'm not sure what approach you need to take, as I'm not 100% clear on what you're trying to do. However, something like this might be helpful for getting your current approach working:

# join the two dataframes - must be the same length
df = pd.concat([df_result, df_airports], axis=1)

# cast latitudes and longitudes to numeric
cols = ["latitude_fuze", "latitude_air", "longitude_fuze", "longitude_air"]
df[cols] = df[cols].apply(pd.to_numeric, errors='coerce', axis=1)

# create a mask where our conditions are met (difference between lat fuze and lat air < 0.1 and difference between long fuze and long air < 0.1)
mask = ((abs(df["latitude_fuze"] - df["latitude_air"]) < 0.1) & (abs(df["longitude_fuze"] - df["longitude_air"]) < 0.1))

# fill the type column
df.loc[mask, 'Type'] = "Airport"