如何在Shiny工时追踪应用中填充时间戳间隙为Other活动

核心逻辑 #

按日期拆分数据，提取每组内活动间的时间空白段，将这些空白段转为名为Other的活动，最后把原数据和补全的间隙数据合并排序，保证单日从首个活动开始到最后一个活动结束的时间完全连续。

代码实现 #

假设从SQLite读取后的数据框为time_tracks，包含字段：activity（活动名称）、start_time（开始时间戳）、end_time（结束时间戳），均为POSIXct类型。

library(tidyverse)
library(lubridate)

# 从SQLite读取数据（示例代码）
# time_tracks <- DBI::dbReadTable(你的数据库连接, "表名") %>%
#   mutate(across(c(start_time, end_time), ymd_hms))

# 时间间隙补全逻辑
filled_tracks <- time_tracks %>%
  # 按日期分组处理
  group_by(date = as_date(start_time)) %>%
  # 标记每组内的时间间隙
  mutate(
    next_activity_start = lead(start_time),
    gap_start = end_time,
    gap_end = next_activity_start,
    # 筛选出真实存在的间隙
    valid_gap = !is.na(next_activity_start) & gap_start < gap_end
  ) %>%
  # 提取间隙数据，转为Other活动
  filter(valid_gap) %>%
  transmute(
    activity = "Other",
    start_time = gap_start,
    end_time = gap_end,
    date = date
  ) %>%
  # 和原数据合并
  bind_rows(time_tracks %>% mutate(date = as_date(start_time))) %>%
  # 按日期+开始时间重新排序
  arrange(date, start_time) %>%
  # 清理辅助字段
  select(-date) %>%
  ungroup()

关键细节 #

• 矢量化适配：全程用dplyr窗口函数lead()实现，无循环操作，能高效处理大数据集。
• 日期边界控制：仅补全单日内部的间隙，不会跨日期生成Other活动（比如当日最后一个活动结束到次日首个活动开始的时间段不处理）。
• 无效间隙过滤：自动跳过无空白的连续活动、当日最后一个活动等场景，避免生成无效数据行。

验证方法 #

执行以下代码检查时间连续性，若返回空数据框则说明补全成功：

filled_tracks %>%
  mutate(next_start = lead(start_time)) %>%
  filter(!is.na(next_start) & end_time != next_start)

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