我有一个很大的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/