I have a dataset with observations over several years for each ID. I want to filter the dataset to choose the 30 day period which has the most rows for each ID.
Example: Format (Date/Month/Year)
ID Date
1 01/01/2021
1 05/01/2021
1 08/01/2021
1 07/06/2021
1 08/06/2021
Expected Result:
ID Date
1 01/01/2021
1 05/01/2021
1 08/01/2021
Since you have added both runner and dplyr in tags, the following strategy, will work
library(runner)
library(dplyr)
library(data.table) # for rleid() function. Can work without it also
dat %>% group_by(ID, l = runner(x = Date,
idx = Date,
k = "30 days",
lag = "-29 days",
f = length),
l = Reduce(function(i, j) if(i > j) i else i + l[i+1],
seq_len(length(l)-1), l[1], accumulate = TRUE),
l = rleid(l)) %>%
mutate(l = n()) %>% group_by(ID) %>%
filter(l == max(l)) %>%
select(-l)
# A tibble: 3 x 2
# Groups: ID [1]
ID Date
<int> <date>
1 1 2021-01-01
2 1 2021-01-05
3 1 2021-01-08
dput used
dat <- structure(list(ID = c(1L, 1L, 1L, 1L, 1L), Date = structure(c(18628,
18632, 18635, 18785, 18786), class = "Date")), row.names = c(NA,
-5L), class = "data.frame")
dat
> dat
ID Date
1 1 2021-01-01
2 1 2021-01-05
3 1 2021-01-08
4 1 2021-06-07
5 1 2021-06-08
Note it will work without rleid also
dat %>% group_by(ID, l = runner(x = Date,
idx = Date,
k = "30 days",
lag = "-29 days",
f = length),
l = Reduce(function(i, j) if(i > j) i else i + l[i+1],
seq_len(length(l)-1), l[1], accumulate = TRUE)) %>%
mutate(l = n()) %>% group_by(ID) %>%
filter(l == max(l)) %>%
select(-l)
# A tibble: 5 x 3
# Groups: ID, l [2]
ID Date l
<int> <date> <int>
1 1 2021-01-01 3
2 1 2021-01-05 3
3 1 2021-01-08 3
4 1 2021-06-07 5
5 1 2021-06-08 5