with_tz with a vector of timezones

Question

I have a dataframe like so:

library(dplyr)
data <- data_frame(
  timestamp_utc = c('2015-11-18 03:55:04', '2015-11-18 03:55:08', 
                    '2015-11-18 03:55:10'),
  local_tz = c('America/New_York', 'America/Los_Angeles', 
               'America/Indiana/Indianapolis')
  )

I need to create a new variable that converts the UTC timestamp to the local time as defined in the local_tz column. However, both format and with_tz (from lubridate) expect only one timezone, not a vector of timezones. I'm looking for something like this:

mutate(data, timestamp_local = with_tz(timestamp_utc, tzone = local_tz))

Any ideas?

Answer 1

First make sure your data is loaded as a date - I had to convert to date first:

data$timestamp_utc <- as.POSIXct(data$timestamp_utc, tz = "UTC")

Then you can use the function rowwise from dplyr, in combination with do:

library(lubridate)
library(dplyr)
z <- data %>% rowwise() %>%
              do(timestamp_local = with_tz(.$timestamp_utc, tzone = .$local_tz))
data$timestamp_local <- z$timestamp_local

data$timestamp_local
[[1]]
[1] "2015-11-17 22:55:04 EST"

[[2]]
[1] "2015-11-17 19:55:08 PST"

[[3]]
[1] "2015-11-17 22:55:10 EST"

We need to make the timestamp_local column a list, as otherwise all the timezones are converted back to one you can only have one timezone in a vector).

Answer 2

Here is one method. With this, the result has to be a string, otherwise unlist() or c() will turn the result back to the system timezone for every element in the list.

It's still slow though because it is not vectorized.

> get_local_time <- function(timestamp_utc, local_tz) {
    l <- lapply(seq(length(timestamp_utc)), 
                function(x) {format(with_tz(timestamp_utc[x], local_tz[x]), "%FT%T%z")})
    unlist(l)
    }

> mutate(data, timestamp_local = get_local_time(timestamp_utc, tzone = local_tz))

Source: local data frame [3 x 3]

        timestamp_utc                     local_tz          timestamp_local
               (time)                        (chr)                    (chr)
1 2015-11-18 03:55:04             America/New_York 2015-11-17T22:55:04-0500
2 2015-11-18 03:55:08          America/Los_Angeles 2015-11-17T19:55:08-0800
3 2015-11-18 03:55:10 America/Indiana/Indianapolis 2015-11-17T22:55:10-0500

Update 2015-11-24

Using dplyr::combine() rather than unlist() allows the variable to remain datetimes with the right timezone attributes rather than converting to strings.

> get_local_time <- function(timestamp_utc, local_tz) {
    l <- lapply(seq(length(timestamp_utc)), 
                function(x) {with_tz(timestamp_utc[x], local_tz[x])})
    combine(l)
    }

> mutate(data, timestamp_local = get_local_time(timestamp_utc, tzone = local_tz))

Source: local data frame [3 x 3]

        timestamp_utc                     local_tz     timestamp_local
               (time)                        (chr)              (time)
1 2015-11-18 03:55:04             America/New_York 2015-11-17T22:55:04
2 2015-11-18 03:55:08          America/Los_Angeles 2015-11-17T19:55:08
3 2015-11-18 03:55:10 America/Indiana/Indianapolis 2015-11-17T22:55:10

Answer 3

The trick is to use group_by() with local_tz before mutate():

data$timestamp_utc <- as.POSIXct(data$timestamp_utc, tz = "UTC")

data %>% 
  group_by(local_tz) %>%
  mutate(timestamp_local = with_tz(timestamp_utc, local_tz))