按组基于指定日期(date2)填充DataFrame中的NA值

给定如下DataFrame（df）：

group      date1      date2 value
1      A 2022-01-01 2022-01-07    NA
2      A 2022-01-02 2022-01-07     1
3      A 2022-01-04 2022-01-07    NA
4      A 2022-01-10 2022-01-07    NA
5      B 2022-01-01 2022-01-06     3
6      B 2022-01-03 2022-01-06    NA
7      B 2022-01-04 2022-01-06    NA
8      B 2022-01-06 2022-01-06    NA
9      C 2022-01-01 2022-01-09    NA
10     C 2022-01-03 2022-01-09    NA
11     C 2022-01-04 2022-01-09     2
12     C 2022-01-11 2022-01-09    NA

需要按组填充value列的NA值，规则为：

• 每个组中，找到第一个非NA的value值
• 该值之后的所有NA值，仅当date1 <= date2时，才填充为这个第一个非NA值
• 若date1 > date2，即使在第一个非NA值之后，仍保留NA

期望输出：

group      date1      date2 value
1      A 2022-01-01 2022-01-07    NA
2      A 2022-01-02 2022-01-07     1
3      A 2022-01-04 2022-01-07     1
4      A 2022-01-10 2022-01-07    NA
5      B 2022-01-01 2022-01-06     3
6      B 2022-01-03 2022-01-06     3
7      B 2022-01-04 2022-01-06     3
8      B 2022-01-06 2022-01-06     3
9      C 2022-01-01 2022-01-09    NA
10     C 2022-01-03 2022-01-09    NA
11     C 2022-01-04 2022-01-09     2
12     C 2022-01-11 2022-01-09    NA

df的dput代码：

df<-structure(list(group = c("A", "A", "A", "A", "B", "B", "B", "B", 
"C", "C", "C", "C"), date1 = c("2022-01-01", "2022-01-02", "2022-01-04", 
"2022-01-10", "2022-01-01", "2022-01-03", "2022-01-04", "2022-01-06", 
"2022-01-01", "2022-01-03", "2022-01-04", "2022-01-11"), date2 = c("2022-01-07", 
"2022-01-07", "2022-01-07", "2022-01-07", "2022-01-06", "2022-01-06", 
"2022-01-06", "2022-01-06", "2022-01-09", "2022-01-09", "2022-01-09", 
"2022-01-09"), value = c(NA, 1, NA, NA, 3, NA, NA, NA, NA, NA, 
2, NA)), class = "data.frame", row.names = c(NA, -12L))

可以通过dplyr包的分组操作实现，完整代码如下：

library(dplyr)

# 转换日期类型并按组填充
df <- df %>%
  mutate(across(c(date1, date2), as.Date)) %>%
  group_by(group) %>%
  mutate(
    # 获取当前组第一个非NA的value值
    first_valid_val = first(value[!is.na(value)]),
    # 标记该行是否在第一个非NA值的行之后
    after_first_valid = row_number() > which(!is.na(value))[1],
    # 按条件填充NA
    value = ifelse(is.na(value) & after_first_valid & date1 <= date2, first_valid_val, value)
  ) %>%
  # 移除辅助计算列
  select(-first_valid_val, -after_first_valid) %>%
  ungroup()

print(df)

代码说明 #

• across(c(date1, date2), as.Date)：将日期字符串转换为Date类型，确保日期比较逻辑正确
• first(value[!is.na(value)])：提取每个组内第一个非NA的value值
• row_number() > which(!is.na(value))[1]：判断当前行是否处于第一个非NA值的行之后
• ifelse条件判断：仅当「value为NA」「在第一个非NA值之后」「date1不晚于date2」三个条件同时满足时，才用第一个非NA值填充，否则保留原value

运行上述代码后即可得到符合要求的输出结果。

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