<p>几个关键概念</p>
<ol>
<li>在两个数据帧之间进行笛卡尔乘积以获得所有组合(在两个数据帧之间以相同的值进行连接是实现此<code>foo=1</code>的方法)</li>
<li>一旦两组数据都在一起,就让两组lat/lon计算距离)<a href="https://pypi.org/project/geopy/" rel="nofollow noreferrer">geopy</a>已用于此目的</li>
<li>清理列,使用<code>sort_values()</code>查找最小距离</li>
<li>最后,使用<code>groupby()</code>和<code>agg()</code>获得最短距离的<em>第一个</em>值</li>
</ol>
<p>有两个数据帧可供使用</p>
<ol>
<li><code>dfdist</code>包含所有组合和距离</li>
<li><code>dfnearest</code>其中包含结果</li>
</ol>
<pre><code>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
</code></pre>
<p><strong>输出</strong></p>
<pre><code>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
</code></pre>
<h3>减少测试组合大小的方法</h3>
<pre><code># 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)
)
</code></pre>