我想計算 df1 中每個建筑物到 df2 中每個城市的距離。問題是我在 df1 中有 ~30 行，在 df2 中有 ~30,000 行。
所需的輸出在 output_df 中設定。
我該怎么做？是否可以使用 Geopy 或者會花費太長時間？
df1 = pd.DataFrame({'Building': ['One World Trade Center', 'Central Park Tower','Willis Tower', '111 West 57th Street', 'One Vanderbilt'],
'Latitude': [40.713005, 40.765957, 41.878872, 40.764760, 40.752971],
'Longitude': [-74.013190, -73.980844, -87.635908, -73.977581, -73.978541],
})
df2 = pd.DataFrame({'City': ['Santa Barbra, CA', 'Washington, D.C.'],
'Latitude': [34.024212, 38.9072],
'Longitude': [-118.496475, -77.0369],
})
output_df = pd.DataFrame({'City': ['Santa Barbra', 'Santa Barbra', 'Santa Barbra', 'Santa Barbra', 'Santa Barbra', 'Washington D.C.', 'Washington D.C.', 'Washington D.C.', 'Washington D.C.', 'Washington D.C.'],
'Building': ['One World Trade Center', 'Central Park Tower', 'Willis Tower', '111 West 57th Street', 'One Vanderbilt', 'One World Trade Center', 'Central Park Tower', 'Willis Tower', '111 West 57th Street', 'One Vanderbilt'],
'Latitude': [40.713005, 40.765957, 41.878872, 40.764760, 40.752971, 40.713005, 40.765957, 41.878872, 40.764760, 40.752971],
'Longitude': [-74.013190, -73.980844, -87.635908, -73.977581, -73.978541, -74.013190, -73.980844, -87.635908, -73.977581, -73.978541],
'Distance': ['dis_to_SB', 'dis_to_SB', 'dis_to_SB', 'dis_to_SB', 'dis_to_SB', 'dis_to_DC', 'dis_to_DC', 'dis_to_DC', 'dis_to_DC', 'dis_to_DC']})
output_df.set_index(['City', 'Building'])
uj5u.com熱心網友回復：
distance()從 GeoPy使用：
from geopy import distance
pd.merge(df2.rename(columns={'Latitude':'C-Lat','Longitude':'C-Lon'}), df1, how='cross') \
.assign(Distance=lambda r: \
r.apply(lambda x: distance.distance((x['C-Lat'],x['C-Lon']),(x['Latitude'],x['Longitude'])).miles, axis=1)
).drop(columns=['C-Lat','C-Lon'])
City Building Latitude Longitude Distance
0 Santa Barbra, CA One World Trade Center 40.713005 -74.013190 2464.602573
1 Santa Barbra, CA Central Park Tower 40.765957 -73.980844 2466.054087
2 Santa Barbra, CA Willis Tower 41.878872 -87.635908 1759.257288
3 Santa Barbra, CA 111 West 57th Street 40.764760 -73.977581 2466.230348
4 Santa Barbra, CA One Vanderbilt 40.752971 -73.978541 2466.233832
5 Washington, D.C. One World Trade Center 40.713005 -74.013190 203.461336
6 Washington, D.C. Central Park Tower 40.765957 -73.980844 207.017141
7 Washington, D.C. Willis Tower 41.878872 -87.635908 595.065660
8 Washington, D.C. 111 West 57th Street 40.764760 -73.977581 207.103384
9 Washington, D.C. One Vanderbilt 40.752971 -73.978541 206.571970
uj5u.com熱心網友回復：
希望這是您正在尋找的：
from numpy import radians, cos, sin, sqrt from numpy import arcsin as asin
df1 = pd.DataFrame({'Building': ['One World Trade Center', 'Central Park Tower','Willis Tower', '111 West 57th Street', 'One Vanderbilt'],
'Latitude': [40.713005, 40.765957, 41.878872, 40.764760, 40.752971],
'Longitude': [-74.013190, -73.980844, -87.635908, -73.977581, -73.978541],
})
df2 = pd.DataFrame({'City': ['Santa Barbra, CA', 'Washington, D.C.'],
'Latitude': [34.024212, 38.9072],
'Longitude': [-118.496475, -77.0369],
})
完全加入
df1['key'] = 1
df2['key'] = 1
df = pd.merge(df1, df2, on = 'key')
2個城市之間的距離
def haversine(lon1, lat1, lon2, lat2):
"""
Calculate the great circle distance between two points
on the earth (specified in decimal degrees)
"""
# convert decimal degrees to radians
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))
# Radius of earth in kilometers is 6371
km = 6371* c
return km
df['distance'] = df.apply(lambda x:haversine(x['Latitude_x'], x['Longitude_x'], x['Latitude_y'], x['Longitude_y']) * 0.90, axis = 1)
print(df)
現在距離在km。haversine我從這篇文章中獲得的功能。
uj5u.com熱心網友回復：
為簡單起見，假設地球是一個半徑為r的完美球體。
設 θ = 經度（倫敦格林威治以東）和 φ = 緯度（赤道以北）。然后，您可以將坐標轉換為直角坐標，如下所示：
- x = r cos(θ) cos(φ)
- y = r sin(θ) cos(φ)
- z = r sin(φ)
如果您有兩個這些經度/緯度向量，則兩者的點積為：
x 1 x 2 y 1 y 2 z 1 z 2
= r 2 cos(θ 1 )cos(φ 1 )cos(θ 2 )cos(φ 2 ) r 2 sin(θ 1 )cos(φ 1 )sin(θ 2 )cos(φ 2 ) r 2 sin (φ 1 )sin(φ 2 )
= r 2 (cos(θ 1 )cos(φ 1 )cos(θ 2 )cos(φ 2 ) sin(θ 1 )cos(φ 1 )sin(θ 2 )cos(φ 2 ) sin(φ 1 )sin(φ 2 ))
= r 2 (cos(φ 1 )cos(φ 2 )(cos(θ 1 )cos(θ 2 ) sin(θ 1 )sin(θ 2 )) sin(φ 1 )sin(φ 2 ))
= r 2 (cos(φ 1 )cos(φ 2 )cos(θ 1 -θ 2 ) sin(φ 1 )sin(φ 2 ))
但點積也等于 r 2 cos(α)，其中 α 是兩個向量之間的角度。因此，
α = acos(cos(φ 1 )cos(φ 2 )cos(θ 1 -θ 2 ) sin(φ 1 )sin(φ 2 ))
這給出了一個以弧度為單位的角度。要轉換為距離（沿著一個大圓），我們只需乘以地球的半徑即可。但由于地球不是一個完美的球體，它的半徑不是一個常數，而是在兩極的 6356.7523 公里到赤道的 6378.1370 公里之間變化。
對于“平均”半徑，我將使用體積半徑6371.0008 公里，轉換為適當的測量單位。
# Choose one of the following:
EARTH_RADIUS = 6371.0008 # kilometers
EARTH_RADIUS = 3958.7564 # miles
EARTH_RADIUS = 3440.0652 # nautical miles
綜上所述，這是一個用于估計兩點之間距離的函式：
import math
def geo_distance(lat1, lon1, lat2, lon2):
# Convert all angles to radians
lat1_r = math.radians(lat1)
lon1_r = math.radians(lon1)
lat2_r = math.radians(lat2)
lon2_r = math.radians(lon2)
# Calculate the distance
dp = math.cos(lat1_r) * math.cos(lat2_r) * math.cos(lon1_r - lon2_r) math.sin(lat1_r) * math.sin(lat2_r)
angle = math.acos(dp)
return EARTH_RADIUS * angle
以及加利福尼亞州圣巴巴拉和華盛頓特區之間距離的測驗用例（以英里為單位）。
>>> geo_distance(34.024212, -118.496475, 38.9072, -77.0369)
2308.3300743996306
如果您需要更快的計算，您可以預先計算每個點的矩形 (x, y, z) 坐標，并據此計算點積，因此您不需要經常呼叫三角函式.
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/491334.html