如何按ID分组创建标记目标日期是否在区间内的变量

需求说明 #

需要创建名为positive的新变量，规则如下：

• 同一ID对应多行数据
• 若某行的targ_date处于该行st_date与en_date组成的日期区间内（包含边界），则该行positive赋值为1，否则为0
• 需处理日期字段的NA值，只要任一日期为NA，positive直接赋值为0

复现数据 #

ID<-c("4674","4674","4674","4674","4674","4674","4674",   
      "25694","25694","25694","25694","25694","25694","25694", 
      "92252","92252","92252","92252","92252") 

st_date<-c(NA,NA,"2022-05-09",NA,NA,"2013-07-01","2013-11-27", 
           NA,NA,NA,NA,NA,"2011-04-14","2011-08-04", 
           NA,NA,"2015-08-07","2017-08-18",NA) 

en_date<-c(NA,NA,"2022-06-10",NA,NA,"2013-08-01","2013-12-27", 
           NA,NA,NA,NA,NA,"2011-06-13","2011-08-15", 
           NA,NA,"2015-09-09","2017-09-18",NA) 

targ_date<-c("2022-05-15","2013-05-01",NA,NA,NA,"2013-06-30",NA, 
             NA,NA,NA,NA,NA,"2011-06-13","2011-07-15", 
             NA,NA,"2015-08-20","2017-08-23",NA) 

positive<-c(0,0,1,0,0,0,0, 0,0,0,0,0,1,0, 0,0,1,0,0) 

data<-data.frame(ID,st_date,en_date,targ_date,positive)

尝试过的无效代码 #

ds <- ds %>% 
  group_by(ID) %>% 
  mutate( between_any = as.numeric(inrange(targ_date, st_date, en_date)) ) %>% 
  ungroup()

正确解决方案 #

步骤说明 #

1. 先将字符型日期转换为日期格式，否则无法进行大小比较
2. 逐行判断：优先处理NA值，再判断目标日期是否在当前行的起止区间内

代码实现 #

library(dplyr)
library(lubridate)

# 转换日期列格式为日期类型
data_processed <- data %>%
  mutate(
    st_date = ymd(st_date),
    en_date = ymd(en_date),
    targ_date = ymd(targ_date)
  )

# 创建positive变量
data_processed <- data_processed %>%
  mutate(
    positive = case_when(
      # 任一日期为NA时返回0
      is.na(st_date) | is.na(en_date) | is.na(targ_date) ~ 0,
      # 目标日期在区间内（包含边界）返回1
      targ_date >= st_date & targ_date <= en_date ~ 1,
      # 其他情况返回0
      TRUE ~ 0
    )
  )

原代码问题分析 #

• 未转换日期类型：字符型日期无法正确进行区间比较
• inrange函数用法错误：该函数用于判断向量元素是否在另一个向量的范围中，而非逐行匹配当前行的三个日期
• 多余的group_by(ID)：需求是逐行判断，不需要按ID分组

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