我正在努力创建一个新变量来捕获给定公司的会计年度（fyear）开始时的股价（prc）。
在数据中，会计年度定义为开始日期和结束日期，以及每月的股票价格。股票价格基于当月最后一个交易日的价格，因此并不总是在当月最后一天。
例如：由于会计年度从2001年1月1日开始，所以我想获得2000年12月底的股票价格。
这是数据示例：
dt <- data.table(id = rep(c(59328, 61241), each = 36), fyear = c(rep(2001,
each = 12), rep(2002, each = 12), rep(2003, each = 12), rep(2001,
each = 12), rep(2002, each = 12), rep(2003, each = 12)),
fyear_start = as.Date(c(rep("2001-01-01", each = 12), rep("2002-01-01",
each = 12), rep("2003-01-01", each = 12), rep("2000-07-01",
each = 12), rep("2001-07-01", each = 12), rep("2002-07-01",
each = 12))), fyear_end = as.Date(c(rep("2001-12-31",
each = 12), rep("2002-12-31", each = 12), rep("2003-12-31",
each = 12), rep("2001-06-30", each = 12), rep("2002-06-30",
each = 12), rep("2003-06-30", each = 12))), prc_month_end = as.Date(c("2001-01-31",
"2001-02-28", "2001-03-30", "2001-04-30", "2001-05-31",
"2001-06-29", "2001-07-31", "2001-08-31", "2001-09-28",
"2001-10-31", "2001-11-30", "2001-12-31", "2002-01-31",
"2002-02-28", "2002-03-28", "2002-04-30", "2002-05-31",
"2002-06-28", "2002-07-31", "2002-08-30", "2002-09-30",
"2002-10-31", "2002-11-29", "2002-12-31", "2003-01-31",
"2003-02-28", "2003-03-31", "2003-04-30", "2003-05-30",
"2003-06-30", "2003-07-31", "2003-08-29", "2003-09-30",
"2003-10-31", "2003-11-28", "2003-12-31", "2000-07-31",
"2000-08-31", "2000-09-29", "2000-10-31", "2000-11-30",
"2000-12-29", "2001-01-31", "2001-02-28", "2001-03-30",
"2001-04-30", "2001-05-31", "2001-06-29", "2001-07-31",
"2001-08-31", "2001-09-28", "2001-10-31", "2001-11-30",
"2001-12-31", "2002-01-31", "2002-02-28", "2002-03-28",
"2002-04-30", "2002-05-31", "2002-06-28", "2002-07-31",
"2002-08-30", "2002-09-30", "2002-10-31", "2002-11-29",
"2002-12-31", "2003-01-31", "2003-02-28", "2003-03-31",
"2003-04-30", "2003-05-30", "2003-06-30")), prc = c(37,
28.56, 26.31, 30.91, 27.01, 29.25, 29.81, 27.96, 20.44,
24.42, 32.66, 31.45, 35.04, 28.55, 30.41, 28.61, 27.62,
18.27, 18.79, 16.67, 13.89, 17.3, 20.88, 15.57, 15.7,
17.26, 16.28, 18.37, 20.82, 20.81, 24.89, 28.59, 27.52,
32.95, 33.54, 32.05, 24.6, 21.5, 26.54, 31, 28.25, 28.9,
18.26, 13.55, 8.15, 9.84, 13.56, 15.86, 16.05, 13.5,
14.71, 11.18, 11.43, 9.72, 8.03, 8.85, 5.34, 6.14, 9,
6.46, 5.24, 5.49, 6.18, 7.44, 7.28, 6.41, 7.3, 11.29,
11.11, 15.2, 17.97, 14.9))
前三行：
id fyear fyear_start fyear_end prc_month_end prc
1: 59328 2001 2001-01-01 2001-12-31 2001-01-31 37.00
2: 59328 2001 2001-01-01 2001-12-31 2001-02-28 28.56
3: 59328 2001 2001-01-01 2001-12-31 2001-03-30 26.31
我已阅读以下文章以获取指导，但没有得到预期的结果。
How to loop lapply to create LAG terms over multiple variables in R
使用最新邮件的解决方案，我可以使股票价格滞后。但是，它采用的是上个月的股价，而没有考虑会计年度。
vars <- c("prc")
rpv <- rep(1:2, each=length(vars))
dt_test <- dt[, paste(vars, "lag", rpv, sep="_") := Map(shift, .SD, rpv), by=id, .SDcols=vars]
How to create lag variables
与上述相同，滞后可变股票价格基于上个月。
Create lead and lag variables in R
与上述相同，滞后可变股票价格基于上个月。
无法使用data.table的.SD [1] /。N语句，因为它返回会计年度的第一个月/最后一个月，而不是上一个会计年度的最后一个月。
有没有办法使一个会计年度返回上一会计年度的最后一个月股票价格？
预期结果如下：
output <- data.table(id = rep(c(59328, 61241), each = 3), fyear = c(2001,
2002, 2003, 2001, 2002, 2003), fyear_start = as.Date(c("2001-01-01",
"2002-01-01", "2003-01-01", "2000-07-01", "2001-07-01", "2002-07-01")),
fyear_end = as.Date(c("2001-12-31", "2002-12-31", "2003-12-31",
"2001-06-30", "2002-06-30", "2003-06-30")), begin_prc = c(NA,
31.45, 15.57, NA, 15.86, 6.46))
id fyear fyear_start fyear_end begin_prc
1: 59328 2001 2001-01-01 2001-12-31 NA
2: 59328 2002 2002-01-01 2002-12-31 31.45
3: 59328 2003 2003-01-01 2003-12-31 15.57
4: 61241 2001 2000-07-01 2001-06-30 NA
5: 61241 2002 2001-07-01 2002-06-30 15.86
6: 61241 2003 2002-07-01 2003-06-30 6.46
我将不胜感激。提前致谢。
最佳答案
有没有办法使一个会计年度返回上一会计年度的最后一个月股票价格？
out = unique(dt[, .(id, fyear, fyear_start, fyear_end)])
out[, prc_end := {
dt[.(id = .SD$id, prc_month_end = .SD$fyear_start - 1L), on=.(id, prc_month_end), roll=TRUE, x.prc]
}]
id fyear fyear_start fyear_end prc_end
1: 59328 2001 2001-01-01 2001-12-31 NA
2: 59328 2002 2002-01-01 2002-12-31 31.45
3: 59328 2003 2003-01-01 2003-12-31 15.57
4: 61241 2001 2000-07-01 2001-06-30 NA
5: 61241 2002 2001-07-01 2002-06-30 15.86
6: 61241 2003 2002-07-01 2003-06-30 6.46
这是滚动更新连接：对于表
out的行使用
.(id, fyear_start - 1)（数据子集）构造查找向量.SD = out查找
dt的行，将最后一个矢量fyear_start - 1“滚动”到最近的较早日期取匹配的值
x.prc，prc中的dt列符号
x.*来自x[i]连接/查找语法。有关更多详细信息，请参见?data.table。关于r - 使用条件创建滞后变量并按ID分组，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/57012555/