rdplyrgroup-bynatemperature
Group rows of values above and below specific threshold in R
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
Solution
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
- Rcpp Rf_warningcall compiler warnings
- Modify the name of factor variables in lm function(summary function)
- Extreme value analysis and quantile estimation using log Pearson type 3 (Pearson III) distribution - R vs Python
- How to hide NAs when using xlsx::saveWorkbook?
- How do I retrieve a simple numeric value from a named numeric vector in R?
- Matching pair-wise columns from left to right across rows in one dataframe to another dataframe and adding new columns with matching values
- Income to outcome flow chart in Sankey plotly R
- color mapping in geom_conn_bundle not showing correctly
- Print R package startup message AFTER automatic package conflict messages instead of before
- Summing a set of R dataframe rows (column-wise), while retaining the first n columns
- Added variable / partial regression plots for groups in an interaction?
- how to make a topoplot in R with coordinates variable distribution
- List of all functions in base R?
- Plotting multiple plots for different initial conditions in one graph
- Printing repetitively on the same line in R
- Generating UI/Server based on initial selection
- Subset dataframe based on pickerInput
- How to let user pick the data in R-shiny?
- Couldn't show my simple bar charts separately on Shiny R dashboardBody
- How to programmatically filter contents of a second shiny app displayed via iframe
- How to select specific interesting groups for the boxplot in R Shiny app?
- Crosstable and Plot grouping with reactive values
- Is there a way to make multiple Shiny picker inputs where the selections must be disjoint?
- Delay/avoid duplication of shiny server side functions until after credentials
- Predictions only returns value "1"
- How to display a busy indicator in a shiny app?
- Append doesn't work when writing to CSV in R
- Changing the start date of a gantt chart in DiagrammeR
- Check for installed packages before running install.packages()
- Compare two columns element-wise