R 根据日期和预定义值有条件地改变新列 data.table

生效日期:

DT<-data.table::data.table(
          ID = c(1L, 1L, 1L, 2L, 2L, 2L, 3L, 3L),
       C_OPR = c("ABCD01", "ABCD11", NA, "EFGH", NA, NA, "KLMN", NA),
       D_OPR = c(NA, NA, "PQRST", NA, "EFGHIJ", NA, NA, NA),
        DATE = c("2007-07-07","2005-05-05","2002-02-02",
                 "2002-02-02","2004-04-04",NA,"2001-01-01",NA),
   INDX_DATE = c("2006-06-06","2006-06-06","2006-06-06",
                 "2001-01-01","2001-01-01","2001-01-01","2005-05-05",
                 "2005-05-05")
)

ALFA_DEF<-c("ABCD","EFGH")

输出:

   ID  C_OPR  D_OPR       DATE  INDX_DATE
1:  1 ABCD01   <NA> 2007-07-07 2006-06-06
2:  1 ABCD11   <NA> 2005-05-05 2006-06-06
3:  1   <NA>  PQRST 2002-02-02 2006-06-06
4:  2   EFGH   <NA> 2002-02-02 2001-01-01
5:  2   <NA> EFGHIJ 2004-04-04 2001-01-01
6:  2   <NA>   <NA>       <NA> 2001-01-01
7:  3   KLMN   <NA> 2001-01-01 2005-05-05
8:  3   <NA>   <NA>       <NA> 2005-05-05

请注意，情况并非如此.

   ID  C_OPR  D_OPR       DATE  INDX_DATE ALFA
1:  1 ABCD01   <NA> 2007-07-07 2006-06-06    1
2:  1 ABCD11   <NA> 2005-05-05 2006-06-06    1
3:  1   <NA>  PQRST 2002-02-02 2006-06-06    1
4:  2   EFGH   <NA> 2002-02-02 2001-01-01    0
5:  2   <NA> EFGHIJ 2004-04-04 2001-01-01    0
6:  2   <NA>   <NA>       <NA> 2001-01-01    0
7:  3   KLMN   <NA> 2001-01-01 2005-05-05    0
8:  3   <NA>   <NA>       <NA> 2005-05-05    0

逻辑:

包含ALFA_DEF的任何C_OPR或D_OPR，其中DATE小于INDX_DATE-在同一组(ID)中，生成ALFA=1，否则为0.

不更正日期的try :

DT[, ALPHA := +any( grepl(paste0(ALPHA_DEF, collapse="|"),c(D_OPR, C_OPR)), by=ID]

最好是data.table解决方案，但也欢迎dplyr.

♪H♪