How to change time in combined time-date variable (POSIXlt)?

Question

I'm working with a combined time-date variable (format: 2019-05-25 09:02:52; see code below) for times on which ESM measurements were taken. These measurements fell into fixed intervals and I now want to set all times in the interval to the average of the interval. In the case of the provided example, I want to set all entries between 07:30:00 and 10:30:00 to 09:00:00 (regardless of the date).

         Name      Scheduled.Time
1 User #10165 2019-05-25 09:02:52
2 User #10165 2019-05-25 12:01:32
3 User #10165 2019-05-25 15:43:06
4 User #10165 2019-05-26 09:00:26
5 User #10165 2019-05-26 12:18:24
6 User #10165 2019-05-26 16:09:09
> head_daglijst_shrt <- head(daglijst_shrt)

I have tried accomplishing this by using the following code, which has worked for me using regular variables. However, now it doesn't seem to have the desired effect.

daglijst$Scheduled.Time["%H:%M:%S"][daglijst$Scheduled.Time["%H:%M:%S"] > "07:30:00" & 
                                      daglijst$Scheduled.Time["%H:%M:%S"] > "10:30:00"] <- 
  "09:00:00"

This resulted in the following error:

Error in as.POSIXlt.character(x, tz, ...) : 
  character string is not in a standard unambiguous format"

The only solution I can think of now is first splitting the variable into separate date and time variables, changing the time, and then merging them back. However, that doesn't seem optimal.

It would be great if someone had an alternative way to code this.

> head_daglijst_shrt <- dput(head(daglijst_shrt))
structure(list(Name = structure(c(1L, 1L, 1L, 1L, 1L, 1L), .Label = c("User #10165", 
"User #12545", "User #12803", "User #12829", "User #12843", "User #12844", 
"User #12845", "User #12847", "User #12848", "User #12849", "User #12853", 
"User #12858", "User #12859", "User #12861", "User #12866", "User #12868", 
"User #12906", "User #12907"), class = "factor"), Scheduled.Time = structure(c(2L, 
5L, 9L, 16L, 17L, 23L), .Label = c("2019-05-25 09:00:00 CEST", 
"2019-05-25 09:02:52 CEST", "2019-05-25 09:03:51 CEST", "2019-05-25 09:10:34 CEST", 
"2019-05-25 12:01:32 CEST", "2019-05-25 12:02:22 CEST", "2019-05-25 12:16:20 CEST", 
"2019-05-25 12:30:00 CEST", "2019-05-25 15:43:06 CEST", "2019-05-25 16:00:00 CEST", 
"2019-05-25 16:02:11 CEST", "2019-05-25 16:06:58 CEST", "2019-05-26 08:46:20 CEST", 
"2019-05-26 08:47:24 CEST", "2019-05-26 09:00:00 CEST", "2019-05-26 09:00:26 CEST", 
"2019-05-26 12:18:24 CEST", "2019-05-26 12:30:00 CEST", "2019-05-26 12:55:19 CEST", 
"2019-05-26 12:58:41 CEST", "2019-05-26 15:49:41 CEST", "2019-05-26 16:00:00 CEST", 
"2019-05-26 16:09:09 CEST", "2019-05-26 16:12:39 CEST", "2019-05-27 08:41:32 CEST", 
"2019-05-27 09:00:00 CEST", "2019-05-27 09:20:49 CEST", "2019-05-27 09:25:17 CEST", 
"2019-05-27 12:30:00 CEST", "2019-05-27 12:31:04 CEST", "2019-05-27 12:42:50 CEST", 
"2019-05-27 12:58:20 CEST", "2019-05-27 15:55:24 CEST", "2019-05-27 16:00:00 CEST", 
"2019-05-27 16:06:00 CEST", "2019-05-27 16:07:35 CEST", "2019-05-28 08:40:38 CEST", 
"2019-05-28 08:43:06 CEST", "2019-05-28 09:00:00 CEST", "2019-05-28 09:12:35 CEST", 
"2019-05-28 09:16:23 CEST", "2019-05-28 09:21:37 CEST", "2019-05-28 12:11:31 CEST", 
"2019-05-28 12:22:47 CEST", "2019-05-28 12:30:00 CEST", "2019-05-28 12:37:53 CEST", 
"2019-05-28 12:40:40 CEST", "2019-05-28 15:26:24 CEST", "2019-05-28 15:36:55 CEST", 
"2019-05-28 15:48:55 CEST", "2019-05-28 16:00:00 CEST", "2019-05-28 16:13:46 CEST", 
"2019-05-29 08:56:52 CEST", "2019-05-29 09:00:00 CEST", "2019-05-29 09:05:01 CEST", 
"2019-05-29 09:08:50 CEST", "2019-05-29 09:23:08 CEST", "2019-05-29 12:11:13 CEST", 
"2019-05-29 12:17:01 CEST", "2019-05-29 12:30:00 CEST", "2019-05-29 12:38:50 CEST", 
"2019-05-29 12:40:33 CEST", "2019-05-29 15:48:42 CEST", "2019-05-29 16:00:00 CEST", 
"2019-05-29 16:02:54 CEST", "2019-05-29 16:11:21 CEST", "2019-05-29 16:31:08 CEST", 
"2019-05-30 08:45:53 CEST", "2019-05-30 09:00:00 CEST", "2019-05-30 09:01:31 CEST", 
"2019-05-30 09:15:48 CEST", "2019-05-30 09:40:29 CEST", "2019-05-30 12:03:07 CEST", 
"2019-05-30 12:10:13 CEST", "2019-05-30 12:30:00 CEST", "2019-05-30 12:38:47 CEST", 
"2019-05-30 12:49:51 CEST", "2019-05-30 15:42:34 CEST", "2019-05-30 15:58:38 CEST", 
"2019-05-30 16:00:00 CEST", "2019-05-30 16:13:45 CEST", "2019-05-30 16:32:18 CEST", 
"2019-05-31 08:47:19 CEST", "2019-05-31 09:00:00 CEST", "2019-05-31 09:04:27 CEST", 
"2019-05-31 09:31:41 CEST", "2019-05-31 12:08:42 CEST", "2019-05-31 12:12:36 CEST", 
"2019-05-31 12:25:35 CEST", "2019-05-31 12:30:00 CEST", "2019-05-31 15:48:06 CEST", 
"2019-05-31 16:00:00 CEST", "2019-05-31 16:24:20 CEST", "2019-05-31 16:33:39 CEST", 
"2019-06-01 08:45:36 CEST", "2019-06-01 08:50:40 CEST", "2019-06-01 08:51:13 CEST", 
"2019-06-01 09:00:00 CEST", "2019-06-01 12:11:39 CEST", "2019-06-01 12:30:00 CEST", 
"2019-06-01 13:02:12 CEST", "2019-06-01 13:03:23 CEST", "2019-06-01 15:55:42 CEST", 
"2019-06-01 16:00:00 CEST", "2019-06-01 16:05:15 CEST", "2019-06-01 16:05:54 CEST", 
"2019-06-02 08:39:10 CEST", "2019-06-02 09:00:00 CEST", "2019-06-02 12:16:45 CEST", 
"2019-06-02 12:30:00 CEST", "2019-06-02 15:58:12 CEST", "2019-06-02 16:00:00 CEST", 
"2019-06-03 09:00:00 CEST", "2019-06-03 09:04:42 CEST", "2019-06-03 09:04:48 CEST", 
"2019-06-03 09:07:09 CEST", "2019-06-03 12:30:00 CEST", "2019-06-03 12:36:39 CEST", 
"2019-06-03 12:48:58 CEST", "2019-06-03 13:06:20 CEST", "2019-06-03 16:00:00 CEST", 
"2019-06-03 16:03:32 CEST", "2019-06-03 17:03:39 CEST", "2019-06-04 09:00:00 CEST", 
"2019-06-04 09:24:15 CEST", "2019-06-04 09:55:02 CEST", "2019-06-04 12:30:00 CEST", 
"2019-06-04 13:22:21 CEST", "2019-06-04 13:44:38 CEST", "2019-06-04 15:52:51 CEST", 
"2019-06-04 15:57:11 CEST", "2019-06-04 16:00:00 CEST", "2019-06-04 16:50:23 CEST", 
"2019-06-05 09:00:00 CEST", "2019-06-05 09:11:28 CEST", "2019-06-05 09:14:44 CEST", 
"2019-06-05 09:52:18 CEST", "2019-06-05 12:23:50 CEST", "2019-06-05 12:30:00 CEST", 
"2019-06-05 13:01:50 CEST", "2019-06-05 13:36:56 CEST", "2019-06-05 15:48:12 CEST", 
"2019-06-05 16:00:00 CEST", "2019-06-05 16:09:19 CEST", "2019-06-05 16:44:42 CEST", 
"2019-06-06 08:21:00 CEST", "2019-06-06 08:44:06 CEST", "2019-06-06 11:51:50 CEST", 
"2019-06-06 12:26:14 CEST", "2019-06-06 15:57:43 CEST", "2019-06-06 16:02:51 CEST"
), class = "factor")), .Names = c("Name", "Scheduled.Time"), row.names = c(NA, 
6L), class = "data.frame")
> View(head_daglijst_shrt)

user_9163951 · Answer

The data.table package has a function called as.ITime(), which can be used to work with times, for example:

mydatetime <- as.POSIXct("2019-09-01 10:30:00")
as.ITime(mydatetime) > as.ITime("07:30:00")
## [1] TRUE
as.ITime(mydatetime) > as.ITime("11:30:00")
## [1] FALSE

user_6574038 · Answer

Your dput shows that your Scheduled.Time column is in "factor" format, which is a bit strange. We convert it into "POSIXct".

daglijst$Scheduled.Time <- as.POSIXct(daglijst$Scheduled.Time)

Then we could use gsub with regular expressions to extract the time digits (without the ":") and convert them to "numeric". Now we may find the positions that lay within the desired interval, and we'll apply another gsub onto a subset with this positions to replace with "09:00:00".

t <- as.numeric(gsub(".*\s(\d+)\:(\d+)\:(\d+).*", "\1\2\3", 
                     daglijst$Scheduled.Time))
# [1]  90252 120132 154306  90026 121824 160909

pos <- which(73000 <= t & t <= 103000)
# [1] 1 4

daglijst$Scheduled.Time[pos] <- gsub("(\d+)\:(\d+)\:(\d+)", "09:00:00", 
                                     daglijst$Scheduled.Time[pos])
#          Name      Scheduled.Time
# 1 User #10165 2019-05-25 09:00:00
# 2 User #10165 2019-05-25 12:01:32
# 3 User #10165 2019-05-25 15:43:06
# 4 User #10165 2019-05-26 09:00:00
# 5 User #10165 2019-05-26 12:18:24
# 6 User #10165 2019-05-26 16:09:09

Scheduled.Time is "POSIXct" format:

str(daglijst)
# 'data.frame': 6 obs. of  2 variables:
# $ Name          : Factor w/ 18 levels "User #10165",..: 1 1 1 1 1 1
# $ Scheduled.Time: POSIXct, format: "2019-05-25 09:00:00" "2019-05-25 12:01:32" "2019-05-25 15:43:06" ...

How to change time in combined time-date variable (POSIXlt)?

2 Answers