15 minute groups for logons instead of hour

Question

I have some code to take unit log ons and log offs by hour and group them so that I can see how many units are logged in by hour and division.

Code is as follows:

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

Data looks as below:

Data

I would like to get this data into the following format

enter image description here

As you can see, I need it in 15 minute intervals, as well as by day of week, and I want to sum results by specified time intervals.

I am having trouble with this and requesting the communities help

Thank you

as above. It details what I have done. And where I am going

It's easier to help you if you include a simple [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) with sample input and desired output that can be used to test and verify possible solutions. Please [do not post code or data in images](https://meta.stackoverflow.com/q/285551/2372064) — MrFlick, Aug 15 '23 at 16:08
Thanks for the tip. I am new to stack overflow (Just found it today) I am not familiar with how things are usually done. — Tyran Douglas, Aug 15 '23 at 16:28
Welcome to SO, Tyran Douglas! Questions on SO (especially in R) do much better if they are reproducible and self-contained. By that I mean including attempted code (which you've provided, thanks!), sample representative data (perhaps via `dput(head(x))` or building data programmatically (e.g., `data.frame(...)`), possibly stochastically), perhaps actual output (with verbatim errors/warnings) versus intended output. In addition to MrFlick's link, there's also [mcve] and https://stackoverflow.com/tags/r/info. (Those are three of the most common references for helping to fill-out questions.) — r2evans, Aug 15 '23 at 16:49
It is not clear to me how one goes from rows with "On", "Off", and "Workload" to your expected output. The variable distribution of values for each day-of-the-week and quarter-hour suggests either (a) some expectation of distribution by hour/day, or (b) you want to evenly distribute the three fields so that all numbers are relatively uniformly distributed across time, so that you can process the new data as if it were the data from that second source. — r2evans, Aug 15 '23 at 16:53
Units have log in and log off time, and I want to sum by 15 minute interval how many were logged in and logged in, given that there are many units with many different start and end times — Tyran Douglas, Aug 15 '23 at 17:26

15 minute groups for logons instead of hour

0 Answers0