R语言数据清洗：将运动时段DateTime数据按小时汇总格式转换求助

我们可以通过时间区间处理、重叠时长计算和小时级汇总实现需求，以下是具体步骤：

1. 加载必要工具包 #

需要用到lubridate处理时间、dplyr做数据操作、tidyr拆分数据、hms格式化小时：

library(lubridate)
library(dplyr)
library(tidyr)
library(hms)

2. 预处理原始数据，转换时间格式 #

先把字符串类型的开始/结束时间转换为可计算的datetime类型：

data_raw <- structure(list(`Bout Start` = c("2/8/2017 9:01:00 AM", "2/8/2017 9:23:00 AM", "2/8/2017 9:42:00 AM", "2/8/2017 11:49:00 AM", "2/8/2017 1:39:00 PM"), `Bout End` = c("2/8/2017 9:12:00 AM", "2/8/2017 9:38:00 AM", "2/8/2017 9:52:00 AM", "2/8/2017 12:05:00 PM", "2/8/2017 1:58:00 PM"),`Time in Bout` = c(11, 15, 10, 16, 19)), row.names = c(NA, -5L), class = c("tbl_df", "tbl", "data.frame"))

data_clean <- data_raw %>%
  mutate(
    start = mdy_hms(`Bout Start`),
    end = mdy_hms(`Bout End`)
  )

3. 生成每个Bout覆盖的所有小时区间 #

对于跨小时的Bout（比如11:49到12:05），拆分到对应的小时段：

data_hours <- data_clean %>%
  rowwise() %>%
  mutate(
    hour_start = floor_date(start, "hour"),
    hour_end = floor_date(end, "hour"),
    hours = list(seq(hour_start, hour_end, by = "hour"))
  ) %>%
  unnest(hours) %>%
  ungroup()

4. 计算每个小时内的实际活动时长 #

通过时间区间的交集计算每个小时内的活动分钟数：

data_duration <- data_hours %>%
  mutate(
    hour_interval = interval(hours, hours + hours(1)),
    bout_interval = interval(start, end),
    overlap = as.duration(intersect(hour_interval, bout_interval)) / dminutes(1)
  ) %>%
  select(hours, overlap)

5. 汇总小时级数据并补全缺失小时 #

先按小时汇总总时长，再补全天内所有小时（时长为0的小时也保留）：

# 按小时汇总
hourly_summary <- data_duration %>%
  group_by(hours) %>%
  summarise(`Time in Bout (Hourly)` = sum(overlap), .groups = "drop") %>%
  mutate(
    Date = as.Date(hours),
    Hour = as_hms(hours)
  ) %>%
  select(Date, Hour, `Time in Bout (Hourly)`)

# 生成目标日期的完整小时序列（示例为2017-02-08）
target_date <- ymd("2017-02-08")
all_hours <- tibble(
  hours = seq(ymd_h(paste(target_date, 0)), ymd_h(paste(target_date, 23)), by = "hour")
) %>%
  mutate(
    Date = as.Date(hours),
    Hour = as_hms(hours)
  )

# 补全时长为0的小时
final_data <- all_hours %>%
  left_join(hourly_summary, by = c("Date", "Hour")) %>%
  mutate(`Time in Bout (Hourly)` = replace_na(`Time in Bout (Hourly)`, 0)) %>%
  select(Date, Hour, `Time in Bout (Hourly)`)

验证结果 #

查看目标小时段的结果，与需求格式一致：

final_data %>% filter(Hour %in% hms(c("08:00:00", "09:00:00", "10:00:00", "11:00:00", "12:00:00")))

输出：

# A tibble: 5 × 3
  Date       Hour   `Time in Bout (Hourly)`
  <date>     <time>                   <dbl>
1 2017-02-08 08:00:00                    0
2 2017-02-08 09:00:00                   36
3 2017-02-08 10:00:00                    0
4 2017-02-08 11:00:00                   11
5 2017-02-08 12:00:00                    5

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