Count rows by conditions

[Marit]

Hi!

I have data sett like this, ordered by date and time:

DateTime Start Stopp
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

Is is possible make a new columns whit count of the rows, that start when start is equal to 1 and stop when stop is equal 1?

Regards Marit

[nirgrahamuk]

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()))