Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using dplyr::if_else() in R to change the time zone of POSIXct timestamps based on value of another variable

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
like image 459
KTrooper Avatar asked Sep 03 '19 04:09

KTrooper


1 Answers

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
like image 57
Ronak Shah Avatar answered Nov 05 '22 09:11

Ronak Shah