R语言数据处理需求：筛选并匹配手术前后最近影像ID

针对你提出的三个需求，以下是基于tidyverse工具链的实现方案：

1. 数据预处理：宽格式转长格式 #

多组影像时间和ID的宽格式不利于批量处理，先转换为长格式：

library(tidyverse)

data_long <- data %>%
  pivot_longer(
    cols = starts_with(c("time_to_image_", "image_id_")),
    names_to = c(".value", "image_num"),
    names_pattern = "(time_to_image|image_id)_(\\d+)"
  ) %>%
  mutate(image_num = as.integer(image_num))

2. 筛选符合条件的记录 #

筛选出手术前24小时内存在影像且手术后存在任意影像的记录：

# 标记每个影像是否属于术前24小时内、术后
data_long <- data_long %>%
  mutate(
    is_pre_24h = time_to_image >= (time_to_procedure - 24) & time_to_image <= time_to_procedure,
    is_post = time_to_image > time_to_procedure
  )

# 筛选有效ID
valid_ids <- data_long %>%
  group_by(unique_id) %>%
  summarise(
    has_pre_24h = any(is_pre_24h, na.rm = TRUE),
    has_post = any(is_post, na.rm = TRUE)
  ) %>%
  filter(has_pre_24h & has_post) %>%
  pull(unique_id)

# 保留有效数据
data_filtered <- data %>% filter(unique_id %in% valid_ids)

3. 添加术前最近影像ID #

提取每个有效记录中，手术前24小时内时间最接近手术的影像ID：

pre_image_info <- data_long %>%
  filter(unique_id %in% valid_ids, is_pre_24h) %>%
  group_by(unique_id) %>%
  arrange(desc(time_to_image)) %>% # 按时间从晚到早排序，首行即为最近的术前影像
  slice(1) %>%
  select(unique_id, pre_closest_image_id = image_id)

data_filtered <- data_filtered %>% left_join(pre_image_info, by = "unique_id")

4. 添加术后最近影像ID #

提取每个有效记录中，手术后时间最接近手术的影像ID：

post_image_info <- data_long %>%
  filter(unique_id %in% valid_ids, is_post) %>%
  group_by(unique_id) %>%
  arrange(time_to_image) %>% # 按时间从早到晚排序，首行即为最近的术后影像
  slice(1) %>%
  select(unique_id, post_closest_image_id = image_id)

data_filtered <- data_filtered %>% left_join(post_image_info, by = "unique_id")

完整可运行代码 #

将生成数据、处理逻辑整合，直接运行即可得到结果：

set.seed(123)
library(tidyverse)
library(stringi)

num_rows <- 100

unique_id <- stringi::stri_rand_strings(num_rows, 5)

time_to_procedure <- runif(num_rows, 0.5, 30)

data <- tibble(
  unique_id = unique_id,
  time_to_procedure = time_to_procedure
)

for (i in 1:6) {
  time_col_name <- paste0("time_to_image_", i)
  image_col_name <- paste0("image_id_", i)
  
  data[[time_col_name]] <- runif(num_rows, 0.5, 50)
  
  if (i > 1) {
    data[[time_col_name]] <- pmax(data[[time_col_name]], data[[paste0("time_to_image_", i - 1)]])
  }
  
  data[[image_col_name]] <- replicate(num_rows, paste0(sample(c(0:9, letters), 5, replace = TRUE), collapse = ""))
}

data <- data %>% arrange(time_to_image_1)

# 宽转长
data_long <- data %>%
  pivot_longer(
    cols = starts_with(c("time_to_image_", "image_id_")),
    names_to = c(".value", "image_num"),
    names_pattern = "(time_to_image|image_id)_(\\d+)"
  ) %>%
  mutate(image_num = as.integer(image_num))

# 标记影像类型
data_long <- data_long %>%
  mutate(
    is_pre_24h = time_to_image >= (time_to_procedure - 24) & time_to_image <= time_to_procedure,
    is_post = time_to_image > time_to_procedure
  )

# 筛选有效ID
valid_ids <- data_long %>%
  group_by(unique_id) %>%
  summarise(
    has_pre_24h = any(is_pre_24h, na.rm = TRUE),
    has_post = any(is_post, na.rm = TRUE)
  ) %>%
  filter(has_pre_24h & has_post) %>%
  pull(unique_id)

# 添加术前最近影像ID
pre_image_info <- data_long %>%
  filter(unique_id %in% valid_ids, is_pre_24h) %>%
  group_by(unique_id) %>%
  arrange(desc(time_to_image)) %>%
  slice(1) %>%
  select(unique_id, pre_closest_image_id = image_id)

data_filtered <- data %>%
  filter(unique_id %in% valid_ids) %>%
  left_join(pre_image_info, by = "unique_id")

# 添加术后最近影像ID
post_image_info <- data_long %>%
  filter(unique_id %in% valid_ids, is_post) %>%
  group_by(unique_id) %>%
  arrange(time_to_image) %>%
  slice(1) %>%
  select(unique_id, post_closest_image_id = image_id)

data_filtered <- data_filtered %>% left_join(post_image_info, by = "unique_id")

# 查看结果
head(data_filtered)

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