Looping Through Two Separate Dataframes, Haversine Function, Store The Values

I have two dataFrames that I want to loop through, apply a Haversine function, and structure the results within a new array. I want to grab the lat, lng coordinates for the first r

Solution 1:

If you use loop with pandas and numpy, chances are high that you are doing it wrong. Learn and apply the vectorized functions that these libraries provide:

# Build an index that contain every pairing of Store - University
idx = pd.MultiIndex.from_product([da_store.index, da_univ.index], names=['Store', 'Univ'])

# Pull the coordinates of the store and the universities together# We don't need their name here
df = pd.DataFrame(index=idx) \
        .join(da_store[['lat', 'lon']], on='Store') \
        .join(da_univ[['LATITUDE', 'LONGITUDE']], on='Univ')


defhaversine_np(lon1, lat1, lon2, lat2):
    """
    Calculate the great circle distance between two points
    on the earth (specified in decimal degrees)

    All args must be of equal length.    

    """
    lon1, lat1, lon2, lat2 = map(np.radians, [lon1, lat1, lon2, lat2])

    dlon = lon2 - lon1
    dlat = lat2 - lat1

    a = np.sin(dlat/2.0)**2 + np.cos(lat1) * np.cos(lat2) * np.sin(dlon/2.0)**2

    c = 2 * np.arcsin(np.sqrt(a))
    km = 6367 * c
    return km

df['Distance'] = haversine_np(*df[['lat', 'lon', 'LATITUDE', 'LONGITUDE']].values.T)

# The closest university to each store
min_distance = df.loc[df.groupby('Store')['Distance'].idxmin(), 'Distance']

# Pulling everything together
min_distance.to_frame().join(da_store, on='Store').join(da_univ, on='Univ') \
    [['Restaurant Name', 'INSTNM', 'Distance']]

Result:

                    Restaurant Name                                       INSTNM   Distance
Store Univ                                                                                 
0     1               Weymouth Dual  New England College of Business and Finance  15.651923
1     4               Somerset Dual                            Bay State College  68.921108
2     3     Westboro Mass Pike West           Bancroft School of Massage Therapy   9.580468
3     2     Charlton Mass Pike East                           Assumption College  26.514269
4     2     Charlton Mass Pike West                           Assumption College  26.508821