基于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

逻辑规则 #

• 按ID分组
• 若组内任意一行同时满足两个条件：
  1. C_OPR或D_OPR包含ALFA_DEF中的任意值
  2. 该行DATE小于INDX_DATE
• 则该组所有行的ALFA值为1，否则为0

已尝试代码（未考虑日期条件） #

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

解决方案 #

data.table 实现 #

先将日期列转换为标准日期格式，再按分组判断条件：

# 转换日期格式
DT[, c("DATE", "INDX_DATE") := lapply(.SD, as.Date), .SDcols = c("DATE", "INDX_DATE")]

# 生成ALFA列
DT[, ALFA := +any(
  # 匹配ALFA_DEF开头的字符串
  (grepl(paste0("^(", paste(ALFA_DEF, collapse="|"), ")"), C_OPR) | 
   grepl(paste0("^(", paste(ALFA_DEF, collapse="|"), ")"), D_OPR)) & 
  # 排除NA日期并验证时间条件
  !is.na(DATE) & DATE < INDX_DATE
), by = ID]

dplyr 实现 #

同样先处理日期格式，再分组计算：

library(dplyr)

DT %>%
  mutate(across(c(DATE, INDX_DATE), as.Date)) %>%
  group_by(ID) %>%
  mutate(
    ALFA = as.integer(any(
      (grepl(paste0("^(", paste(ALFA_DEF, collapse="|"), ")"), C_OPR) |
       grepl(paste0("^(", paste(ALFA_DEF, collapse="|"), ")"), D_OPR)) &
      !is.na(DATE) & DATE < INDX_DATE
    ))
  ) %>%
  ungroup()

关键说明 #

• 正则表达式中用^()确保匹配字符串开头（比如ABCD01匹配ABCD、EFGHIJ匹配EFGH）
• 加入!is.na(DATE)避免空日期干扰判断逻辑
• +any(...)或as.integer(any(...))将布尔值转换为0/1格式的数值

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