如何在R中批量生成按时间先后排序的time_range列？

原始数据 #

data <- data.frame(
  group = 1:5,
  A_time1 = c("12:03", "07:09", "11:22", "08:23", "01:15"),
  A_time2 = c("11:05", "11:22", "07:22", "02:04", "07:19"),
  B_time1 = c("02:07", "14:23", "18:09", "04:11", "05:27"),
  B_time2 = c("08:51", "18:27", "21:36", "09:01", "13:32"),
  Z_time1 = c("18:28", "04:33", "14:38", "03:44", "14:01"),
  Z_time2 = c("09:45", "14:45", "12:21", "21:07", "15:02")
)

需求 #

为A、B、Z每个前缀对应的_time1和_time2列，批量生成{前缀}_time_range新列，要求将每组中较早的时间放在前面，格式为早时间-晚时间。

你尝试的代码（存在问题） #

# 单列尝试：未指定前缀对应的列名，语法错误
data %>% mutate(A_time_range = ifelse(as.numeric(gsub(":","",time1)) < as.numeric(gsub(":","",time2)),paste0(time1,"-",time2),paste0(time2,"-",time1)))

# 批量尝试：语法错误（contain应为contains）+ 逻辑错误（未成对处理_time1和_time2）
data %>% mutate_at(vars(contain("_time")), funs(. = ifelse(as.numeric(gsub(":","",.)) < as.numeric(gsub(":","",.)), ,paste0(time1,"-",time2),paste0(time2,"-",time1)),.names = '{col}_time_range')

解决方案 #

方法一：手动处理单前缀（适合前缀数量少的场景） #

直接针对每个前缀编写逻辑，简单直观：

library(dplyr)

data <- data %>%
  mutate(
    # 生成A的时间范围
    A_time_range = ifelse(
      as.numeric(gsub(":", "", A_time1)) < as.numeric(gsub(":", "", A_time2)),
      paste0(A_time1, "-", A_time2),
      paste0(A_time2, "-", A_time1)
    ),
    # 生成B的时间范围
    B_time_range = ifelse(
      as.numeric(gsub(":", "", B_time1)) < as.numeric(gsub(":", "", B_time2)),
      paste0(B_time1, "-", B_time2),
      paste0(B_time2, "-", B_time1)
    ),
    # 生成Z的时间范围
    Z_time_range = ifelse(
      as.numeric(gsub(":", "", Z_time1)) < as.numeric(gsub(":", "", Z_time2)),
      paste0(Z_time1, "-", Z_time2),
      paste0(Z_time2, "-", Z_time1)
    )
  )

方法二：批量循环处理（适合前缀数量多的场景） #

自动提取前缀并循环生成列，避免重复代码：

library(dplyr)
library(stringr)

# 提取所有前缀（A、B、Z）
prefixes <- unique(str_extract(names(data)[str_detect(names(data), "_time")], "^[A-Z]"))

# 循环每个前缀生成对应的time_range列
for(p in prefixes) {
  time1_col <- paste0(p, "_time1")
  time2_col <- paste0(p, "_time2")
  range_col <- paste0(p, "_time_range")
  
  data <- data %>%
    mutate(
      !!range_col := ifelse(
        as.numeric(gsub(":", "", .data[[time1_col]])) < as.numeric(gsub(":", "", .data[[time2_col]])),
        paste0(.data[[time1_col]], "-", .data[[time2_col]]),
        paste0(.data[[time2_col]], "-", .data[[time1_col]])
      )
    )
}

方法三：tidyverse重塑法（更优雅的长表转宽表处理） #

通过数据重塑统一处理所有前缀，代码更简洁：

library(dplyr)
library(tidyr)

data_processed <- data %>%
  # 转成长表，拆分前缀和时间类型
  pivot_longer(
    cols = -group, 
    names_to = c("prefix", "time_type"), 
    names_sep = "_",
    values_to = "time"
  ) %>%
  # 转回宽表，每个前缀对应time1和time2
  pivot_wider(names_from = time_type, values_from = time) %>%
  # 生成时间范围列
  mutate(
    time_range = ifelse(
      as.numeric(gsub(":", "", time1)) < as.numeric(gsub(":", "", time2)),
      paste0(time1, "-", time2),
      paste0(time2, "-", time1)
    )
  ) %>%
  # 再次转成长表，合并前缀和时间类型
  pivot_longer(
    cols = c(time1, time2, time_range),
    names_to = "time_type",
    values_to = "value"
  ) %>%
  # 转回宽表，恢复原始列结构
  pivot_wider(names_from = c(prefix, time_type), values_from = value) %>%
  # 按列名排序，匹配原始数据的列顺序
  select(group, sort(names(.)))

最终输出示例 #

处理后的数据会包含每个前缀对应的_time_range列，例如：

group A_time1 A_time2 A_time_range B_time1 B_time2 B_time_range Z_time1 Z_time2 Z_time_range
1     12:03   11:05   11:05-12:03 02:07   08:51   02:07-08:51 18:28   09:45   09:45-18:28
2     07:09   11:22   07:09-11:22 14:23   18:27   14:23-18:27 04:33   14:45   04:33-14:45
3     11:22   07:22   07:22-11:22 18:09   21:36   18:09-21:36 14:38   12:21   12:21-14:38
4     08:23   02:04   02:04-08:23 04:11   09:01   04:11-09:01 03:44   21:07   03:44-21:07
5     01:15   07:19   01:15-07:19 05:27   13:32   05:27-13:32 14:01   15:02   14:01-15:02

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