在R中根据美国各州时区转换UTC时间列

我有一个R数据框，包含UTC时区日期列UTC_date，还有数千条美国各州的记录。需要新增一列，把UTC时间转换为对应各州的本地时间。

示例数据框：

df<-structure(list(UTC_date = structure(list(sec = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0), min = c(14L, 21L, 17L, 38L, 45L, 25L, 44L, 11L,09L, 27L), hour = c(3L, 0L, 16L, 16L, 17L, 8L, 17L, 1L, 2L, 4L), mday = c(2L, 2L, 15L, 12L, 19L, 18L, 25L, 17L, 07L, 17L), mon = c(9L, 9L, 9L, 9L, 9L, 9L, 9L, 9L, 9L, 9L), year = c(121L,121L, 121L, 121L, 121L, 121L, 121L, 121L, 121L, 121L), wday = c(1L,4L, 1L, 1L, 1L, 4L, 1L, 3L, 3L, 3L), yday = c(297L, 300L,297L, 297L, 297L, 300L, 297L, 299L, 299L, 299L), isdst = c(1L,1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), zone = c("CDT", "CDT","CDT", "CDT", "CDT", "CDT", "CDT", "CDT", "CDT", "CDT"), gmtoff = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_)), class = c("POSIXlt", "POSIXt")), StoreState = c("TX","MS", "AZ", "SC", "WI", "MO", "TX", "TX", "MO", "TX")), row.names = c(NA,10L), class = "data.frame")
str(df)
head(df)

输入样例：

UTC_Date                State
2021-10-02 03:14:00     TX

期望输出样例：

UTC_Date                State                         Local Time as per State
2021-10-02 03:14:00     TX                              2021-10-02  21:14:00

1. 准备州与时区映射表 #

先整理美国各州对应的IANA时区，确保覆盖数据里的所有州：

state_tz_map <- data.frame(
  State = c("TX", "MS", "AZ", "SC", "WI", "MO"),
  TimeZone = c("America/Chicago", "America/Chicago", "America/Phoenix", 
               "America/New_York", "America/Chicago", "America/Chicago")
)

2. 统一UTC时间格式 #

原数据的UTC_date是POSIXlt格式，先转换为POSIXct并明确时区为UTC：

df$UTC_date_ct <- as.POSIXct(df$UTC_date, tz = "UTC")

3. 批量转换时区（tidyverse方案） #

用dplyr和purrr实现高效批量转换，先安装加载依赖包：

install.packages(c("dplyr", "purrr", "lubridate"))
library(dplyr)
library(purrr)
library(lubridate)

合并映射表并转换时间：

df <- df %>%
  left_join(state_tz_map, by = c("StoreState" = "State")) %>%
  mutate(Local_Time = map2(UTC_date_ct, TimeZone, ~with_tz(.x, tzone = .y)))

4. 基础R替代方案（无需额外包） #

如果不想用tidyverse，用基础R循环实现：

# 创建州-时区映射向量
tz_vec <- c(
  "TX" = "America/Chicago",
  "MS" = "America/Chicago",
  "AZ" = "America/Phoenix",
  "SC" = "America/New_York",
  "WI" = "America/Chicago",
  "MO" = "America/Chicago"
)

# 转换UTC时间格式
df$UTC_date_ct <- as.POSIXct(df$UTC_date, tz = "UTC")

# 逐行转换时区
df$Local_Time <- NA
for(i in 1:nrow(df)){
  df$Local_Time[i] <- format(with_tz(df$UTC_date_ct[i], tz = tz_vec[df$StoreState[i]]))
}
df$Local_Time <- as.POSIXct(df$Local_Time)

5. 验证结果 #

查看转换后的核心列：

select(df, UTC_date, StoreState, Local_Time)

注：with_tz函数会自动处理夏令时/冬令时规则，用户示例中的输出可能存在时区计算误差，实际转换结果以R的时区规则为准

内容的提问来源于stack exchange，提问作者Dexter1611