group by - flagging every 6 rows in R if there are two (1) within them

Question

Closed. This question needs debugging details. It is not currently accepting answers. question from:https://stackoverflow.com/questions/65651346/flagging-every-6-rows-in-r-if-there-are-two-1-within-them

Answer 1

It seems you are new to SO. This is a good and important read which will make sure you attract people to work on your Question.

Here is a solution for how I understood your question. A time sequence over one year is created in steps of 10 minutes.

Vector check has sampled 0 or 1 over the length of interval. For reproducability I have set a seed.

A DF is made out of both. This DF is grouped for month, day and hour and a value is created which sums the 1 's per hour. If this number is 2 or greater a new variable flag gets an NA , if not it gets an empty string.

At the end only the relevant variables are selected.

library(tidyverse)
library(lubridate)

set.seed(1)
interval <- seq(ymd_hms('2020-12-01 00:00:00'), 
                by = '10 min',length.out=(60*24*365/10))

check <- sample(c(0,1), length(interval), replace = T)
df <- data.frame(interval, check)

df %>% 
  mutate(hour = hour(interval)) %>% 
  group_by(month(interval),day(interval), hour(interval)) %>% 
  mutate(N = sum(check)) %>% 
  mutate(flag = ifelse(N >= 2, NA, '')) %>% 
  ungroup() %>% 
  dplyr::select(interval, check,N, flag) 
#> # A tibble: 52,560 x 4
#>    interval            check     N flag 
#>    <dttm>              <dbl> <dbl> <chr>
#>  1 2020-12-01 00:00:00     0     2 <NA> 
#>  2 2020-12-01 00:10:00     1     2 <NA> 
#>  3 2020-12-01 00:20:00     0     2 <NA> 
#>  4 2020-12-01 00:30:00     0     2 <NA> 
#>  5 2020-12-01 00:40:00     1     2 <NA> 
#>  6 2020-12-01 00:50:00     0     2 <NA> 
#>  7 2020-12-01 01:00:00     0     2 <NA> 
#>  8 2020-12-01 01:10:00     0     2 <NA> 
#>  9 2020-12-01 01:20:00     1     2 <NA> 
#> 10 2020-12-01 01:30:00     1     2 <NA> 
#> # … with 52,550 more rows