I'm working with some timestamps in POSIXct format. Right now they are all showing up as being in the timezone "UTC", but in reality some are known to be in the "America/New_York" timezone. I'd like to correct the timestamps so that they all read as the correct times.
I initially used an ifelse()
statement along with lubridate::with_tz()
. This didn't work as expected because ifelse()
didn't return values in POSIXct.
Then I tried dplyr::if_else()
based on other posts here, and that's not working as expected either.
I can change a single timestamp or even a list of timestamps to a different timezone using with_tz()
(so I know it works), but when I use it within if_else()
the output is such that all the values are returned given the "yes" argument in if_else()
.
library(lubridate)
library(dplyr)
x <- data.frame("ts" = as.POSIXct(c("2017-04-27 13:44:00 UTC",
"2017-03-10 12:22:00 UTC", "2017-03-22 10:24:00 UTC"), tz = "UTC"),
"tz" = c("UTC","EST","UTC"))
x <- mutate(x, ts_New = if_else(tz == "UTC", with_tz(ts, "America/New_York"), ts))
Expected results are below where ts_New has timestamps adjusted to new time zone but only when values in tz = "UTC". Timestamps with tz = "America/New_York" shouldn't change.
ts tz ts_NEW
1 2017-04-27 13:44:00 UTC 2017-04-27 09:44:00
2 2017-03-10 12:22:00 EST 2017-03-10 12:22:00
3 2017-01-22 10:24:00 UTC 2017-03-22 06:24:00
Actual results are below where all ts_New timestamps are adjusted to new time zone regardless of value in tz
x
ts tz ts_New
1 2017-04-27 13:44:00 UTC 2017-04-27 09:44:00
2 2017-03-10 12:22:00 EST 2017-03-10 07:22:00
3 2017-03-22 10:24:00 UTC 2017-03-22 06:24:00
This doesn't answer your original question about why with_tz
doesn't work with if_else
but here is one workaround. We subtract 4 hours (difference between UTC and EST) where tz == "UTC"
.
library(dplyr)
library(lubridate)
x %>% mutate(ts_New = if_else(tz == "UTC", ts - hours(4), ts))
# ts tz ts_New
#1 2017-04-27 13:44:00 UTC 2017-04-27 09:44:00
#2 2017-03-10 12:22:00 EST 2017-03-10 12:22:00
#3 2017-03-22 10:24:00 UTC 2017-03-22 06:24:00
Or in base R
x$ts_New <- x$ts
inds <- x$tz == "UTC"
x$ts_New[inds] <- x$ts_New[inds] - 4 * 60 * 60
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With