使用Pandas统计时间区间内的已开启记录数

Great question! Handling time-windowed counts in Pandas is super straightforward once you leverage rolling window functionality with time offsets—let me walk you through this step by step.

First, let's make sure we're aligned on the problem: for each row in your DataFrame, you want to count how many "开启" (active) records exist within the T-hour window leading up to that row's timestamp. The rolling window approach works for any time interval, not just 1 hour.

Step 1: Prepare Sample Data (Match Your Scenario) #

Let's build a sample DataFrame to simulate your use case:

import pandas as pd

# Create sample data with timestamps and statuses
data = {
    'timestamp': pd.date_range(start='2024-01-01 00:00', periods=10, freq='30min'),
    'status': ['开启', '开启', '关闭', '开启', '关闭', '开启', '开启', '关闭', '开启', '关闭']
}
df = pd.DataFrame(data)

# Convert status to numeric values (1 = 开启, 0 = 关闭) for easier counting
df['status'] = df['status'].map({'开启': 1, '关闭': 0})

# Ensure timestamp column is datetime type (critical for time-based operations)
df['timestamp'] = pd.to_datetime(df['timestamp'])

Step 2: Universal Approach for Any Time Interval T #

This method works whether T is 1 hour, 2 hours, 30 minutes, or any other interval:

1. Sort by timestamp first (if your data isn't already ordered by time—this is non-negotiable for rolling windows):

df = df.sort_values('timestamp')

2. Use rolling() with a time-based window to count active records in the T-length window:

# Set timestamp as the index to enable time-based rolling
df = df.set_index('timestamp')

# Define your time interval T (e.g., '2H' for 2 hours, '1H' for 1 hour, '30min' for 30 minutes)
T = '2H'
# Calculate the count of "开启" records in the T window leading up to each timestamp
df[f'open_count_last_{T}'] = df['status'].rolling(window=T).sum()

# Reset index to restore original column structure (optional but useful for readability)
df = df.reset_index()

Running this will add a new column where each value represents the number of "开启" records in the T-hour window before that row's timestamp.

Why This Beats the dt.hour Offset Method #

The dt.hour approach you mentioned works for 1-hour intervals, but this rolling window method is far more flexible:

• It supports any time interval (e.g., '1H30min', '45min', '1D' for 1 day)
• It automatically handles non-uniform timestamps (no need to manually group or shift hours)
• It calculates precise time windows for every row, not just hour-aligned groups

Quick Edge Case Fix #

If a row's window has no records (e.g., the first row in your dataset), the sum will return NaN. You can fill these with 0 using:

df[f'open_count_last_{T}'] = df['status'].rolling(window=T).sum().fillna(0)

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