is there a way to filter for the most recent occurrence within a time window?

Given your input:

df <- read.table(text = "Time     ID  Event
7:47:28  X1  A
7:47:30  X2  B
7:48:02  X3  A
7:48:04  X4  A
7:48:05  X5  B
7:50:11  X1  A
7:50:12  X2  B
7:50:15  X5  B
7:55:50  X6  A
7:55:52  X2  B", header = TRUE)

# convert to HMS
df$Time <- lubridate::hms(df$Time)

You can use slide_index_dfr to capture the IDs of B 5 seconds ahead and set it up into a dataframe. You can then change the names and add it back to your df.

xx <- slider::slide_index_dfr(df, df$Time, ~if(.$Event[1] == "A") .$ID[.$Event == "B"] else character(), .after = 5)
colnames(xx) <- paste0("Col", seq_len(ncol(xx)))
cbind(df, xx)
#>          Time ID Event Col1 Col2
#> 1  7H 47M 28S X1     A   X2 <NA>
#> 2  7H 47M 30S X2     B <NA> <NA>
#> 3   7H 48M 2S X3     A   X5 <NA>
#> 4   7H 48M 4S X4     A   X5 <NA>
#> 5   7H 48M 5S X5     B <NA> <NA>
#> 6  7H 50M 11S X1     A   X2   X5
#> 7  7H 50M 12S X2     B <NA> <NA>
#> 8  7H 50M 15S X5     B <NA> <NA>
#> 9  7H 55M 50S X6     A   X2 <NA>
#> 10 7H 55M 52S X2     B <NA> <NA>

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top