如何为DataFrame列批量应用地理编码函数并拆分经纬度列

我有一个调用地图API的函数get_coordinates，可根据非结构化地址数据返回纬度（lat）和经度（lon）。单独调用时运行正常：

address = "12 & 14 CHIN BEE AVENUE,, SINGAPORE 619937"
lat, lon = get_coordinates(api_key, address)
print(lat, lon)

能得到类似1.3332439 103.7118193的结果。

我通过SQL查询生成了包含待地理编码地址的DataFrame，想将该函数批量应用到DataFrame的地址列，并把经纬度分别存入新列中。

尝试过两种方式都有问题：

• 用apply创建新DataFrame时运行极慢：df2 = df.apply(get_coordinates(api_key, df['DeliveryAddress']))
• 尝试给DataFrame新增列时参数传递错误：df['coords'] = df['DeliveryAddress'].apply(get_coordinates(api_key, df['DeliveryAddress']))

附当前单地址调用的函数代码：

def get_coordinates(api_key, address):
    base_url = "http://someURL.net/REST/v1/Locations"
    params = {
        "query": address,
        "key": api_key
    }
    response = requests.get(base_url, params=params)
    response.raise_for_status()
    data = response.json()
    coordinates = data["resourceSets"][0]["resources"][0]["point"]["coordinates"]
    return coordinates

address = "12 & 14 CHIN BEE AVENUE,, SINGAPORE 619937"
lat, lon = get_coordinates(api_key, address)
print(lat, lon)

1. 修正参数传递错误 #

apply方法要求传入的函数仅接收单个元素（每行的地址字符串），你之前的写法直接把整个地址Series传入函数，导致参数不匹配。可以用两种方式修正：

方式一：用lambda包装函数 #

通过lambda固定api_key参数，让函数只接收地址输入：

# 生成包含经纬度的列表列
df['coords'] = df['DeliveryAddress'].apply(lambda addr: get_coordinates(api_key, addr))

# 将列表拆分为独立的lat和lon列
df[['lat', 'lon']] = pd.DataFrame(df['coords'].tolist(), index=df.index)

# 可选：删除临时的coords列
df.drop('coords', axis=1, inplace=True)

方式二：修改函数默认参数 #

给函数的api_key设置默认值，这样调用时只需传入地址：

def get_coordinates(address, api_key=your_api_key):
    base_url = "http://someURL.net/REST/v1/Locations"
    params = {
        "query": address,
        "key": api_key
    }
    response = requests.get(base_url, params=params)
    response.raise_for_status()
    data = response.json()
    coordinates = data["resourceSets"][0]["resources"][0]["point"]["coordinates"]
    return coordinates

# 直接调用apply
df['coords'] = df['DeliveryAddress'].apply(get_coordinates)
# 拆分列
df[['lat', 'lon']] = pd.DataFrame(df['coords'].tolist(), index=df.index)

2. 解决运行速度慢的问题 #

之前速度慢的核心原因是逐行同步调用API，每个请求都要等待响应，大量地址会累积耗时。可以通过以下方式优化：

方式一：添加请求延迟（避免API限流） #

多数地图API有请求频率限制，不加延迟可能被限流甚至封禁，同时也能避免瞬间请求过多导致的拥堵：

import time

def get_coordinates(api_key, address):
    base_url = "http://someURL.net/REST/v1/Locations"
    params = {
        "query": address,
        "key": api_key
    }
    # 添加0.5秒延迟，可根据API限制调整
    time.sleep(0.5)
    response = requests.get(base_url, params=params)
    response.raise_for_status()
    data = response.json()
    coordinates = data["resourceSets"][0]["resources"][0]["point"]["coordinates"]
    return coordinates

方式二：异步请求批量处理 #

用aiohttp替代requests做异步请求，同时发起多个请求，大幅缩短总耗时：

import aiohttp
import asyncio

async def async_get_coordinates(session, api_key, address):
    base_url = "http://someURL.net/REST/v1/Locations"
    params = {
        "query": address,
        "key": api_key
    }
    async with session.get(base_url, params=params) as response:
        response.raise_for_status()
        data = await response.json()
        return data["resourceSets"][0]["resources"][0]["point"]["coordinates"]

async def batch_geocode(api_key, addresses):
    async with aiohttp.ClientSession() as session:
        tasks = [async_get_coordinates(session, api_key, addr) for addr in addresses]
        return await asyncio.gather(*tasks)

# 调用异步函数获取所有经纬度
coords_list = asyncio.run(batch_geocode(api_key, df['DeliveryAddress'].tolist()))

# 赋值给DataFrame
df[['lat', 'lon']] = pd.DataFrame(coords_list, index=df.index)

3. 增加异常处理（可选但推荐） #

实际调用API时可能遇到地址无法解析、请求失败等情况，添加异常处理避免程序崩溃：

def get_coordinates(api_key, address):
    base_url = "http://someURL.net/REST/v1/Locations"
    params = {
        "query": address,
        "key": api_key
    }
    try:
        time.sleep(0.5)
        response = requests.get(base_url, params=params)
        response.raise_for_status()
        data = response.json()
        # 检查返回数据结构完整性
        if data.get("resourceSets") and len(data["resourceSets"]) > 0:
            resources = data["resourceSets"][0].get("resources", [])
            if len(resources) > 0:
                return resources[0]["point"]["coordinates"]
        # 数据异常时返回空值
        return [None, None]
    except Exception as e:
        print(f"处理地址 {address} 时出错: {str(e)}")
        return [None, None]

内容的提问来源于stack exchange，提问作者opperman.eric