R语言实现：按行判断个体BMI众数是否在指定日期及之前出现

我有长格式的BMI数据，同一个体同一天可能存在多个BMI值，目前正在用多条件ifelse语句清理这些不一致的数据。作为最后手段，希望用个体的专属众数进行插补，但需满足该众数必须出现在当前行的OBSERVATION_DATE日期及之前（不允许用未来值插补过往数据）。

需要按个体（以MRN为ID变量）分组实现逻辑：针对每一行，检查当前行观测日期及之前的BMI3_round值中是否出现过该个体的BMI3_mode，逐行返回结果——若存在则返回TRUE或众数，若不存在则返回FALSE或NA，而非仅返回每个个体的聚合结果。

附数据结构：

BMIdf2<-structure(list(MRN = c(8271L, 8271L, 8271L, 8271L, 8271L, 8271L, 
9997L, 9997L, 9997L, 9997L, 9997L, 9997L, 9997L, 9997L, 9997L, 
9997L, 9997L, 9997L, 9997L, 9997L, 9997L, 9997L, 9997L, 9997L, 
9997L, 9997L, 9997L, 9997L, 9997L, 9997L, 9997L, 9997L, 9997L, 
9997L, 9997L, 9997L, 9997L, 9997L, 9997L, 9997L, 9997L, 9997L, 
9997L, 9997L, 9997L, 9997L, 9997L, 9997L, 9997L, 9997L, 9997L, 
9997L, 9997L, 9997L, 9997L, 9997L, 9997L, 9997L, 9997L, 9997L, 
9997L, 9997L, 9997L, 76133L, 76133L, 76133L, 76133L, 76133L, 
76133L, 76133L, 76133L, 76133L, 76133L, 76133L, 76133L, 76133L, 
76133L, 76133L, 76133L, 76133L, 76133L, 76133L, 76133L, 76133L, 
76133L, 76133L, 76133L, 76133L, 76133L, 76133L, 76133L, 76133L, 
76133L, 76133L, 76133L, 76133L, 76133L, 76133L, 76133L, 76133L, 
76133L, 76133L, 76133L, 76133L, 76133L, 76133L, 76133L, 76133L, 
76133L, 76133L), OBSERVATION_DATE = structure(c(18039, 18058, 
18086, 18102, 18128, 18494, 15747, 15817, 15845, 18134, 18152, 
18153, 18169, 18183, 18183, 18183, 18183, 18184, 18205, 18205, 
18205, 18205, 18205, 18205, 18205, 18205, 18205, 18214, 18226, 
18226, 18226, 18226, 18234, 18235, 18242, 18247, 18249, 18249, 
18249, 18249, 18267, 18288, 18288, 18288, 18288, 18310, 18330, 
18351, 18373, 18393, 18414, 18435, 18457, 18478, 18499, 18520, 
18541, 18541, 18541, 18541, 18716, 18716, 18716, 18716, 16762, 
16779, 16780, 16790, 16790, 16790, 16790, 16791, 16807, 16807, 
16807, 16807, 16821, 16835, 16868, 16869, 16874, 16877, 16881, 
16919, 16975, 17017, 17077, 17280, 17290, 17318, 17318, 17318, 
17318, 17326, 17326, 17326, 17326, 17331, 17331, 17331, 17331, 
17333, 17336, 17339, 17347, 17360, 17381, 17402, 17423, 17444, 
17465), tzone = "America/Denver", class = "Date"), BMI3_round = c(38, 
38, 38, NA, 38, NA, NA, 30, NA, 29, 29, 29, 30, 30, 30, 30, 30, 
30, 29, 29, 29, 30, 30, 30, 29, 29, 29, 29, 29, 29, 28, 28, 29, 
29, 29, 29, 28, 28, 28, 28, 29, 28, 28, 28, 28, 28, 29, 28, 29, 
28, 28, 28, 28, 28, 28, 28, 28, 28, 28, 28, NA, NA, NA, NA, 35, 
NA, 35, 35, 35, 35, 35, NA, 34, 34, 35, 35, 34, 35, 34, 34, 33, 
NA, 33, 32, 33, 33, 34, 33, 30, 32, 32, 32, 32, 32, 32, 32, 32, 
32, 32, 33, 33, 33, NA, 33, 33, 32, 31, 31, 31, 32, 31), BMI3_mode = c(38, 
38, 38, 38, 38, 38, 28, 28, 28, 28, 28, 28, 28, 28, 28, 28, 28, 
28, 28, 28, 28, 28, 28, 28, 28, 28, 28, 28, 28, 28, 28, 28, 28, 
28, 28, 28, 28, 28, 28, 28, 28, 28, 28, 28, 28, 28, 28, 28, 28, 
28, 28, 28, 28, 28, 28, 28, 28, 28, 28, 28, 28, 28, 28, 28, 33, 
33, 33, 33, 33, 33, 33, 33, 33, 33, 33, 33, 33, 33, 33, 33, 33, 
33, 33, 33, 33, 33, 33, 33, 33, 33, 33, 33, 33, 33, 33, 33, 33, 
33, 33, 33, 33, 33, 33, 33, 33, 33, 33, 33, 33, 33)), row.names = c(NA, 
-111L), class = c("data.table", "data.frame"))

用data.table实现逐行检查逻辑 #

利用data.table的分组和逐行操作特性，高效实现需求：

library(data.table)

# 按MRN分组处理，生成两个结果列
BMIdf2[, {
  # 提前获取个体的众数（每个个体唯一）
  individual_mode <- BMI3_mode[1]
  # 逐行检查当前行及之前的BMI3_round是否包含众数
  mode_available <- sapply(seq_len(.N), function(i) {
    any(BMI3_round[1:i] == individual_mode, na.rm = TRUE)
  })
  # 返回结果列
  .(
    mode_available = mode_available,
    imputed_bmi = ifelse(mode_available, individual_mode, NA)
  )
}, by = MRN]

代码说明 #

1. 分组处理：按MRN分组，保证每个个体的逻辑独立计算
2. 众数复用：提前提取个体的众数individual_mode，避免重复取值
3. 逐行检查：用sapply遍历每个个体的每一行，i为当前行索引，BMI3_round[1:i]取当前行及之前的所有BMI值，any(..., na.rm=TRUE)忽略NA值，判断是否存在众数
4. 结果输出：
   • mode_available：逻辑值，标记当前行是否可用众数插补
   • imputed_bmi：直接返回可用的众数，不可用时返回NA

优化验证 #

以MRN=9997为例，其众数为28，前30行的BMI3_round中未出现28，因此前30行的mode_available均为FALSE；从第31行开始，BMI3_round出现28，后续行的mode_available均为TRUE，完全符合“不使用未来值插补”的要求。

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