在R语言中为数据集添加包含相同日期列名的same_day新列

我有一个大型数据集，其中包含每位患者多种疾病的诊断日期列。希望生成一个名为same_day的新列，记录日期相同的列名，以此了解哪些疾病是在同一天被诊断的。

初始数据 #

创建数据的代码 #

data.frame(
  ID = c(1,2,3,4,5,6),
  col1 = as.Date(c("2011-02-01","2020-09-01",NA,"2010-06-01",NA,"2015-02-02")),
  col2 = as.Date(c("2010-02-01","2018-09-01","2012-03-01",NA,NA,NA)),
  col3 = as.Date(c("2010-02-01",NA,"2012-03-01","2003-05-01","2000-02-01",NA)),
  col4 = as.Date(c("2011-02-01",NA,"2019-09-10","2014-03-05",NA,"2015-02-02"))
)

数据预览 #

ID       col1       col2       col3       col4
1  1 2011-02-01 2010-02-01 2010-02-01 2011-02-01
2  2 2020-09-01 2018-09-01       <NA>       <NA>
3  3       <NA> 2012-03-01 2012-03-01 2019-09-10
4  4 2010-06-01       <NA> 2003-05-01 2014-03-05
5  5       <NA>       <NA> 2000-02-01       <NA>
6  6 2015-02-02       <NA>       <NA> 2015-02-02

期望结果 #

创建结果的代码 #

data.frame(
  ID = c(1,2,3,4,5,6),
  col1 = as.Date(c("2011-02-01","2020-09-01",NA,"2010-06-01",NA,"2015-02-02")),
  col2 = as.Date(c("2010-02-01","2018-09-01","2012-03-01",NA,NA,NA)),
  col3 = as.Date(c("2010-02-01",NA,"2012-03-01","2003-05-01","2000-02-01",NA)),
  col4 = as.Date(c("2011-02-01",NA,"2019-09-10","2014-03-05",NA,"2015-02-02")),
  same_day = c("col1_col4,col2_col3","none","col2_col3","none","none","col1_col4")
)

结果预览 #

ID       col1       col2       col3       col4            same_day
1  1 2011-02-01 2010-02-01 2010-02-01 2011-02-01 col1_col4,col2_col3
2  2 2020-09-01 2018-09-01       <NA>       <NA>                none
3  3       <NA> 2012-03-01 2012-03-01 2019-09-10           col2_col3
4  4 2010-06-01       <NA> 2003-05-01 2014-03-05                none
5  5       <NA>       <NA> 2000-02-01       <NA>                none
6  6 2015-02-02       <NA>       <NA> 2015-02-02           col1_col4

解决方案 #

方法一：使用dplyr + tidyr（适合熟悉tidyverse的用户） #

library(dplyr)
library(tidyr)

# 加载初始数据
df <- data.frame(
  ID = c(1,2,3,4,5,6),
  col1 = as.Date(c("2011-02-01","2020-09-01",NA,"2010-06-01",NA,"2015-02-02")),
  col2 = as.Date(c("2010-02-01","2018-09-01","2012-03-01",NA,NA,NA)),
  col3 = as.Date(c("2010-02-01",NA,"2012-03-01","2003-05-01","2000-02-01",NA)),
  col4 = as.Date(c("2011-02-01",NA,"2019-09-10","2014-03-05",NA,"2015-02-02"))
)

# 生成same_day列
df <- df %>%
  rowwise() %>%
  mutate(
    # 整理每行的非NA日期和对应列名
    date_col_pairs = list(
      tibble(date = c(col1, col2, col3, col4), col_name = c("col1", "col2", "col3", "col4")) %>%
        filter(!is.na(date))
    ),
    # 按日期分组，合并同日期的列名
    matched_groups = list(
      date_col_pairs %>%
        group_by(date) %>%
        summarise(group_str = paste(sort(col_name), collapse = "_"), .groups = "drop") %>%
        filter(nchar(group_str) > 5)  # 筛选包含多个列的组
    ),
    # 转换为最终字符串，无匹配则填"none"
    same_day = ifelse(nrow(matched_groups) == 0, "none", paste(matched_groups$group_str, collapse = ","))
  ) %>%
  select(-date_col_pairs, -matched_groups) %>%
  ungroup()

print(df)

方法二：Base R实现（无需额外包，适合大型数据集） #

# 加载初始数据
df <- data.frame(
  ID = c(1,2,3,4,5,6),
  col1 = as.Date(c("2011-02-01","2020-09-01",NA,"2010-06-01",NA,"2015-02-02")),
  col2 = as.Date(c("2010-02-01","2018-09-01","2012-03-01",NA,NA,NA)),
  col3 = as.Date(c("2010-02-01",NA,"2012-03-01","2003-05-01","2000-02-01",NA)),
  col4 = as.Date(c("2011-02-01",NA,"2019-09-10","2014-03-05",NA,"2015-02-02"))
)

# 定义逐行处理函数
get_same_day_groups <- function(row) {
  # 筛选非NA的列
  non_na_idx <- !is.na(row)
  non_na_cols <- names(row)[non_na_idx]
  non_na_dates <- row[non_na_idx]
  
  # 如果非NA列数<=1，直接返回"none"
  if (length(non_na_cols) <= 1) return("none")
  
  # 按日期分组
  date_groups <- split(non_na_cols, non_na_dates)
  
  # 生成符合要求的组字符串
  valid_groups <- sapply(date_groups, function(cols) {
    if (length(cols) > 1) paste(sort(cols), collapse = "_") else NULL
  })
  
  # 转换为最终结果
  if (length(valid_groups) == 0) {
    "none"
  } else {
    paste(valid_groups, collapse = ",")
  }
}

# 应用函数到每一行
df$same_day <- apply(df[, -1], 1, get_same_day_groups)

print(df)

说明 #

两种方法均能实现需求：

• 逐行筛选出非NA的日期列，按日期分组
• 将同一日期的列名排序后用下划线连接，确保组内列名顺序一致（例如统一为col2_col3而非col3_col2）
• 无匹配组时自动填充"none"
• Base R方法无需依赖外部包，处理超大型数据集时性能更稳定

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