Group by multiple columns based on distance between data points

Viewed 109

In the below table 1 row refers to 1 property. The latitude and longitude refers to the properties GPS coordinates and the price is the price for the property. The distances are the distance between one row and another in kilometers. This means, 0.1 is 100 meters, and 0.05 is 50 meters.

The goal is to group the properties which are within a 100 meter radius and get the average price.

   latitude longitude price distance0   distance1   distance2   distance3   distance4   distance5   distance6   distance7   distance8   distance9
0   55.6632 12.6288 2595000 0.000000    0.046580    0.046580    0.031053    0.031053    0.093159    0.046580    0.046580    0.046580    0.093159
1   55.6637 12.6291 2850000 0.046580    0.000000    0.000000    0.015527    0.015527    0.046580    0.000000    0.093159    0.093159    0.046580
2   55.6637 12.6291 2850000 0.046580    0.000000    0.000000    0.015527    0.015527    0.046580    0.000000    0.093159    0.093159    0.046580
3   55.6632 12.6290 3198000 0.031053    0.015527    0.015527    0.000000    0.000000    0.062106    0.015527    0.077633    0.077633    0.062106
4   55.6632 12.6290 2995000 0.031053    0.015527    0.015527    0.000000    0.000000    0.062106    0.015527    0.077633    0.077633    0.062106
5   55.6638 12.6294 2395000 0.093159    0.046580    0.046580    0.062106    0.062106    0.000000    0.046580    0.139739    0.139739    0.000000
6   55.6637 12.6291 2995000 0.046580    0.000000    0.000000    0.015527    0.015527    0.046580    0.000000    0.093159    0.093159    0.046580
7   55.6642 12.6285 4495000 0.046580    0.093159    0.093159    0.077633    0.077633    0.139739    0.093159    0.000000    0.000000    0.139739
8   55.6632 12.6285 3998000 0.046580    0.093159    0.093159    0.077633    0.077633    0.139739    0.093159    0.000000    0.000000    0.139739
9   55.6638 12.6294 3975000 0.093159    0.046580    0.046580    0.062106    0.062106    0.000000    0.046580    0.139739    0.139739    0.000000

The resulting dataframe would have a new column at the end with the average price for properties within a 100 meter radius.

EDIT: The thinking is, if we take the column "distance0" as an example. Going through every data point that is under 0.1, it should take the price of those data points and and return the average in index 0 in a new column called "avg_price_per_100m_radius"

2 Answers

With the objective in mind, to compute the average property value within a 100m radius, the purpose of including the 10 extra columns in the output is unclear to me. You could do the following, given only the three first columns:

from geopy.distance import geodesic

data = {"latitude": [55.6632, 55.6637, 55.6637], "longitude": [12.6288, 12.6291, 12.6294], "price":[2595000, 2850000, 2950000]}

df = pd.DataFrame(data)

df:

    latitude    longitude   price
0   55.6632     12.6288     2595000
1   55.6637     12.6291     2850000
2   55.6637     12.6294     2950000

Construct a custom function, and iterate over each row:

def get_average_prices(x):
    
    coords = (x["latitude"], x["longitude"])
    
    # Get distance to all other properties
    total_price = 0
    for _, row in df.iterrows():
        distance = geodesic(coords, (row["latitude"], row["longitude"])).meters
        if distance <= 100:
            total_price += row["price"]
            
    # Dont count itself (the current row)
    total_price -= x["price"] 
    x['avg_price'] = total_price/(len(df)-1)
    
    return x

df.apply(lambda x: get_average_prices(x), axis=1)

Output:

    latitude    longitude   price       avg_price
0   55.6632     12.6288     2595000.0   2900000.0
1   55.6637     12.6291     2850000.0   2772500.0
2   55.6637     12.6294     2950000.0   2722500.0

The average prices do not include the property themselves, although that could easily be adapted in the code. This method is slow, so if your DataFrame is huge, you might want to develop a faster, vectorized, method.

Solution

You could try this.

dist_cols = [f'distance{i}' for i in range(df.index.size)]
df['avg_price'] = (
    (df.loc[:, dist_cols] < 0.1).to_numpy().astype(int) * 
    (df.price.to_numpy().reshape(-1, 1))
).mean(axis=0)

Output:

enter image description here

A Closer Look

To understand how it works, try running the following code-block.

pd.DataFrame((df.loc[:, dist_cols] < 0.1).to_numpy().astype(int) * (df.price.to_numpy().reshape(-1, 1)), columns=dist_cols)

Dummy Data

I prefer adding the dummy-data section for reproducibility of the answer.

import numpy as np
import pandas as pd

from io import StringIO

import warnings
warnings.filterwarnings("ignore")

## Dataframe as string
s = """
   latitude longitude price distance0   distance1   distance2   distance3   distance4   distance5   distance6   distance7   distance8   distance9
0   55.6632 12.6288 2595000 0.000000    0.046580    0.046580    0.031053    0.031053    0.093159    0.046580    0.046580    0.046580    0.093159
1   55.6637 12.6291 2850000 0.046580    0.000000    0.000000    0.015527    0.015527    0.046580    0.000000    0.093159    0.093159    0.046580
2   55.6637 12.6291 2850000 0.046580    0.000000    0.000000    0.015527    0.015527    0.046580    0.000000    0.093159    0.093159    0.046580
3   55.6632 12.6290 3198000 0.031053    0.015527    0.015527    0.000000    0.000000    0.062106    0.015527    0.077633    0.077633    0.062106
4   55.6632 12.6290 2995000 0.031053    0.015527    0.015527    0.000000    0.000000    0.062106    0.015527    0.077633    0.077633    0.062106
5   55.6638 12.6294 2395000 0.093159    0.046580    0.046580    0.062106    0.062106    0.000000    0.046580    0.139739    0.139739    0.000000
6   55.6637 12.6291 2995000 0.046580    0.000000    0.000000    0.015527    0.015527    0.046580    0.000000    0.093159    0.093159    0.046580
7   55.6642 12.6285 4495000 0.046580    0.093159    0.093159    0.077633    0.077633    0.139739    0.093159    0.000000    0.000000    0.139739
8   55.6632 12.6285 3998000 0.046580    0.093159    0.093159    0.077633    0.077633    0.139739    0.093159    0.000000    0.000000    0.139739
9   55.6638 12.6294 3975000 0.093159    0.046580    0.046580    0.062106    0.062106    0.000000    0.046580    0.139739    0.139739    0.000000
"""

## Read dataframe
df = pd.read_csv(StringIO(s), sep='\s*',engine='python', delimiter=None)
Related