Python_计算两个省市之间的直线距离

存在如下表格：

import pandas as pd
from geopy.geocoders import Nominatim
from geopy.distance import geodesic
from tqdm import tqdm

# 初始化地理编码器
geolocator = Nominatim(user_agent="geo_distance_calculator", timeout=10)

# 缓存字典
coordinate_cache = {}

# 获取省市的经纬度
def get_coordinates(province, city):
    key = f"{province}-{city}"
    if key in coordinate_cache:
        return coordinate_cache[key]
    try:
        location = geolocator.geocode(f"{province} {city}")
        if location:
            coord = (location.latitude, location.longitude)
            coordinate_cache[key] = coord
            return coord
        else:
            return None
    except Exception as e:
        print(f"Error geocoding {province} {city}: {e}")
        return None

# 计算两点之间的直线距离
def calculate_distance(coord1, coord2):
    if coord1 and coord2:
        return geodesic(coord1, coord2).kilometers
    else:
        return None

# 读取 Excel 文件
input_file = "free.xlsx"  # 替换为你的文件名
output_file = "free_output.xlsx"
df = pd.read_excel(input_file)

# 添加进度条
tqdm.pandas()

# 添加新的列用于保存距离
df["发货坐标"] = df.progress_apply(lambda row: get_coordinates(row["发货省"], row["发货市"]), axis=1)
df["到货坐标"] = df.progress_apply(lambda row: get_coordinates(row["到货省"], row["到货市"]), axis=1)
df["直线距离 (公里)"] = df.progress_apply(lambda row: calculate_distance(row["发货坐标"], row["到货坐标"]), axis=1)

# 保存到新的 Excel 文件
df.to_excel(output_file, index=False)
print(f"处理完成，结果已保存到 {output_file}")