'difference between two datetime displays wrong output
I want to keep one record for every 3400 seconds to 3600 seconds, this chunk of code checks first row with the next row and if the id is the same it will then check if the time is 3400 or more secs apart, if it is it returns true, if not then it returns false. However, as you can see from the below output, it is returning FALSE from 12:00:23 to 13:00:09 (because the rows in between are false). Is there a way to amend it so it will give me my desired output (as below)?
TIA
Data:
id<-c('8959', '8959', '8959', '8959', '8959', '8959', '8959','8959',
'8959', '8959', '8959', '8959', '8959','8959', '8959', '8959', '8959',
'8959', '8959', '8959','8959','8959', '8959', '8959',
'8959', '8959','8959','8959','8959','8959')
t<-c('2021-08-14 06:00:53', '2021-08-14 07:00:10','2021-08-14 08:00:20',
'2021-08-14 09:00:30','2021-08-14 10:00:10','2021-08-14 11:00:11',
'2021-08-14 12:00:23','2021-08-14 12:10:09','2021-08-14 12:20:08',
'2021-08-14 12:30:13','2021-08-14 12:40:13','2021-08-14 12:50:21',
'2021-08-14 13:00:09','2021-08-14 13:10:08','2021-08-14 13:20:53',
'2021-08-14 13:30:08', '2021-08-14 13:40:09', '2021-08-14 13:50:08',
'2021-08-14 14:00:20','2021-08-14 14:10:08','2021-08-14 14:20:09',
'2021-08-14 14:30:20','2021-08-14 14:40:15','2021-08-14 15:00:08',
'2021-08-14 16:00:12','2021-08-14 16:26:18','2021-08-14 16:30:09',
'2021-08-14 17:00:13','2021-08-14 18:00:53','2021-08-14 19:00:36')
library(lubridate)
oc.df<-as.data.frame(cbind(id,t))
oc.df$t<-ymd_hms(oc.df$t)
Chunk of code:
ind.data <- oc.df %>%
group_by(id) %>%
mutate(difftime = as.numeric(hms::as_hms(t) - hms::as_hms(lag(t, 1)))/3400) %>%
mutate(independent = case_when(
is.na(difftime) ~ TRUE,
difftime >= 1 ~ TRUE,
difftime < 1 ~ FALSE,
TRUE ~ FALSE)
)
Current output:
t id difftime. independent
2021-08-14 06:00:53 8959 -18.141785714 FALSE
2021-08-14 07:00:10 8959 1.270357143 TRUE
2021-08-14 08:00:20 8959 1.289285714 TRUE
2021-08-14 09:00:30 8959 1.289285714 TRUE
2021-08-14 10:00:10 8959 1.278571429 TRUE
2021-08-14 11:00:11 8959 1.286071429 TRUE
2021-08-14 12:00:23 8959 1.290000000 TRUE
2021-08-14 12:10:09 8959 0.209285714 FALSE
2021-08-14 12:20:08 8959 0.213928571 FALSE
2021-08-14 12:30:13 8959 0.216071429 FALSE
2021-08-14 12:40:13 8959 0.214285714 FALSE
2021-08-14 12:50:21 8959 0.217142857 FALSE
2021-08-14 13:00:09 8959 0.210000000 FALSE
2021-08-14 13:10:08 8959 0.213928571 FALSE
2021-08-14 13:20:53 8959 0.230357143 FALSE
2021-08-14 13:30:08 8959 0.198214286 FALSE
2021-08-14 13:40:09 8959 0.214642857 FALSE
2021-08-14 13:50:08 8959 0.213928571 FALSE
2021-08-14 14:00:20 8959 0.218571429 FALSE
2021-08-14 14:10:08 8959 0.210000000 FALSE
2021-08-14 14:20:09 8959 0.214642857 FALSE
2021-08-14 14:30:20 8959 0.218214286 FALSE
2021-08-14 14:40:15 8959 0.212500000 FALSE
2021-08-14 15:00:08 8959 0.426071429 FALSE
2021-08-14 16:00:12 8959 1.287142857 TRUE
2021-08-14 16:26:18 8959 0.559285714 FALSE
2021-08-14 16:30:09 8959 0.082500000 FALSE
2021-08-14 17:00:13 8959 0.644285714 FALSE
2021-08-14 18:00:53 8959 1.300000000 TRUE
2021-08-14 19:00:36 8959 1.279642857 TRUE
Desired output:
t id difftime. independent
2021-08-14 06:00:53 8959 -18.141785714 FALSE
2021-08-14 07:00:10 8959 1.270357143 TRUE
2021-08-14 08:00:20 8959 1.289285714 TRUE
2021-08-14 09:00:30 8959 1.289285714 TRUE
2021-08-14 10:00:10 8959 1.278571429 TRUE
2021-08-14 11:00:11 8959 1.286071429 TRUE
2021-08-14 12:00:23 8959 1.290000000 TRUE
2021-08-14 13:00:09 8959 0.210000000 TRUE
2021-08-14 14:00:20 8959 0.218571429 TRUE
2021-08-14 15:00:08 8959 0.426071429 TRUE
2021-08-14 16:00:12 8959 1.287142857 TRUE
2021-08-14 17:00:13 8959 0.644285714 TRUE
2021-08-14 18:00:53 8959 1.300000000 TRUE
2021-08-14 19:00:36 8959 1.279642857 TRUE
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|