跨午夜类别行的时间差计算问题（续）

问题背景 #

这是《Calculating Time Differences Between Rows Where the Category Passes Over Midnight》问题的扩展场景：原单患者数据集的解决方案无法适配多患者疼痛评分数据，存在两个关键问题：

1. 未按patient_id分组后再排序，导致跨午夜的疼痛评分延续逻辑混乱
2. 无疼痛评估的日期未生成对应行，这类行需要补充并设置pain_score=NA、time_diff_hours=24

示例数据集 #

library(tidyverse)

df <-
  tibble(
    patient_id = c(1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2),
    time = lubridate::as_datetime(c("2022-01-04 02:00:00", "2022-01-04 07:00:00", "2022-01-04 15:00:00", "2022-01-04 20:00:00", "2022-01-06 02:00:00", "2022-01-06 08:00:00",
                                    "2022-01-04 04:00:00", "2022-01-04 14:00:00", "2022-01-04 22:00:00", "2022-01-05 02:00:00", "2022-01-05 20:00:00", "2022-01-06 12:00:00", "2022-01-06 18:00:00")),
    day = c(1, 1, 1, 1, 3,3, 1, 1, 1, 2, 2, 3, 3),
    pain_score = c("None", "Mild", "Mild", "Moderate", "None", "Mild", "Moderate", "Severe", "Moderate", "Mild", "Moderate", "Severe", "Moderate")
  )

修正后的解决方案代码 #

df |>
  # 按患者分组，确保每个患者的计算逻辑独立
  group_by(patient_id) |>
  # 生成该患者记录范围内的完整日期序列
  complete(date = seq.Date(lubridate::date(min(time)), 
                           lubridate::date(max(time)), 
                           by = "day")) |>
  # 将日期转换为当日0点的datetime格式
  mutate(time = lubridate::as_datetime(date)) |>
  # 合并原数据的疼痛评分记录，保留原时间点信息
  left_join(df |> mutate(date = lubridate::date(time)), 
            by = c("patient_id", "date"), 
            suffix = c("_day_start", "")) |>
  # 按患者和时间排序，保证逻辑顺序正确
  arrange(patient_id, time) |>
  # 双向填充疼痛评分，处理跨日期延续和缺失日期的评分补充
  fill(pain_score, .direction = "downup") |>
  # 计算时长并修正无评估日期的字段
  mutate(
    # 优先取原数据的下一个记录时间，否则取次日0点
    next_time = coalesce(lead(time), lubridate::ceiling_date(time, "day")),
    time_diff_hours = as.numeric(next_time - time, units = "hours"),
    # 无评估的日期设置pain_score为NA，时长固定为24小时
    pain_score = ifelse(is.na(time_), NA, pain_score),
    time_diff_hours = ifelse(is.na(time_), 24, time_diff_hours)
  ) |>
  # 整理输出字段，清理临时辅助列
  select(patient_id, time, day = lubridate::day(time), pain_score, time_diff_hours) |>
  ungroup()

代码说明 #

1. 分组隔离：通过group_by(patient_id)避免不同患者的数据互相干扰，保证每个患者的计算逻辑独立
2. 补全日期：用complete生成每个患者从最早到最晚记录的完整日期序列，解决无评估日期缺失的问题
3. 合并原数据：通过left_join将原数据的疼痛评分记录合并到补全的日期行中，保留原时间点的评估信息
4. 评分填充：fill(..., .direction = "downup")实现双向填充，既处理跨日期的疼痛评分延续，也补充缺失日期前的历史评分
5. 时长计算：用coalesce优先取原数据的下一个评估时间，否则用次日0点计算时长；对无评估的日期直接设置固定时长和NA评分
6. 字段整理：清理临时辅助列，保留需求指定的输出字段

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