r - 使用数据表进行双周分箱

Question

希望将双周日期列添加到数据表中。我有一个可行的解决方案，但它看起来很乱。另外，我觉得滚动连接应该可以解决问题，但我不确定如何操作。是否有更好的解决方案来为双周约会创建分组？

# Mock data table
dt <- data.table(value = runif(20), date = seq(as.Date("2015-01-01"), as.Date("2015-01-20"), by = "days"))
# Bi-weekly dates starting with most recent date and working backwards
bidates <- data.table(bi = seq(dt[, max(date)], dt[, min(date)], by = -14))
# Expand out bi-weekly dates to match up with every date in that range
bidates <- bidates[, seq(bi - 13, bi, by = "days"), by = bi]
# Key and merge
setkey(dt, date)
setkey(bidates, V1)
dt[bidates, bi := i.bi]

Answer 1

以下是使用滚动连接的方法:

bis = dt[, .(date = seq(max(date), min(date), by = -14))][, bi := date]

setkey(bis, date)
setkey(dt, date)

bis[dt, roll = -Inf]
#          date         bi     value
# 1: 2015-01-01 2015-01-06 0.2433854
# 2: 2015-01-02 2015-01-06 0.5454916
# 3: 2015-01-03 2015-01-06 0.3334531
# 4: 2015-01-04 2015-01-06 0.9134877
# 5: 2015-01-05 2015-01-06 0.4557901
# 6: 2015-01-06 2015-01-06 0.3459536
# 7: 2015-01-07 2015-01-20 0.8024527
# 8: 2015-01-08 2015-01-20 0.1833166
# 9: 2015-01-09 2015-01-20 0.1024560
#10: 2015-01-10 2015-01-20 0.4052751
#11: 2015-01-11 2015-01-20 0.9564279
#12: 2015-01-12 2015-01-20 0.6413953
#13: 2015-01-13 2015-01-20 0.7614291
#14: 2015-01-14 2015-01-20 0.2176500
#15: 2015-01-15 2015-01-20 0.3352939
#16: 2015-01-16 2015-01-20 0.4847095
#17: 2015-01-17 2015-01-20 0.8450636
#18: 2015-01-18 2015-01-20 0.8513685
#19: 2015-01-19 2015-01-20 0.2012410
#20: 2015-01-20 2015-01-20 0.3847956

从 1.9.5+ 版本开始，您不需要设置 key ，可以:

bis[dt, roll = -Inf, on = 'date']