Using R, how can I correctly subtract a second from a POSIXct variable using dplyr's lead and lag functions?

Question

I have 12 dates and times in a table in POSIXct format:

                Time
1  2017-03-11 01:10:09
2  2017-03-11 03:07:58
3  2017-03-12 19:16:47
4  2017-03-13 09:52:04
5  2017-03-17 20:36:35
6  2017-03-18 03:10:54
7  2017-03-18 07:29:31
8  2017-03-18 10:13:37
9  2017-03-20 10:19:31
10 2017-03-20 12:11:39
11 2017-03-20 12:11:39
12 2017-03-20 14:16:12

If an entry matches the following entry, I want to remove one second from the time. For example, row 10 should appear as "2017-03-20 12:11:38".

I'm trying to do something like the following but that works:

df %>% mutate(Time = ifelse(Time == lead(Time), Time-1, Time))

Answer 1

Your approach is correct however, base::ifelse strips off the attribute making POSIXct column as numeric. To avoid that since you are using dplyr you can use dplyr::if_else which preserves the class.

Also note that lead will generate NA for last value in Time so the comparison Time == lead(Time) would generate NA. We could set missing argument as Time in if_else.

library(dplyr)
df %>% mutate(Time = if_else(Time == lead(Time), Time-1, Time, missing = Time))

#                  Time
#1  2017-03-11 01:10:09
#2  2017-03-11 03:07:58
#3  2017-03-12 19:16:47
#4  2017-03-13 09:52:04
#5  2017-03-17 20:36:35
#6  2017-03-18 03:10:54
#7  2017-03-18 07:29:31
#8  2017-03-18 10:13:37
#9  2017-03-20 10:19:31
#10 2017-03-20 12:11:38
#11 2017-03-20 12:11:39
#12 2017-03-20 14:16:12

Answer 2

Instead of ifelse we can use case_when and with case_when we can also have multiple conditions instead of using a nested ifelse or if_else

library(dplyr)
library(lubridate)
df %>%
   mutate(Time = as.POSIXct(Time), 
          Time = case_when(Time == lead(Time) ~ 
                   Time - seconds(1), TRUE ~ Time))

-output

#          Time
#1  2017-03-11 01:10:09
#2  2017-03-11 03:07:58
#3  2017-03-12 19:16:47
#4  2017-03-13 09:52:04
#5  2017-03-17 20:36:35
#6  2017-03-18 03:10:54
#7  2017-03-18 07:29:31
#8  2017-03-18 10:13:37
#9  2017-03-20 10:19:31
#10 2017-03-20 12:11:38
#11 2017-03-20 12:11:39
#12 2017-03-20 14:16:12

---

Or using base R, we create a logical index and assign

i1 <- c(df$Time[-1]  == df$Time[-nrow(df)], FALSE)
df$Time <- as.POSIXct(df$Time)
df$Time[i1] <- df$Time[i1] - 1

data

df <- structure(list(Time = c("2017-03-11 01:10:09", "2017-03-11 03:07:58", 
"2017-03-12 19:16:47", "2017-03-13 09:52:04", "2017-03-17 20:36:35", 
"2017-03-18 03:10:54", "2017-03-18 07:29:31", "2017-03-18 10:13:37", 
"2017-03-20 10:19:31", "2017-03-20 12:11:39", "2017-03-20 12:11:39", 
"2017-03-20 14:16:12")), class = "data.frame", row.names = c("1", 
"2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12"))