R使用dplyr的between判断日期区间含NA时报错如何解决？

现有数据 #

构造数据的代码如下：

df1 <- structure(list(ID = c(1, 2, 2, 2, 3, 4, 5, 6, 6, 7, 8, 8, 9, 
10), dateA = structure(c(14974, 18628, 18628, 18628, 14882, 16800, 
14882, 17835, 17835, 16832, 16556, 16556, 15949, 16801), class = "Date"), 
dateB = structure(c(14610, 15340, 15706, 17501, 14730, NA, 
14700, 16191, 17106, 16801, 15810, 16436, 14655, 15431), class = "Date"), 
dateC = structure(c(18628, 15705, 17500, 18628, 18628, NA, 
18628, 17105, 18628, 18628, 16435, 16556, 15706, 18628), class = "Date")), row.names = c(NA, 
-14L), class = c("data.table", "data.frame"))

数据预览：

ID      dateA      dateB      dateC
 1:  1 2010-12-31 2010-01-01 2021-01-01
 2:  2 2021-01-01 2012-01-01 2012-12-31
 3:  2 2021-01-01 2013-01-01 2017-11-30
 4:  2 2021-01-01 2017-12-01 2021-01-01
 5:  3 2010-09-30 2010-05-01 2021-01-01
 6:  4 2015-12-31       <NA>       <NA>
 7:  5 2010-09-30 2010-04-01 2021-01-01
 8:  6 2018-10-31 2014-05-01 2016-10-31
 9:  6 2018-10-31 2016-11-01 2021-01-01
10:  7 2016-02-01 2016-01-01 2021-01-01
11:  8 2015-05-01 2013-04-15 2014-12-31
12:  8 2015-05-01 2015-01-01 2015-05-01
13:  9 2013-09-01 2010-02-15 2013-01-01
14: 10 2016-01-01 2012-04-01 2021-01-01

问题描述 #

需求是判断dateA是否落在dateB和dateC构成的闭区间内，初始代码如下：

library(dplyr)
df1 %>% 
  mutate(match= ifelse(between(dateA, dateB, dateC), 1, 0))

运行后报错：

Error: Problem with `mutate()` column `match`.
i `match = ifelse(between(dateA, dateB, dateC), 1, 0)`.
x Not yet implemented NAbounds=TRUE for this non-numeric and non-character type

测试发现删除含NA的第6行后代码可正常运行：

df1 %>% 
  slice(-6) %>% 
  mutate(match= ifelse(between(dateA, dateB, dateC), 1, 0))

现在需要实现：保留含NA的行，同时正常执行判断逻辑。 #

解决方案 #

报错的核心原因是dplyr::between对日期类型的边界NA值支持不完善，可直接替换为逻辑比较表达式，NA参与比较时会自动返回NA，符合常规缺失值处理逻辑：

library(dplyr)
df1 %>% 
  mutate(match = ifelse(dateA >= dateB & dateA <= dateC, 1, 0))

如果需要自定义NA行的返回值（比如默认返回0或者其他标识），可增加条件判断：

# 含NA的行match返回0的写法
df1 %>% 
  mutate(match = case_when(
    is.na(dateB) | is.na(dateC) ~ 0,
    dateA >= dateB & dateA <= dateC ~ 1,
    TRUE ~ 0
  ))

两种方案都可以保留所有行，不需要删除含NA的记录。

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