CODEWITHSUNDEEP
rtime
High Low
High Low

Reputation: 67

Creating a column based on a time-range

I have been trying to create a new column based on the time range: punctual(before 12:00:00), late(between 12:00:00 to 15:00:00) and very late(after 15:00:00). I can create a column based on a fixed timing and not range.

Data

                 time worker Day
1 2020-07-21 15:25:00   Ryan Tue
2 2020-07-21 11:20:00    Tim Tue
3 2020-07-21 11:30:00   John Tue
4 2020-07-21 14:00:00   Adam Tue

Desired Output

                 time worker Day   Arrival
1 2020-07-21 15:25:00   Ryan Tue very late
2 2020-07-21 11:20:00    Tim Tue  punctual
3 2020-07-21 11:30:00   John Tue  punctual
4 2020-07-21 14:00:00   Adam Tue      late

error code

df<-df %>% mutate(hour = lubridate::hour(time), minutes = lubridate::minutes(time),Arrival = case_when(hour <- 12 | (hour == 12 & minutes <= 30) ~ 'punctual', 
                                                                                             hour <- 15 | (hour == 15 & minutes <= 30) ~ 'late',
                                                                                             TRUE ~ 'very late'))

Upvotes: 1

Views: 334

Answers (1)

Ronak Shah
Ronak Shah

Reputation: 388982

You can use case_when and specify each condition individually :

library(dplyr)
library(lubridate)

df %>%
  mutate(hour = hour(time),
         Arrival = case_when(hour < 12 ~ 'punctual', 
                             hour < 15 ~ 'late', 
                             TRUE ~ 'very late'))

Or use cut in base R by specifying breaks.

df$Arrival <- cut(as.integer(format(df$time, '%H')), c(0, 11, 14, 23), 
                  c('punctual', 'late', 'very late'))

df
#                 time worker Day    Arrival
#1 2020-07-21 15:25:00   Ryan Tue  very late
#2 2020-07-21 11:20:00    Tim Tue   punctual
#3 2020-07-21 11:30:00   John Tue   punctual
#4 2020-07-21 14:00:00   Adam Tue       late

To extend this for up-to minute level accuracy we can use case_when like :

df %>% 
  mutate(hour = hour(time), 
         minutes = minute(time),
         Arrival = case_when(
                hour < 12 | (hour == 12 & minutes <= 30) ~ 'punctual', 
                hour < 15 | (hour == 15 & minutes <= 30) ~ 'late',
                TRUE ~ 'very late'))

data

Make sure that time column is of class POSIXct in your data.

df <- structure(list(time = structure(c(1595345100, 1595330400, 1595331000, 
1595340000), class = c("POSIXct", "POSIXt"), tzone = "UTC"), 
    worker = c("Ryan", "Tim", "John", "Adam"), Day = c("Tue", 
  "Tue", "Tue", "Tue")), row.names = c("1", "2", "3", "4"), class = "data.frame")

Upvotes: 1

Related Questions