You can use:
from math import radians, cos, sin, asin, sqrt
def haversine(lon1, lat1, lon2, lat2):
lon1, lat1, lon2, lat2 = map(radians, [lon1, lat1, lon2, lat2])
# haversine formula
dlon = lon2 - lon1
dlat = lat2 - lat1
a = sin(dlat/2)**2 + cos(lat1) * cos(lat2) * sin(dlon/2)**2
c = 2 * asin(sqrt(a))
r = 6371 # Radius of earth in kilometers. Use 3956 for miles
return c * r
First need cross join with merge
, remove row with same values in city_x
and city_y
by boolean indexing
:
df['tmp'] = 1
df = pd.merge(df,df,on='tmp')
df = df[df.city_x != df.city_y]
print (df)
city_x lat_x lng_x tmp city_y lat_y lng_y
1 Berlin 52.52437 13.41053 1 Potsdam 52.39886 13.06566
2 Berlin 52.52437 13.41053 1 Hamburg 53.57532 10.01534
3 Potsdam 52.39886 13.06566 1 Berlin 52.52437 13.41053
5 Potsdam 52.39886 13.06566 1 Hamburg 53.57532 10.01534
6 Hamburg 53.57532 10.01534 1 Berlin 52.52437 13.41053
7 Hamburg 53.57532 10.01534 1 Potsdam 52.39886 13.06566
Then apply haversine function:
df['dist'] = df.apply(lambda row: haversine(row['lng_x'],
row['lat_x'],
row['lng_y'],
row['lat_y']), axis=1)
Filter distance:
df = df[df.dist < 500]
print (df)
city_x lat_x lng_x tmp city_y lat_y lng_y dist
1 Berlin 52.52437 13.41053 1 Potsdam 52.39886 13.06566 27.215704
2 Berlin 52.52437 13.41053 1 Hamburg 53.57532 10.01534 255.223782
3 Potsdam 52.39886 13.06566 1 Berlin 52.52437 13.41053 27.215704
5 Potsdam 52.39886 13.06566 1 Hamburg 53.57532 10.01534 242.464120
6 Hamburg 53.57532 10.01534 1 Berlin 52.52437 13.41053 255.223782
7 Hamburg 53.57532 10.01534 1 Potsdam 52.39886 13.06566 242.464120
And last create list
or get size
with groupby
:
df1 = df.groupby('city_x')['city_y'].apply(list)
print (df1)
city_x
Berlin [Potsdam, Hamburg]
Hamburg [Berlin, Potsdam]
Potsdam [Berlin, Hamburg]
Name: city_y, dtype: object
df2 = df.groupby('city_x')['city_y'].size()
print (df2)
city_x
Berlin 2
Hamburg 2
Potsdam 2
dtype: int64
Also is possible use numpy haversine solution
:
def haversine_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['tmp'] = 1
df = pd.merge(df,df,on='tmp')
df = df[df.city_x != df.city_y]
#print (df)
df['dist'] = haversine_np(df['lng_x'],df['lat_x'],df['lng_y'],df['lat_y'])
city_x lat_x lng_x tmp city_y lat_y lng_y dist
1 Berlin 52.52437 13.41053 1 Potsdam 52.39886 13.06566 27.198616
2 Berlin 52.52437 13.41053 1 Hamburg 53.57532 10.01534 255.063541
3 Potsdam 52.39886 13.06566 1 Berlin 52.52437 13.41053 27.198616
5 Potsdam 52.39886 13.06566 1 Hamburg 53.57532 10.01534 242.311890
6 Hamburg 53.57532 10.01534 1 Berlin 52.52437 13.41053 255.063541
7 Hamburg 53.57532 10.01534 1 Potsdam 52.39886 13.06566 242.311890