pythonmapdl_python – Pandas Dataframe：根据地理坐标(经度和纬度)连接范围内的项目...

我有一个包含纬度和经度的地方的数据框.想象一下,例如城市.

df = pd.DataFrame([{'city':"Berlin", 'lat':52.5243700, 'lng':13.4105300},

{'city':"Potsdam", 'lat':52.3988600, 'lng':13.0656600},

{'city':"Hamburg", 'lat':53.5753200, 'lng':10.0153400}]);

现在我试图让所有城市都在另一个城市的半径范围内.假设距离柏林500公里,距汉堡500公里等所有城市.我会通过复制原始数据帧并使用距离函数连接来完成此操作.

中间结果有点像这样：

Berlin --> Potsdam

Berlin --> Hamburg

Potsdam --> Berlin

Potsdam --> Hamburg

Hamburg --> Potsdam

Hamburg --> Berlin

分组(减少)后的最终结果应该是这样的.备注：如果值列表包含城市的所有列,那将会很酷.

Berlin --> [Potsdam, Hamburg]

Potsdam --> [Berlin, Hamburg]

Hamburg --> [Berlin, Potsdam]

或者只是一个城市周围500公里的城市数量.

Berlin --> 2

Potsdam --> 2

Hamburg --> 2

由于我对Python很陌生,所以我会很感激任何起点.我很熟悉长距离.但不确定Scipy或Pandas中是否有有用的距离/空间方法.

很高兴,如果你能给我一个起点.到目前为止,我尝试过跟随this post.

更新：这个问题背后的原始想法来自Two Sigma Connect Rental Listing Kaggle Competition.这个想法是让那些列在另一个列表周围的100米.其中a)表示密度,因此表示热门区域; b)如果比较地址,您可以查看是否存在交叉,因此是否存在噪声区域.因此,您不需要完整的项目与项目关系,因为您不仅需要比较距离,还需要比较地址和其他元数据. PS：我没有向Kaggle上传解决方案.我只是想学习.

解决方法:

您可以使用：

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

首先需要与merge交叉连接,在boolean indexing中删除city_x和city_y中具有相同值的行：

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

df['dist'] = df.apply(lambda row: haversine(row['lng_x'],

row['lat_x'],

row['lng_y'],

row['lat_y']), axis=1)

过滤距离：

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

最后创建列表或使用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

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

标签：python,pandas,latitude-longitude,haversine