Converting UTC time to Local Time in R using Lat and Long

Question

I have a data frame in R with the following structure:

df <- data.frame(
long = c(-112, 34, 39), 
lat = c(41, -92, -76), 
state=c("utah", "arkansas", "maryland"), 
utc_time = c(1593855055, 1593826821, 1593826447)
)

      long      lat    state      utc_time 

1     -112       41     utah     1593855055 

2       34      -92  arkansas    1593826821 

3       39      -76  maryland    1593826447 


  

I would like to convert utc_time to local time using the lat,long or the state code. I have no idea what time the original utc stamp is from, but it seems likely GMT.

Thoughts? Thank you.

Update:

Thank you MrFlick. Yes, that was helpful. While I could not get the timezone package to work. I did use the accompanying shape file and did a spatial join.

TZ <- st_read("shape file from http://efele.net/maps/tz/us/")
df <- st_as_sf(df, coords = c("long", "lat"), crs = st_crs(TZ))
df <- st_join(df, TZ)

Now I have a dataframe that looks like this:

df <- data.frame(
TZID = c("America/New_York", "America/Denver", "America/New_York"), 
utc_time = c(1593855055, 1593826821, 1593826447)
)

I'm still struggling to get R to recognize it as local time:

df$date <- as.POSIXct(df$utc_time, tz=TZID)

I don't know if I'm vectorizing incorrectly, or if I misunderstand the POSIXct command. Never the less, I found your answer amazingly helpful. Thank you.

Answer 1

Here a more base R way (elaborating on my comment above). After getting the time zones from coordinates,

tz <- lutz::tz_lookup_coords(df$lat, df$long, method="accurate")

you may use Map, which gives you a list of times with different time zones.

tmp <- Map(function(x, tz, ...) 
  as.POSIXct(x, tz, ...), df$utc_time, tz, origin="1970-01-01")
# [[1]]
# [1] "2020-07-04 03:30:55 MDT"
# 
# [[2]]
# [1] "2020-07-03 20:40:21 CDT"
# 
# [[3]]
# [1] "2020-07-03 21:34:07 EDT"

You can add the time as character to the data frame

df$local.time <- sapply(tmp, as.character)
#   lat long    state   utc_time               tz          local.time
# 1  41 -112     utah 1593855055   America/Denver 2020-07-04 03:30:55
# 2  34  -92 arkansas 1593826821  America/Chicago 2020-07-03 20:40:21
# 3  39  -76 maryland 1593826447 America/New_York 2020-07-03 21:34:07

---

Data:

Note, that longitudes and lattitudes seem to be twisted, here the data I used:

df <- structure(list(lat = c(41, 34, 39), long = c(-112, -92, -76), 
    state = c("utah", "arkansas", "maryland"), utc_time = c(1593855055, 
    1593826821, 1593826447)), class = "data.frame", row.names = c(NA, 
-3L))

Answer 2

Here's a possible solution using lutz to look up the time zone, then some function from lubridate for the conversions.

library(lutz)
library(dplyr)
library(tidyr)
library(purrr)
library(lubridate)

Your data, edited so as to be correct:

df <- data.frame(
  long = c(-112, -92, -76), 
  lat = c(41, 34, 39), 
  state = c("utah", "arkansas", "maryland"), 
  utc_time = c(1593855055, 1593826821, 1593826447)
)

Now process the data.

df %>% 
  mutate(utc_dt   = as_datetime(utc_time),
         tz       = map2_chr(lat, long, tz_lookup_coords, method = "accurate"),
         local_dt = pmap(list(utc_dt, tz), with_tz)) %>% 
  unnest(local_dt)

This assumes that utc_dt is seconds since the UNIX epoch. We then map latitude and longitude to the function tz_lookup_coords, which returns the time zone. Then we map the UTC datetime and the timezone to the function with_tz, which returns the local datetime. The column is a list column, so we need to unnest to get the value back.

Result:

# A tibble: 3 x 7
   long   lat state      utc_time utc_dt              tz               local_dt           
  <dbl> <dbl> <chr>         <dbl> <dttm>              <chr>            <dttm>             
1  -112    41 utah     1593855055 2020-07-04 09:30:55 America/Denver   2020-07-04 03:30:55
2   -92    34 arkansas 1593826821 2020-07-04 01:40:21 America/Chicago  2020-07-03 19:40:21
3   -76    39 maryland 1593826447 2020-07-04 01:34:07 America/New_York 2020-07-03 19:34:07