R语言公共交通分析：大数据框循环生成两站换乘的运行时效率问题

Hey Joe, let’s work through how to fix that runtime and efficiency issue you’re hitting with generating two-stop transfer combinations. Looping through large DataFrames like links (100k rows) and incl_1stop is almost always a bad idea in Pandas—we can leverage vectorized operations and smart data handling to speed things up drastically. Here are my top recommendations:

1. Replace Loops with Vectorized Merges (The Biggest Win) #

Instead of iterating through each row in incl_1stop, use Pandas’ built-in merge function to match valid transfers in bulk. First, define your "adaptation" rules clearly—for a one-stop route (A→B→C) to connect to another link (C→D), you’ll typically need:

• The end stop of the one-stop route (C) matches the start stop of the new link
• The arrival time at C is earlier than the departure time from C, with a reasonable transfer window (e.g., 2–30 minutes to account for walking and waiting)

Step-by-Step Implementation: #

import pandas as pd

# Rename columns in incl_1stop to avoid conflicts during merge
incl_1stop_renamed = incl_1stop.rename(columns={
    'end_stop': 'transfer_stop',
    'arrival_time': 'transfer_arrival'
})

# Rename columns in links for clarity
links_renamed = links.rename(columns={
    'start_stop': 'next_start',
    'departure_time': 'next_departure',
    'end_stop': 'final_stop',
    'arrival_time': 'final_arrival'
})

# Perform initial merge on matching transfer stop
merged = pd.merge(
    incl_1stop_renamed,
    links_renamed,
    left_on='transfer_stop',
    right_on='next_start',
    how='inner'
)

# Filter for valid transfer time windows (adjust values to fit your use case)
min_transfer_sec = 2 * 60
max_transfer_sec = 30 * 60
valid_two_stop = merged[
    (merged['next_departure'] - merged['transfer_arrival']).dt.total_seconds() >= min_transfer_sec
    & (merged['next_departure'] - merged['transfer_arrival']).dt.total_seconds() <= max_transfer_sec
]

# Clean up columns to get your final two-stop routes (A→B→C→D)
final_routes = valid_two_stop[[
    'start_stop', 'first_transfer_stop', 'transfer_stop', 'final_stop',
    'initial_departure', 'transfer_arrival', 'next_departure', 'final_arrival'
]]

2. Add Indexes to Speed Up Merges #

Pandas uses indexes for fast lookups—adding indexes to key columns will reduce merge time significantly:

# Add sorted indexes to links (start stop + departure time for precise filtering)
links = links.set_index(['start_stop', 'departure_time']).sort_index()

# Add sorted index to incl_1stop (end stop + arrival time)
incl_1stop = incl_1stop.set_index(['end_stop', 'arrival_time']).sort_index()

When you merge later, Pandas will use these sorted indexes to match rows far faster than scanning entire columns.

3. Chunk Processing for Memory-Limited Environments #

If your combined data is too large to fit in memory, split links into smaller chunks and process them one at a time:

final_routes_list = []

# Split links by start stop (logical grouping to reduce merge scope)
for start_stop, chunk in links.groupby('start_stop'):
    # Merge chunk with relevant rows from incl_1stop
    temp_merge = pd.merge(
        incl_1stop[incl_1stop.index.get_level_values('end_stop') == start_stop],
        chunk.reset_index(),
        left_on='end_stop',
        right_on='start_stop'
    )
    # Apply transfer time filter
    temp_valid = temp_merge[
        (temp_merge['departure_time'] - temp_merge['arrival_time']).dt.total_seconds() >= 120
        & (temp_merge['departure_time'] - temp_merge['arrival_time']).dt.total_seconds() <= 1800
    ]
    final_routes_list.append(temp_valid)

# Combine all chunks into one final DataFrame
final_two_stop_routes = pd.concat(final_routes_list, ignore_index=True)

4. Use Specialized Libraries for Extreme Scale #

If even vectorized Pandas operations are too slow, switch to libraries built for big data:

• Dask: Works with Pandas-like syntax but parallelizes operations across CPU cores and handles out-of-core data seamlessly.
• Vaex: Uses memory mapping to process datasets larger than your RAM, with lightning-fast filtering and merging.

5. Pre-Filter Data to Reduce Merge Size #

Before merging, trim down your datasets to only relevant rows:

• Remove links with departure times that are too early/late to be part of a valid transfer.
• Filter incl_1stop to only routes where the arrival time leaves enough room for a transfer (e.g., exclude routes arriving in the last 30 minutes of service).

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