r-数据.表:计算时间移动窗口内的行时间统计信息

Question

library(data.table)
library(lubridate)
df <- data.table(col1 = c('A', 'A', 'A', 'B', 'B', 'B'), col2 = c("2015-03-06 01:37:57", "2015-03-06 01:39:57", "2015-03-06 01:45:28", "2015-03-06 02:31:44", "2015-03-06 03:55:45", "2015-03-06 04:01:40"))

对于每一行，我想计算与" col1"值相同的行的标准偏差(col2(，并且在此行之前10分钟的窗口内的时间(include(

我使用下一个方法：

df$col2 <- as_datetime(df$col2)
gap <- 10L
df[, feat1 := .SD[.(col1 = col1, t1 = col2 - gap * 60L, t2 = col2)
                   , on = .(col1, col2 >= t1, col2 <= t2)
                   , .(sd_time = sd(as.numeric(col2))), by = .EACHI]$sd_time][]

结果，我仅看到Na值而不是秒的值

例如，第三行(col =" a"和col2 =" 2015-03-06 01:45:28"(我已经通过手动计算：

v <- c("2015-03-06 01:37:57", "2015-03-06 01:39:57", "2015-03-06 01:45:28")
v <- as_datetime(v)
sd(v) = 233.5815

Answer 1

两个替代data.table解决方案(我以前答案的变体(：

# option 1
df[.(col1 = col1, t1 = col2, t2 = col2 + gap * 60L)
   , on = .(col1, col2 >= t1, col2 <= t2)
   , .(col1, col2 = x.col2, times = as.numeric(t1))
   ][, .(feat1 = sd(times))
     , by = .(col1, col2)]
# option 2
df[, feat1 := .SD[.(col1 = col1, t1 = col2, t2 = col2 + gap * 60L)
                  , on = .(col1, col2 >= t1, col2 <= t2)
                  , .(col1, col2 = x.col2, times = as.numeric(t1))
                  ][, .(sd_times = sd(times))
                    , by = .(col1, col2)]$sd_times][]

两者都给出：

   col1                col2     feat1
1:    A 2015-03-06 00:37:57        NA
2:    A 2015-03-06 00:39:57  84.85281
3:    A 2015-03-06 00:45:28 233.58153
4:    B 2015-03-06 01:31:44        NA
5:    B 2015-03-06 02:55:45        NA
6:    B 2015-03-06 03:01:40 251.02291

Answer 2

纯data.table解决方案：

df[,col3:=as.numeric(col2)]
df[, feat1 := {
  d <- data$col3 - col3
  sd(data$col3[col1 == data$col1 & d <= 0 & d >= -gap * 60L])
},
by = list(col3, col1)]

循环循环的另一种方法，col1的所有组合，col2与 mapply：

df[,col3:=as.numeric(col2)]
df[, feat1:=mapply(Date = col3,ID = col1, function(Date, ID) {
  DateVect=df[col1 == ID,col3]
  d <- DateVect - Date
  sd(DateVect[d <= 0 & d >= -gap * 60L])})][]