Python计算Excel两组经纬度最近位置时遇类型错误求助

运行代码时出现以下错误：

File "C:\Users\mohamed.h.mohamad\.spyder-py3\temp.py", line 19, in dist
    lat1, long1, lat2, long2 = map(radians, [lat1, long1, lat2, long2])

TypeError: must be real number, not str

已尝试将Lat和Long转为Float64类型但问题未解决，代码如下：

import pandas as pd
from math import radians, cos, sin, asin, sqrt
Sites = pd.read_excel("E:\Request/site.xlsx")
Sites.head()
Complaint = pd.read_excel("E:\Request/complaints.xlsx")
Complaint.head()
Sites['lat'].astype(dtype = 'float64')
Sites['long'].astype(dtype = 'float64')
Complaint['lat'].astype(dtype = 'float64')
Complaint['long'].astype(dtype = 'float64')
def dist(lat1, long1, lat2, long2):
    # convert decimal degrees to radians 
    lat1, long1, lat2, long2 = map(radians, [lat1, long1, lat2, long2])
    # haversine formula 
    dlon = long2 - long1 
    dlat = lat2 - lat1 
    a = sin(dlat/2)**2 + cos(lat1) * cos(lat2) * sin(dlon/2)**2
    c = 2 * asin(sqrt(a)) 
    # Radius of earth in kilometers is 6371
    km = 6371* c
    return km

def find_nearest(lat, long):
    distances = Sites.apply(
        lambda row: dist(lat, long, row['lat'], row['long']), 
        axis=1)
    return Sites.loc[distances.idxmin(), 'Site']
Complaint['Site'] = Complaint.apply(
    lambda row: find_nearest(row['lat'], row['long']), 
    axis=1)
# To check the data frame if it has a new column of hotel name (for each and every member's location in the list)
Complaint.head()
Complaint = pd.merge(Complaint,Sites[['Site','lat','lon']],on='Site', how='left')
# Rename the new columns as both the columns has same name, and python gets confused 
Complaint=Complaint.rename(columns = {'lat_x':'m_lat','lon_x':'m_lon','lat_y':'h_lat','lon_y':'h_lon'})
Complaint.head()

def haversine(lon1, lat1, lon2, lat2):
    """
    Calculate the great circle distance between two points 
    on the earth (specified in decimal degrees)
    """
    # convert decimal degrees to radians 
    lon1, lat1, lon2, lat2 = map(radians, [lon1, lat1, lon2, lat2])
    # haversine formula 
    dlon = lon2 - lon1 
    dlat = lat2 - lat1 
    a = sin(dlat/2)**2 + cos(lat1) * cos(lat2) * sin(dlon/2)**2
    c = 2 * asin(sqrt(a)) 
    # Radius of earth in kilometers is 6371
    km = 6371* c
    return km
# Creating a new column to generate the output by passing lat long information to Haversine Equation
Complaint['distance'] = [haversine(Complaint.m_lon[i],Complaint.m_lat[i],Complaint.h_lon[i],Complaint.h_lat[i]) for i in range(len(Complaint))]
Complaint['distance'] = Complaint['distance'].round(decimals=3)
# Printing the data table 
Complaint.head()
Complaint.to_excel("output.xlsx")

1. 修复类型转换问题 #

astype()方法不会原地修改DataFrame，必须将结果赋值回原列，否则原列数据类型仍为字符串：

# 替换原来的类型转换代码
Sites['lat'] = Sites['lat'].astype('float64')
Sites['long'] = Sites['long'].astype('float64')
Complaint['lat'] = Complaint['lat'].astype('float64')
Complaint['long'] = Complaint['long'].astype('float64')

如果数据中存在无法转换的非数值字符，可添加errors='coerce'将无效值转为NaN，方便后续处理：

Sites['lat'] = pd.to_numeric(Sites['lat'], errors='coerce')
Sites['long'] = pd.to_numeric(Sites['long'], errors='coerce')
Complaint['lat'] = pd.to_numeric(Complaint['lat'], errors='coerce')
Complaint['long'] = pd.to_numeric(Complaint['long'], errors='coerce')

2. 修复列名不一致问题 #

合并数据时，Sites表中使用的列是long，但合并代码里写的是lon，这会导致合并后h_lon全为NaN，需要统一列名：

# 修改合并代码，匹配Sites表的列名
Complaint = pd.merge(Complaint,Sites[['Site','lat','long']],on='Site', how='left')
# 对应修改重命名代码
Complaint=Complaint.rename(columns = {'lat_x':'m_lat','long_x':'m_lon','lat_y':'h_lat','long_y':'h_lon'})

3. 优化距离计算效率 #

原代码用循环计算haversine距离效率较低，可改用pandas的向量化操作：

# 替换循环计算的代码
Complaint['distance'] = haversine(Complaint['m_lon'], Complaint['m_lat'], Complaint['h_lon'], Complaint['h_lat'])
Complaint['distance'] = Complaint['distance'].round(decimals=3)

同时确保haversine函数支持向量化输入（math模块函数不支持，可改用numpy实现）：

import numpy as np

def haversine(lon1, lat1, lon2, lat2):
    # 转换为弧度
    lon1, lat1, lon2, lat2 = map(np.radians, [lon1, lat1, lon2, lat2])
    # 半正矢公式
    dlon = lon2 - lon1 
    dlat = lat2 - lat1 
    a = np.sin(dlat/2)**2 + np.cos(lat1) * np.cos(lat2) * np.sin(dlon/2)**2
    c = 2 * np.arcsin(np.sqrt(a)) 
    km = 6371 * c
    return km

4. 处理缺失值 #

转换类型后若存在NaN，可根据需求删除或填充：

# 删除包含缺失值的行
Sites = Sites.dropna(subset=['lat', 'long'])
Complaint = Complaint.dropna(subset=['lat', 'long'])

内容的提问来源于stack exchange，提问作者Mohamed Hassan