R - Conditional operations on time variables and dates

Question

Assumed, I have the dataframe below and want to do some operations on the last variable HMS. The aim is to create a new variable, lets say TimeStampNew and assign the next day if the observation is after 17:30:00 of the day. When the observation indicates no value ('NA'), then the TimeStamp of the same date should be used.

The problem is, to convert the HMS-variable from the class character in a format allowing for conditions. I already checked the chron-package or converting it to a object of type POSIXct as possible alternatives.

A similar question I already asked in this post: R - Condition on character variable "weekday" and replace with special "date" in a data frame . I already tried to solve the issue with some operations of the dplyr package as suggested in the post mentioned above. However, I didn't receive the desired solution due to the tricky time format.

Has anybody an solution proposal to this question?!

Thanks in advance!

    TimeStamp      HMS
1  2010-02-22 19:55:00
2  2012-10-10 07:53:00
3  2012-10-24 07:55:00
4  2013-07-15 08:14:00
5  2013-07-24 08:23:00
6  2013-12-02 10:00:00
7  2012-02-23 07:56:00
8  2012-03-06 08:45:00
9  2013-09-15 19:54:00
10 2007-03-28 NA      
11 2007-08-09 NA      
12 2008-08-07 NA

Answer 1

Using difftime and an ifelse statement:

df1$TimeStamp <- as.Date(df1$TimeStamp, format = "%Y-%m-%d")

df1$TimeStampNew <- ifelse(
  difftime(as.POSIXct(df1$HMS, format = "%H:%M:%S"), 
  as.POSIXct("17:30:00", format = "%H:%M:%S")) > 0,
  as.character(df1$TimeStamp + 1), 
  as.character(df1$TimeStamp))

df1$TimeStampNew[is.na(df1$HMS)] <- as.character(df1$TimeStamp[is.na(df1$HMS)])

The dataframe it produces:

> df1
    TimeStamp      HMS TimeStampNew
1  2010-02-22 19:55:00   2010-02-23
2  2012-10-10 07:53:00   2012-10-10
3  2012-10-24 07:55:00   2012-10-24
4  2013-07-15 08:14:00   2013-07-15
5  2013-07-24 08:23:00   2013-07-24
6  2013-12-02 10:00:00   2013-12-02
7  2012-02-23 07:56:00   2012-02-23
8  2012-03-06 08:45:00   2012-03-06
9  2013-09-15 19:54:00   2013-09-16
10 2007-03-28     <NA>   2007-03-28
11 2007-08-09     <NA>   2007-08-09
12 2008-08-07     <NA>   2008-08-07

Answer 2

Assume DF as shown reproducibly in the Note at the end. In particular Timestamp and HMS are both assumed to be character columns. Timestamp could alternately be a "Date" class column.

The code below replaces NA values in HMS column with "00:00:00" and adds 1 day to the TimeStamp (after converting it to "Date" class) if HMS is past 17:30.

transform(DF, Date = 
  as.Date(TimeStamp) + (replace(HMS, is.na(HMS), "00:00:00") > "17:30:00"))

giving this data frame where the new Date column is of class "Date".

    TimeStamp      HMS       Date
1  2010-02-22 19:55:00 2010-02-23
2  2012-10-10 07:53:00 2012-10-10
3  2012-10-24 07:55:00 2012-10-24
4  2013-07-15 08:14:00 2013-07-15
5  2013-07-24 08:23:00 2013-07-24
6  2013-12-02 10:00:00 2013-12-02
7  2012-02-23 07:56:00 2012-02-23
8  2012-03-06 08:45:00 2012-03-06
9  2013-09-15 19:54:00 2013-09-16
10 2007-03-28     <NA> 2007-03-28
11 2007-08-09     <NA> 2007-08-09
12 2008-08-07     <NA> 2008-08-07

Note

The input DF in reproducible form:

Lines <- "
    TimeStamp      HMS
1  2010-02-22 19:55:00
2  2012-10-10 07:53:00
3  2012-10-24 07:55:00
4  2013-07-15 08:14:00
5  2013-07-24 08:23:00
6  2013-12-02 10:00:00
7  2012-02-23 07:56:00
8  2012-03-06 08:45:00
9  2013-09-15 19:54:00
10 2007-03-28 NA      
11 2007-08-09 NA      
12 2008-08-07 NA"

DF <- read.table(text = Lines, as.is = TRUE)