如何在R语言中找到每条记录日期之前的最近策略重构日期

需要为每条记录计算对应策略在当前记录日期之前最近的一次重构日期，重构日期的判定标准是reconType字段不为"NA"的日期。

原尝试代码及错误结果 #

strategy <- rep(c('a', 'b', 'c'), each = 3)
date <- rep(c(as.Date("2023-01-02"), as.Date("2023-01-03"), as.Date("2023-01-04")), times = 3)
reconType <- c('Annual','NA','NA','Annual','Annual','NA','NA','Annual','NA')

df1 <- data.frame(strategy, date, reconType)

# 原错误代码
df1$lastDate <- max(df1$date[!is.na(df1$Recon.Type) &  df1$date < df1$date & df1$strategy == df1$strategy])
print(df1)

错误输出：

strategy       date reconType lastDate
1        a 2023-01-02    Annual     <NA>
2        a 2023-01-03        NA     <NA>
3        a 2023-01-04        NA     <NA>
4        b 2023-01-02    Annual     <NA>
5        b 2023-01-03    Annual     <NA>
6        b 2023-01-04        NA     <NA>
7        c 2023-01-02        NA     <NA>
8        c 2023-01-03    Annual     <NA>
9        c 2023-01-04        NA     <NA>

期望输出 #

strategy       date reconType lastDate
1        a 2023-01-02    Annual     <NA>
2        a 2023-01-03        NA     2023-01-02
3        a 2023-01-04        NA     2023-01-02
4        b 2023-01-02    Annual     <NA>
5        b 2023-01-03    Annual     2023-01-02
6        b 2023-01-04        NA     2023-01-03
7        c 2023-01-02        NA     <NA>
8        c 2023-01-03    Annual     <NA>
9        c 2023-01-04        NA     2023-01-03

解决方案 #

方法1：使用dplyr分组处理（推荐） #

按策略分组后，逐行筛选当前组内早于记录日期且符合重构条件的日期，取最大值：

library(dplyr)

df1 <- df1 %>%
  group_by(strategy) %>%
  mutate(lastDate = sapply(date, function(x) {
    max(date[reconType != "NA" & date < x], na.rm = TRUE)
  })) %>%
  ungroup()

# 将无有效日期导致的Inf值替换为NA
df1$lastDate[is.infinite(df1$lastDate)] <- NA

print(df1)

方法2：Base R实现 #

用ave函数按策略分组，逐行计算符合条件的最近日期：

df1$lastDate <- ave(df1$date, df1$strategy, FUN = function(dates) {
  sapply(dates, function(x) {
    # 筛选当前组内符合条件的日期
    valid_dates <- dates[df1$reconType[df1$date == dates] != "NA" & dates < x]
    if(length(valid_dates) == 0) NA else max(valid_dates)
  })
})

print(df1)

原代码错误原因 #

1. 列名错误：代码中Recon.Type与实际列名reconType大小写不匹配，导致筛选条件失效；
2. 未分组处理：没有按strategy分组，无法针对每个策略单独计算最近重构日期；
3. 逻辑错误：df1$date < df1$date永远为假，无法筛选出早于当前记录的日期。

内容的提问来源于stack exchange，提问作者Supriya Bharati