Search code examples
rdatetimelubridate

Convert character vector of times into proper format and bin in R


So I have a vector which looks as follows:

TIME
7:16:00
16:00:00
17:35:00
16:10:00
5:25:00
18:00:00

These are read into R as character strings. My goal is to bin them into 4 groups

GROUPS

  • Bin 1: 12:01am - 6:00am
  • Bin 2: 6:01am - 12:00pm
  • Bin 3: 12:01pm - 6:00pm
  • Bin 4: 6:01pm - 12:00am

How exactly do I go about doing this? Preference is to use lubridate

Final Output:

TIME        Bin
7:16:00     Bin 2: 6:01am  - 12:00pm
16:00:00    Bin 3: 12:01pm - 6:00pm
17:35:00    Bin 3: 12:01pm - 6:00pm
16:10:00    Bin 3: 12:01pm - 6:00pm
5:25:00     Bin 1: 12:01am - 6:00am
18:00:00    Bin 3: 12:01pm - 6:00pm

Solution

  • here is one possibility using lubridate and tidyverse

    library(lubridate)
    library(tidyverse)
    
    df <- data.frame(TIME = 
                 c("7:16:00", "16:00:00",
                   "17:35:00", "16:10:00",
                   "5:25:00", "18:00:00")
    )
    
    df %>%
      mutate(Bin = paste("Bin ", ceiling((hour(hms(TIME) + minute(hms(TIME)) / 60) / 6)))
    
          TIME    Bin
    1  7:16:00 Bin  2
    2 16:00:00 Bin  3
    3 17:35:00 Bin  3
    4 16:10:00 Bin  3
    5  5:25:00 Bin  1
    6 18:00:00 Bin  3
    

    Here is a base R alternative:

    df$h <- as.numeric(sub("(\\d+):.*", "\\1", df$TIME))
    df$m <- as.numeric(sub(".*:(\\d+):.*", "\\1", df$TIME)) 
    df$Bin <- paste("Bin", ceiling((df$h + df$m / 60) / 6))