CODEWITHSUNDEEP
rdateif-statementbinary
Evan
Evan

Reputation: 1499

How to create binary variable if two columns fit within normal working hours?

I have a df:

Date       Time
06/20/20   1740
02/19/20   1600
09/22/20   1530
12/09/20   1326
06/12/19   1119
07/19/19   1703
08/02/20   1805
12/05/19   0932
11/09/20   1134
01/14/20   1634

The times are listed in military time. I would like to create a data frame that has a third variable (working_hours) that is a binary. If the date is a Monday through Friday and the Time is between 0900 and 1700 then I'd like a 1, otherwise 0. I'd also like to make a variable, Morning, for if the time is before or after noon (1200). Any thoughts?

Output:
    Date       Time   Working_Hours   Morning
    06/20/20   1740   0               0
    02/19/20   1600   1               0
    09/22/20   1530   1               0
    12/09/20   1326   1               0
    06/12/19   1119   1               1
    07/19/19   1703   0               0
    08/02/20   1805   0               0
    12/07/19   0932   0               1
    11/09/20   1134   1               1
    01/12/20   1634   0               0

Upvotes: 0

Views: 30

Answers (1)

Ronak Shah
Ronak Shah

Reputation: 389215

You can use -

library(dplyr)

df %>%
  mutate(Date = lubridate::mdy(Date), 
         Working_Hours = as.integer(format(Date, '%u') %in% 1:5 & between(Time, 900, 1700)), 
         Morning = as.integer(Time < 1200))

Or in base R -

df$Date <- as.Date(df$Date, '%m/%d/%y')
transform(df, Working_Hours = as.integer(format(Date, '%u') %in% 1:5 & Time >= 900 & Time <= 1700), 
          Morning = as.integer(Time < 1200))

#         Date Time Working_Hours Morning
#1  2020-06-20 1740             0       0
#2  2020-02-19 1600             1       0
#3  2020-09-22 1530             1       0
#4  2020-12-09 1326             1       0
#5  2019-06-12 1119             1       1
#6  2019-07-19 1703             0       0
#7  2020-08-02 1805             0       0
#8  2019-12-07  932             0       1
#9  2020-11-09 1134             1       1
#10 2020-01-12 1634             0       0

Upvotes: 2

Related Questions