Calculate distance between points and price per area in Pandas I'm trying to find distances between all points (latitude, longitude), and for each point, get the average price_area (price/area) of the closest points around it. This code is taking too long:

def create_pa_radius(df, radius):

    df['pa_' + str(radius)] = np.nan

    for index, row in df.iterrows():
        point = [row['latitude'], row['longitude']]
        df['distances'] = df.apply(lambda x: geo_dist(point, [x['latitude'], x['longitude']]).km, axis = 1)
        samples = df.price_area[df.distances &lt; radius/1000]
        mean = samples.mean()

        df['pa_' + str(radius)].iloc[index] = mean

    return df


I would like at least to understand how to make this kind of iteration faster.
The problem you are having is most likely due to your code being O(n²) on the size of your dataframe. (It's possible you have a small df and a really bad implementation of geo_dist, but I'll ignore that.)

Let's strip out most of your code:

for index, row in df.iterrows():
    df['distances'] = df.apply(lambda x: geo_dist(point, [x['latitude'], x['longitude']]).km, axis = 1)


These lines can be rewritten:

for index, row in df.iterrows():        
    for i, r2 in df.iterrows():
        p2 = (r2['latitude'], r2['longitude'])
        df.distances[i] = geo_dist(point, *p2).km


The same is true for the filtering of distances &lt; radius/1000: you are looping over all n rows, and then for each row you are looping over all n rows again. This is n * n operations, or O(n²).

There is no simple way to fix this. If you insist on computing the distances from each point to its surrounding points, you will have to structure your code this way. However, there are some alternatives:


You could use a kd-tree or a space partitioning hash of your own design to access your points. This would make pandas secondary in your access, requiring you to write more python code yourself.
You could take advantage of some other heuristic to determine proximity, and use this to filter your data before running your n² algorithm on it: note that 3² + 3² = 18, while (3 + 3)² = 36. Breaking your data down into smaller units can have a substantial impact on your performance. For example: in the United States, zip codes within a given state all start with a few identical 2 digit prefixes. You might build a dictionary of "adjoining" states, by hand, and then pre-filter your locations using a zip-code prefix, such that you only consider other points that have the same 2-digit prefixes, or the 2-digit prefixes of an adjacent state. If your locations were US cities, this would let you eliminate more than 90% of your data before you start your n² algorithm.
There are surely some other approaches, which will tend to vary based on your application. Perhaps if you mention what problem you are trying to solve, someone will have a suggestion.