R语言if_else语句处理时间字段返回异常值问题排查

原始数据：

# A tibble: 6 × 3
  rowid Arrival             Depart             
  <int> <dttm>              <dttm>             
1     1 2023-02-11 07:00:00 2023-02-11 17:30:00
2     2 2023-02-13 10:00:00 2023-02-13 18:00:00
3     3 2023-02-14 08:00:00 2023-02-14 17:00:00
4     4 2023-02-15 08:00:00 2023-02-15 17:00:00
5     5 2023-02-16 08:00:00 2023-02-16 18:00:00
6     6 2023-02-18 07:00:00 2023-02-18 17:30:00

structure(list(rowid = 1:6, Arrival = structure(c(1676098800, 
1676282400, 1676361600, 1676448000, 1676534400, 1676703600), tzone = "UTC", class = c("POSIXct", 
"POSIXt")), Depart = structure(c(1676136600, 1676311200, 1676394000, 
1676480400, 1676570400, 1676741400), tzone = "UTC", class = c("POSIXct", 
"POSIXt"))), row.names = c(NA, -6L), class = c("tbl_df", "tbl", 
"data.frame"))

定义变量：

ri <- 2
int <- test_int[2]

其中int是一个interval：

> int
[1] 2023-02-13 06:00:00 EST--2023-02-13 13:00:00 EST

执行代码：

test <- test %>% mutate(
  interval_start = if_else(rowid == ri, int_start(int), Arrival),
  interval_end = if_else(rowid == ri, int_end(int), Depart)
) %>% select(Arrival, interval_start, Depart, interval_end)  

得到异常结果（非rowid=2的行，interval_start/interval_end显示时间比原Arrival/Depart早5小时）：

# A tibble: 6 × 4
  Arrival             interval_start      Depart              interval_end       
  <dttm>              <dttm>              <dttm>              <dttm>             
1 2023-02-11 07:00:00 2023-02-11 02:00:00 2023-02-11 17:30:00 2023-02-11 12:30:00
2 2023-02-13 10:00:00 2023-02-13 06:00:00 2023-02-13 18:00:00 2023-02-13 13:00:00
3 2023-02-14 08:00:00 2023-02-14 03:00:00 2023-02-14 17:00:00 2023-02-14 12:00:00
4 2023-02-15 08:00:00 2023-02-15 03:00:00 2023-02-15 17:00:00 2023-02-15 12:00:00
5 2023-02-16 08:00:00 2023-02-16 03:00:00 2023-02-16 18:00:00 2023-02-16 13:00:00
6 2023-02-18 07:00:00 2023-02-18 02:00:00 2023-02-18 17:30:00 2023-02-18 12:30:00

structure(list(Arrival = structure(c(1676098800, 1676282400, 
1676361600, 1676448000, 1676534400, 1676703600), tzone = "UTC", class = c("POSIXct", 
"POSIXt")), interval_start = structure(c(1676098800, 1676286000, 
1676361600, 1676448000, 1676534400, 1676703600), class = c("POSIXct", 
"POSIXt")), Depart = structure(c(1676136600, 1676311200, 1676394000, 
1676480400, 1676570400, 1676741400), tzone = "UTC", class = c("POSIXct", 
"POSIXt")), interval_end = structure(c(1676136600, 1676311200, 
1676394000, 1676480400, 1676570400, 1676741400), class = c("POSIXct", 
"POSIXt"))), row.names = c(NA, -6L), class = c("tbl_df", "tbl", 
"data.frame"))

dplyr::if_else对输出的类型和属性有严格一致性要求：

• 原数据的Arrival/Depart是带UTC时区属性的POSIXct对象
• int_start(int)/int_end(int)返回的是带EST时区的POSIXct对象
• if_else会强制统一输出的时区属性，这里以第二个参数（int_start(int)）的时区为准，导致非目标行的Arrival/Depart被转换为EST时区显示，时间戳本身未变化（看结构里的时间戳值和原数据一致），只是时区切换后显示时间早了5小时（UTC与EST的时差）。

有三种可行方案：

方案1：统一两边的时区 #

将int_start(int)/int_end(int)转换为UTC时区后传入if_else：

test <- test %>% mutate(
  interval_start = if_else(rowid == ri, 
                          with_tz(int_start(int), tzone = "UTC"), 
                          Arrival),
  interval_end = if_else(rowid == ri, 
                        with_tz(int_end(int), tzone = "UTC"), 
                        Depart)
) %>% select(Arrival, interval_start, Depart, interval_end)

方案2：使用base::ifelse替代dplyr::if_else #

base::ifelse不会严格强制属性一致，会保留原向量的时区属性：

test <- test %>% mutate(
  interval_start = ifelse(rowid == ri, int_start(int), Arrival),
  interval_end = ifelse(rowid == ri, int_end(int), Depart)
) %>% select(Arrival, interval_start, Depart, interval_end)

方案3：使用case_when #

case_when在处理属性不一致的场景时更灵活：

test <- test %>% mutate(
  interval_start = case_when(rowid == ri ~ int_start(int), TRUE ~ Arrival),
  interval_end = case_when(rowid == ri ~ int_end(int), TRUE ~ Depart)
) %>% select(Arrival, interval_start, Depart, interval_end)

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