Loop over two Pandas dataframes and apply function counting airports within given distance of city

Question

I have two pandas dataframes, the first containing cities and their respective coordinates, the other containing airports and their coordinates (examples below). I'd like to get a count for how many airports are within a certain distance (geodesic) of a given city and keep that as a column in the cities dataframe. Here is the head of the dataframes (airports then cities):

|                     Name                         |     IATA    |     City     |   Latitude   |  Longitude  |
|--------------------------------------------------|-------------|--------------|--------------|-------------|
| Hartsfield Jackson Atlanta International Airport |     ATL     |    Atlanta   |   33.636700  | -84.428101  |
| Los Angeles International Airport                |     LAX     |  Los Angeles |   33.942501  | -118.407997 |
| Chicago O'Hare International Airport             |     ORD     |    Chicago   |   41.978600  | -87.904800  |

|                  city                  |     city_lat     |     city_long     |     airports_80miles     |
|----------------------------------------|------------------|-------------------|--------------------------|
| Akron, OH Metro Area                   |     41.146639    |    -81.350110     |            0             |
| Albany, OR Metro Area                  |     44.488898    |    -122.537208    |            0             |
| Albany-Schenectady-Troy, NY Metro Area |     42.787920    |    -73.942348     |            0             |

Here is the straightforward function to be used:

def distance(origin, destination):
    lat1, lon1 = origin
    lat2, lon2 = destination
    radius = 6371 # km
    lat1 = math.radians(lat1)
    lat2 = math.radians(lat2)
    lon1 = math.radians(lon1)
    lon2 = math.radians(lon2)
    dlat = (lat2-lat1)
    dlon = (lon2-lon1)
    a = math.sin(dlat/2) * math.sin(dlat/2) + math.cos(lat1) \
        * math.cos(lat2) * math.sin(dlon/2) * math.sin(dlon/2)
    c = 2 * math.atan2(math.sqrt(a), math.sqrt(1-a))
    d = radius * c

    return d*0.62

How can i apply this distance function on each city, looping over the dataframe of airports and their coordinates?

Thanks in advance!

Answer 1

Use apply function twice:

Let's say the airport dataframe is df1 and city dataframe is df2 and the threshold distance is 80.

threshold_distance = 80.0

df2["Airports_within_threshold"] = df2.apply(lambda x: 
                                         df1.apply(lambda y: 
                                                   distance((x["city_lat"], x["city_long"]),
                                                            (y["Latitude"],y["Longitude"])) 
                                                   < threshold_distance, axis = 1), 
                                         axis = 1).sum(axis = 1)