如何高效统计同日期下间隔15分钟的航线班次频次？

Got it, the matrix approach works for small datasets but falls apart with large volumes—totally makes sense. Let's switch to a more scalable method using pandas' grouping and vectorized operations, which will handle big data much better.

Step 1: Parse Raw Data into a Structured DataFrame #

First, let's clean up that raw flight data into a proper Date, Time, Route structure:

import pandas as pd

# Raw input data
raw_data = "12Feb2018 12.17.00 NYC-BOS 12Feb2018 12.19.14 NYC-BOS 12Feb2018 21.07.05 SGF-XNA 13Feb2018 11.17.00 NYC-BOS 13Feb2018 11.19.14 NYC-BOS 13Feb2018 22.07.05 SGF-XNA"

# Split into chunks of 3 elements each (Date, Time, Route)
chunks = [raw_data.split()[i:i+3] for i in range(0, len(raw_data.split()), 3)]

# Create DataFrame
df = pd.DataFrame(chunks, columns=["Date", "Time", "Route"])

# Convert Time to full datetime objects for easy window calculations
df["datetime"] = pd.to_datetime(df["Date"] + " " + df["Time"], format="%d%b%Y %H.%M.%S")

Step 2: Scalable Window Counting #

Instead of creating an O(n²) matrix, we'll use grouping + sorted time search which runs in O(n log n) time per group—way better for large datasets. This method avoids comparing every flight pair directly:

def count_flights_in_window(group):
    # Sort flights in the group by departure time
    group_sorted = group.sort_values("datetime").reset_index(drop=True)
    
    # For each flight, find the last index where departure time is <= current flight + 15 mins
    # This gives the end of the 15-minute window starting at the current flight
    window_end_indices = group_sorted["datetime"].searchsorted(
        group_sorted["datetime"] + pd.Timedelta(minutes=15),
        side="right"
    )
    
    # Calculate how many other flights are in each window (exclude the current flight itself)
    companion_counts = window_end_indices - group_sorted.index - 1
    
    # For your sample output, it looks like you want to count all flights that have at least one companion in the window
    return (companion_counts > 0).sum()

# Group by Date and Route, then apply the counting function
result = df.groupby(["Date", "Route"]).apply(count_flights_in_window).reset_index(name="Frequency")

Step 3: Output Adjustments (If Needed) #

Running the code above on your sample data will produce this result (aligned with the actual flight data):

If your sample's SGF-XNA value was a typo, this matches the raw data. If you meant to count total overlapping pairs (e.g., 2 flights = 1 unique pair), tweak the function like this:

def count_total_overlapping_pairs(group):
    group_sorted = group.sort_values("datetime").reset_index(drop=True)
    window_end_indices = group_sorted["datetime"].searchsorted(
        group_sorted["datetime"] + pd.Timedelta(minutes=15),
        side="right"
    )
    # Sum all companion counts, then divide by 2 to avoid double-counting pairs
    return sum(window_end_indices - group_sorted.index - 1) // 2

Why This Works #

• Grouping: We isolate each date-route combination so we only compare relevant flights, avoiding unnecessary calculations.
• Binary Search: searchsorted uses binary search (O(log n) per entry) to find window bounds, which is exponentially faster than the O(n²) matrix method for large datasets.
• Vectorized Operations: All calculations run on pandas' optimized backend, avoiding slow Python loops.

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