如何在R数据框中更简洁地计算多列日期差值？

我需要计算R数据框中多列日期的差值，已知可以用difftime(Date2, Date1, unit = "days")计算单组日期差，下面这段代码能运行，但想找更整洁的方式在数据框内实现：

library(glue)
library(tidyverse)

col_of_interest <- c("InstantDate")
col_orders <- paste0(col_of_interest, "_", rep(c(1:11), each = 1))

data_date <- data %>% 
  select(any_of(col_orders))

datadiff <- data_date[2:11] - data_date[1:10]

我尝试了下面的代码但没成功：

data_date <- data %>% 
  select(any_of(col_orders)) %>%
  mutate(for(i in (seq(vars) - 1)) "Day_diff_{i}" := difftime(vars[i+1], vars[i], units = "days"))

示例数据：

data <- structure(list(Total_1 = c("NULL", "NULL", "NULL", "NULL", "NULL", 
"NULL"), Total_2 = c("17", "5", "3", "13", "NULL", "0"), Total_3 = c("15", 
"NULL", NA, "2", "6", NA), Total_4 = c("9", NA, NA, "8", NA, 
NA), Total_5 = c("15", NA, NA, "14", NA, NA), Total_6 = c("NULL", 
NA, NA, NA, NA, NA), Total_7 = c(NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_), 
Total_8 = c(NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_), Total_9 = c(NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_), Total_10 = c(NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_
), Total_11 = c(NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_), InstantDate_1 = structure(c(18327, 
18330, 18332, 18332, 18332, 18333), class = "Date"), InstantDate_2 = structure(c(18673, 
18858, 18794, 18527, 18516, 18533), class = "Date"), InstantDate_3 = structure(c(18703, 
19044, NA, 18673, 18726, NA), class = "Date"), InstantDate_4 = structure(c(18786, 
NA, NA, 18905, NA, NA), class = "Date"), InstantDate_5 = structure(c(18855, 
NA, NA, 19006, NA, NA), class = "Date"), InstantDate_6 = structure(c(19229, 
NA, NA, NA, NA, NA), class = "Date"), InstantDate_7 = structure(c(NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_), class = "Date"), 
InstantDate_8 = structure(c(NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_), class = "Date"), InstantDate_9 = structure(c(NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_), class = "Date"), 
InstantDate_10 = structure(c(NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_), class = "Date"), InstantDate_11 = structure(c(NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_), class = "Date"), 
VisitType_1 = c("NULL", "NULL", "NULL", "NULL", "NULL", "NULL"
), VisitType_2 = c("FOLLOW UP", "FOLLOW UP", "VIRTUAL VISIT", 
"OFFICE VISIT", "NULL", "VIRTUAL VISIT"), VisitType_3 = c("FOLLOW UP", 
"FOLLOW UP", NA, "VIRTUAL VISIT", "VIRTUAL VISIT", NA), VisitType_4 = c("FOLLOW UP", 
NA, NA, "VIRTUAL VISIT", NA, NA), VisitType_5 = c("FOLLOW UP", 
NA, NA, "FOLLOW UP", NA, NA), VisitType_6 = c("FOLLOW UP", 
NA, NA, NA, NA, NA), VisitType_7 = c(NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_
), VisitType_8 = c(NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_), VisitType_9 = c(NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_), VisitType_10 = c(NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_
), VisitType_11 = c(NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_)), row.names = c(NA, 
-6L), class = c("tbl_df", "tbl", "data.frame"))

解决方案 #

你之前的代码错误在于mutate中不能直接使用for循环语法，需要利用tidyverse的批量列处理能力。以下是几种简洁实现方式：

方法1：使用map2与准引用（!!!）批量生成差值列

library(tidyverse)

col_of_interest <- "InstantDate"
col_orders <- paste0(col_of_interest, "_", 1:11)

data_date <- data %>% 
  select(any_of(col_orders)) %>%
  mutate(
    # 用map2配对相邻日期列，计算差值后命名为Day_diff_1到Day_diff_10
    !!!set_names(
      map2(select(., col_orders[2:11]), select(., col_orders[1:10]), 
           ~ difftime(.x, .y, units = "days")),
      paste0("Day_diff_", 1:10)
    )
  )

方法2：使用across按列位置处理

这种方法不需要提前指定列名，直接根据数据框的列位置计算相邻差值：

data_date <- data %>% 
  select(any_of(col_orders)) %>%
  mutate(
    across(1:(ncol(.)-1), 
           ~ difftime(pick(cur_column() + 1), ., units = "days"),
           .names = "Day_diff_{str_remove(.col, 'InstantDate_')}")
  )

方法3：结合lead函数

如果日期列是按顺序排列的，也可以用lead获取下一列的值来计算差值：

data_date <- data %>% 
  select(any_of(col_orders)) %>%
  mutate(
    across(all_of(col_orders[1:10]), 
           ~ difftime(lead(., order_by = seq_along(.)), ., units = "days"),
           .names = "Day_diff_{str_remove(.col, 'InstantDate_')}")
  )

这些方法都能在管道流内完成计算，自动生成命名规范的差值列，代码更整洁易读，同时保留原始日期列。

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