使用data.table计算时间滑动窗口内的同分组行数

Here's the complete code to achieve your requirement, leveraging data.table's efficient non-equi self-join functionality:

library(data.table)
library(lubridate)

# Initialize the dataset
df <- data.table(
  col1 = c('B', 'A', 'A', 'B', 'B', 'B'),
  col2 = c("2015-03-06 01:37:57", "2015-03-06 01:39:57", "2015-03-06 01:45:28", 
           "2015-03-06 02:31:44", "2015-03-06 03:55:45", "2015-03-06 04:01:40")
)

# Convert timestamp column to datetime type for arithmetic operations
df[, col2 := as_datetime(col2)]

# Define the 10-minute window in seconds
window_seconds <- 10L * 60L

# Calculate the matching row count for each row
df[, counts := df[.SD, on = .(col1, col2 >= col2 - window_seconds, col2 <= col2), .N, by = .EACHI]$N]

# View the final result
print(df)

Output: #

col1                col2 counts
1:    B 2015-03-06 01:37:57      1
2:    A 2015-03-06 01:39:57      1
3:    A 2015-03-06 01:45:28      2
4:    B 2015-03-06 02:31:44      1
5:    B 2015-03-06 03:55:45      1
6:    B 2015-03-06 04:01:40      2

How It Works: #

1. Datetime Conversion: We convert col2 to a datetime object using lubridate::as_datetime to enable timestamp arithmetic.
2. Non-Equi Self-Join:
   • The on = .(col1, col2 >= col2 - window_seconds, col2 <= col2) clause matches rows with the same col1 value and timestamps within the 10-minute window ending at the current row's time.
   • by = .EACHI ensures we compute the count of matching rows ( .N) individually for every row in the original dataset.
3. Assign Counts: We extract the computed counts from the join result and assign them to a new counts column in the original data.table.

If you want to continue with the partial code you started, here's an alternative implementation of the count step:

counts <- df[, .(t1 = col2 - window_seconds, t2 = col2)][df, on = .(col1, col2 >= t1, col2 <= t2), .N, by = .EACHI]$N
df[, counts := counts]

This approach first creates a temporary table of time ranges for each row, then joins back to the original data to calculate the required counts.

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