几个关键概念
- 在两个数据帧之间进行笛卡尔积以获得所有组合（在两个数据帧之间加入相同值是解决此问题的方法
foo=1)
- 一旦两组数据在一起，就用两组纬度/经度来计算距离）geopy已用于此目的
- 清理列，使用
sort_values()找到最小距离
- 最后一个
groupby() and agg() to get first最短距离值
有两个数据框可供使用
-
dfdist包含所有组合和距离
-
dfnearest其中包含结果
dfstat = pd.DataFrame({'STOP_ID': ['19970', '19971', '19972', '19973', '19974'],
'STOP_NAME': ['Royal Park Railway Station (Parkville)',
'Flemington Bridge Railway Station (North Melbo...',
'Macaulay Railway Station (North Melbourne)',
'North Melbourne Railway Station (West Melbourne)',
'Clifton Hill Railway Station (Clifton Hill)'],
'LATITUDE': ['-37.781193',
'-37.788140',
'-37.794267',
'-37.807419',
'-37.788657'],
'LONGITUDE': ['144.952301',
'144.939323',
'144.936166',
'144.942570',
'144.995417'],
'TICKETZONE': ['1', '1', '1', '1', '1'],
'ROUTEUSSP': ['Upfield',
'Upfield',
'Upfield',
'Flemington,Sunbury,Upfield,Werribee,Williamsto...',
'Mernda,Hurstbridge'],
'geometry': ['POINT (144.95230 -37.78119)',
'POINT (144.93932 -37.78814)',
'POINT (144.93617 -37.79427)',
'POINT (144.94257 -37.80742)',
'POINT (144.99542 -37.78866)']})
dfsub = pd.DataFrame({'id': ['4901', '4902', '4903', '4904', '4905'],
'postcode': ['3000', '3002', '3003', '3005', '3006'],
'suburb': ['MELBOURNE',
'EAST MELBOURNE',
'WEST MELBOURNE',
'WORLD TRADE CENTRE',
'SOUTHBANK'],
'state': ['VIC', 'VIC', 'VIC', 'VIC', 'VIC'],
'lat': ['-37.814563', '-37.816640', '-37.806255', '-37.822262', '-37.823258'],
'lon': ['144.970267', '144.987811', '144.941123', '144.954856', '144.965926']})
import geopy.distance
# cartesian product so we get all combinations
dfdist = (dfsub.assign(foo=1).merge(dfstat.assign(foo=1), on="foo")
# calc distance in km between each suburb and each train station
.assign(km=lambda dfa: dfa.apply(lambda r:
geopy.distance.geodesic(
(r["LATITUDE"],r["LONGITUDE"]),
(r["lat"],r["lon"])).km, axis=1))
# reduce number of columns to make it more digestable
.loc[:,["postcode","suburb","STOP_ID","STOP_NAME","km"]]
# sort so shortest distance station from a suburb is first
.sort_values(["postcode","suburb","km"])
# good practice
.reset_index(drop=True)
)
# finally pick out stations nearest to suburb
# this can easily be joined back to source data frames as postcode and STOP_ID have been maintained
dfnearest = dfdist.groupby(["postcode","suburb"])\
.agg({"STOP_ID":"first","STOP_NAME":"first","km":"first"}).reset_index()
print(dfnearest.to_string(index=False))
dfnearest
output
postcode suburb STOP_ID STOP_NAME km
3000 MELBOURNE 19973 North Melbourne Railway Station (West Melbourne) 2.564586
3002 EAST MELBOURNE 19974 Clifton Hill Railway Station (Clifton Hill) 3.177320
3003 WEST MELBOURNE 19973 North Melbourne Railway Station (West Melbourne) 0.181463
3005 WORLD TRADE CENTRE 19973 North Melbourne Railway Station (West Melbourne) 1.970909
3006 SOUTHBANK 19973 North Melbourne Railway Station (West Melbourne) 2.705553
一种减少测试组合大小的方法
# pick nearer places, based on lon/lat then all combinations
dfdist = (dfsub.assign(foo=1, latr=dfsub["lat"].round(1), lonr=dfsub["lon"].round(1))
.merge(dfstat.assign(foo=1, latr=dfstat["LATITUDE"].round(1), lonr=dfstat["LONGITUDE"].round(1)),
on=["foo","latr","lonr"])
# calc distance in km between each suburb and each train station
.assign(km=lambda dfa: dfa.apply(lambda r:
geopy.distance.geodesic(
(r["LATITUDE"],r["LONGITUDE"]),
(r["lat"],r["lon"])).km, axis=1))
# reduce number of columns to make it more digestable
.loc[:,["postcode","suburb","STOP_ID","STOP_NAME","km"]]
# sort so shortest distance station from a suburb is first
.sort_values(["postcode","suburb","km"])
# good practice
.reset_index(drop=True)
)
