R语言：当time_of_capture大于end_time时迁移end_time至start_time列

我有一个包含start_time、end_time和time_of_capture列的数据框，部分行的end_time存在输入错误：当time_of_capture大于end_time时，这些end_time的值本应属于start_time列。需要实现当time_of_capture大于end_time时，将end_time的数值迁移至start_time列。此前尝试多种case_when写法均未成功，示例表格前3行需要迁移（因为捕获时间不能晚于结束时间）。数据结构如下：

structure(list(start_time = c("22:00:00", "NA", "NA", "NA", "NA", 
"NA", "NA", "NA", "NA", "NA", "18:30:00", "NA", "NA", "NA", "18:30:00", 
"NA", "NA", "NA", "NA", "NA", "17:00:00", "18:30:00", "18:30:00", 
"19:50:00", "NA"), end_time = c("23:15:00", "NA", "NA", "NA", 
"NA", "NA", "NA", "NA", "NA", "NA", "19:45:00", "NA", "NA", "NA", 
"19:45:00", "NA", "NA", "NA", "NA", "NA", "18:15:00", "19:45:00", 
"19:45:00", "21:16:00", "NA"), time_of_capture = c("22:19:00", 
"22:19:00", "22:19:00", "22:23:00", "22:27:00", "22:29:00", "22:30:00", 
"22:33:00", "23:38:00", "23:45:00", "19:08:00", "19:40:00", "19:17:00", 
"17:52:00", "18:35:00", "00:07:00", "00:08:00", "00:10:00", "00:15:00", 
"00:23:00", "17:22:00", "18:39:00", "18:32:00", "19:52:00", "19:59:00"
)), row.names = c(NA, 25L), class = "data.frame")

核心问题是原始时间列是字符型，直接比较字符串会出错，需先转换为可比较的时间类型再处理。以下是可行实现步骤：

步骤1：加载工具包并预处理数据 #

library(dplyr)
library(hms) # 专门处理时分秒格式的工具包

# 导入数据（直接用你提供的结构）
df <- structure(list(start_time = c("22:00:00", "NA", "NA", "NA", "NA", 
"NA", "NA", "NA", "NA", "NA", "18:30:00", "NA", "NA", "NA", "18:30:00", 
"NA", "NA", "NA", "NA", "NA", "17:00:00", "18:30:00", "18:30:00", 
"19:50:00", "NA"), end_time = c("23:15:00", "NA", "NA", "NA", 
"NA", "NA", "NA", "NA", "NA", "NA", "19:45:00", "NA", "NA", "NA", 
"19:45:00", "NA", "NA", "NA", "NA", "NA", "18:15:00", "19:45:00", 
"19:45:00", "21:16:00", "NA"), time_of_capture = c("22:19:00", 
"22:19:00", "22:19:00", "22:23:00", "22:27:00", "22:29:00", "22:30:00", 
"22:33:00", "23:38:00", "23:45:00", "19:08:00", "19:40:00", "19:17:00", 
"17:52:00", "18:35:00", "00:07:00", "00:08:00", "00:10:00", "00:15:00", 
"00:23:00", "17:22:00", "18:39:00", "18:32:00", "19:52:00", "19:59:00"
)), row.names = c(NA, 25L), class = "data.frame")

# 将字符型时间转为hms格式，同时把字符型"NA"转为真正的NA值
df <- df %>%
  mutate(
    start_time = as_hms(ifelse(start_time == "NA", NA, start_time)),
    end_time = as_hms(ifelse(end_time == "NA", NA, end_time)),
    time_of_capture = as_hms(time_of_capture)
  )

步骤2：执行迁移逻辑 #

用case_when实现条件判断，完成值的迁移：

df_fixed <- df %>%
  mutate(
    # 当end_time不为空，且捕获时间晚于结束时间时，把end_time赋值给start_time
    start_time = case_when(
      !is.na(end_time) & time_of_capture > end_time ~ end_time,
      TRUE ~ start_time # 其他情况保持原start_time不变
    ),
    # 可选：如果迁移后需要清空原end_time，取消下面注释
    # end_time = case_when(
    #   !is.na(end_time) & time_of_capture > end_time ~ NA_real_,
    #   TRUE ~ end_time
    # )
  )

步骤3：验证结果 #

查看前3行的修复效果：

head(df_fixed, 3)

输出会显示前3行的start_time已被替换为原end_time的值，符合需求。

• 必须转换时间类型：直接比较字符型时间会因字符顺序出错（比如跨天的"00:07:00"和"23:59:00"字符串比较会判断错误，hms格式能正确处理时分秒的大小关系）。
• 处理字符型"NA"：原始数据中的"NA"是字符串，需先转为真正的NA值，才能让后续的is.na()判断生效。

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