我有一个很大的data.table。我想只对选择行进行汇总,但要使用所有数据(即不只是选择行)。这是一个例子:

library(data.table)
library(lubridate)
dt = data.table(
    date = seq.Date(as.Date("2017-01-01"), as.Date("2017-12-31"), by = "1 day")
)

dt$day = day(dt$date)
dt$value = rnorm(nrow(dt))


我想要的是30天的滚动平均值。通常,这可以通过以下方式完成:

library(RcppRoll)
ma30 = dt[, roll_mean(value, 30, fill = NA, align = "right"), by = day]


但是,在这种情况下,我只关心日等于15时的滚动平均值。是否可以通过某种方式编写上述声明,以便我可以取所有前30天的平均值,但只能取每个月的15号?换句话说,我想使用365个数据点,但只能进行12次计算(或者11次,因为无论如何第一个都是NA)。

提前致谢。

最佳答案

两种可能的方法:

# option 1:
dt[, roll_mn := roll_mean(value, 30, fill = NA, align = "right") * NA^(day != 15)]

# option 2:
dt[, roll_mn := ifelse(day == 15, roll_mean(value, 30, fill = NA, align = "right"), NA)]


你得到:


> dt[1:100]
           date day        value    roll_mn
  1: 2017-01-01   1 -0.422983983         NA
  2: 2017-01-02   2 -1.549878162         NA
....
 13: 2017-01-13  13  0.712481269         NA
 14: 2017-01-14  14 -0.445772094         NA
 15: 2017-01-15  15  0.248979648         NA
 16: 2017-01-16  16 -1.074193951         NA
 17: 2017-01-17  17 -1.827261716         NA
....
 44: 2017-02-13  13  1.054362321         NA
 45: 2017-02-14  14 -0.148639594         NA
 46: 2017-02-15  15  1.018076577 -0.1322037
 47: 2017-02-16  16 -0.721586512         NA
 48: 2017-02-17  17 -0.778778137         NA
....
 72: 2017-03-13  13  0.565180699         NA
 73: 2017-03-14  14 -0.006097837         NA
 74: 2017-03-15  15 -0.438781066  0.1109928
 75: 2017-03-16  16  0.688891096         NA
 76: 2017-03-17  17 -0.499419195         NA
....
 99: 2017-04-09   9 -0.657354771         NA
100: 2017-04-10  10  0.922903744         NA





更大数据集的基准(包括@Frank在注释中提到的非等价联接选项):

# create benchmark dataset
set.seed(2018)
dt <- data.table(date = seq.Date(as.Date("0-01-01"), as.Date("2017-12-31"), by = "1 day"))
dt[, `:=` (day = day(date), value = rnorm(nrow(dt)))]

# benchmark
> system.time(dt[, v1 := roll_mean(value, 30, fill = NA, align = "right") * NA^(day != 15)])
   user  system elapsed
  0.011   0.000   0.011
> system.time(dt[, v2 := ifelse(day == 15, roll_mean(value, 30, fill = NA, align = "right"), NA)])
   user  system elapsed
  0.034   0.005   0.039
> system.time(dt[day == 15, v3 := dt[.SD[, .(d_dn = date - 30, d_up = date)], on=.(date > d_dn, date <= d_up), mean(value), by=.EACHI]$V1])
   user  system elapsed
  0.043   0.001   0.044


警告:非等距联接方法还将为第一行提供一个值,其中day == 15



使用的数据:

set.seed(2018)
dt <- data.table(date = seq.Date(as.Date("2017-01-01"), as.Date("2017-12-31"), by = "1 day"))
dt[, `:=` (day = day(date), value = rnorm(nrow(dt)))]

关于r - 仅在特定行与data.table聚合,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/51326343/

10-11 22:30
查看更多