基于组内多条件与日期比较创建新变量pr的实现方法

我有如下格式的数据集（完整数据见dput）：

id    date       u         v
   <chr> <date>     <chr> <int>
 1 a     2019-05-14 NA        0
 2 a     2018-06-29 u         1
 3 b     2020-12-02 u         1
 4 b     2017-08-16 NA        1
 5 b     2016-04-07 NA        0
 6 c     2018-05-22 u         1
 7 c     2018-05-22 u         1
 8 e     2019-03-06 u         1
 9 e     2019-03-06 NA        1

需要创建新变量pr，规则为：

• 在每个id分组内
• 当u == "u"时，判断该条记录的date是否存在相等或更早的日期（同组内）满足v == 1（需排除当前这条u的记录）
• 已知u对应的v恒为1

我知道大致用dplyr的分组mutate写法，但无法实现组内日期比较和排除当前记录的判断。预期输出如下：

id    date       u         v    pr
   <chr> <date>     <chr> <int> <int>
 1 a     2019-05-14 NA        0    NA
 2 a     2018-06-29 u         1     0
 3 b     2020-12-02 u         1     1
 4 b     2017-08-16 NA        1    NA
 5 b     2016-04-07 NA        0    NA
 6 c     2018-05-22 u         1     1
 7 c     2018-05-22 u         1     1
 8 e     2019-03-06 u         1     1
 9 e     2019-03-06 NA        1    NA
10 f     2020-10-20 u         1     0
11 f     2019-01-25 NA        0    NA
12 h     2020-02-24 NA        0    NA
13 h     2018-10-15 u         1     0
14 h     2018-03-07 NA        0    NA
15 i     2021-02-02 u         1     1
16 i     2020-11-19 NA        1    NA
17 i     2020-11-19 NA        1    NA
18 j     2019-02-11 u         1     1
19 j     2017-06-26 u         1     0
20 k     2018-12-13 u         1     0
21 k     2017-07-18 NA        0    NA
22 l     2018-05-08 u         1     1
23 l     2018-02-15 NA        0    NA
24 l     2018-02-15 u         1     0
25 l     2017-11-07 NA        0    NA
26 l     2015-09-10 NA        0    NA

数据集dput：

structure(list(id = c("a", "a", "b", "b", "b", "c", "c", "e", 
"e", "f", "f", "h", "h", "h", "i", "i", "i", "j", "j", "k", "k", 
"l", "l", "l", "l", "l"), date = structure(c(18030, 17711, 18598, 
17394, 16898, 17673, 17673, 17961, 17961, 18555, 17921, 18316, 
17819, 17597, 18660, 18585, 18585, 17938, 17343, 17878, 17365, 
17659, 17577, 17577, 17477, 16688), class = "Date"), u = c(NA, 
"u", "u", NA, NA, "u", "u", "u", NA, "u", NA, NA, "u", NA, "u", 
NA, NA, "u", "u", "u", NA, "u", NA, "u", NA, NA), v = c(0L, 1L, 
1L, 1L, 0L, 1L, 1L, 1L, 1L, 1L, 0L, 0L, 1L, 0L, 1L, 1L, 1L, 1L, 
1L, 1L, 0L, 1L, 0L, 1L, 0L, 0L), pr = c(NA, 0L, 1L, NA, NA, 1L, 
1L, 1L, NA, 0L, NA, NA, 0L, NA, 1L, NA, NA, 1L, 0L, 0L, NA, 1L, 
NA, 0L, NA, NA)), row.names = c(NA, -26L), class = c("tbl_df", 
"tbl", "data.frame"))

可以通过dplyr结合组内逻辑判断实现，核心思路是按id分组后，对每条u=="u"的记录，筛选同组内排除当前行且满足v==1、日期早于等于当前行的记录，判断是否存在这类记录并转为1/0，非u=="u"的行设为NA。

代码实现：

library(dplyr)

x %>%
  group_by(id) %>%
  mutate(
    pr = case_when(
      u == "u" ~ as.integer(
        any(
          v[-cur_row()] == 1 & date[-cur_row()] <= date[cur_row()]
        )
      ),
      TRUE ~ NA_integer_
    )
  ) %>%
  ungroup()

代码解释： #

• group_by(id)：按id分组处理每条数据
• cur_row()：获取当前行的索引，v[-cur_row()]和date[-cur_row()]表示排除当前行后的组内v和date数据
• v[-cur_row()] == 1 & date[-cur_row()] <= date[cur_row()]：筛选同组内排除当前行后，v=1且日期早于等于当前行日期的记录
• any(...)：判断是否存在满足条件的记录，返回布尔值TRUE/FALSE
• as.integer(...)：将布尔值转为1（存在）或0（不存在）
• case_when：仅对u=="u"的行赋值，其他行设为NA

运行该代码后即可得到预期的pr变量。

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