python+pandas 从 excel中地址批量获取经纬度后写入excel

# -- coding:utf-8 --
import pandas as pd
import requests

ak_str='j6F1ZAGzwya*****56YP'  #百度地图开放平台AK

def get_position(name,AK):
    url = f'http://api.map.baidu.com/geocoding/v3/?address={name}&output=json&ak={AK}'
    res = requests.get(url)  
    val = res.json()
    retval = {'地址': name,
              '经度': val['result']['location']['lng'],
              '维度': val['result']['location']['lat'],
              }
    longitude = retval['经度']
    latitude = retval['维度']
    return longitude, latitude

# 获取地理信息
dataSet = pd.read_excel("D:\python_file\hangjia.xlsx")
df = dataSet["address"]  #获取excel中地址列数据
df_location = df.apply(get_position,AK=ak_str) #pd apply数据执行 get_position
dataSet["longitude"] = df_location.map(lambda x: x[0]) 
dataSet["latitude"] = df_location.map(lambda x: x[1])
print(dataSet)
dataSet.to_excel("D:\python_file\hangjia.xlsx", index=False,encoding='utf_8_sig')

注意几个坑：

1、requests版本不对，导致出现莫名其妙错误 报requests.get(url)相关错误，多半是这个原因；

2、pip install -U requests 老出错，用 python -m pip install -U requests 解决