Group rows of values above and below specific threshold in R

Question

I have a data frame of furnace temperature logged over time. I would like to determine when the temperature begins increasing (furnace turns on) and when temperature starts decreasing (furnace turns off).

I want to group temperatures above and below a set number and find the min/max values in the group to determine when the furnace turns on/off. As shown in the shaded plot below, grouping data points within the shaded regions and finding the min/max will yield the date and time when the furnace turns on/off.

I'm stuck on the Group variable. How can I group only the numeric values in AboveBelow without including cells with NA? The groups also need to be different so only the min/max within those groups are calculated.

Sample data: Creates a column of temperature above 95 and below 70 with the rest as NA:

library(tidyverse)

date_seq <- seq(as.POSIXct("2020-01-05 12:00:00"), 
            as.POSIXct("2020-01-05 12:07:10"), 
            by=(10))

a <- data.frame(Date = strftime(date_seq, format="%Y-%m-%d"),
             Time = strftime(date_seq, format="%H:%M:%S"),
            Temp=c(65,64,65,67,72,77,85,92,97,100,101,100,98,91,86,79,71,68,67,66,65,65,65,64,65,
                       67,72,77,85,92,97,100,101,100,98,91,86,79,71,68,67,66,65,66))

a <- a %>% mutate(AboveBelow= ifelse(Temp>95 | Temp<70, Temp, NA))

Sample result:

   Date       Time       Temp       AboveBelow Group OnOff
1  2020-01-05 12:00:00   65         65         1      
2  2020-01-05 12:00:10   64         64         1     On
3  2020-01-05 12:00:20   65         65         1      
4  2020-01-05 12:00:30   67         67         1      
5  2020-01-05 12:00:40   72         NA         NA      
6  2020-01-05 12:00:50   77         NA         NA      
7  2020-01-05 12:01:00   85         NA         NA      
8  2020-01-05 12:01:10   92         NA         NA      
9  2020-01-05 12:01:20   97         97         2      
10 2020-01-05 12:01:30  100        100         2      
11 2020-01-05 12:01:40  101        101         2     Off
12 2020-01-05 12:01:50  100        100         2      
13 2020-01-05 12:02:00   98         98         2      
14 2020-01-05 12:02:10   91         NA         NA      
15 2020-01-05 12:02:20   86         NA         NA      
16 2020-01-05 12:02:30   79         NA         NA      
17 2020-01-05 12:02:40   71         NA         NA      
18 2020-01-05 12:02:50   68         68         3      
19 2020-01-05 12:03:00   67         67         3      
20 2020-01-05 12:03:10   66         66         3      
21 2020-01-05 12:03:20   65         65         3      
22 2020-01-05 12:03:30   65         65         3      
23 2020-01-05 12:03:40   65         65         3      
24 2020-01-05 12:03:50   64         64         3     On
25 2020-01-05 12:04:00   65         65         3      
26 2020-01-05 12:04:10   67         67         3      
27 2020-01-05 12:04:20   72         NA         NA      
28 2020-01-05 12:04:30   77         NA         NA      
29 2020-01-05 12:04:40   85         NA         NA      
30 2020-01-05 12:04:50   92         NA         NA      
31 2020-01-05 12:05:00   97         97         4      
32 2020-01-05 12:05:10  100        100         4      
33 2020-01-05 12:05:20  101        101         4     Off
34 2020-01-05 12:05:30  100        100         4      
35 2020-01-05 12:05:40   98         98         4      
36 2020-01-05 12:05:50   91         NA         NA      
37 2020-01-05 12:06:00   86         NA         NA      
38 2020-01-05 12:06:10   79         NA         NA      
39 2020-01-05 12:06:20   71         NA         NA      
40 2020-01-05 12:06:30   68         68         5      
41 2020-01-05 12:06:40   67         67         5      
42 2020-01-05 12:06:50   66         66         5      
43 2020-01-05 12:07:00   65         65         5     On
44 2020-01-05 12:07:10   66         66         5

score 1 · Answer 1 · answered Jan 20 '21 at 23:38

This could help without group by, since you can evaluate a condition in a vectorized way:

a %>% 
  mutate(OnOff = ifelse(is.na(AboveBelow),"",
                         ifelse(AboveBelow < 70 & Temp == min(Temp),"On",
                                ifelse(AboveBelow >95 & Temp == max(Temp),"Off",""))))

Results will be:

         Date     Time Temp AboveBelow OnOff
1  2020-01-05 12:00:00   65         65      
2  2020-01-05 12:00:10   64         64    On
3  2020-01-05 12:00:20   65         65      
4  2020-01-05 12:00:30   67         67      
5  2020-01-05 12:00:40   72         NA      
6  2020-01-05 12:00:50   77         NA      
7  2020-01-05 12:01:00   85         NA      
8  2020-01-05 12:01:10   92         NA      
9  2020-01-05 12:01:20   97         97      
10 2020-01-05 12:01:30  100        100      
11 2020-01-05 12:01:40  101        101   Off
12 2020-01-05 12:01:50  100        100      
13 2020-01-05 12:02:00   98         98      
14 2020-01-05 12:02:10   91         NA      
15 2020-01-05 12:02:20   86         NA      
16 2020-01-05 12:02:30   79         NA      
17 2020-01-05 12:02:40   71         NA      
18 2020-01-05 12:02:50   68         68      
19 2020-01-05 12:03:00   67         67      
20 2020-01-05 12:03:10   66         66      
21 2020-01-05 12:03:20   65         65      
22 2020-01-05 12:03:30   65         65      
23 2020-01-05 12:03:40   65         65      
24 2020-01-05 12:03:50   64         64    On
25 2020-01-05 12:04:00   65         65      
26 2020-01-05 12:04:10   67         67      
27 2020-01-05 12:04:20   72         NA      
28 2020-01-05 12:04:30   77         NA      
29 2020-01-05 12:04:40   85         NA      
30 2020-01-05 12:04:50   92         NA      
31 2020-01-05 12:05:00   97         97      
32 2020-01-05 12:05:10  100        100      
33 2020-01-05 12:05:20  101        101   Off
34 2020-01-05 12:05:30  100        100      
35 2020-01-05 12:05:40   98         98      
36 2020-01-05 12:05:50   91         NA      
37 2020-01-05 12:06:00   86         NA      
38 2020-01-05 12:06:10   79         NA      
39 2020-01-05 12:06:20   71         NA      
40 2020-01-05 12:06:30   68         68      
41 2020-01-05 12:06:40   67         67      
42 2020-01-05 12:06:50   66         66      
43 2020-01-05 12:07:00   65         65      
44 2020-01-05 12:07:10   66         66

This way you get the cut point (On and Off). Regards!

Thank you @Manu for your suggestion. I tried your solution with no luck. I figured out that it only worked because the max for both peaks was 101. If I change the second max to 103, your script skips the first max (101) because 103 is now the highest. `a <- data.frame(Date = strftime(date_seq, format="%Y-%m-%d"), Time = strftime(date_seq, format="%H:%M:%S"), Temp=c(65,64,65,67,72,77,85,92,97,100,101,100,98,91,86,79,71,68,67,66,65,65,65,64,65, 67,72,77,85,92,97,100,103,100,98,91,86,79,71,68,67,66,65,66))` — finc, Jan 21 '21 at 00:00
Ah ok, if I understood you well, you want the local minima and maxima per cycle? Then if the patter cycle is repetitive, it occurs after a block of NAs? Could you confirm? — Manu, Jan 21 '21 at 16:10

score 1 · Accepted Answer · answered Jan 21 '21 at 05:16

You can use rle to create Group and with the help of case_when create OnOff column.

library(dplyr)

a %>%
  group_by(Group = with(rle(!is.na(AboveBelow)), rep(cumsum(values), lengths))) %>%
  mutate(OnOff = case_when(AboveBelow < 70 & Temp == min(Temp) ~ 'On', 
                           AboveBelow > 95 & Temp == max(Temp) ~ 'Off', 
                           TRUE ~ ''), 
         Group = replace(Group, is.na(AboveBelow), NA))

which returns :

#         Date     Time Temp AboveBelow Group OnOff
#1  2020-01-05 12:00:00   65         65     1      
#2  2020-01-05 12:00:10   64         64     1    On
#3  2020-01-05 12:00:20   65         65     1      
#4  2020-01-05 12:00:30   67         67     1      
#5  2020-01-05 12:00:40   72         NA    NA      
#6  2020-01-05 12:00:50   77         NA    NA      
#7  2020-01-05 12:01:00   85         NA    NA      
#8  2020-01-05 12:01:10   92         NA    NA      
#9  2020-01-05 12:01:20   97         97     2      
#10 2020-01-05 12:01:30  100        100     2      
#11 2020-01-05 12:01:40  101        101     2   Off
#12 2020-01-05 12:01:50  100        100     2      
#13 2020-01-05 12:02:00   98         98     2      
#14 2020-01-05 12:02:10   91         NA    NA      
#15 2020-01-05 12:02:20   86         NA    NA      
#16 2020-01-05 12:02:30   79         NA    NA      
#17 2020-01-05 12:02:40   71         NA    NA      
#18 2020-01-05 12:02:50   68         68     3      
#19 2020-01-05 12:03:00   67         67     3      
#20 2020-01-05 12:03:10   66         66     3      
#21 2020-01-05 12:03:20   65         65     3      
#22 2020-01-05 12:03:30   65         65     3      
#23 2020-01-05 12:03:40   65         65     3      
#24 2020-01-05 12:03:50   64         64     3    On
#25 2020-01-05 12:04:00   65         65     3      
#26 2020-01-05 12:04:10   67         67     3      
#27 2020-01-05 12:04:20   72         NA    NA      
#28 2020-01-05 12:04:30   77         NA    NA      
#29 2020-01-05 12:04:40   85         NA    NA      
#30 2020-01-05 12:04:50   92         NA    NA      
#31 2020-01-05 12:05:00   97         97     4      
#32 2020-01-05 12:05:10  100        100     4      
#33 2020-01-05 12:05:20  101        101     4   Off
#34 2020-01-05 12:05:30  100        100     4      
#35 2020-01-05 12:05:40   98         98     4      
#36 2020-01-05 12:05:50   91         NA    NA      
#37 2020-01-05 12:06:00   86         NA    NA      
#38 2020-01-05 12:06:10   79         NA    NA      
#39 2020-01-05 12:06:20   71         NA    NA      
#40 2020-01-05 12:06:30   68         68     5      
#41 2020-01-05 12:06:40   67         67     5      
#42 2020-01-05 12:06:50   66         66     5      
#43 2020-01-05 12:07:00   65         65     5    On
#44 2020-01-05 12:07:10   66         66     5

Group rows of values above and below specific threshold in R

2 Answers2