检查日期时间中的上一行，如果时间大于某个值，则放在一个组中并获取其持续时间(R，dplyr，润滑)

Question

我有一个数据集，df：(数据集包含超过4000行(

DATEB
9/9/2019 7:51:58 PM
9/9/2019 7:51:59 PM
9/9/2019 7:51:59 PM
9/9/2019 7:52:00 PM
9/9/2019 7:52:01 PM
9/9/2019 7:52:01 PM
9/9/2019 7:52:02 PM
9/9/2019 7:52:03 PM
9/9/2019 7:54:00 PM
9/9/2019 7:54:02 PM
9/10/2019 8:00:00PM

我希望分组放置(如果时间不在上一行的 10 秒内(，然后采用新形成的组的持续时间。

期望输出：

Group   Duration
a       5 sec
b       2 sec
c       0 sec


dput:

structure(list(DATEB = structure(c(2L, 3L, 3L, 4L, 5L, 5L, 6L, 
7L, 8L, 9L, 1L), .Label = c("      9/10/2019 8:00:00 PM", "      9/9/2019 7:51:58 PM", 
"      9/9/2019 7:51:59 PM", "      9/9/2019 7:52:00 PM", "      9/9/2019 7:52:01 PM", 
"      9/9/2019 7:52:02 PM", "      9/9/2019 7:52:03 PM", "      9/9/2019 7:54:00 PM", 
"      9/9/2019 7:54:02 PM"), class = "factor")), class = "data.frame", row.names = c(NA, 
-11L))

我已经尝试了下面的代码，效果很好，除了，我只想要几秒钟内的单位。下面的代码给出了分钟和秒的单位。

library(dplyr)
library(lubridate)

df2 <- mutate(df,
DATEB = lubridate::mdy_hms(DATEB))
df2$time_since_last_row <- df2$DATEB - lag(df2$DATEB)
df2$time_since_last_row[[1]] <- 0 # replace the first NA
df2$group_10s <- 0
for ( i in 2:nrow(df2))
{
if(df2$time_since_last_row[[i]]>seconds(10))
df2$group_10s[[i]] <-  df2$group_10s[[i-1]] +1 
else 
df2$group_10s[[i]] <-  df2$group_10s[[i-1]]
}


df3 <- group_by(df2,
group_10s) %>%
summarise(volume_in_group=n(),
min_DATEB=min(DATEB),
max_DATEB=max(DATEB),
group_duration = max_DATEB - min_DATEB)

#nirgrahamuk-R community

任何建议不胜感激。

Answer 1

这就是我要做的：

gap_threshold <- 10
df %>%
mutate(DATEB = lubridate::mdy_hms(DATEB), 
gap = c(0, diff(DATEB))) %>% 
group_by(grp = cumsum(gap > gap_threshold)) %>% 
summarise(begin = min(DATEB), end = max(DATEB), 
duration = difftime(end, begin, units = "secs"))

# A tibble: 3 x 4
grp begin               end                 duration
<int> <dttm>              <dttm>              <drtn>  
1     0 2019-09-09 19:51:58 2019-09-09 19:52:03 5 secs  
2     1 2019-09-09 19:54:00 2019-09-09 19:54:02 2 secs  
3     2 2019-09-10 20:00:00 2019-09-10 20:00:00 0 secs

请注意，输出中的列数多于仅用于演示的请求。

每当后续两行之间的间距大于给定的gap_threshold组计数grp将提前 1。最后，对每组进行min()和max()，并根据这些进行持续时间计算。

Answer 2

事实上，我以前做过类似的事情。您可以使用以下命令修改最后一个块：

df3 <- group_by(df2, group_10s) %>%
summarise(
volume_in_group=n(),
min_DATEB=min(DATEB),
max_DATEB=max(DATEB),
group_duration = as.numeric(max_DATEB - min_DATEB, units = "secs")
)