CODEWITHSUNDEEP
r
Tilo
Tilo

Reputation: 429

Setting New categorical variable values based on Timestamp in R

I have a dataframe with two columns Date_Time and ENTRY. Date_Time has hourly data captured for 2 months and I need to fill Entry column values using logic in R. on below logic -> Entry should set to "FULL" only during 10 AM to 12 AM& 2PM to 5PM -> Entry should set to "EMPTY" excluding hours 10 AM to 12 AM& 2PM to 5PM

Snapshot of expected output

   **Date_Time**    **ENTRY**
    6/6/17 6:00 AM  EMPTY
    6/6/17 7:00 AM  EMPTY
    6/6/17 8:00 AM  EMPTY
    6/7/17 9:00 AM  EMPTY
    6/8/17 10:00 AM FULL
    6/9/17 11:00 AM FULL
    6/9/17 12:00 AM FULL
    6/9/2017 13:00 AM   EMPTY
    6/9/2017 14:00 AM   FULL
    6/9/2017 15:00 AM   FULL
    6/9/2017 16:00 AM   FULL
    6/9/2017 17:00 AM   FULL
    6/9/2017 18:00 AM   EMPTY

Upvotes: 0

Views: 135

Answers (1)

pogibas
pogibas

Reputation: 28369

Solution using data.table (assuming that your table is called d):

library(data.table)
setDT(d)
d[, AMPM := sapply(strsplit(Data_Time, " "), "[[", 3)]
d[, TIME := as.numeric(gsub(":.*", "", sapply(strsplit(Data_Time, " "), "[[", 2)))]
d[, ENTRY := "EMPTY"]
d[(AMPM == "AM" & TIME >= 10 & TIME <= 12) | 
  (AMPM == "PM" & TIME >= 2 & TIME <= 5), 
  ENTRY := "FULL"][, .(Data_Time, ENTRY)]

enter image description here

Upvotes: 1

Related Questions