Python | 地址解析经纬度

运行环境：Jupyter notebook(python 3.12.7)

chinese-address-parser不支持直接获取经纬度，
因此为了解析出标准地址和经度纬度，这里主要使用的是高德API。

方法与步骤

（1）准备地图API KEY

申请高德地图KEY + 百度地图AK（免费版一般够用）→ 高德和百度双API验证。

我的应用 | 高德控制台

控制台 | 百度地图开放平台

（2）密钥循环器

个人开发者key日限额5000次(免费)，如果超出5000次的数据量 就要用到多个key（借同学同事的），可使用intertools.cycle创建密钥循环器。

（3）地址及辅助信息做拼接

根据需要调整ADDRESS_COLS地址拼接列，例如我需要用到“所在地”+“医疗机构名称”+“详细地址”拼接出原始地址，这样可以避免“详细地址”字段为空的情况。

（4）获取经纬度(地理编码)

• 使用地图API的地理编码功能，获取：经度 | 纬度

• 通过地图API获取：省 | 市 | 区 + 标准地址

（5）置信度检测与交叉验证

• API置信度

• 同时调用百度/腾讯API，对比经纬度偏差

（6）导出结果

• 运行过程每10条显示进度信息

• 导出EXCEL，保留了所有原始字段

• 颜色标注关键状态列，对于偏差较大的结果可进行人工核验

代码

确保安装相关包：

pip install pandas requests geopy openpyxl

• 适用于≤5000条数据的代码，单独高德API版 + 无交叉验证

import pandas as pd import requests from geopy.distance import geodesic from openpyxl import load_workbook from openpyxl.styles import PatternFill import time # 配置参数 GAODE_KEY = '替换高德key' # 替换为你的高德密钥 INPUT_FILE = r'C:\Users\User\Desktop\新建文件夹\副本.xlsx' #输入文件 OUTPUT_FILE = r'C:\Users\User\Desktop\新建文件夹\TEST.xlsx' #输出文件 SHEET_NAME = 'Sheet1' ADDRESS_COLS = ['所在地', '医疗机构名称', '详细地址'] #拼接字段 RATE_LIMIT = 0.1 # 请求间隔(秒) def gaode_geocode(address, api_key): """高德地理编码""" url = f"https://restapi.amap.com/v3/geocode/geo?address={address}&key={api_key}" try: response = requests.get(url, timeout=5) data = response.json() if data.get('status') == '1' and data.get('count') != '0': geo = data['geocodes'][0] province = geo.get('province', '') city = geo.get('city', province) # 处理直辖市 return { 'gaode_省份': province, 'gaode_城市': city, 'gaode_区县': geo.get('district', ''), 'gaode_标准地址': geo.get('formatted_address', ''), 'gaode_经度': geo.get('location', '').split(',')[0] if geo.get('location') else '', 'gaode_纬度': geo.get('location', '').split(',')[1] if geo.get('location') else '', 'gaode_解析状态': '成功', 'gaode_置信度': geo.get('level', '') } return { 'gaode_省份': '', 'gaode_城市': '', 'gaode_区县': '', 'gaode_标准地址': '', 'gaode_经度': '', 'gaode_纬度': '', 'gaode_解析状态': f"失败: {data.get('info', '未知错误')}", 'gaode_置信度': '' } except Exception as e: return { 'gaode_省份': '', 'gaode_城市': '', 'gaode_区县': '', 'gaode_标准地址': '', 'gaode_经度': '', 'gaode_纬度': '', 'gaode_解析状态': f"异常: {str(e)}", 'gaode_置信度': '' } def validate_with_district_center(row): """行政区划中心验证""" if row['gaode_解析状态'] != '成功' or not row['gaode_经度']: return '无法验证' try: district = row['gaode_区县'] or row['gaode_城市'] or row['gaode_省份'] url = f"https://restapi.amap.com/v3/config/district?keywords={district}&key={GAODE_KEY}" resp = requests.get(url, timeout=5) data = resp.json() if data['status'] == '1' and data['districts']: center = data['districts'][0]['center'].split(',') center_lng, center_lat = float(center[0]), float(center[1]) target_lng = float(row['gaode_经度']) target_lat = float(row['gaode_纬度']) distance = geodesic((center_lat, center_lng), (target_lat, target_lng)).km if distance < 3: return f'准确(距中心{distance:.1f}km)' return f'偏差较大(距中心{distance:.1f}km)' return '获取中心失败' except: return '验证异常' def process_excel(input_file, output_file, sheet_name, address_cols): """处理Excel主流程""" df = pd.read_excel(input_file, sheet_name=sheet_name) # 校验地址列 missing_cols = [col for col in address_cols if col not in df.columns] if missing_cols: raise ValueError(f"缺少必要列: {missing_cols}") print(f"开始处理 {len(df)} 条记录...") results = [] for index, row in df.iterrows(): # 拼接地址 address = ''.join(str(row[col]).strip() for col in address_cols if pd.notna(row[col])) if not address: empty_result = { '原始地址': '', 'gaode_省份': '', 'gaode_城市': '', 'gaode_区县': '', 'gaode_标准地址': '', 'gaode_经度': '', 'gaode_纬度': '', 'gaode_解析状态': '空地址', 'gaode_置信度': '' } results.append(empty_result) continue # 获取地理编码 gaode = gaode_geocode(address, GAODE_KEY) merged = { '原始地址': address, **gaode } results.append(merged) # 进度显示 if (index+1) % 10 == 0: print(f"已处理 {index+1}/{len(df)} 条") time.sleep(RATE_LIMIT) # 合并结果 result_df = pd.DataFrame(results) final_df = pd.concat([df, result_df], axis=1) # 行政区验证 print("进行行政区验证...") final_df['行政区划验证'] = final_df.apply(validate_with_district_center, axis=1) # 保存结果 final_df.to_excel(output_file, index=False) print(f"结果已保存至: {output_file}") # 添加颜色标记 add_color_to_excel(output_file) def add_color_to_excel(file_path): """结果着色""" wb = load_workbook(file_path) ws = wb.active color_map = { '成功': '00FF00', # 绿色 '失败': 'FF0000', # 红色 '异常': 'FFFF00', # 黄色 '准确': '00FF00', '偏差': 'FFC000', # 橙色 '空地址': '808080' # 灰色 } # 获取列索引 col_index = {cell.value: idx for idx, cell in enumerate(ws[1], 1)} for row in ws.iter_rows(min_row=2): # 高德状态着色 gaode_status = row[col_index['gaode_解析状态']-1].value if '成功' in gaode_status: row[col_index['gaode_解析状态']-1].fill = PatternFill(fgColor=color_map['成功'], fill_type="solid") elif '失败' in gaode_status: row[col_index['gaode_解析状态']-1].fill = PatternFill(fgColor=color_map['失败'], fill_type="solid") elif '异常' in gaode_status: row[col_index['gaode_解析状态']-1].fill = PatternFill(fgColor=color_map['异常'], fill_type="solid") # 行政区验证着色 district_valid = row[col_index['行政区划验证']-1].value if '准确' in district_valid: row[col_index['行政区划验证']-1].fill = PatternFill(fgColor=color_map['准确'], fill_type="solid") elif '偏差' in district_valid: row[col_index['行政区划验证']-1].fill = PatternFill(fgColor=color_map['偏差'], fill_type="solid") # 空地址标记 if row[col_index['gaode_解析状态']-1].value == '空地址': row[col_index['gaode_解析状态']-1].fill = PatternFill(fgColor=color_map['空地址'], fill_type="solid") wb.save(file_path) if __name__ == '__main__': process_excel(INPUT_FILE, OUTPUT_FILE, SHEET_NAME, ADDRESS_COLS)

运行结果截图：

• 适用于≤5000条数据的代码，高德&百度双API验证 + 不带密钥循环器

import pandas as pd import requests from geopy.distance import geodesic from openpyxl import load_workbook from openpyxl.styles import PatternFill import time # 配置参数 GAODE_KEY = '替换高德key' # 替换为你的高德密钥 BAIDU_KEY = '替换百度key' # 替换为你的百度密钥 INPUT_FILE = r'C:\User\Desktop\副本.xlsx' #输入文件 OUTPUT_FILE = r'C:\User\Desktop\标准化地址结果.xlsx' #输出文件 SHEET_NAME = 'Sheet1' ADDRESS_COLS = ['所在地', '医疗机构名称', '详细地址'] #拼接字段 RATE_LIMIT = 0.1 # 请求间隔(秒) def baidu_geocode(address, api_key): """百度地理编码（无SN版）""" url = f"http://api.map.baidu.com/geocoding/v3/?address={address}&ak={api_key}&output=json" try: response = requests.get(url, timeout=5) data = response.json() if data.get('status') == 0: result = data.get('result', {}) return { 'baidu_标准地址': result.get('formatted_address', ''), 'baidu_经度': result.get('location', {}).get('lng', ''), 'baidu_纬度': result.get('location', {}).get('lat', ''), 'baidu_置信度': result.get('confidence', ''), 'baidu_解析状态': '成功', 'baidu_级别': result.get('level', '') } return { 'baidu_标准地址': '', 'baidu_经度': '', 'baidu_纬度': '', 'baidu_置信度': '', 'baidu_解析状态': f"失败: {data.get('message', '未知错误')}", 'baidu_级别': '' } except Exception as e: return { 'baidu_标准地址': '', 'baidu_经度': '', 'baidu_纬度': '', 'baidu_置信度': '', 'baidu_解析状态': f"异常: {str(e)}", 'baidu_级别': '' } def gaode_geocode(address, api_key): """高德地理编码""" url = f"https://restapi.amap.com/v3/geocode/geo?address={address}&key={api_key}" try: response = requests.get(url, timeout=5) data = response.json() if data.get('status') == '1' and data.get('count') != '0': geo = data['geocodes'][0] province = geo.get('province', '') city = geo.get('city', province) # 处理直辖市 return { 'gaode_省份': province, 'gaode_城市': city, 'gaode_区县': geo.get('district', ''), 'gaode_标准地址': geo.get('formatted_address', ''), 'gaode_经度': geo.get('location', '').split(',')[0] if geo.get('location') else '', 'gaode_纬度': geo.get('location', '').split(',')[1] if geo.get('location') else '', 'gaode_解析状态': '成功', 'gaode_置信度': geo.get('level', '') } return { 'gaode_省份': '', 'gaode_城市': '', 'gaode_区县': '', 'gaode_标准地址': '', 'gaode_经度': '', 'gaode_纬度': '', 'gaode_解析状态': f"失败: {data.get('info', '未知错误')}", 'gaode_置信度': '' } except Exception as e: return { 'gaode_省份': '', 'gaode_城市': '', 'gaode_区县': '', 'gaode_标准地址': '', 'gaode_经度': '', 'gaode_纬度': '', 'gaode_解析状态': f"异常: {str(e)}", 'gaode_置信度': '' } def cross_validate(gaode, baidu): """结果交叉验证""" validation = {} gaode_ok = gaode['gaode_解析状态'] == '成功' baidu_ok = baidu['baidu_解析状态'] == '成功' if not gaode_ok and not baidu_ok: return { '交叉验证结果': '双API解析失败', '验证说明': f"高德:{gaode['gaode_解析状态']}, 百度:{baidu['baidu_解析状态']}" } if not gaode_ok: return {'交叉验证结果': '仅百度成功', '验证说明': f"高德:{gaode['gaode_解析状态']}"} if not baidu_ok: return {'交叉验证结果': '仅高德成功', '验证说明': f"百度:{baidu['baidu_解析状态']}"} try: # 计算经纬度距离差异 point_a = (float(gaode['gaode_纬度']), float(gaode['gaode_经度'])) point_b = (float(baidu['baidu_纬度']), float(baidu['baidu_经度'])) distance = geodesic(point_a, point_b).km validation['验证说明'] = f"坐标差{distance:.3f}公里" if distance < 0.5: validation['交叉验证结果'] = '坐标高度一致' elif distance < 2: validation['交叉验证结果'] = '坐标基本一致' else: validation['交叉验证结果'] = '坐标差异较大' # 补充行政区划比对 gaode_addr = f"{gaode['gaode_省份']}{gaode['gaode_城市']}{gaode['gaode_区县']}" if gaode_addr in baidu['baidu_标准地址']: validation['验证说明'] += " | 行政区划一致" else: validation['验证说明'] += " | 行政区划不符" return validation except: return {'交叉验证结果': '验证异常', '验证说明': '坐标转换失败'} def validate_with_district_center(row): """行政区划中心验证""" if row['gaode_解析状态'] != '成功' or not row['gaode_经度']: return '无法验证' try: district = row['gaode_区县'] or row['gaode_城市'] or row['gaode_省份'] url = f"https://restapi.amap.com/v3/config/district?keywords={district}&key={GAODE_KEY}" resp = requests.get(url, timeout=5) data = resp.json() if data['status'] == '1' and data['districts']: center = data['districts'][0]['center'].split(',') center_lng, center_lat = float(center[0]), float(center[1]) target_lng = float(row['gaode_经度']) target_lat = float(row['gaode_纬度']) distance = geodesic((center_lat, center_lng), (target_lat, target_lng)).km if distance < 3: return f'准确(距中心{distance:.1f}km)' return f'偏差较大(距中心{distance:.1f}km)' return '获取中心失败' except: return '验证异常' def process_excel(input_file, output_file, sheet_name, address_cols): """处理Excel主流程""" df = pd.read_excel(input_file, sheet_name=sheet_name) # 校验地址列 missing_cols = [col for col in address_cols if col not in df.columns] if missing_cols: raise ValueError(f"缺少必要列: {missing_cols}") print(f"开始处理 {len(df)} 条记录...") results = [] for index, row in df.iterrows(): # 拼接地址 address = ''.join(str(row[col]).strip() for col in address_cols if pd.notna(row[col])) if not address: empty_result = {k: '' for k in ['原始地址', 'gaode_省份', 'gaode_城市', 'gaode_区县', 'gaode_标准地址', 'gaode_经度', 'gaode_纬度', 'gaode_解析状态', 'baidu_标准地址', 'baidu_经度', 'baidu_纬度', 'baidu_解析状态']} empty_result.update({'交叉验证结果': '空地址', '验证说明': '地址为空'}) results.append(empty_result) continue # 获取地理编码 gaode = gaode_geocode(address, GAODE_KEY) time.sleep(RATE_LIMIT) baidu = baidu_geocode(address, BAIDU_KEY) merged = { '原始地址': address, **gaode, **baidu, **cross_validate(gaode, baidu) } results.append(merged) # 进度显示 if (index+1) % 10 == 0: print(f"已处理 {index+1}/{len(df)} 条") time.sleep(RATE_LIMIT) # 合并结果 result_df = pd.DataFrame(results) final_df = pd.concat([df, result_df], axis=1) # 行政区验证 print("进行行政区验证...") final_df['行政区划验证'] = final_df.apply(validate_with_district_center, axis=1) # 保存结果 final_df.to_excel(output_file, index=False) print(f"结果已保存至: {output_file}") # 添加颜色标记 add_color_to_excel(output_file) def add_color_to_excel(file_path): """结果着色""" wb = load_workbook(file_path) ws = wb.active color_map = { '成功': '00FF00', # 绿色 '失败': 'FF0000', # 红色 '异常': 'FFFF00', # 黄色 '一致': '00FF00', '偏差': 'FFC000', # 橙色 '空地址': '808080' # 灰色 } # 获取列索引 col_index = {cell.value: idx for idx, cell in enumerate(ws[1], 1)} for row in ws.iter_rows(min_row=2): # 高德状态着色 gaode_status = row[col_index['gaode_解析状态']-1].value if '成功' in gaode_status: row[col_index['gaode_解析状态']-1].fill = PatternFill(fgColor=color_map['成功'], fill_type="solid") # 百度状态着色 baidu_status = row[col_index['baidu_解析状态']-1].value if '成功' in baidu_status: row[col_index['baidu_解析状态']-1].fill = PatternFill(fgColor=color_map['成功'], fill_type="solid") # 交叉验证着色 cross_result = row[col_index['交叉验证结果']-1].value if '高度一致' in cross_result: row[col_index['交叉验证结果']-1].fill = PatternFill(fgColor=color_map['成功'], fill_type="solid") elif '差异较大' in cross_result: row[col_index['交叉验证结果']-1].fill = PatternFill(fgColor=color_map['偏差'], fill_type="solid") # 行政区验证着色 district_valid = row[col_index['行政区划验证']-1].value if '准确' in district_valid: row[col_index['行政区划验证']-1].fill = PatternFill(fgColor=color_map['成功'], fill_type="solid") elif '偏差' in district_valid: row[col_index['行政区划验证']-1].fill = PatternFill(fgColor=color_map['偏差'], fill_type="solid") wb.save(file_path) if __name__ == '__main__': process_excel(INPUT_FILE, OUTPUT_FILE, SHEET_NAME, ADDRESS_COLS)

运行结果截图：

• 适用于＞5000条数据的代码，高德&百度双API验证 + 带密钥循环器

import pandas as pd import requests from geopy.distance import geodesic from openpyxl import load_workbook from openpyxl.styles import PatternFill import time import itertools # 配置参数 GAODE_KEYS = [ '替换高德key1', '替换高德key2', '替换高德key3' ] BAIDU_KEYS = [ '替换百度key1', '替换百度key2', '替换百度key3' ] # 创建密钥循环迭代器 gaode_key_cycle = itertools.cycle(GAODE_KEYS) baidu_key_cycle = itertools.cycle(BAIDU_KEYS) INPUT_FILE = r'C:\User\Desktop\定点医药机构清单_10647.xlsx' # 输入文件 OUTPUT_FILE = r'C:\User\Desktop\定点医药机构清单_10647.xlsx' # 输出文件 SHEET_NAME = 'Sheet1' # 工作表名 ADDRESS_COLS = ['所在地', '医疗机构名称', '详细地址'] # 多个地址字段，将按顺序拼接 RATE_LIMIT = 0.1 # 请求间隔(秒) def baidu_geocode(address, api_key): """百度地理编码（无SN版）""" url = f"http://api.map.baidu.com/geocoding/v3/?address={address}&ak={api_key}&output=json" try: response = requests.get(url, timeout=5) data = response.json() if data.get('status') == 0: result = data.get('result', {}) return { 'baidu_标准地址': result.get('formatted_address', ''), 'baidu_经度': result.get('location', {}).get('lng', ''), 'baidu_纬度': result.get('location', {}).get('lat', ''), 'baidu_置信度': result.get('confidence', ''), 'baidu_解析状态': '成功', 'baidu_级别': result.get('level', ''), 'baidu_api_key': api_key[-4:] # 记录后四位 } return { 'baidu_标准地址': '', 'baidu_经度': '', 'baidu_纬度': '', 'baidu_置信度': '', 'baidu_解析状态': f"失败: {data.get('message', '未知错误')}", 'baidu_级别': '', 'baidu_api_key': api_key[-4:] } except Exception as e: return { 'baidu_标准地址': '', 'baidu_经度': '', 'baidu_纬度': '', 'baidu_置信度': '', 'baidu_解析状态': f"异常: {str(e)}", 'baidu_级别': '', 'baidu_api_key': api_key[-4:] } def gaode_geocode(address, api_key): """高德地理编码""" url = f"https://restapi.amap.com/v3/geocode/geo?address={address}&key={api_key}" try: response = requests.get(url, timeout=5) data = response.json() if data.get('status') == '1' and data.get('count') != '0': geo = data['geocodes'][0] province = geo.get('province', '') city = geo.get('city', province) # 处理直辖市 return { 'gaode_省份': province, 'gaode_城市': city, 'gaode_区县': geo.get('district', ''), 'gaode_标准地址': geo.get('formatted_address', ''), 'gaode_经度': geo.get('location', '').split(',')[0] if geo.get('location') else '', 'gaode_纬度': geo.get('location', '').split(',')[1] if geo.get('location') else '', 'gaode_解析状态': '成功', 'gaode_置信度': geo.get('level', ''), 'gaode_api_key': api_key[-4:] # 记录后四位 } return { 'gaode_省份': '', 'gaode_城市': '', 'gaode_区县': '', 'gaode_标准地址': '', 'gaode_经度': '', 'gaode_纬度': '', 'gaode_解析状态': f"失败: {data.get('info', '未知错误')}", 'gaode_置信度': '', 'gaode_api_key': api_key[-4:] } except Exception as e: return { 'gaode_省份': '', 'gaode_城市': '', 'gaode_区县': '', 'gaode_标准地址': '', 'gaode_经度': '', 'gaode_纬度': '', 'gaode_解析状态': f"异常: {str(e)}", 'gaode_置信度': '', 'gaode_api_key': api_key[-4:] } def cross_validate(gaode, baidu): """结果交叉验证""" validation = {} gaode_ok = gaode['gaode_解析状态'] == '成功' baidu_ok = baidu['baidu_解析状态'] == '成功' if not gaode_ok and not baidu_ok: return { '交叉验证结果': '双API解析失败', '验证说明': f"高德:{gaode['gaode_解析状态']}, 百度:{baidu['baidu_解析状态']}" } if not gaode_ok: return {'交叉验证结果': '仅百度成功', '验证说明': f"高德:{gaode['gaode_解析状态']}"} if not baidu_ok: return {'交叉验证结果': '仅高德成功', '验证说明': f"百度:{baidu['baidu_解析状态']}"} try: # 计算经纬度距离差异 point_a = (float(gaode['gaode_纬度']), float(gaode['gaode_经度'])) point_b = (float(baidu['baidu_纬度']), float(baidu['baidu_经度'])) distance = geodesic(point_a, point_b).km validation['验证说明'] = f"坐标差{distance:.3f}公里" if distance < 0.5: validation['交叉验证结果'] = '坐标高度一致' elif distance < 2: validation['交叉验证结果'] = '坐标基本一致' else: validation['交叉验证结果'] = '坐标差异较大' gaode_addr = f"{gaode['gaode_省份']}{gaode['gaode_城市']}{gaode['gaode_区县']}" if gaode_addr in baidu['baidu_标准地址']: validation['验证说明'] += " | 行政区划一致" else: validation['验证说明'] += " | 行政区划不符" return validation except: return {'交叉验证结果': '验证异常', '验证说明': '坐标转换失败'} def validate_with_district_center(row): """行政区划中心验证""" if row['gaode_解析状态'] != '成功' or not row['gaode_经度']: return '无法验证' try: district = row['gaode_区县'] or row['gaode_城市'] or row['gaode_省份'] current_key = GAODE_KEYS[0] if not row['gaode_api_key'] else [k for k in GAODE_KEYS if k.endswith(row['gaode_api_key'])][0] url = f"https://restapi.amap.com/v3/config/district?keywords={district}&key={current_key}" resp = requests.get(url, timeout=5) data = resp.json() if data['status'] == '1' and data['districts']: center = data['districts'][0]['center'].split(',') center_lng, center_lat = float(center[0]), float(center[1]) target_lng = float(row['gaode_经度']) target_lat = float(row['gaode_纬度']) distance = geodesic((center_lat, center_lng), (target_lat, target_lng)).km if distance < 3: return f'准确(距中心{distance:.1f}km)' return f'偏差较大(距中心{distance:.1f}km)' return '获取中心失败' except: return '验证异常' def process_excel(input_file, output_file, sheet_name, address_cols): """处理Excel主流程""" df = pd.read_excel(input_file, sheet_name=sheet_name) # 校验地址列 missing_cols = [col for col in address_cols if col not in df.columns] if missing_cols: raise ValueError(f"缺少必要列: {missing_cols}") print(f"开始处理 {len(df)} 条记录...") results = [] for index, row in df.iterrows(): # 拼接地址 address = ''.join(str(row[col]).strip() for col in address_cols if pd.notna(row[col])) if not address: empty_result = { '原始地址': '', 'gaode_省份': '', 'gaode_城市': '', 'gaode_区县': '', 'gaode_标准地址': '', 'gaode_经度': '', 'gaode_纬度': '', 'gaode_解析状态': '空地址', 'gaode_api_key': '', 'baidu_标准地址': '', 'baidu_经度': '', 'baidu_纬度': '', 'baidu_解析状态': '空地址', 'baidu_api_key': '', '交叉验证结果': '空地址', '验证说明': '地址为空' } results.append(empty_result) continue # 轮换密钥 current_gaode_key = next(gaode_key_cycle) current_baidu_key = next(baidu_key_cycle) # 获取地理编码 gaode = gaode_geocode(address, current_gaode_key) time.sleep(RATE_LIMIT) baidu = baidu_geocode(address, current_baidu_key) merged = { '原始地址': address, **gaode, **baidu, **cross_validate(gaode, baidu) } results.append(merged) # 进度显示 if (index+1) % 10 == 0: print(f"已处理 {index+1}/{len(df)} 条") time.sleep(RATE_LIMIT) # 合并结果 result_df = pd.DataFrame(results) final_df = pd.concat([df, result_df], axis=1) # 行政区验证 print("进行行政区验证...") final_df['行政区划验证'] = final_df.apply(validate_with_district_center, axis=1) # 保存结果 final_df.to_excel(output_file, index=False) print(f"结果已保存至: {output_file}") # 添加颜色标记 add_color_to_excel(output_file) def add_color_to_excel(file_path): """结果着色""" wb = load_workbook(file_path) ws = wb.active color_map = { '成功': '00FF00', # 绿色 '失败': 'FF0000', # 红色 '异常': 'FFFF00', # 黄色 '一致': '00FF00', '偏差': 'FFC000', # 橙色 '空地址': '808080' # 灰色 } # 获取列索引 col_index = {cell.value: idx for idx, cell in enumerate(ws[1], 1)} for row in ws.iter_rows(min_row=2): # 高德状态着色 gaode_status = row[col_index['gaode_解析状态']-1].value if '成功' in gaode_status: row[col_index['gaode_解析状态']-1].fill = PatternFill(fgColor=color_map['成功'], fill_type="solid") # 百度状态着色 baidu_status = row[col_index['baidu_解析状态']-1].value if '成功' in baidu_status: row[col_index['baidu_解析状态']-1].fill = PatternFill(fgColor=color_map['成功'], fill_type="solid") # 交叉验证着色 cross_result = row[col_index['交叉验证结果']-1].value if '高度一致' in cross_result: row[col_index['交叉验证结果']-1].fill = PatternFill(fgColor=color_map['成功'], fill_type="solid") elif '差异较大' in cross_result: row[col_index['交叉验证结果']-1].fill = PatternFill(fgColor=color_map['偏差'], fill_type="solid") # 行政区验证着色 district_valid = row[col_index['行政区划验证']-1].value if '准确' in district_valid: row[col_index['行政区划验证']-1].fill = PatternFill(fgColor=color_map['成功'], fill_type="solid") elif '偏差' in district_valid: row[col_index['行政区划验证']-1].fill = PatternFill(fgColor=color_map['偏差'], fill_type="solid") wb.save(file_path) if __name__ == '__main__': process_excel(INPUT_FILE, OUTPUT_FILE, SHEET_NAME, ADDRESS_COLS)

运行结果截图：

以10647条爬虫获取的医疗机构地址为例，运行2+hours出结果：

80%地址解析结果可以通过，20%解析结果仍需人工进行复核与调整。