CODEWITHSUNDEEP
rdplyr
RL_Pug
RL_Pug

Reputation: 867

How to use mutate() with a Date_Time month/Day/Year 00:00 in R

I'm having trouble with the mutate and doing a casewhen.

This is how my data looks this Date and Time are in one column

       Date_Time 
1   1/23/2020 20:41
2   1/23/2020 10:54
3   1/23/2020 23:55
4   1/23/2020 20:34
5   1/23/2020 20:23
6   1/23/2020 10:26
7   1/23/2020 8:43
8   1/23/2020 7:57
9   1/24/2020 0:32
10  1/24/2020 0:40
11  1/23/2020 20:19
12  1/23/2020 20:53
13  1/24/2020 0:46

I want to create a column that classifies whether the date and time fall within a certain time of the day

I tried 

y <- x %>% mutate(Period = case_when(Date_Time >= '1/23/2020 00:01' & Date_Time <= '1/23/2020 17:00' ~ 'A',
                                                                     Date_Time >= '1/23/2020 17:01' & Date_Time <= '1/23/2020 21:59' ~ 'B',
                                                                     Date_Time >= '1/23/2020 22:00' ~ 'C'
))

But seomthing is wrong as some columns that should fall within B are being recorded as C.

What is the correct logic for this?

What I want is 

If Date_Time falls within 1/23/2020 00:01 & 1/23/2020 17:00 then its A
If Date_Time falls within 1/23/2020 17:01 & 1/23/2020 21:59 then its B
If Date_Time falls after 1/23/2020 22:00 then its C

Upvotes: 1

Views: 638

Answers (1)

Rui Barradas
Rui Barradas

Reputation: 76402

You need to coerce to class "POSIXct" first, then mutate.

library(dplyr)

x$Date_Time <- as.POSIXct(x$Date_Time, format = "%m/%d/%Y %H:%M")

dt1 <- as.POSIXct('1/23/2020 00:01', format = '%m/%d/%Y %H:%M')
dt2 <- as.POSIXct('1/23/2020 17:00', format = '%m/%d/%Y %H:%M')
dt3 <- as.POSIXct('1/23/2020 17:01', format = '%m/%d/%Y %H:%M')
dt4 <- as.POSIXct('1/23/2020 21:59', format = '%m/%d/%Y %H:%M')
dt5 <- as.POSIXct('1/23/2020 22:00', format = '%m/%d/%Y %H:%M')

y <- x %>% 
  mutate(Period = case_when(Date_Time >= dt1 & Date_Time <= dt2 ~ 'A',
                            Date_Time >= dt3 & Date_Time <= dt4 ~ 'B',
                            Date_Time >= dt5 ~ 'C',
                            TRUE ~ NA_character_
  ))

y
#             Date_Time Period
#1  2020-01-23 20:41:00      B
#2  2020-01-23 10:54:00      A
#3  2020-01-23 23:55:00      C
#4  2020-01-23 20:34:00      B
#5  2020-01-23 20:23:00      B
#6  2020-01-23 10:26:00      A
#7  2020-01-23 08:43:00      A
#8  2020-01-23 07:57:00      A
#9  2020-01-24 00:32:00      C
#10 2020-01-24 00:40:00      C
#11 2020-01-23 20:19:00      B
#12 2020-01-23 20:53:00      B
#13 2020-01-24 00:46:00      C

Upvotes: 1

Related Questions