Is there a function to creating a floor time or ceiling time for a character "time" vector formatted 00:00:00

Question

I am working with a tibble containing call center data, and essentially what I am trying to determine is how many calls come in during a range of hours in 15 minute increments starting from 7:00 am to 7:00 pm. My data set looks like something below.

Eventually I know I can do a group by and summary, but how do I put the Contact Time into a floor or ceiling number?

Example: Anything between 08:00:00 and 08:10:00 would be 08:00:00, Anything between 08:10:01 and 08:20:00 would be 08:15:00, and Anything between 08:20:01 and 08:30:00 would be 08:30:00, so on and so fourth.

tbl <- tibble(ContactDate = "2019-11-01", ContactTime = "08:11:41", Month = "Nov", Day = "Friday", ConnectTimeSecs = "216", QueueTimeSecs = "31", TalkTimeSecs = "110")

Actually a cool question. :) – Georgery Feb 20 '20 at 16:04 — Georgery, Feb 20 '20 at 16:04

score 1 · Answer 1 · answered Feb 20 '20 at 16:01

You probably want this:

library(lubridate)
library(dplyr)

tbl %>%
    mutate(contactDateTime = ymd_hms(paste(ContactDate, ContactTime))) %>%
    mutate(contactDateTime = floor_date(contactDateTime, unit = "15minutes"))

Output:

  ContactDate ContactTime Month Day    ConnectTimeSecs QueueTimeSecs TalkTimeSecs contactDateTime    
  <chr>       <chr>       <chr> <chr>  <chr>           <chr>         <chr>        <dttm>             
1 2019-11-01  08:11:41    Nov   Friday 216             31            110          2019-11-01 08:00:00

Well, and if you then want to aggregate over the 15-minute intervals, add this:

tbl %>%
    mutate(contactDateTime = ymd_hms(paste(ContactDate, ContactTime))) %>%
    mutate(contactDateTime = floor_date(contactDateTime, unit = "15minutes")) %>%
    group_by(contactDateTime) %>%
    summarise(numberOfCalls = n())

and you get this:

  contactDateTime     numberOfCalls
  <dttm>                      <int>
1 2019-11-01 08:00:00             1

Is there a function to creating a floor time or ceiling time for a character "time" vector formatted 00:00:00

1 Answers1