Python读取Excel地址查询经纬度报'NoneType'无latitude属性错误

问题场景 #

读取存储地址信息的Excel文档，批量调用Nominatim接口查询对应经纬度时触发上述报错，手动传入单个有效地址运行代码可正常返回结果，测试代码及正常输出如下：

import pandas as pd
from geopy.geocoders import Nominatim

geolocator = Nominatim(user_agent="myApp")

df2 = pd.DataFrame({'Location':
            ['188 Glezen Lane Wayland MA 01778']})

df2[['location_lat', 'location_long']] = df2['Location'].apply(
    geolocator.geocode).apply (lambda x: pd.Series(
        [x.latitude, x.longitude], index = ['location_lat', 'location_long']))

print(df2)

正常运行输出：

Location  location_lat  location_long
0  188 Glezen Lane Wayland MA 01778     42.386672     -71.345426

待读取的Excel表内容如下：

原批量运行的报错代码：

import pandas as pd
from geopy.geocoders import Nominatim

geolocator = Nominatim(user_agent="myApp")

data = pd.read_excel (r'C:\Users\scott.correia\Desktop\SoS_Test.xlsx') 

df2 = pd.DataFrame(data, columns = ['Location'])

df2[['location_lat', 'location_long']] = df2['Location'].apply(
    geolocator.geocode).apply (lambda x: pd.Series(
        [x.latitude, x.longitude], index = ['location_lat', 'location_long']))

print(df2)

报错原因 #

geolocator.geocode()方法在地址匹配失败、请求超时、触发服务限流时会返回None，原代码未做非空判断，直接对返回值读取.latitude、.longitude属性，遇到None值时就会触发该报错。单地址测试可正常运行是因为测试用的地址可被正常解析，Excel中存在无法被Nominatim识别的地址条目。

修复后代码 #

修复点包含三个部分：增加请求超时配置、添加请求频率限制适配Nominatim免费服务规则、对解析结果做非空判断，解析失败时填充空值避免报错。

import pandas as pd
from geopy.geocoders import Nominatim
from geopy.extra.rate_limiter import RateLimiter

# 初始化地理编码器，设置10秒超时避免请求卡死
geolocator = Nominatim(user_agent="myApp", timeout=10)
# 配置请求间隔为1秒，符合Nominatim免费接口调用规则，避免被限流
geocode = RateLimiter(geolocator.geocode, min_delay_seconds=1)

data = pd.read_excel(r'C:\Users\scott.correia\Desktop\SoS_Test.xlsx') 
df2 = pd.DataFrame(data, columns=['Location'])

def parse_geocode(address):
    location = geocode(address)
    # 地址解析失败时返回空值
    if not location:
        return pd.Series([None, None], index=['location_lat', 'location_long'])
    return pd.Series([location.latitude, location.longitude], index=['location_lat', 'location_long'])

df2[['location_lat', 'location_long']] = df2['Location'].apply(parse_geocode)

print(df2)
# 如需保存结果可取消下方注释
# df2.to_excel('地址经纬度解析结果.xlsx', index=False)

注意事项 #

• 运行后若存在个别地址经纬度为空，属于正常情况，可手动核对对应地址的拼写、补全省市区/邮编等信息后重新查询
• 不要调低请求间隔，Nominatim公开接口对免费调用有严格的频率限制，高频请求会被临时封禁IP

内容的提问来源于stack exchange，提问作者Scott