如何用Pandas基于经纬度与时间计算缺失的平均速度值

给定如下包含缺失值的DataFrame，部分行仅记录时间，latitude、longitude、speed均为缺失值。需要基于这些缺失行的前一行有效数据和后一行有效数据，通过Haversine公式计算行驶距离，结合时间差得到平均速度，填充到缺失行中。

原始DataFrame代码 #

import pandas as pd
import numpy as np

# 替换ID为实际业务值，此处用示例值'VEH001'
data = [
    ['VEH001', '2022-04-23T03:36:26Z', 60, 10, 83],
    ['VEH001', '2022-04-23T03:37:30Z', np.nan, np.nan, np.nan],
    ['VEH001', '2022-04-23T03:37:48Z', np.nan, np.nan, np.nan],
    ['VEH001', '2022-04-23T03:38:24Z', 61, 11, 72],
    ['VEH001', '2022-04-23T03:44:20Z', 63, 13, 75],
    ['VEH001', '2022-04-23T03:45:02Z', np.nan, np.nan, np.nan],
    ['VEH001', '2022-04-23T03:45:06Z', np.nan, np.nan, np.nan],
    ['VEH001', '2022-04-23T03:45:08Z', np.nan, np.nan, np.nan],
    ['VEH001', '2022-04-23T03:45:12Z', np.nan, np.nan, np.nan],
    ['VEH001', '2022-04-23T03:45:48Z', 69, 15, 61]
]

df = pd.DataFrame(data=data,
                  columns=['ID', 'time', 'latitude', 'longitude', 'speed'])

实现步骤 #

步骤1：预处理时间列 #

将time列转换为datetime类型，方便后续计算时间差：

df['time'] = pd.to_datetime(df['time'])

步骤2：匹配前后有效行数据 #

用向前填充（ffill）和向后填充（bfill）为缺失行绑定前后的有效经纬度与时间，同时标记缺失行：

# 标记speed缺失的行
df['is_missing'] = df['speed'].isna()

# 获取前一行有效数据
df['prev_lat'] = df['latitude'].ffill()
df['prev_lon'] = df['longitude'].ffill()
df['prev_time'] = df['time'].ffill()

# 获取后一行有效数据
df['next_lat'] = df['latitude'].bfill()
df['next_lon'] = df['longitude'].bfill()
df['next_time'] = df['time'].bfill()

步骤3：实现Haversine距离计算公式 #

用于计算球面上两点的大圆距离（单位：千米）：

def haversine(lat1, lon1, lat2, lon2):
    # 转换为弧度
    lat1, lon1, lat2, lon2 = map(np.radians, [lat1, lon1, lat2, lon2])
    
    # Haversine核心计算逻辑
    dlat = lat2 - lat1
    dlon = lon2 - lon1
    a = np.sin(dlat/2)**2 + np.cos(lat1) * np.cos(lat2) * np.sin(dlon/2)**2
    c = 2 * np.arcsin(np.sqrt(a))
    # 地球半径取6371千米
    km = 6371 * c
    return km

步骤4：计算平均速度并填充缺失值 #

基于前后有效点的距离和时间差计算平均速度，替换缺失的speed值：

# 计算前后有效点的总距离
df['total_distance'] = haversine(df['prev_lat'], df['prev_lon'], df['next_lat'], df['next_lon'])

# 计算总时间差（转换为小时）
df['total_hours'] = (df['next_time'] - df['prev_time']).dt.total_seconds() / 3600

# 计算平均速度，避免除以0的情况
df['avg_speed'] = np.where(df['total_hours'] == 0, 0, df['total_distance'] / df['total_hours'])

# 填充缺失的speed值
df['speed'] = np.where(df['is_missing'], df['avg_speed'], df['speed'])

# 清理临时辅助列（可选）
df = df.drop(['is_missing', 'prev_lat', 'prev_lon', 'prev_time', 'next_lat', 'next_lon', 'next_time', 'total_distance', 'total_hours', 'avg_speed'], axis=1)

处理完成后，原本缺失speed的行将被填充为基于前后有效点计算的平均速度。

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