在以下情况下，我在计算回报时有一个问题。对于每个ID，如果Date=EventDate，我希望计算"事件日期"；买入并持有回报；从活动日期前5天到活动日期后5天。
更具体地说，下表中，我想为每个ID计算11个返回值，其中ID=1的返回值为(9/10-1(、(12/10-1(、(14/10-1(，ID=2的返回值分别为(57/10-1(，(60/50-1(，(49/50-1(、~，(65/50-1(，(57/50-1(和(55/50-1(。(即事件日期前6天的价格是回报计算中的分母。(
+----+------------+-------+------------+
| ID | Date | Price | EventDate |
+----+------------+-------+------------+
| 1 | 2011-03-06 | 10 | NA |
| 1 | 2011-03-07 | 9 | NA |
| 1 | 2011-03-08 | 12 | NA |
| 1 | 2011-03-09 | 14 | NA |
| 1 | 2011-03-10 | 15 | NA |
| 1 | 2011-03-11 | 17 | NA |
| 1 | 2011-03-12 | 12 | 2011-03-12 |
| 1 | 2011-03-13 | 14 | NA |
| 1 | 2011-03-14 | 17 | NA |
| 1 | 2011-03-15 | 14 | NA |
| 1 | 2011-03-16 | 17 | NA |
| 1 | 2011-03-17 | 16 | NA |
| 1 | 2011-03-18 | 15 | NA |
| 1 | 2011-03-19 | 16 | NA |
| 1 | 2011-03-20 | 17 | NA |
| 1 | 2011-03-21 | 18 | NA |
| 1 | 2011-03-22 | 11 | NA |
| 1 | 2011-03-23 | 15 | NA |
| 1 | 2011-03-24 | 12 | 2011-03-24 |
| 1 | 2011-03-25 | 13 | NA |
| 1 | 2011-03-26 | 15 | NA |
| 2 | 2011-06-11 | 48 | NA |
| 2 | 2011-06-12 | 49 | NA |
| 2 | 2011-06-13 | 50 | NA |
| 2 | 2011-06-14 | 57 | NA |
| 2 | 2011-06-15 | 60 | NA |
| 2 | 2011-06-16 | 49 | NA |
| 2 | 2011-06-17 | 64 | NA |
| 2 | 2011-06-18 | 63 | NA |
| 2 | 2011-06-19 | 67 | 2011-06-19 |
| 2 | 2011-06-20 | 70 | NA |
| 2 | 2011-06-21 | 58 | NA |
| 2 | 2011-06-22 | 65 | NA |
| 2 | 2011-06-23 | 57 | NA |
| 2 | 2011-06-24 | 55 | NA |
| 2 | 2011-06-25 | 57 | NA |
| 2 | 2011-06-26 | 60 | NA |
+----+------------+-------+------------+
最后，我希望在下表中添加一个新的专栏。
+----+------------+-------+------------+---------------+
| ID | Date | Price | EventDate | BuyHoldReturn |
+----+------------+-------+------------+---------------+
| 1 | 2011-03-06 | 10 | NA | NA |
| 1 | 2011-03-07 | 9 | NA | -0.1 |
| 1 | 2011-03-08 | 12 | NA | 0.2 |
| 1 | 2011-03-09 | 14 | NA | 0.4 |
| 1 | 2011-03-10 | 15 | NA | 0.5 |
| 1 | 2011-03-11 | 17 | NA | 0.7 |
| 1 | 2011-03-12 | 12 | 2011-03-12 | 0.2 |
| 1 | 2011-03-13 | 14 | NA | 0.4 |
| 1 | 2011-03-14 | 17 | NA | 0.7 |
| 1 | 2011-03-15 | 14 | NA | 0.4 |
| 1 | 2011-03-16 | 17 | NA | 0.7 |
| 1 | 2011-03-17 | 16 | NA | 0.6 |
| 1 | 2011-03-18 | 15 | NA | NA |
| 1 | 2011-03-19 | 16 | NA | 0.066666667 |
| 1 | 2011-03-20 | 17 | NA | 0.133333333 |
| 1 | 2011-03-21 | 18 | NA | 0.2 |
| 1 | 2011-03-22 | 11 | NA | -0.266666667 |
| 1 | 2011-03-23 | 15 | NA | 0 |
| 1 | 2011-03-24 | 12 | 2011-03-24 | -0.2 |
| 1 | 2011-03-25 | 13 | NA | -0.133333333 |
| 1 | 2011-03-26 | 15 | NA | 0 |
| 2 | 2011-06-11 | 48 | NA | NA |
| 2 | 2011-06-12 | 49 | NA | NA |
| 2 | 2011-06-13 | 50 | NA | NA |
| 2 | 2011-06-14 | 57 | NA | 0.14 |
| 2 | 2011-06-15 | 60 | NA | 0.2 |
| 2 | 2011-06-16 | 49 | NA | -0.02 |
| 2 | 2011-06-17 | 64 | NA | 0.28 |
| 2 | 2011-06-18 | 63 | NA | 0.26 |
| 2 | 2011-06-19 | 67 | 2011-06-19 | 0.34 |
| 2 | 2011-06-20 | 70 | NA | 0.4 |
| 2 | 2011-06-21 | 58 | NA | 0.16 |
| 2 | 2011-06-22 | 65 | NA | 0.3 |
| 2 | 2011-06-23 | 57 | NA | 0.14 |
| 2 | 2011-06-24 | 55 | NA | 0.1 |
| 2 | 2011-06-25 | 57 | NA | NA |
| 2 | 2011-06-26 | 60 | NA | NA |
+----+------------+-------+------------+---------------+
我有一个使用下面代码的想法，但不知道如何计算活动日期前后的11次买入和持有回报。
data<-data%>%
group_by(ID)%>%
mutate(BuyHoldReturn=ifelse(Date==EventDate, ....
提前感谢！
- 我们可以试试
library(dplyr)
df |> group_by(ID) |> mutate( x = Price/lag(Price) - 1 ,
y = which(Date == EventDate) - 1:n() ,
BuyHoldReturn = case_when(between(y , -5 , 5) ~ x , TRUE ~ NA_real_)) |>
select(-x , -y)
- 输出
# A tibble: 28 × 5
# Groups: ID [2]
ID Date Price EventDate BuyHoldReturn
<int> <chr> <int> <chr> <dbl>
1 1 2011-03-06 10 NA NA
2 1 2011-03-07 9 NA -0.1
3 1 2011-03-08 12 NA 0.333
4 1 2011-03-09 14 NA 0.167
5 1 2011-03-10 15 NA 0.0714
6 1 2011-03-11 17 NA 0.133
7 1 2011-03-12 12 2011-03-12 -0.294
8 1 2011-03-13 14 NA 0.167
9 1 2011-03-14 17 NA 0.214
10 1 2011-03-15 14 NA -0.176
# … with 18 more rows