CODEWITHSUNDEEP
r
Katie Kim
Katie Kim

Reputation: 3

How to count as one day from 5 pm to the next day 2 am in R?

I'm currently checking the daily progress of surveys participants to see if people have completed daily surveys. People receive our survey every day at 5 pm, and the survey disappears at 2 am the next day. so for example if a person receives a survey on 12/1/2019 and submit it at 1 am on 12/2/2019, the submission date would be 12/1/2019. However, currently the survey program calculates people's submission date based on their submission time. I would like to change this date and time range using R programming.

I have four columns in my data; (ID, Day, Date, Time).

I have four columns in my data; (ID, Day, Date, Time)

Thanks in advance for your help!

Here is sample data as csv:

ID,Day,Date,Time
DNP005,1,12/13/2019,01:31:47
DNP005,2,12/14/2019,21:48:33
DNP005,3,12/15/2019,00:10:02
DNP005,4,12/16/2019,00:41:44
DNP005,5,12/17/2019,22:34:10
DNP005,6,12/18/2019,17:54:06
DNP005,7,12/19/2019,00:35:17

and the output from dput:

structure(list(ID = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = "DNP005", class = "factor"),                                                                                                                  
    Day = 1:7, Date = structure(1:7, .Label = c("12/13/2019",                                                                                                                                                       
    "12/14/2019", "12/15/2019", "12/16/2019", "12/17/2019", "12/18/2019",                                                                                                                                           
    "12/19/2019"), class = "factor"), Time = structure(c(4L,                                                                                                                                                        
    6L, 1L, 3L, 7L, 5L, 2L), .Label = c("00:10:02", "00:35:17",                                                                                                                                                     
    "00:41:44", "01:31:47", "17:54:06", "21:48:33", "22:34:10"                                                                                                                                                      
    ), class = "factor")), class = "data.frame", row.names = c(NA,                                                                                                                                                  
-7L))

Upvotes: 0

Views: 75

Answers (2)

Kyle Chesney
Kyle Chesney

Reputation: 148

Using tidyverse and lubridate:

library(tidyverse)
library(lubridate)

# Create test data
df <- data.frame(
    date_completed = c("12/1/2019 17:55:00", "12/2/2019 01:55:00")
    )


 df <- df %>%
    mutate(survey_issued = case_when(
                mdy_hms(date_completed) %>% hour() < 17 ~ (mdy_hms(date_completed) - days(1)) %>% as_date(),
                TRUE ~ mdy_hms(date_completed) %>% as_date()
            )
    )

Note that this will create a new date object, if you need to retain a character type, you can reconvert:


 df <- df %>%
    mutate(survey_issued = case_when(
                mdy_hms(date_completed) %>% hour() < 17 ~ format((mdy_hms(date_completed) - days(1)) %>% as_date(), '%m/%d/%Y'),
                TRUE ~ format(mdy_hms(date_completed) %>% as_date(), '%m/%d/%Y')
            )
    )

Edit: just noticed from the alt-text in your image that Date and time are separate columns, here are updated snippets:

Creating a new column with date type.

df <- df %>%
    mutate(survey_issued = case_when(
                hms(Time) %>% hour() < 17 ~ (mdy(Date) - days(1)),
                TRUE ~ mdy(Date) %>% as_date()
                )
          )

Creating a new column and converting date type to character.

df <- df %>%
    mutate(survey_issued = case_when(
                hms(Time) %>% hour() < 17 ~ (mdy(Date) - days(1)) %>% strftime('%m/%d/%Y') %>% as.character(),
                TRUE ~ Date %>% as.character()
                )
          )

Upvotes: 0

r2evans
r2evans

Reputation: 160407

Sample data:

submissions <- as.POSIXct("2019-12-21 00:01:00", tz="UTC") + 3600*(0:24)
submissions
#  [1] "2019-12-21 00:01:00 UTC" "2019-12-21 01:01:00 UTC" "2019-12-21 02:01:00 UTC"
#  [4] "2019-12-21 03:01:00 UTC" "2019-12-21 04:01:00 UTC" "2019-12-21 05:01:00 UTC"
#  [7] "2019-12-21 06:01:00 UTC" "2019-12-21 07:01:00 UTC" "2019-12-21 08:01:00 UTC"
# [10] "2019-12-21 09:01:00 UTC" "2019-12-21 10:01:00 UTC" "2019-12-21 11:01:00 UTC"
# [13] "2019-12-21 12:01:00 UTC" "2019-12-21 13:01:00 UTC" "2019-12-21 14:01:00 UTC"
# [16] "2019-12-21 15:01:00 UTC" "2019-12-21 16:01:00 UTC" "2019-12-21 17:01:00 UTC"
# [19] "2019-12-21 18:01:00 UTC" "2019-12-21 19:01:00 UTC" "2019-12-21 20:01:00 UTC"
# [22] "2019-12-21 21:01:00 UTC" "2019-12-21 22:01:00 UTC" "2019-12-21 23:01:00 UTC"
# [25] "2019-12-22 00:01:00 UTC"

Solution:

data.frame(
  submission = submissions,
  submitted = as.Date(submissions),
  survey = as.Date(submissions) - (as.integer(format(submissions, format="%H")) < 2)
)
#             submission  submitted     survey
# 1  2019-12-21 00:01:00 2019-12-21 2019-12-20
# 2  2019-12-21 01:01:00 2019-12-21 2019-12-20
# 3  2019-12-21 02:01:00 2019-12-21 2019-12-21
# 4  2019-12-21 03:01:00 2019-12-21 2019-12-21
# 5  2019-12-21 04:01:00 2019-12-21 2019-12-21
# 6  2019-12-21 05:01:00 2019-12-21 2019-12-21
# 7  2019-12-21 06:01:00 2019-12-21 2019-12-21
# 8  2019-12-21 07:01:00 2019-12-21 2019-12-21
# 9  2019-12-21 08:01:00 2019-12-21 2019-12-21
# 10 2019-12-21 09:01:00 2019-12-21 2019-12-21
# 11 2019-12-21 10:01:00 2019-12-21 2019-12-21
# 12 2019-12-21 11:01:00 2019-12-21 2019-12-21
# 13 2019-12-21 12:01:00 2019-12-21 2019-12-21
# 14 2019-12-21 13:01:00 2019-12-21 2019-12-21
# 15 2019-12-21 14:01:00 2019-12-21 2019-12-21
# 16 2019-12-21 15:01:00 2019-12-21 2019-12-21
# 17 2019-12-21 16:01:00 2019-12-21 2019-12-21
# 18 2019-12-21 17:01:00 2019-12-21 2019-12-21
# 19 2019-12-21 18:01:00 2019-12-21 2019-12-21
# 20 2019-12-21 19:01:00 2019-12-21 2019-12-21
# 21 2019-12-21 20:01:00 2019-12-21 2019-12-21
# 22 2019-12-21 21:01:00 2019-12-21 2019-12-21
# 23 2019-12-21 22:01:00 2019-12-21 2019-12-21
# 24 2019-12-21 23:01:00 2019-12-21 2019-12-21
# 25 2019-12-22 00:01:00 2019-12-22 2019-12-21

Shown as a frame solely to compare the wrong and right dates.

Upvotes: 1

Related Questions