R语言按日期范围筛选多列并计算HIV病毒载量最大值问题

数据框 #

df <- structure (list(
  subject_id = c("232-5467", "232-6784", "232-3457", "232-0987", "232-1245", "232-1945"), 
  HIV_VL_result_date_1 = c("2015-10-11","2015-10-10","2015-11-06","2016-02-02","2017-12-04","2019-02-15"),
  VL_results_1 = c("LDL", "LDL", "LDL", "<100", "44405", "2322"), 
  HIV_VL_result_date_2 = c("2017-05-21", "2022-04-07", "2016-08-21", "2016-11-01", "2018-02-26",NA),
  VL_results_2 = c("LDL", "5613", "LDL", "LDL", "93356", NA), 
  HIV_VL_result_date_3 = c("2018-06-27", "2022-07-15", "2022-04-13", "2017-03-01","2018-05-19",NA), 
  VL_results_3 = c("LDL", "6590", "LDL", "LDL", "19078",NA), 
  HIV_VL_result_date_4 = c("2020-04-16", "2022-08-15", NA, "2022-06-07", "2020-01-16",NA),
  VL_results_4 = c("LDL", "375", NA, "36", "44",NA),
  HIV_VL_result_date_5 = c("2021-03-25", "2023-01-28", NA, NA, "2022-05-03",NA),
  VL_results_5 = c("LDL", "9125", NA, NA, "LDL",NA),
  HIV_VL_result_date_6 = c("2022-03-07", NA, NA, NA, "2022-11-15",NA),
  VL_results_6 = c("LDL", NA, NA, NA, "<20",NA),
  preg_date = c("2022-03-04","2022-08-13","2022-05-04","2022-06-02","2022-04-14",NA),
  estimated_start_date = c("2021-06-24", "2021-11-06", "2021-08-20","2021-09-27","2021-08-04",NA)), 
  class = "data.frame", row.names = c(NA, -6L))

需求 #

筛选处于estimated_start_date与preg_date日期范围内的HIV_VL_result_date对应的VL_results数据，计算每行该范围内的最大值并在df中生成新列。

已尝试代码 #

df <- df %>%
  mutate_at(
    vars(starts_with("VL_results_")),
    ~ case_when(
      . == "LDL" ~ 0,
      . == "<20" ~ 20,
      . == "<50" ~ 50,
      TRUE ~ as.numeric(.)
    )
  )

df <- viral_suppres_edit %>%
  filter(!is.na(preg_date), !is.na(estimated_start_date)) %>%
  mutate(
    preg_date = ymd(preg_date),
    estimated_start_date = ymd(estimated_start_date),
    HIV_VL_result_date_1 = ymd(HIV_VL_result_date_1),
    HIV_VL_result_date_2 = ymd(HIV_VL_result_date_2),
    HIV_VL_result_date_3 = ymd(HIV_VL_result_date_3),
    HIV_VL_result_date_4 = ymd(HIV_VL_result_date_4),
    HIV_VL_result_date_5 = ymd(HIV_VL_result_date_5),
    HIV_VL_result_date_6 = ymd(HIV_VL_result_date_6),
  ) %>%
  rowwise() %>%
  mutate(
    highest_hivvl = max(as.numeric(VL_results_1), as.numeric(VL_results_2), as.numeric(VL_results_3), as.numeric(VL_results_4), as.numeric(VL_results_5), as.numeric(VL_results_6),
    na.rm = TRUE))

问题 #

筛选逻辑未生效，仍取整行VL最大值，尝试过pivot_longer等方法仍未解决，请求修正代码或提供更高效实现方式。

解决方案 #

使用tidyverse的宽转长操作可以更清晰地处理日期筛选和最大值计算，避免逐列判断的繁琐：

library(tidyverse)
library(lubridate)

# 处理数据：转换日期格式，宽转长，筛选范围后计算最大值
df_processed <- df %>%
  # 批量转换所有日期列格式
  mutate(across(c(preg_date, estimated_start_date, starts_with("HIV_VL_result_date_")), ymd)) %>%
  # 过滤掉日期缺失的行
  filter(!is.na(preg_date), !is.na(estimated_start_date)) %>%
  # 宽转长，将配对的日期和结果列合并
  pivot_longer(
    cols = starts_with(c("HIV_VL_result_date_", "VL_results_")),
    names_to = c(".value", "test_num"),
    names_pattern = "(HIV_VL_result_date|VL_results)_(\\d+)"
  ) %>%
  # 转换VL结果为数值
  mutate(VL_results = case_when(
    VL_results == "LDL" ~ 0,
    VL_results == "<20" ~ 20,
    VL_results == "<50" ~ 50,
    !is.na(VL_results) ~ as.numeric(VL_results),
    TRUE ~ NA_real_
  )) %>%
  # 筛选处于目标日期范围内的检测记录
  filter(between(HIV_VL_result_date, estimated_start_date, preg_date)) %>%
  # 按受试者分组计算VL最大值
  group_by(subject_id) %>%
  summarise(highest_hivvl = max(VL_results, na.rm = TRUE)) %>%
  # 合并回原数据框，保留所有原始行
  right_join(df %>% mutate(across(c(preg_date, estimated_start_date), ymd)), by = "subject_id") %>%
  # 处理无符合条件记录的情况（将Inf转为NA）
  mutate(highest_hivvl = ifelse(is.infinite(highest_hivvl), NA_real_, highest_hivvl))

代码说明 #

1. 批量日期转换：用across一次性转换所有日期列，替代逐列手动转换。
2. 宽转长简化逻辑：通过pivot_longer的names_pattern自动配对日期和结果列，大幅简化后续筛选操作。
3. 统一数值转换：将文本型的VL结果统一转为数值，确保计算有效性。
4. 精准范围筛选：用between函数直接筛选处于起始日期和妊娠日期之间的检测记录。
5. 分组计算最大值：按受试者分组后取符合条件的VL最大值，再合并回原数据，保留所有原始行结构。
6. 异常值处理：对没有符合条件记录的行，将max返回的Inf转为NA，保证结果合理性。

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