CODEWITHSUNDEEP
python-3.xpandasnumpylambda
mhhhhhhh
mhhhhhhh

Reputation: 1

Speed up nested loops over a dataframe using using apply()

I have a dataframe using Pandas in Python that contains latitude and longitude coordinates on each row. My goal is to add another column called "close_by" that contains a count of the number of other entries in the data set that are within 1 mile, using haversine.

I have seen other guides for similar problems like: https://engineering.upside.com/a-beginners-guide-to-optimizing-pandas-code-for-speed-c09ef2c6a4d6 But they involve using df.apply() to update each row to add the distance between the coordinates and some static per-defined point. I have not had any luck finding or coming up with a solution.

Essentially, this is what I'm trying to optimize:

for index1, row1 in business_data.iterrows():
    for index2, row2 in business_data.iterrows():
        distance = mpu.haversine_distance((business_data.at[index1,'latitude'], business_data.at[index1,'longitude']), (business_data.at[index2,'latitude'], business_data.at[index2,'longitude']))
        distance = distance * 0.621371

        if distance <= 1:
            business_data.at[index1,'close_by'] = row1["close_by"] + 1

I have about 50,000 rows and on my computer it takes about 5 seconds per row.

Thank you for any suggestions!

Upvotes: 0

Views: 831

Answers (1)

Nils Werner
Nils Werner

Reputation: 36765

By the looks of it, mpu.haversine_distance() uses math instead of numpy functions, so it is not vectorizable.

Using this vectorized haversine distance function instead, you can easily vectorize your problem:

df = pd.DataFrame([
    {'latitude': 49.001, 'longitude': 11.0},
    {'latitude': 49.0, 'longitude': 11.0},
    {'latitude': 49.001, 'longitude': 11.001},
    {'latitude': -49.0, 'longitude': 11.0},
])


lon = df['longitude'].to_numpy()
lat = df['latitude'].to_numpy()

radius = 1.0

df['close_by'] = np.count_nonzero(haversine_np(lon, lat, lon[:, None], lat[:, None]) < radius, axis=0) - 1

df
#   latitude    longitude   nearby
# 0 49.001      11.000      2
# 1 49.000      11.000      2
# 2 49.001      11.001      2
# 3 -49.000     11.000      0

Upvotes: 1

Related Questions