如何在R的data.table中动态分配变量名并获取NA的起止时间？

数据背景 #

有一份人员执行任务时的化学物质浓度观测数据：3名人员，每人执行2项任务，每项任务重复2次；在5个时间点同时测量3种化学物质的浓度，部分数据存在缺失。数据构造代码如下：

test_dt <- data.table(person = rep(LETTERS[1:3],each = 20),
                      task = rep(LETTERS[24:25], each = 10),
                      reps = rep(c(1,2),each = 5),
                      time = 1:5, chem1 = rnorm(60,1,0.2),
                      chem2 = rnorm(60,4,1.1),chem3 = rnorm(60,2,0.75))
test_dt[person == "A" & reps == 2,`:=`(chem3 = NA_real_)]
test_dt[person == "B" & task == "X" & reps == 1 & time %in% 3:5,chem1 := NA_real_]
test_dt[person == "C" & task == "Y" & reps == 2 & time %in% 3:4,chem2 := NA_real_]

需求 #

按person、task、reps分组，获取每种化学物质数据首次出现NA的时间和NA结束的时间。

错误尝试及报错 #

尝试了以下代码：

lapply(c("chem1","chem2","chem3"),function(var){
  start_var = paste0("na_start_",var)
  end_var = paste0("na_end_",var)
  test_dt[is.na(get(var)), 
          .(deparse(substitute(start_var)) = min(time),
            deparse(substitute(end_var)) = max(time)),
          .(person,task,reps)]
})

出现报错：

"  test_dt[is.na(get(var)), 
          .(deparse(substitute(start_var)) ="
>             deparse(substitute(end_var)) = max(time)),
Error: unexpected ')' in "            deparse(substitute(end_var)) = max(time))"
>           .(person,task,reps)]
Error: unexpected ']' in "          .(person,task,reps)]"
> })
Error: unexpected '}' in "}"

解决方案 #

原代码的问题在于动态命名列的方式错误——deparse(substitute())不能直接用于data.table的j表达式中作为列名，以下是两种可行的解决方法：

方法1：用setNames重命名结果列 #

先计算NA的起止时间，再对结果列进行重命名：

lapply(c("chem1","chem2","chem3"), function(var){
  start_var <- paste0("na_start_", var)
  end_var <- paste0("na_end_", var)
  # 筛选有NA的行，按分组计算起止时间
  res <- test_dt[is.na(get(var)), 
                 .(min_time = min(time), max_time = max(time)),
                 by = .(person, task, reps)]
  # 重命名列
  setNames(res, c("person", "task", "reps", start_var, end_var))
})

方法2：在j表达式中直接生成动态命名列 #

结合eval和as.name，在分组计算时直接生成目标列名：

lapply(c("chem1","chem2","chem3"), function(var){
  start_var <- paste0("na_start_", var)
  end_var <- paste0("na_end_", var)
  test_dt[is.na(get(var)), 
          .(
            eval(as.name(start_var)) := min(time),
            eval(as.name(end_var)) := max(time)
          ),
          by = .(person, task, reps)]
})

可选：合并所有结果到一个表 #

如果需要将三种化学物质的NA起止时间合并为一个data.table，可以用Reduce和merge实现：

all_res <- Reduce(function(x, y) merge(x, y, by = c("person", "task", "reps"), all = TRUE),
                  lapply(c("chem1","chem2","chem3"), function(var){
                    start_var <- paste0("na_start_", var)
                    end_var <- paste0("na_end_", var)
                    res <- test_dt[is.na(get(var)), 
                                   .(min_time = min(time), max_time = max(time)),
                                   by = .(person, task, reps)]
                    setNames(res, c("person", "task", "reps", start_var, end_var))
                  }))
# 查看合并后的结果
print(all_res)

扩展：保留所有分组（包括无NA的） #

如果需要保留所有分组，无NA的分组起止时间设为NA，可以去掉is.na(get(var))的筛选，改为在j中判断：

lapply(c("chem1","chem2","chem3"), function(var){
  start_var <- paste0("na_start_", var)
  end_var <- paste0("na_end_", var)
  test_dt[, 
          .(
            eval(as.name(start_var)) := if(any(is.na(get(var)))) min(time[is.na(get(var))]) else NA_integer_,
            eval(as.name(end_var)) := if(any(is.na(get(var)))) max(time[is.na(get(var))]) else NA_integer_
          ),
          by = .(person, task, reps)]
})

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