如何基于pandas实现按日滑动60分钟时段行程数分组统计

Great question! Let's break this down step by step using pandas, with groupby at the core of our daily segmentation. The goal is to count trips in every consecutive 60-minute window starting at each minute (0:00 to 22:59) within a day, with all windows constrained to the same calendar day.

Assumptions About Your Data #

First, let's assume your dataset (df) has two datetime columns:

• start_time: The timestamp when a trip begins
• end_time: The timestamp when a trip ends

All timestamps should be in datetime64 format (use pd.to_datetime() to convert if needed).

Step 1: Generate Minute-Level Trip Presence Counts #

First, we need to create a minute-by-minute timeline that tracks how many active trips are running at each minute. This forms the base for our sliding window calculations.

import pandas as pd
import numpy as np

# Convert timestamps to minute-level precision
df['start_min'] = df['start_time'].floor('min')
df['end_min'] = df['end_time'].ceil('min')

# Create a complete timeline of minutes covering all trip activity
min_time = df['start_min'].min()
max_time = df['end_min'].max()
all_minutes = pd.date_range(start=min_time, end=max_time, freq='min')

# Convert timestamps to integer timestamps (in minutes since epoch) for fast interval checks
start_ts = df['start_min'].view('int64') // 60_000_000_000  # Nanoseconds to minutes
end_ts = df['end_min'].view('int64') // 60_000_000_000
all_ts = all_minutes.view('int64') // 60_000_000_000

# Calculate how many trips are active at each minute
in_interval = (start_ts[:, None] <= all_ts) & (all_ts < end_ts[:, None])
minute_trip_counts = pd.Series(in_interval.sum(axis=0), index=all_minutes)

This efficiently counts active trips per minute without looping through each trip (critical for large datasets).

Step 2: Group by Day & Calculate Sliding 60-Minute Sums #

Pandas' rolling() function calculates backward-looking windows by default, but we need forward-looking windows (e.g., 0:00 → 0:00-1:00). We'll use a reverse-index trick to get forward behavior, then group by day to keep windows constrained to a single day.

# Group the minute-level counts by calendar date
daily_groups = minute_trip_counts.groupby(minute_trip_counts.index.date)

final_results = []

for date, daily_data in daily_groups:
    # Reverse the daily data to turn backward rolling into forward rolling
    reversed_daily = daily_data.iloc[::-1]
    
    # Calculate rolling sum for 60-minute windows (requires all 60 minutes to exist)
    rolling_sums = reversed_daily.rolling(window=60, min_periods=60).sum()
    
    # Reverse back to restore original time order
    rolling_sums = rolling_sums.iloc[::-1]
    
    # Filter out windows starting at 23:00 or later (per your requirement)
    cutoff = pd.Timestamp(date) + pd.Timedelta(hours=23)
    valid_windows = rolling_sums[rolling_sums.index < cutoff]
    
    final_results.append(valid_windows)

# Combine results into a single Series
trip_window_counts = pd.concat(final_results)

Step 3: Interpret the Results #

• The index of trip_window_counts is the start time of each 60-minute window (e.g., 2024-01-01 00:00:00 corresponds to the window 0:00-1:00).
• The value is the number of trips that overlapped with that 60-minute window.

Key Details to Note #

• Trip Overlap Logic: A trip is counted in a window if any part of it falls within the 60-minute period. Adjust the in_interval condition if you need to count only trips that start or end within the window.
• Instant Trips: If a trip starts and ends in the same minute, ceil('min') will set end_min to the next minute, ensuring the trip is counted in that minute.
• Cross-Day Trips: Trips that span midnight are only counted in windows from their start time until 23:59 of the first day, and in windows from 0:00 until their end time of the second day (keeping all windows constrained to their respective days).

Example Test Case #

Let's verify with sample data:

# Sample trip data
sample_data = [
    {"start_time": pd.Timestamp("2024-01-01 00:00:00"), "end_time": pd.Timestamp("2024-01-01 01:00:00")},
    {"start_time": pd.Timestamp("2024-01-01 00:30:00"), "end_time": pd.Timestamp("2024-01-01 01:30:00")},
    {"start_time": pd.Timestamp("2024-01-01 23:00:00"), "end_time": pd.Timestamp("2024-01-02 00:00:00")}
]
df = pd.DataFrame(sample_data)

Running the above code will give you:

• trip_window_counts.loc["2024-01-01 00:00:00"] = 2 (both trips overlap with 0:00-1:00)
• trip_window_counts.loc["2024-01-01 01:00:00"] = 1 (only the second trip overlaps with 1:00-2:00)
• trip_window_counts.loc["2024-01-01 22:59:00"] = 59 (the third trip is active from 23:00-23:59, so 59 minutes of the 60-minute window have an active trip)

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