在Python/Pandas中计算点列表间时间与距离的最优方法

我是Python编程新手，正在学习阶段。现在需要处理大量点位数据，不需要生成所有起终点的排列组合矩阵，只需要按Excel里的每行数据逐一处理。尝试用googlemaps库的distance_matrix函数，但这个函数返回的是矩阵形式的结果，不符合我的需求。

谷歌驾车时间计算代码 #

def Google_drive_time(o, d, departure_time
                      ,model, mode, 
                      avoid=False
                      ):
   def make_chunks(l, n):
        for i in range(0, len(l), n):
            yield l[i:i + n]

    assert (type(o) == tuple and len(o) == 2 and isinstance(o[0], float) 
        and isinstance(o[1], float))
    
    models = {'optimistic','best_guess','pessimistic'}
    if model not in models:
        raise ValueError("results: model must be one of %r." % models)

mychunks = list(make_chunks(d, 25))
    google_times, google_distances = [], []
    for chunk in mychunks:
        result = gmaps.distance_matrix(origins=o,
                                       destinations=chunk
                                       ,mode=mode
                                       ,avoid=avoid
                                       ,units='imperial'
                                       ,traffic_model=model
                                       ,departure_time=departure_time)

Excel数据读取整合代码 #

def extract_combine_input_mix(inputfile):
    import itertools
    
    times = pd.read_excel(input_file, sheet_name='Times')
    origins = pd.read_excel(input_file, sheet_name='Origins')
    destinations = pd.read_excel(input_file, sheet_name='Destinations')
    
    #Combine key values from each input
    days=times['Time: Day of Week'].dropna().unique().astype(int)
    hours=times['Time: Hour'].dropna().unique().astype(int)
    minutes=times['Time: Minute'].dropna().unique().astype(int)
    origins_list=origins['Origin']
    destinations_list=destinations['Destination']
    
    
    df = pd.DataFrame(list(itertools.product(origins_list,destinations_list,days,hours,minutes)),\
                      columns=['Origin','Destination','Time: Day of Week','Time: Hour','Time: Minute'])
    
    df=pd.merge(df,origins,on='Origin',how='left')
    df=pd.merge(df,destinations,on='Destination',how='left')
    
    return df


df=extract_combine_input_mix(input_file)
columns = df.columns.values.tolist()

df['origin'] = list(zip(df['Origin Latitude'], df['Origin Longitude']))
df['destination'] = list(zip(df['Destination Latitude'], df['Destination Longitude']))
df['time'] = list(zip(df['Time: Day of Week'], df['Time: Hour'], df['Time: Minute']))

核心思路 #

既然不需要矩阵结果，而是要逐行匹配Excel里的起终点对，直接让distance_matrix接收等长的起点、终点列表，解析结果时取每行对应位置的元素即可。同时保留批量处理逻辑，避免触发API调用限制。

修改后的代码 #

1. 调整Excel数据处理（移除不必要的全排列）

如果你的业务不需要所有起终点的排列组合，直接读取并关联原始数据即可：

def extract_combine_input_mix(inputfile):
    # 读取各工作表数据
    times = pd.read_excel(input_file, sheet_name='Times')
    origins = pd.read_excel(input_file, sheet_name='Origins')
    destinations = pd.read_excel(input_file, sheet_name='Destinations')
    
    # 按实际业务逻辑关联数据（这里假设Times表每行对应一个待处理的任务，关联起终点）
    # 替换成你的实际关联逻辑，避免用itertools.product生成全排列
    df = times.merge(origins, on='Origin', how='left')
    df = df.merge(destinations, on='Destination', how='left')
    
    # 构造起终点经纬度元组，以及Unix时间戳格式的出发时间
    df['origin'] = list(zip(df['Origin Latitude'], df['Origin Longitude']))
    df['destination'] = list(zip(df['Destination Latitude'], df['Destination Longitude']))
    df['departure_time'] = df.apply(lambda row: pd.Timestamp(
        year=2024,  # 可根据实际需求调整年份，或从Excel读取
        month=1,
        day=row['Time: Day of Week'],
        hour=row['Time: Hour'],
        minute=row['Time: Minute']
    ).timestamp(), axis=1)
    
    return df

df = extract_combine_input_mix(input_file)

2. 适配逐行处理的谷歌API调用函数

def Google_drive_time(origins, destinations, departure_times, model, mode, avoid=False):
    # 验证traffic_model参数合法性
    valid_models = {'optimistic','best_guess','pessimistic'}
    if model not in valid_models:
        raise ValueError(f"model must be one of {valid_models}")
    
    # 批量处理函数，按API限制拆分请求
    def make_chunks(data, chunk_size):
        for i in range(0, len(data), chunk_size):
            yield data[i:i+chunk_size]
    
    # 打包起终点和出发时间为批次
    batches = list(make_chunks(list(zip(origins, destinations, departure_times)), 25))
    
    results = []
    for batch in batches:
        batch_origins = [item[0] for item in batch]
        batch_destinations = [item[1] for item in batch]
        batch_times = [item[2] for item in batch]
        
        # 调用distance_matrix，等长的origins和destinations会一一对应
        result = gmaps.distance_matrix(
            origins=batch_origins,
            destinations=batch_destinations,
            mode=mode,
            avoid=avoid,
            units='imperial',
            traffic_model=model,
            departure_time=batch_times[0] if len(set(batch_times)) == 1 else None
            # 若同批次出发时间不同，需拆分批次或按API规则处理
        )
        
        # 解析结果，提取每行对应的行驶时间和距离
        for row in result['rows']:
            element = row['elements'][0]
            if element['status'] == 'OK':
                results.append({
                    'drive_time_seconds': element['duration_in_traffic']['value'],
                    'distance_meters': element['distance']['value']
                })
            else:
                results.append({
                    'drive_time_seconds': None,
                    'distance_meters': None,
                    'error_status': element['status']
                })
    
    return results

# 调用函数处理所有行数据
output_results = Google_drive_time(
    origins=df['origin'].tolist(),
    destinations=df['destination'].tolist(),
    departure_times=df['departure_time'].tolist(),
    model='best_guess',
    mode='driving'
)

# 将结果合并回原DataFrame
df = pd.concat([df, pd.DataFrame(output_results)], axis=1)

关键说明 #

• 当origins和destinations为等长列表时，distance_matrix返回的矩阵中，rows[i]['elements'][0]就是第i个起点到第i个终点的结果，完美对应Excel每行数据。
• 注意Google Maps API的调用配额限制，批量处理时控制单次请求的数量（建议不超过25对）。
• 出发时间需转换为Unix时间戳，pd.Timestamp.timestamp()可快速实现该转换。

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