基于data.table处理含NA的出生日期计算年龄问题

使用R的data.table工具处理大数据集的年龄计算时，参考了MichaelChirico的get_age函数，但当数据中存在空的出生日期条目时，foverlaps()会因NA值抛出如下错误：

Error in foverlaps(data.table(start = bdr <- bday%%1461L, end = bdr), :
NA values in data.table 'x' start column: 'start'. All rows with NA values in the range columns must be removed for foverlaps() to work.

需要保留这些含NA的记录，且对应的年龄输出NA，解决方案如下：

修改后的get_age函数 #

核心逻辑是先标记NA位置，仅对有效日期计算年龄，最后将NA填充回结果，保证原数据行数不变：

library(data.table)
get_age <- function(birthdays, ref_dates){
  # 标记出生日期或参考日期为NA的位置
  na_idx <- is.na(birthdays) | is.na(ref_dates)
  # 初始化结果向量，默认填充NA
  age_res <- rep(NA_real_, length(birthdays))
  
  # 仅处理非NA的有效记录
  if (sum(!na_idx) > 0) {
    birthdays_non_na <- birthdays[!na_idx]
    ref_dates_non_na <- ref_dates[!na_idx]
    
    x <- data.table(bday <- unclass(birthdays_non_na),
                    rem = ((ref <- unclass(ref_dates_non_na)) - bday) %% 1461)
    
    x[ , cycle_type := 
         foverlaps(data.table(start = bdr <- bday %% 1461L, end = bdr),
                   data.table(start = c(0L, 59L, 424L, 790L, 1155L), 
                              end = c(58L, 423L, 789L, 1154L, 1460L), 
                              val = c(3L, 2L, 1L, 4L, 3L),
                              key = "start,end"))$val]
    
    I4 <- diag(4L)[ , -4L]
    x[ , extra := 
         foverlaps(data.table(start = rem, end = rem),
                   data.table(start = st <- cumsum(c(0L, rep(365L, 3L) +
                                                       I4[.BY[[1L]],])),
                              end = c(st[-1L] - 1L, 1461L),
                              int_yrs = 0:3, key = "start,end"))[ , int_yrs + (i.start - start) / (end + 1L - start)], by = cycle_type]
    
    # 将计算结果赋值到对应非NA位置
    age_res[!na_idx] <- 4L * ((ref - bday) %/% 1461L) + x$extra
  }
  
  return(age_res)
}

测试验证 #

用示例数据验证修改后的函数：

library(lubridate)
test <- structure(list(city = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10), date = c(10101992, 
15101996, 1031997, 1061900, 13011870, 14071983, 11121995, NA, 
11121995, 29021996), reference = c(20032023, 20032023, 20032023, 
20032023, 20032023, 20032023, 20032023, 20032023, 20032023, 20032023
), date1 = structure(c(8318, 9784, 9921, -25416, -36512, 4942, 
9475, NA, 9475, 9555), class = "Date"), reference1 = structure(c(19436, 
19436, 19436, 19436, 19436, 19436, 19436, 19436, 19436, 19436
), class = "Date")), row.names = c(NA, -10L), class = c("tbl_df", 
"tbl", "data.frame"))

test$date1 <- dmy(test$date)
test$reference1 <- dmy(test$reference)
test$age <- get_age(test$date1, test$reference1)

运行后得到符合预期的结果：含NA的出生日期对应的年龄为NA，其余记录计算出准确年龄：

city     date reference      date1 reference1      age
1     1 10101992  20032023 1992-10-10 2023-03-20 30.44110
2     2 15101996  20032023 1996-10-15 2023-03-20 26.42740
3     3  1031997  20032023 1997-03-01 2023-03-20 26.05191
4     4  1061900  20032023 1900-06-01 2023-03-20 122.80000
5     5 13011870  20032023 1870-01-13 2023-03-20 153.17808
6     6 14071983  20032023 1983-07-14 2023-03-20 39.68033
7     7 11121995  20032023 1995-12-11 2023-03-20 27.27049
8     8       NA  20032023       <NA> 2023-03-20       NA
9     9 11121995  20032023 1995-12-11 2023-03-20 27.27049
10   10 29021996  20032023 1996-02-29 2023-03-20 27.05191

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