Convert UTC Time to Local Time with Variable Time Zones in R

Question

I have a data frame with one column containing POSIXct timestamps, all of which are in UTC time, and another column containing time zones, like so:

time_data
   time_stamp        time_zone       
   <dttm>              <chr>           
 1 2020-02-06 07:08:59 America/Chicago 
 2 2020-02-06 07:43:50 America/Denver  
 3 2020-02-06 08:44:51 America/New_York
 4 2020-02-06 08:45:07 America/New_York
 5 2020-02-06 08:45:10 America/New_York
 6 2020-02-06 08:45:14 America/New_York
 7 2020-02-06 08:45:30 America/New_York
 8 2020-02-06 08:45:47 America/Chicago 
 9 2020-02-06 08:45:48 America/New_York
10 2020-02-06 08:45:49 America/New_York

I know that I can use the lubridate::with_tz function to convert an individual UTC POSIXct timestamp to a timestamp that's localized to a time zone, like so:

with_tz(time_data$time_stamp[1], tz=time_data$time_zone[1])
"2020-02-06 01:08:59 CST"

However, I try to do this for the entire vector / column, I get an error:

with_tz(time_data$time_stamp, tz=time_data$time_zone)
Error in as.POSIXlt.POSIXct(x, tz) : invalid 'tz' value

Any tips would be much appreciated! Thanks!

Answer 1

You can achieve this task, but you end up having lists in a new column. When you have multiple time zones, you gotta work a bit more than usual. What you need is to go through each time and time zone in a row and create a new time stamp. You sample data is called mydf. In the following code, I created a date object first since time_stemp is in character in mydf. Then, for each row, take time and time zone and use with_tz(). map2() is handling the job. You can use mapply() too. Since map2() returns lists, I used unnest() in the end.

library(tidyverse)
library(lubridate)

mutate(mydf, time_stamp = as.POSIXct(time_stamp, format = "%Y-%m-%d %H:%M:%S", tz = "UTC"),
       new_time = map2(.x = time_stamp, .y = time_zone, 
                       .f = function(x, y) {with_tz(time = x, tzone = y)})) 


Classes ‘tbl_df’, ‘tbl’ and 'data.frame':   10 obs. of  3 variables:
 $ time_stamp: POSIXct, format: "2020-02-06 07:08:59" "2020-02-06 07:43:50" "2020-02-06 08:44:51" "2020-02-06 08:45:07" ...
 $ time_zone : chr  "America/Chicago" "America/Denver" "America/New_York" "America/New_York" ...
 $ new_time  :List of 10
  ..$ : POSIXct, format: "2020-02-06 01:08:59"
  ..$ : POSIXct, format: "2020-02-06 00:43:50"
  ..$ : POSIXct, format: "2020-02-06 03:44:51"
  ..$ : POSIXct, format: "2020-02-06 03:45:07"
  ..$ : POSIXct, format: "2020-02-06 03:45:10"
  ..$ : POSIXct, format: "2020-02-06 03:45:14"
  ..$ : POSIXct, format: "2020-02-06 03:45:30"
  ..$ : POSIXct, format: "2020-02-06 02:45:47"
  ..$ : POSIXct, format: "2020-02-06 03:45:48"
  ..$ : POSIXct, format: "2020-02-06 03:45:49"

If you do not mind having dates as characters, you can do the following.

mutate(mydf, time_stamp = as.POSIXct(time_stamp, format = "%Y-%m-%d %H:%M:%S", tz = "UTC"),
       new_time = map2(.x = time_stamp, .y = time_zone, 
                   .f = function(x, y) {as.character(with_tz(time = x, tzone = y))})) %>% 
unnest(new_time)

   time_stamp          time_zone        new_time           
   <dttm>              <chr>            <chr>              
 1 2020-02-06 07:08:59 America/Chicago  2020-02-06 01:08:59
 2 2020-02-06 07:43:50 America/Denver   2020-02-06 00:43:50
 3 2020-02-06 08:44:51 America/New_York 2020-02-06 03:44:51
 4 2020-02-06 08:45:07 America/New_York 2020-02-06 03:45:07
 5 2020-02-06 08:45:10 America/New_York 2020-02-06 03:45:10
 6 2020-02-06 08:45:14 America/New_York 2020-02-06 03:45:14
 7 2020-02-06 08:45:30 America/New_York 2020-02-06 03:45:30
 8 2020-02-06 08:45:47 America/Chicago  2020-02-06 02:45:47
 9 2020-02-06 08:45:48 America/New_York 2020-02-06 03:45:48
10 2020-02-06 08:45:49 America/New_York 2020-02-06 03:45:49

DATA

mydf <- structure(list(time_stamp = c("2020-02-06 07:08:59", "2020-02-06 07:43:50", 
"2020-02-06 08:44:51", "2020-02-06 08:45:07", "2020-02-06 08:45:10", 
"2020-02-06 08:45:14", "2020-02-06 08:45:30", "2020-02-06 08:45:47", 
"2020-02-06 08:45:48", "2020-02-06 08:45:49"), time_zone = c("America/Chicago", 
"America/Denver", "America/New_York", "America/New_York", "America/New_York", 
"America/New_York", "America/New_York", "America/Chicago", "America/New_York", 
"America/New_York")), row.names = c(NA, -10L), class = c("tbl_df", 
"tbl", "data.frame"))