如何用Pandas统计同分组下过去10分钟移动时间窗口内的行数？

Hey there! To solve this problem, we need to count, for each row, how many rows share the same col1 value and fall within a 10-minute window ending at the current row's timestamp. Here's a straightforward, efficient approach using pandas:

Step-by-Step Explanation #

1. Sort the Data: First, we sort the DataFrame by col1 and col2 (timestamp). This ensures that when we apply a rolling window, we're only considering rows in chronological order within each group.
2. Group and Apply Rolling Window: We group the sorted data by col1, then use pandas' rolling function with a 10-minute time window. This will count all rows in the window for each entry.

Code Implementation #

import pandas as pd

# Your sample data
d = [{'col1' : ' B', 'col2' : '2015-3-06 01:37:57'}, 
     {'col1' : ' A', 'col2' : '2015-3-06 01:39:57'}, 
     {'col1' : ' A', 'col2' : '2015-3-06 01:45:28'}, 
     {'col1' : ' B', 'col2' : '2015-3-06 02:31:44'}, 
     {'col1' : ' B', 'col2' : '2015-3-06 03:55:45'}, 
     {'col1' : ' B', 'col2' : '2015-3-06 04:01:40'}]
df = pd.DataFrame(d)
df['col2'] = pd.to_datetime(df['col2'])

# Step 1: Sort by group and timestamp
df_sorted = df.sort_values(['col1', 'col2']).reset_index(drop=True)

# Step 2: Calculate rolling count per group
df_sorted['window_count'] = df_sorted.groupby('col1').rolling(
    window='10min',  # 10-minute time window
    on='col2',       # Use timestamp column for window calculation
    closed='both'    # Include both start and end of the window
)['col1'].count().reset_index(level=0, drop=True)

# Optional: Exclude the current row from the count if needed
df_sorted['window_count_excl_self'] = df_sorted['window_count'] - 1

print(df_sorted)

Output Explanation #

Running this code will produce the following result:

• For the second A row (01:45:28), the window includes the previous A row (01:39:57) since it's within 10 minutes, hence the count is 2.
• For the last B row (04:01:40), the previous B row (03:55:45) is within 10 minutes, so the count is 2.

Key Notes #

• Sorting is Critical: The rolling window relies on chronological order within each group, so sorting first is essential to get accurate counts.
• Window Closure: The closed='both' parameter ensures that rows with exactly the same timestamp as the current row are included. If you only want rows strictly before the current timestamp, use closed='left' instead.

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