Python excel入门：地址转经纬度批量导入

1.注册腾讯位置服务，获得key，一个账号只有一万额度

2.下载python并配置环境变量，之后链接国内清华镜像，再用pip命令下载panda库、requests库

pip install requests

3.配置vscode开始写py

import pandas as pd  
import requests  
from urllib.parse import quote 

# Excel文件路径  
file_path = '1.xlsx'  
  
# 读取Excel文件  
df = pd.read_excel(file_path)  
  
address = df['地址']
jingdu = []
weidu = []
zhuangtai = df['状态']

key='FIWBZ-YKU3J-6FMFT-XLVAW-F3P47-KGBA5'
#我的key，一个key每天1万限额

# 参考一下response 的数据格式方便修改
# data = {  
#     "status": 0,  
#     "message": "Success",  
#     "request_id": "3d869387db504dbcaec84b29fcdb7384",  
#     "result": {  
#         "title": "海淀西大街74号",  
#         "location": {"lng": 116.307015, "lat": 39.982915},  
#         "ad_info": {"adcode": "110108"},  
#         "address_components": {  
#             "province": "北京市",  
#             "city": "北京市",  
#             "district": "海淀区",  
#             "street": "彩和坊路",  
#             "street_number": ""  
#         },  
#         "similarity": 0.99,  
#         "deviation": 1000,  
#         "reliability": 7,  
#         "level": 9  
#     }  
# } 

params = {  
    
}  

# 对字符串进行URL编码  
for i in range(len(address)):
    url_encoded_addr = quote(address[i])  # 对每个地址进行URL编码 
    url='{}{}{}{}'.format('https://apis.map.qq.com/ws/geocoder/v1/?address=',url_encoded_addr,'&key=',key)
    response = requests.get(url) 
    if zhuangtai[i] != 'yes':
        if response.status_code == 200:  
            data = response.json() 
            if 'location' in data['result'] and 'lng' in data['result']['location']:  
                df.at[i,'经度'] = data['result']['location']['lng']
                df.at[i,'纬度'] = data['result']['location']['lat']
                df.at[i,'状态'] = 'yes'
                #把df的经度列第i行的值变为 data['result']['location']['lng']
                #可以自由添加自己想要的数据
            else:  
                print('Location not found in response.')  
        else:  
            # 如果请求不成功，打印状态码  
            print('Error:', response.status_code)

#覆盖excel，这里用的w模式完全覆盖
with pd.ExcelWriter(file_path, engine='openpyxl', mode='w') as writer:  
    df.to_excel(writer, sheet_name='Sheet1', index=False)  

print(df)