R语言中基于关联时区列转换UTC日期失败的问题求助

已创建匹配国家与OlsonNames标准时区的Timezone tibble，以及包含UTC时区日期列的样本数据temp。将两表关联后，尝试按tz_new分组转换POSIXct类型日期列，但转换时始终使用第一个匹配的时区，未能按每行对应时区完成转换。

原代码如下：

Timezone <- tibble(
  Country = c("Germany", "Poland", "Austria", "Hungary", "Czech Republic", "Ireland", "Cyprus", "Egypt",
              "Spain", "France", "Greece", "Croatia", "Italy", "Morocco", "Portugal", "Bulgaria", "Romania",
              "Switzerland", "Denmark", "Estonia", "Iceland", "Lithuania", "Latvia", "Montenegro", "Malta",
              "Netherlands", "Norway", "Slovenia", "Slovakia", "United Kingdom", "Sweden", "Ukraine"),
  tz_new = c("Europe/Berlin", "Europe/Warsaw", "Europe/Vienna", "Europe/Budapest", "Europe/Prague",
               "Europe/Dublin", "Asia/Nicosia", "Africa/Cairo", "Europe/Madrid", "Europe/Paris", "Europe/Athens",
               "Europe/Zagreb", "Europe/Rome", "Africa/Casablanca", "Europe/Lisbon", "Europe/Sofia", "Europe/Bucharest",
               "Europe/Zurich", "Europe/Copenhagen", "Europe/Tallinn", "Atlantic/Reykjavik", "Europe/Vilnius", "Europe/Riga",
               "Europe/Podgorica", "Europe/Malta", "Europe/Amsterdam", "Europe/Oslo", "Europe/Ljubljana", "Europe/Bratislava",
               "Europe/London", "Europe/Stockholm", "Europe/Kiev")
)

#sample df 
temp <- tibble(
  ID = 1:5,
  Value = c(10, 20, 15, 30, 25),
  Country = c("Morocco", "Iceland", "Ireland", "Germany", "Poland"),
  Date1 = as.POSIXct(c("2023-08-08 12:00:00", "2023-08-08 13:00:00", "2023-08-08 14:00:00", "2023-08-08 15:00:00", "2023-08-08 16:00:00"), tz = "UTC"),
  Date2 = as.POSIXct(c("2023-08-09 12:00:00", "2023-08-09 13:00:00", "2023-08-09 14:00:00", "2023-08-09 15:00:00", "2023-08-09 16:00:00"), tz = "UTC"),
  Date3 = as.POSIXct(c("2023-08-10 12:00:00", "2023-08-10 13:00:00", "2023-08-10 14:00:00", "2023-08-10 15:00:00", "2023-08-10 16:00:00"), tz = "UTC")
)

df <-temp %>%
left_join(Timezone, by = "Country") %>%
group_by(tz_new) %>%
mutate(across(where(is.POSIXct), ~with_tz(.x, tz_new)))

问题原因 #

原代码错误使用了group_by(tz_new)，虽然每个分组内的tz_new值一致，但在across中传递tz_new时，with_tz会默认取分组内向量的第一个元素作为时区参数，导致所有行的日期列都被转换为第一个分组的时区，而非每行对应的时区。

实际上，left_join后每行已经对应了唯一的tz_new值，不需要分组操作，lubridate::with_tz支持传入与日期向量长度相同的时区向量，可直接按行匹配时区转换。

解决方案 #

方案1：移除分组，直接按行匹配时区转换 #

去掉group_by(tz_new)，直接在mutate中使用across，利用with_tz支持向量时区参数的特性：

df <- temp %>%
  left_join(Timezone, by = "Country") %>%
  mutate(across(where(is.POSIXct), ~with_tz(.x, tz_new)))

方案2：保留分组（若有其他分组需求），明确取分组内唯一时区 #

如果因其他逻辑需要保留分组，可在with_tz中明确取分组内的唯一时区值（如first(tz_new)），确保每个分组使用正确的时区：

df <- temp %>%
  left_join(Timezone, by = "Country") %>%
  group_by(tz_new) %>%
  mutate(across(where(is.POSIXct), ~with_tz(.x, first(tz_new)))) %>%
  ungroup() # 按需取消分组

验证结果 #

运行修正后的代码后，每行的日期列会被转换为对应国家的时区：

• 摩洛哥（Africa/Casablanca）的日期会转换为对应时区时间
• 冰岛（Atlantic/Reykjavik）的日期保持UTC±0（冰岛不实行夏令时）
• 其他国家的日期也会匹配各自的Olson标准时区完成转换

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