Mark true if all value of a day is greter than threshold for next 2 days

my data has columns: Name of group, date, value.
I want to generate a column that if all value for each day exceed limit 10 for consecutive 2 days, for each individual group. One column will show counter increment, if all values for each day exceed limit(greater or equal 10) for consecutive days then increment the counter counter<- counter+1, second column if all values for each day exceed limit(greater or equal 10) for consecutive 2 days then mark true.

set.seed(1)
Data <- data.frame(
  Group = c(1,  1,  1,  1,  1,  1,  1,  1,  1,  1,  2,  2,  2,  2,  2,  2,  2,  2,  2,  2),
  Date= c("01-03-2020 01:00", "01-03-2020 02:00","01-03-2020 03:00","01-03-2020 04:00","02-03-2020 01:00","02-03-2020 02:00",
        "02-03-2020 03:00","03-03-2020 01:00","03-03-2020 02:00","03-03-2020 03:00","01-03-2020 01:00","01-03-2020 02:00",
        "01-03-2020 03:00","01-03-2020 04:00","02-03-2020 01:00","02-03-2020 02:00","02-03-2020 03:00","03-03-2020 01:00",
        "03-03-2020 02:00","03-03-2020 03:00" ),
  value=c(10,   3,  11, 12, 14, 10, 12, 10.5,   13, 16, 10, 12, 11, 12, 14, 10, 12, 9,  13, 16)
)
````Preformatted text`

#>Output
Date value conday Advisory
 1 01-03-2020 01:00  10.0      0    FALSE
 1 01-03-2020 02:00   3.0      0    FALSE
 1 01-03-2020 03:00  11.0      0    FALSE
 1 01-03-2020 04:00  12.0      0    FALSE
 1 02-03-2020 01:00  14.0      1    FALSE
 1 02-03-2020 02:00  10.0      1    FALSE
 1 02-03-2020 03:00  12.0      1    FALSE
 1 03-03-2020 01:00  10.5      2     TRUE
 1 03-03-2020 02:00  13.0      2     TRUE
 1 03-03-2020 03:00  16.0      2     TRUE
 2 01-03-2020 01:00  10.0      1    FALSE
 2 01-03-2020 02:00  12.0      1    FALSE
 2 01-03-2020 03:00  11.0      1    FALSE
 2 01-03-2020 04:00  12.0      1    FALSE
 2 02-03-2020 01:00  14.0      2     TRUE
 2 02-03-2020 02:00  10.0      2     TRUE
 2 02-03-2020 03:00  12.0      2     TRUE
 2 03-03-2020 01:00   9.0      0    FALSE
 2 03-03-2020 02:00  13.0      0    FALSE
 2 03-03-2020 03:00  16.0      0    FALSE
'''

Hi @Nitish29,

I think this does what you want...

library(tidyverse)
library(lubridate)

data %>% 
  mutate(date = dmy_hm(date),
         day_number = day(date)) %>% 
  arrange(group, day_number) %>% 
  group_by(group, day_number) %>% 
  summarise(all_ge10 = all(value>=10)) %>% 
  mutate(
    prev_day_ge10 = lag(all_ge10, default = FALSE),
    two_days_ge10 = if_else(all_ge10 & prev_day_ge10, TRUE, FALSE)
    )

@mattwarkentin : Thanks, One more query, what if I want to calculate it for lag 4 or more days. like if all the value for each day is greater than or equal to 10 for consecutive 4 days mark True.

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.