library(tidyverse)
somedata <- read_delim("DateTime,start,stop
2020-09-29 13:00:00,NA,NA
2020-09-29 13:10:00,NA,NA
2020-09-29 13:20:00,1,NA
2020-09-29 13:30:00,NA,NA
2020-09-29 13:40:00,NA,NA
2020-09-29 13:50:00,NA,1
2020-09-29 14:00:00,NA,NA
2020-09-29 14:10:00,1,NA
2020-09-29 14:20:00,NA,NA
2020-09-29 14:30:00,NA,NA
2020-09-29 14:40:00,NA,1
2020-09-29 14:20:00,NA,NA", delim = ",")
(clean_data <- somedata %>%
mutate_if(is.numeric,
~ ifelse(is.na(.), 0, .)))
(clean_data %>%
mutate(
start_cnt = cumsum(start),
stop_cnt = 1 + cumsum(stop)
) %>%
mutate(group = ifelse(start_cnt < stop_cnt,
ifelse(stop == 1, start_cnt, NA),
start_cnt
)) %>%
group_by(group) -> somedata_grouped)
(smry <- summarise(somedata_grouped, num_of_rows = n()))