DataFrame双列拼接存储的日期与疾病分级匹配提取问题咨询

完整实现代码（基于R tidyverse生态） #

首先加载依赖包：

library(tidyverse)

先构造示例测试数据：

df <- tibble(
  Date = c("2017-10-14=&=2018-01-20=&==&=", "2018-10-14=&=2019-01-20=&=2020-01-01=&="),
  Grade = c("1=&=2=&==&=", "2=&=3=&=4=&=")
)

1. 按疾病分级匹配查询首次出现日期 #

先做通用拆分预处理，把每行的日期和分级拆成配对的列表，过滤空值：

# 通用预处理步骤：拆分字符串、过滤空值、配对日期和分级
df_processed <- df %>%
  mutate(
    date_list = map(str_split(Date, "=&="), ~as.Date(na_if(.x[.x != ""], ""))),
    grade_list = map(str_split(Grade, "=&="), ~as.integer(na_if(.x[.x != ""], ""))),
    # 配对成结构化数据框方便后续查询
    paired_data = map2(date_list, grade_list, ~tibble(date = .x, grade = .y) %>% drop_na())
  )

# 示例：查询首次出现分级为2的日期，可自定义target_grade参数修改查询目标
target_grade <- 2
df_first_occur <- df_processed %>%
  mutate(
    first_date_of_target = map_chr(paired_data, ~{
      filter(.x, grade == target_grade) %>% pull(date) %>% first() %>% as.character()
    })
  ) %>%
  select(first_date_of_target)

2. 输出时变分析用宽表 #

df_timevarying_wide <- df_processed %>%
  mutate(
    wide_data = map(paired_data, ~{
      .x %>%
        mutate(visit = row_number()) %>%
        pivot_wider(
          names_from = visit,
          values_from = c(date, grade),
          names_glue = "{if_else(.value == 'date', 'Follow_up', 'Grade')}_{visit}"
        )
    })
  ) %>%
  unnest(wide_data) %>%
  # 补全所有随访次数列、调整列顺序符合要求
  {
    max_visit <- max(str_extract(names(.), "\\d+$") %>% as.integer(), na.rm = T)
    full_cols <- c(rbind(paste0("Follow_up_", 1:max_visit), paste0("Grade_", 1:max_visit)))
    add_column(., !!!set_names(rep(NA, length(full_cols)), full_cols)[!full_cols %in% names(.)]) %>%
      select(all_of(full_cols))
  }

3. 提取每行最高分级及对应日期 #

df_max_grade <- df_processed %>%
  mutate(
    max_info = map(paired_data, ~{
      .x %>% arrange(desc(grade), date) %>% slice(1) %>%
        rename(Date_max_grade = date, Max_grade = grade)
    })
  ) %>%
  unnest(max_info) %>%
  select(Date_max_grade, Max_grade)

4. 提取首次达到3或4级的日期 #

df_first_3or4 <- df_processed %>%
  mutate(
    First_Date_3_or_4 = map_chr(paired_data, ~{
      filter(.x, grade %in% c(3,4)) %>% pull(date) %>% first() %>% as.character()
    })
  ) %>%
  select(First_Date_3_or_4)

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