在R中为每行按日期顺序生成列名序列的新列

需求说明 #

现有一个包含多列日期的DataFrame，需要为每行生成按日期从最早到最新排序的列名序列，并将该序列作为新列seq添加到原DataFrame中。

初始数据 #

数据创建代码 #

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("1998-09-02",NA,"2013-06-01","2003-05-01","2000-02-01",NA)),
  col4 = as.Date(c("2020-04-01",NA,"2019-09-10","2014-03-05",NA,"2000-08-12"))
)

初始表格 #

ID       col1       col2       col3       col4
1  1 2011-02-01 2010-02-01 1998-09-02 2020-04-01
2  2 2020-09-01 2018-09-01         NA         NA
3  3         NA 2012-03-01 2013-06-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 2000-08-12

期望结果 #

结果数据代码 #

result_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("1998-09-02",NA,"2013-06-01","2003-05-01","2000-02-01",NA)),
  col4 = as.Date(c("2020-04-01",NA,"2019-09-10","2014-03-05",NA,"2000-08-12")),
  seq = c("col3_col2_col1_col4","col2_col1","col2_col3_col4","col3_col1_col4","col3","col4_col1")
)

结果表格 #

ID       col1       col2       col3       col4                 seq
1  1 2011-02-01 2010-02-01 1998-09-02 2020-04-01 col3_col2_col1_col4
2  2 2020-09-01 2018-09-01         NA         NA           col2_col1
3  3         NA 2012-03-01 2013-06-01 2019-09-10      col2_col3_col4
4  4 2010-06-01         NA 2003-05-01 2014-03-05      col3_col1_col4
5  5         NA         NA 2000-02-01         NA                col3
6  6 2015-02-02         NA         NA 2000-08-12           col4_col1

解决方案 #

方法1：基础R实现 #

利用apply函数逐行处理，筛选非NA日期、排序后拼接列名：

# 生成排序后的列名序列
df$seq <- apply(df[, -1], 1, function(row) {
  # 筛选非NA的日期对应列
  non_na_idx <- !is.na(row)
  if (sum(non_na_idx) == 0) return("")
  
  # 获取日期和对应列名
  target_dates <- row[non_na_idx]
  target_cols <- names(df)[-1][non_na_idx]
  
  # 按日期排序列名并拼接
  sorted_cols <- target_cols[order(target_dates)]
  paste(sorted_cols, collapse = "_")
})

方法2：tidyverse实现 #

结合dplyr的行处理和purrr的列表操作，代码更直观：

library(tidyverse)

df <- df %>%
  rowwise() %>%
  mutate(
    seq = list(
      # 把当前行的日期列转为长格式
      enframe(c_across(col1:col4), name = "col", value = "date") %>%
        drop_na(date) %>%  # 去除NA值
        arrange(date) %>%  # 按日期排序
        pull(col)  # 提取排序后的列名
    ) %>%
      map_chr(~paste(., collapse = "_"))  # 拼接成字符串
  ) %>%
  ungroup()

两种方法都能得到符合期望的结果，可根据个人习惯选择使用。

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