请求将单位登录统计从小时级改为15分钟级（附R代码）

我现有一段R代码，用于按小时对单位的登录、登出数据进行分组统计，以此查看各部门每小时的在线单位数量。代码如下：

rm(list = ls())
library(dplyr)
library(lubridate)
library(openxlsx)
library(reshape2)
library(readxl)
library(tidyr)
library("tidyverse")
setwd("X:/_IPD Workload/Workload/02-Projects/01. Active Projects/P102 - MPP/03 Execute/Data")

start_date<-as.Date("2022-02-01")   #Setting up dates to filter by later
end_date<-as.Date("2023-02-01")

df<-read_csv("Unit_Logon All Units.csv")  #Read file

df2<- filter(df, Workload_Minutes >= 5) # Filter where log in time is greater than 5 minutes

df3<-df2[,-c(3:6,9)] #Get rid of unnecessary columns

names(df3)[names(df3) == "Unit_ID"]<-"ID"
names(df3)[names(df3) == "Log_On_Date_Time"]<-"Start"
names(df3)[names(df3) == "Log_Off_Date_Time"]<-"End"
names(df3)[names(df3) == "Unit_Dispatch_Group"]<-"Division"

df3$Start <- as.POSIXct(df3$Start, format = "%m/%d/%Y %H:%M")
df3$End <- as.POSIXct(df3$End, format = "%m/%d/%Y %H:%M")

df4<-df3 %>%
  mutate(Start_hour=as.POSIXct(trunc(Start, units="hours")), #truncate start time to hour
         End_hour=as.POSIXct(trunc(End, units="hours")), #truncate end time to hour
         End_hour=case_when(End-End_hour>5~End_hour, #5-min rule
                            T~End_hour-3600)) %>%
  rowwise() %>%
  do(data.frame(Division=.$Division, ID=.$ID, time=seq(.$Start_hour, .$End_hour, by="1 hour"))) %>%  #get rolling sequence
  group_by(Division, time) %>%
  summarise(n=n_distinct(ID)) #count distinct ID

df5 <- df4 %>% filter(`time` >= start_date & 
                 `time` < end_date)         #Date filter as per dates above

write.xlsx(df5, "Unitsbyhour.xlsx") #Write file

当前数据包含单位ID、登录/登出时间、部门等字段，我希望将数据转换为15分钟时间间隔的统计格式，同时按星期几分组，并对指定时间区间的结果进行汇总，最终输出宽表：行是星期几，列是15分钟时间区间（如00:00、00:15等），单元格为对应区间的在线单位数量。

rm(list = ls())
library(dplyr)
library(lubridate)
library(openxlsx)
library(tidyr)
library(tidyverse)

setwd("X:/_IPD Workload/Workload/02-Projects/01. Active Projects/P102 - MPP/03 Execute/Data")

# 定义时间范围
start_date <- as.Date("2022-02-01")
end_date <- as.Date("2023-02-01")

# 读取并预处理数据
df <- read_csv("Unit_Logon All Units.csv") %>%
  filter(Workload_Minutes >= 5) %>%  # 过滤登录时长≥5分钟的记录
  select(-c(3:6,9)) %>%  # 删除不必要的列
  rename(
    ID = Unit_ID,
    Start = Log_On_Date_Time,
    End = Log_Off_Date_Time,
    Division = Unit_Dispatch_Group
  ) %>%
  mutate(
    # 转换时间格式
    Start = as.POSIXct(Start, format = "%m/%d/%Y %H:%M"),
    End = as.POSIXct(End, format = "%m/%d/%Y %H:%M"),
    # 截断到最近的15分钟起始点
    Start_15min = floor_date(Start, unit = "15 minutes"),
    # 处理结束时间的15分钟规则：如果结束时间距离所在15分钟区间结束不足5分钟，则归到上一个区间
    End_15min = ifelse(
      minute(End) %% 15 < 5,
      floor_date(End - minutes(5), unit = "15 minutes"),
      floor_date(End, unit = "15 minutes")
    ) %>% as.POSIXct()
  )

# 生成每个15分钟区间的记录，并统计在线单位数
df_interval <- df %>%
  rowwise() %>%
  # 生成从Start_15min到End_15min的15分钟序列
  do(data.frame(
    Division = .$Division,
    ID = .$ID,
    interval_time = seq(.$Start_15min, .$End_15min, by = "15 mins")
  )) %>%
  ungroup() %>%
  # 筛选指定时间范围内的记录
  filter(interval_time >= start_date & interval_time < end_date) %>%
  # 添加星期几列（中文或英文可自行调整，这里用英文缩写）
  mutate(
    weekday = wday(interval_time, label = TRUE, abbr = TRUE),
    # 提取时间部分作为列名（如"00:00", "00:15"）
    time_slot = format(interval_time, "%H:%M")
  ) %>%
  # 按部门、星期、时间分组，统计去重后的单位数
  group_by(Division, weekday, time_slot) %>%
  summarise(online_units = n_distinct(ID), .groups = "drop")

# 转换为目标宽格式（星期为行，时间区间为列）
df_wide <- df_interval %>%
  pivot_wider(
    names_from = time_slot,
    values_from = online_units,
    values_fill = 0  # 空值填充为0
  ) %>%
  # 按星期排序
  arrange(match(weekday, c("Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat")))

# 按部门拆分并写入Excel（每个部门一个工作表）
write.xlsx(df_wide, "Units_by_15min_weekday.xlsx", sheetName = unique(df_wide$Division))

关键修改说明 #

• 15分钟间隔处理：改用floor_date()函数将登录/登出时间截断到15分钟区间，替换原有的小时截断逻辑；同时调整了结束时间的判定规则，适配15分钟间隔的5分钟阈值。
• 星期分组：通过wday()函数提取星期标签，若需中文显示，可修改为wday(interval_time, label = TRUE, abbr = TRUE, locale = "zh_CN.UTF-8")（需系统支持中文locale）。
• 宽格式转换：使用pivot_wider()将长表转换为目标宽表结构，空值填充为0，符合需求的展示格式。
• 部门拆分输出：写入Excel时自动按部门生成独立工作表，方便分部门查看数据。

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