按MRN匹配HDL/LDL/VLDL对应时间点的最近观测日期及数值

需求概述 #

• 针对每个MRN编号，需为HDL、LDL、VLDL三个血脂指标分别匹配以下四个时间点最接近的OBSERVATION_DATE，并提取对应指标的数值：
  • Base（基准时间）
  • SixMonths（六个月后）
  • TwelveMonths（十二个月后）
  • TwentyFourMonths（二十四个月后）
• 仅筛选对应指标非空的观测行作为候选池
• 最终生成24个新列：每个指标对应8列（4个匹配日期列 + 4个对应数值列）

样本数据 #

structure(list(MRN = c(1, 1, 1, 2, 2, 2, 
2, 2, 2, 2, 2, 2, 2, 2, 2, 
2, 2, 2, 2, 2, 2, 3, 3, 3, 
3, 3, 3, 4, 4, 4, 5, 5, 5, 
6, 6, 6, 6, 6, 6, 6, 6, 6, 
6, 6, 6, 6, 6, 6, 6, 6), OBSERVATION_DATE = structure(c(18289, 
18289, 18289, 16073, 16073, 16073, 16434, 16434, 16434, 16536, 
16536, 16536, 16821, 16821, 16821, 17196, 17196, 17196, 17604, 
17604, 17604, 19114, 19114, 19114, 19338, 19338, 19338, 19060, 
19060, 19060, 19730, 19730, 19730, 17326, 17326, 17326, 17331, 
17331, 17331, 17333, 17333, 17333, 17336, 17336, 17336, 17339, 
17339, 17339, 17347, 17347), class = "Date"), HDL = c(NA, 47, 
NA, 40, NA, NA, NA, 43, NA, 38, NA, NA, NA, 41, NA, NA, 48, NA, 
NA, 45, NA, NA, 44, NA, NA, 42, NA, NA, NA, 56, 16, NA, NA, NA, 
34, NA, 34, NA, NA, 31, NA, NA, 33, NA, NA, NA, 32, NA, NA, NA
), LDL = c(NA, NA, 83, NA, 92, NA, 107, NA, NA, NA, NA, 112, 
93, NA, NA, 96, NA, NA, 109, NA, NA, NA, NA, 76, 56, NA, NA, 
141, NA, NA, NA, NA, 49, 55, NA, NA, NA, NA, 57, NA, 53, NA, 
NA, NA, 59, 55, NA, NA, NA, 55), VLDL = c(14, NA, NA, NA, NA, 
46, NA, NA, 30, NA, 30, NA, NA, NA, 28, NA, NA, 20, NA, NA, 28, 
17, NA, NA, NA, NA, 21, NA, 35, NA, NA, 15, NA, NA, NA, 24, NA, 
24, NA, NA, NA, 20, NA, 23, NA, NA, NA, 26, 22, NA), Base = structure(c(17647, 
17647, 17647, 17032, 17032, 17032, 17032, 17032, 17032, 17032, 
17032, 17032, 17032, 17032, 17032, 17032, 17032, 17032, 17032, 
17032, 17032, 18577, 18577, 18577, 18577, 18577, 18577, 18894, 
18894, 18894, 19431, 19431, 19431, 16751, 16751, 16751, 16751, 
16751, 16751, 16751, 16751, 16751, 16751, 16751, 16751, 16751, 
16751, 16751, 16751, 16751), class = "Date"), SixMonths = structure(c(17830, 
17830, 17830, 17215, 17215, 17215, 17215, 17215, 17215, 17215, 
17215, 17215, 17215, 17215, 17215, 17215, 17215, 17215, 17215, 
17215, 17215, 18760, 18760, 18760, 18760, 18760, 18760, 19077, 
19077, 19077, 19614, 19614, 19614, 16934, 16934, 16934, 16934, 
16934, 16934, 16934, 16934, 16934, 16934, 16934, 16934, 16934, 
16934, 16934, 16934, 16934), class = "Date"), TwelveMonths = structure(c(18012, 
18012, 18012, 17397, 17397, 17397, 17397, 17397, 17397, 17397, 
17397, 17397, 17397, 17397, 17397, 17397, 17397, 17397, 17397, 
17397, 17397, 18942, 18942, 18942, 18942, 18942, 18942, 19259, 
19259, 19259, 19796, 19796, 19796, 17116, 17116, 17116, 17116, 
17116, 17116, 17116, 17116, 17116, 17116, 17116, 17116, 17116, 
17116, 17116, 17116, 17116), class = "Date"), TwentyFourMonths = structure(c(18377, 
18377, 18377, 17762, 17762, 17762, 17762, 17762, 17762, 17762, 
17762, 17762, 17762, 17762, 17762, 17762, 17762, 17762, 17762, 
17762, 17762, 19307, 19307, 19307, 19307, 19307, 19307, 19624, 
19624, 19624, 20161, 20161, 20161, 17481, 17481, 17481, 17481, 
17481, 17481, 17481, 17481, 17481, 17481, 17481, 17481, 17481, 
17481, 17481, 17481, 17481), class = "Date")), row.names = c(NA, 
-50L), class = "data.frame")

实现方案（R语言） #

以下代码通过数据筛选、分组匹配完成需求：

library(dplyr)
library(tidyr)

# 加载样本数据
df <- structure(...) # 替换为上述样本数据结构

# 1. 提取各指标的有效观测（排除NA值）
hdl_valid <- df %>% filter(!is.na(HDL)) %>% select(MRN, OBSERVATION_DATE, HDL)
ldl_valid <- df %>% filter(!is.na(LDL)) %>% select(MRN, OBSERVATION_DATE, LDL)
vldl_valid <- df %>% filter(!is.na(VLDL)) %>% select(MRN, OBSERVATION_DATE, VLDL)

# 2. 定义匹配函数：为每个MRN的指定时间点找到最接近的观测
match_closest_time <- function(metric_data, metric_name, time_cols) {
  metric_data %>%
    group_by(MRN) %>%
    reframe(
      !!!map(time_cols, ~{
        # 获取当前MRN的目标时间点
        target_date <- first(df[[.x]][MRN == cur_group()$MRN])
        # 计算日期差，筛选差值最小的记录
        date_diff <- abs(OBSERVATION_DATE - target_date)
        closest_idx <- which.min(date_diff)
        
        tibble(
          !!paste0(metric_name, "_", .x, "_date") := OBSERVATION_DATE[closest_idx],
          !!paste0(metric_name, "_", .x, "_value") := !!sym(metric_name)[closest_idx]
        )
      }) %>% bind_cols()
    )
}

# 3. 为三个指标分别匹配四个时间点
hdl_matched <- match_closest_time(hdl_valid, "HDL", c("Base", "SixMonths", "TwelveMonths", "TwentyFourMonths"))
ldl_matched <- match_closest_time(ldl_valid, "LDL", c("Base", "SixMonths", "TwelveMonths", "TwentyFourMonths"))
vldl_matched <- match_closest_time(vldl_valid, "VLDL", c("Base", "SixMonths", "TwelveMonths", "TwentyFourMonths"))

# 4. 合并所有结果，保留唯一MRN的基础时间信息
final_result <- df %>%
  distinct(MRN, Base, SixMonths, TwelveMonths, TwentyFourMonths) %>%
  left_join(hdl_matched, by = "MRN") %>%
  left_join(ldl_matched, by = "MRN") %>%
  left_join(vldl_matched, by = "MRN")

# 查看最终结果
head(final_result)

代码说明 #

• 先筛选出每个指标的有效观测行，避免NA值干扰匹配；
• 自定义函数按MRN分组，计算观测日期与目标时间点的差值，选取差值最小的记录生成对应列；
• 最后将三个指标的匹配结果与原始基础时间数据合并，得到包含24个新列的最终数据集。

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