Sample data that covers multiple days:
set.seed(42)
dat <- data.frame(timestamp = seq(as.POSIXct("2017-01-10 00:00:00"), length.out = 48*4, by = "30 mins"))
dat$value <- sample(15:30, size = nrow(dat), replace = TRUE)
dat$state <- rbinom(nrow(dat), 1, 0.1)
head(dat)
# timestamp value state
# 1 2017-01-10 00:00:00 15 0
# 2 2017-01-10 00:30:00 19 0
# 3 2017-01-10 01:00:00 15 0
# 4 2017-01-10 01:30:00 23 0
# 5 2017-01-10 02:00:00 24 0
# 6 2017-01-10 02:30:00 18 0
Try this:
library(dplyr)
dat %>%
group_by(grp = cumsum(state == 1)) %>%
filter(first(state) == 1, timestamp <= (first(timestamp) + 3600*3)) %>%
summarize(
timestamp_1 = first(timestamp), timestamp_n = last(timestamp),
value_1 = first(value), value_n = last(value),
value_max = max(value), value_min = min(value),
dip = max(first(value) - value)
) %>%
group_by(date = substring(timestamp_1, 1, 10)) %>%
slice_tail(n = 1) %>%
ungroup()
# # A tibble: 4 x 9
# grp timestamp_1 timestamp_n value_1 value_n value_max value_min dip date
# <int> <dttm> <dttm> <int> <int> <int> <int> <int> <chr>
# 1 3 2017-01-10 21:00:00 2017-01-11 00:00:00 20 16 24 16 4 2017-01-10
# 2 7 2017-01-11 21:30:00 2017-01-12 00:30:00 30 26 30 19 11 2017-01-11
# 3 14 2017-01-12 21:00:00 2017-01-13 00:00:00 21 23 28 15 6 2017-01-12
# 4 20 2017-01-13 19:30:00 2017-01-13 22:30:00 29 24 29 16 13 2017-01-13
I wasn't certain if you meant "value[time+3h] - value[1]" or if you meant the max of "value - value[1]" for each group; the current dip is the max dip, not necessarily value - value[1]. To cover and demonstrate this, I've included the first/last timestamp (for proof of 3h window) and first/last/min/max value for each group.
It calculates these stats for all groups during each day, not just the last group of the day; if we group by day first before summarizing, we will lose those cases where the 3h window goes over midnight.
