如何使用Python Pandas根据连接与断开时间按指定间隔统计场所24小时内的当前活跃连接数

Got it, let's break this down into actionable steps that leverage Pandas' time-series tools to efficiently calculate active connections at configurable intervals over a 24-hour window. This approach avoids slow loops and scales well even with large datasets.

Step 1: Prep Your Time Data #

First, make sure your connection/disconnection timestamps are properly parsed as datetime objects. Also, handle any missing disconnection dates (for connections that are still active at the end of your 24-hour window):

import pandas as pd

# Convert columns to datetime (adjust column names if needed)
df['connection_date'] = pd.to_datetime(df['connection_date'])
df['disconnection_date'] = pd.to_datetime(df['disconnection_date'])

# Define your 24-hour window (adjust start time to your needs, e.g., start at midnight)
start_time = pd.Timestamp('2024-01-01 00:00:00')
end_time = start_time + pd.Timedelta(hours=24)

# Fill missing disconnection dates with the end of your 24-hour window
df['disconnection_date'] = df['disconnection_date'].fillna(end_time)

Step 2: Create an Event-Based Dataset #

Instead of checking each interval manually, we'll model connections as "+1" events and disconnections as "-1" events. This lets us use cumulative sums to track active connections over time:

# Create connection events (timestamp = connection time, change = +1)
connection_events = df[['connection_date', 'RouterName']].rename(columns={'connection_date': 'timestamp'})
connection_events['change'] = 1

# Create disconnection events (timestamp = disconnection time, change = -1)
disconnection_events = df[['disconnection_date', 'RouterName']].rename(columns={'disconnection_date': 'timestamp'})
disconnection_events['change'] = -1

# Combine both events into one DataFrame
events = pd.concat([connection_events, disconnection_events]).sort_values(['RouterName', 'timestamp'])

Step 3: Calculate Cumulative Active Connections #

Group events by router, then compute the cumulative sum of changes to get the active connection count at every event timestamp:

# Calculate active connections over time for each router
events['active_connections'] = events.groupby('RouterName')['change'].cumsum()

Step 4: Generate Your Configurable Interval Timestamps #

Create a sequence of timestamps at your desired interval (e.g., every 5 minutes) across the 24-hour window:

# Set your interval (adjust this value to your needs)
interval_minutes = 5

# Generate all timestamps for the 24-hour window
stats_timestamps = pd.date_range(start=start_time, end=end_time, freq=f'{interval_minutes}T')

Step 5: Map Intervals to Active Connection Counts #

Use merge_asof to match each interval timestamp to the most recent event data, giving you the active connection count at that exact moment:

# Create a DataFrame for your interval timestamps, paired with each router
stats_df = pd.DataFrame({'timestamp': stats_timestamps})
stats_df = stats_df.assign(key=1)  # Dummy key for cross join
router_df = pd.DataFrame({'RouterName': df['RouterName'].unique(), 'key': 1})
stats_df = stats_df.merge(router_df, on='key').drop('key', axis=1)

# Merge interval timestamps with event data to get active counts
stats_df = pd.merge_asof(
    stats_df.sort_values(['RouterName', 'timestamp']),
    events.sort_values(['RouterName', 'timestamp']),
    on='timestamp',
    by='RouterName',
    direction='backward'  # Get the latest event before/at the interval timestamp
)

# Fill any missing values (for intervals before the first connection) with 0
stats_df['active_connections'] = stats_df['active_connections'].fillna(0).astype(int)

# Clean up and sort the final output
stats_df = stats_df[['timestamp', 'RouterName', 'active_connections']].sort_values(['RouterName', 'timestamp'])

Key Notes & Optimizations #

• Efficiency: This event-based approach is way faster than looping through each interval and filtering the DataFrame every time—critical if you have thousands of connections.
• Flexibility: Adjust interval_minutes to any value (e.g., 1, 10, 15) to match your needs.
• Aggregation: If you want total active connections across all routers (instead of per-router), just remove the RouterName grouping steps.
• Edge Cases: We handled missing disconnection dates, but you might also want to filter out connections that start after the 24-hour window or end before it (if that's relevant to your use case).

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