Date Time Bins - Adding Minutes

[MW19]

Hi,

I have a R script which creates Date & Hour Time bins to show how many Colleagues are working in any given hour, and also the first Colleague that arrives during that Hour Period.
What I now want to do, is drill down to minute variance.

Example below with values in ()
I need it such as: DateTime (2019-11-11 00:01), Number of Colleagues (2), First Colleague (45).

TimeSheet <- readxl::read_excel("C:/Users/Matt/Colleagues.xlsx")

TimeSheet <- TimeSheet %>% mutate(`Start Time (UTC)` = lubridate::ymd_hms(`Start Time (UTC)`, tz = "UTC"),
                                  `Actual End Time (UTC)` = lubridate::ymd_hms(`Actual End Time (UTC)`, tz = "UTC"))

Seq1 <- seq.POSIXt(as.POSIXct("2018-11-12 00:00:00", tz = "UTC"), 
                   as.POSIXct("2019-12-31 00:00:00", tz = "UTC"), "hour")

DF <- data.frame(DateTime = c(Seq1))


DF2 <- DF %>% group_by(DateTime) %>% 
  mutate(Colleague = sum(DateTime >= TimeSheet$`Start Time (UTC)` & DateTime < TimeSheet$`Actual End Time (UTC)`))

HourColleague <- TimeSheet %>% mutate(ID = 1:nrow(TimeSheet), 
                                      
                                      HourStart = hour(ceiling_date(`Start Time (UTC)`, unit = "hour")),
                                      
                                      HourEnd = hour(floor_date(`Actual End Time (UTC)` - 60, unit = "hour"))) %>%
  filter(HourStart <= HourEnd) %>% 
  
  group_by(ID) %>% 
  
  mutate(HourSeq = list(tidyr::full_seq(c(HourStart, HourEnd), 1))) %>% 
  
  tidyr::unnest(cols = HourSeq) %>% 
  
  mutate(DateTime_bin = make_datetime(year = year(`Start Time (UTC)`),
                                      
                                      month = month(`Start Time (UTC)`),
                                      
                                      day = day(`Start Time (UTC)`),
                                      
                                      hour = HourSeq)) %>%
  
  ungroup()  

HourColleague <- select(HourColleague, DateTime = DateTime_bin, ColleagueNumber)



HourColleague

DF3 <- left_join(DF2, HourColleague)

Thanks for help,

[mara]

Please note that in the answer @FJCC gave you for your previous problem, they created dummy data so it was in the form of a reprex, which makes it much easier for others to help you:

Calc Start and End Times

I think this gets to the goal, though there is probably a more elegant method. It assumes there is only one person present at a time and the person whose shift ends exactly on the hour is not counted as present for that last instant, it is the person whose shifts begins on the hour who is listed. library(dplyr) TimeSheet <- tibble::tribble( ~Colleague.Number, ~Start.Time, ~Actual.End.Time, 45, "17/11/2019 08:40", "17/11/2019 09:27", 42, "17/11/2019 08:15", "17/11/2019 08:40", 32, "17/11/2019 07:20", "17/11/2019 08:15", 35, "17/11/2019 05:00", "17/11/2019 07:20", 42, "14/11/2019 05:40", "14/11/2019 07:16", 42, "12/11/2019 12:20", "12/11/2019 12:54", 40, "12/11/2019 10:00", "…

I'll point you to the relevant part of the code, which uses the hour() function from lubridate, and then, given that lubridate has a comparable minute() function, try adapting it from there:

library(lubridate)

HourColleague <- TimeSheet %>% mutate(ID = 1:nrow(TimeSheet), 
                     HourStart = hour(ceiling_date(Start.Time, unit = "hour")),
                     HourEnd = hour(floor_date(Actual.End.Time - 60, unit = "hour"))) %>%
  filter(HourStart <= HourEnd) %>% 
  group_by(ID) %>% 
  mutate(HourSeq = list(tidyr::full_seq(c(HourStart, HourEnd), 1))) %>% 
  tidyr::unnest(cols = HourSeq) %>% 
  mutate(DateTime_bin = make_datetime(year = year(Start.Time),
                                      month = month(Start.Time),
                                      day = day(Start.Time),
                                      hour = HourSeq)) %>%
  ungroup()