How do I split my time data into intervals in R?

user_4112751 0
Viewed 317

I have some data which looks like this:

time                 author             text               day     times     timeblock  dayblock
2019-08-02 12:16:40|"ab5c9c0a"|"This message was deleted"  |2|   "12:16:40"| "Cycle 1"|  "No"
2019-08-02 12:36:40|"ab5c9c0a"|"Please take a survey"      |2|   "12:36:40"| "Cycle 1"|  "No"
2019-08-02 13:29:40|"43cd8b94"|"Done :D"                   |2|   "13:29:40"| "Cycle 1"|  "No"
2019-08-02 17:41:40|"083fa508"|"<Media omitted>"           |2|   "17:41:40"| "Cycle 1"|  "No"

str(chat)

Classes ‘data.table’ and 'data.frame':  16111 obs. of  7 variables:
 $ time     : POSIXct, format: "2019-08-02 12:16:40" "2019-08-02 12:35:40" "2019-08-02 12:36:40" ...
 $ author   : chr  "ab5c9c0a" "ab5c9c0a" "ab5c9c0a" "43cd8b94" ...
 $ text     : chr  "This message was deleted" "https://docs.google.com/forms/d/e/1FAIpQLSf4hE" "Please take a survey" "Done :D" ...
 $ day      : int  2 2 2 2 2 3 3 3 3 3 ...
 $ times    : chr  "12:16:40" "12:35:40" "12:36:40" "13:29:40" ...
 $ timeblock: Factor w/ 13 levels "Cycle 1","Cycle 2",..:

I wrote this for categorizing the time into 7 am, 10 pm and so on:

chat <- chat %>% 
mutate(
 # Time Segements
 dayblock = case_when(
 time >= hms(070000) & time <= hms(080000) ~ "7 AM",
 time >= hms(080000) & time <= hms(090000) ~ "8 AM",
 time >= hms(090000) & time <= hms(100000) ~ "9 AM",
 time >= hms(100000) & time <= hms(110000) ~ "10 AM",
 time >= hms(110000) & time <= hms(120000) ~ "11 AM",
 time >= hms(120000) & time <= hms(130000) ~ "12 PM",
 time >= hms(130000) & time <= hms(140000) ~ "1 PM",
 time >= hms(140000) & time <= hms(150000) ~ "2 PM",
 time >= hms(150000) & time <= hms(160000) ~ "3 PM",
 time >= hms(160000) & time <= hms(170000) ~ "4 PM",
 time >= hms(170000) & time <= hms(180000) ~ "5 PM",
 time >= hms(180000) & time <= hms(190000) ~ "6 PM",
 time >= hms(190000) & time <= hms(200000) ~ "7 PM",
 time >= hms(200000) & time <= hms(210000) ~ "8 PM",
 time >= hms(210000) & time <= hms(220000) ~ "9 PM",
 time >= hms(220000) & time <= hms(230000) ~ "10 PM",
 time >= hms(230000) & time <= hms(000000) ~ "11 PM",
 time >= hms(000000) & time <= hms(010000) ~ "12 AM",
 time >= hms(010000) & time <= hms(020000) ~ "1 AM",
 time >= hms(020000) & time <= hms(030000) ~ "2 AM",
 time >= hms(030000) & time <= hms(040000) ~ "3 AM",
 time >= hms(040000) & time <= hms(050000) ~ "4 AM",
 time >= hms(050000) & time <= hms(060000) ~ "5 AM",
 time >= hms(060000) & time <= hms(070000) ~ "6 AM",
 T ~ "No")) %>% 
  mutate(dayblock = factor(dayblock))

The expected output is:

time                 author             text               day     times     timeblock  dayblock
2019-08-02 12:16:40|"ab5c9c0a"|"This message was deleted"  |2|   "12:16:40"| "Cycle 1"|  12 PM
2019-08-02 12:36:40|"ab5c9c0a"|"Please take a survey"      |2|   "12:36:40"| "Cycle 1"|  12 PM
2019-08-02 13:29:40|"43cd8b94"|"Done :D"                   |2|   "13:29:40"| "Cycle 1"|  1 PM
2019-08-02 17:41:40|"083fa508"|"<Media omitted>"           |2|   "17:41:40"| "Cycle 1"|  5 PM

But when I run this, all the rows are filled only with the No value. What am I doing wrong?

The current error is:

Problem with `mutate()` input `dayblock`.
i Some strings failed to parse, or all strings are NAs
i Input `dayblock` is `case_when(...)`.Some strings failed to parse, or all strings are NAsProblem with `mutate()` input `dayblock`.

EDIT: While the accepted answer tackles this issue, @Istrel's answer is a far more elegant solution and I would recommend users to try that.

lubridate r time
3 Answers

It seems like you can achieve the same with a format function.

library(tidyverse)
library(lubridate)

chat <- tibble(time = ymd_hms(c("2019-08-02 12:16:40", "2019-08-02 12:36:40", 
    "2019-08-02 13:29:40", "2019-08-02 3:29:40")))

chat <- chat %>%
  mutate(dayblock =  format(time, "%I %p"))

#   time                dayblock
#   <dttm>              <chr>   
# 1 2019-08-02 12:16:40 12 PM   
# 2 2019-08-02 12:36:40 12 PM   
# 3 2019-08-02 13:29:40 01 PM   
# 4 2019-08-02 03:29:40 03 AM   
# 5 2019-08-02 02:01:40 02 AM
user_57623680

The option is change the format that is going into hms

library(dplyr)
library(lubridate)
chat %>% 
  mutate(times = hms(times),       
   dayblock = factor(case_when(
     times >= hms('07:00:00') & times <= hms('08:00:00') ~ "7 AM",
     times >= hms('08:00:00') & times <= hms('09:00:00') ~ "8 AM",
     times >= hms('12:00:00') & times <= hms('13:00:00') ~ "12 PM",
      TRUE ~ "No"))
   )

-output

#            time   author                     text day       times timeblock dayblock
#1 2019-08-02 12:16:40 ab5c9c0a This message was deleted   2 12H 16M 40S   Cycle 1    12 PM
#2 2019-08-02 12:36:40 ab5c9c0a     Please take a survey   2 12H 36M 40S   Cycle 1    12 PM
#3 2019-08-02 13:29:40 43cd8b94                  Done :D   2 13H 29M 40S   Cycle 1       No
#4 2019-08-02 17:41:40 083fa508          <Media omitted>   2 17H 41M 40S   Cycle 1       No

data

chat <- structure(list(time = c("2019-08-02 12:16:40", "2019-08-02 12:36:40", 
"2019-08-02 13:29:40", "2019-08-02 17:41:40"), author = c("ab5c9c0a", 
"ab5c9c0a", "43cd8b94", "083fa508"), text = c("This message was deleted", 
"Please take a survey", "Done :D", "<Media omitted>"), day = c(2L, 
2L, 2L, 2L), times = c("12:16:40", "12:36:40", "13:29:40", "17:41:40"
), timeblock = c("Cycle 1", "Cycle 1", "Cycle 1", "Cycle 1"), 
    dayblock = c("No", "No", "No", "No")), class = "data.frame", 
 row.names = c(NA, 
-4L))
user_37322710

We could also use strftime from R base

chat <- tibble(time = c("2019-08-02 12:16:40", "2019-08-02 12:36:40", 
                                "2019-08-02 13:29:40", "2019-08-02 17:41:40"))
chat$dayblock <-  strftime(chat$time, "%I %p")

 time                dayblock
  <chr>               <chr>   
1 2019-08-02 12:16:40 12 PM   
2 2019-08-02 12:36:40 12 PM   
3 2019-08-02 13:29:40 01 PM   
4 2019-08-02 17:41:40 05 PM
user_40899490
Related