如何用dplyr高效修正R数据框中Check的时间顺序问题

每个ID对应11条Check记录，每条包含Start_time和end_time字段，但部分记录的end_time大于下一条Check的Start_time，破坏了时间顺序。需要用dplyr的mutate函数检测并修正该问题，确保每个Check的end_time始终小于下一条Check的Start_time。

数据示例 #

# 修正语法错误后的原始数据
df <- data.frame(
  ID = rep("A", 11),
  Check = 1:11,
  Start_time = c("2022-09-01 00:00:00.0","2022-09-01 00:00:00.0","2022-09-01 00:00:00.0","2022-09-14 13:03:00.0","2022-09-14 13:04:32.531","2022-09-14 13:04:35.0","2022-09-14 17:04:32.965","2022-09-19 22:05:10.137","2022-09-20 22:09:39.456","2022-09-22 18:44:06.992","2022-09-22 19:40:31.852"),
  end_time = c("2022-09-01 00:00:00.0","2022-09-01 00:00:00.0","2022-09-19 18:03:44.0","2022-09-19 18:05:08.0","2022-09-19 18:05:01.694","2022-09-19 18:05:06.0","2022-09-19 22:05:10.137","2022-09-20 22:09:39.456","2022-09-21 12:40:24.32","2022-09-22 18:44:06.992","2022-09-22 19:40:31.852")
)

尝试过的无效代码 #

df %>%
group_by(ID,Check) %>% 
mutate(end_time = ifelse(end_time>Start_time,Start_time,end_time)

无效原因：

• 按ID和Check分组后，每组仅单条记录，无法获取下一条Check的Start_time
• 未将字符型时间转换为datetime类型，直接比较字符会导致逻辑错误
• 代码缺少闭合括号

低效但可行的方案 #

df$end_time[df$Check==1] <- as_datetime(ifelse(df$end_time[df$Check==1] > df$Start_time[df$Check==2],df$Start_time[df$Check==2],df$end_time[df$Check==1]))
df$end_time[df$Check==2] <- as_datetime(ifelse(df$end_time[df$Check==2]>df$Start_time[df$Check==3],df$Start_time[df$Check==3],df$end_time[df$Check==2]))
df$end_time[df$Check==3] <- as_datetime(ifelse(df$end_time[df$Check==3]>df$Start_time[df$Check==4],df$Start_time[df$Check==4],df$end_time[df$Check==3]))
df$end_time[df$Check==4] <- as_datetime(ifelse(df$end_time[df$Check==4]>df$Start_time[df$Check==5],df$Start_time[df$Check==5],df$end_time[df$Check==4]))
df$end_time[df$Check==5] <- as_datetime(ifelse(df$end_time[df$Check==5]>df$Start_time[df$Check==6],df$Start_time[df$Check==6],df$end_time[df$Check==5]))
df$end_time[df$Check==6] <- as_datetime(ifelse(df$end_time[df$Check==6]>df$Start_time[df$Check==7],df$Start_time[df$Check==7],df$end_time[df$Check==6]))
df$end_time[df$Check==7] <- as_datetime(ifelse(df$end_time[df$Check==7]>df$Start_time[df$Check==8],df$Start_time[df$Check==8],df$end_time[df$Check==7]))
df$end_time[df$Check==8] <- as_datetime(ifelse(df$end_time[df$Check==8]>df$Start_time[df$Check==9],df$Start_time[df$Check==9],df$end_time[df$Check==8]))
df$end_time[df$Check==9] <- as_datetime(ifelse(df$end_time[df$Check==9]>df$Start_time[df$Check==10],df$Start_time[df$Check==10],df$end_time[df$Check==9]))
df$end_time[df$Check==10] <- as_datetime(ifelse(df$end_time[df$Check==10]>df$Start_time[df$Check==11],df$Start_time[df$Check==11],df$end_time[df$Check==10]))

高效dplyr实现方案 #

实现逻辑 #

1. 将字符型的Start_time和end_time转换为datetime类型，确保时间比较有效
2. 按ID分组（支持多ID场景）
3. 使用lead()函数获取当前Check的下一条记录的Start_time
4. 判断当前记录的end_time：如果大于下一条的Start_time则替换，否则保留原值；最后一条记录无下一条，直接保留原end_time

代码实现 #

library(dplyr)
library(lubridate)

df_clean <- df %>%
  # 转换时间列为datetime类型
  mutate(across(c(Start_time, end_time), ymd_hms)) %>%
  # 按ID分组
  group_by(ID) %>%
  # 获取下一条记录的Start_time，最后一条返回NA
  mutate(next_start = lead(Start_time)) %>%
  # 修正end_time
  mutate(end_time = if_else(
    !is.na(next_start) & end_time > next_start, 
    next_start, 
    end_time
  )) %>%
  # 移除辅助列（可选）
  select(-next_start) %>%
  ungroup()

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