如何在R中基于DF_2规则生成REQUEST_DATE_FINAL字段

数据定义 #

DF_1 <- data.frame(
  REQUEST_DATE = c("01-01-2024", "02-01-2024","02-01-2024" ,"03-01-2024","04-01-2024","05-01-2024","06-01-2024","12-01-2024","15-01-2024"),
  REQUEST_TIME = c("05:23:52", "20:00:01", "22:30:39","15:15:32","17:10:17","07:00:12","11:22:00","18:52:00","08:44:19"))
DF_1$REQUEST_DATE <- as.Date(DF_1$REQUEST_DATE, format = "%d-%m-%Y")
DF_1$REQUEST_TIME <- format(DF_1$REQUEST_TIME, format = "%H:%M:%S")


DF_2 <- data.frame(
  DATE = c(seq(as.Date("2024-01-01"), as.Date("2024-01-15"), by = "days")),
  USEFUL_DATE = c("NO","YES","YES","YES","YES","NO","NO","YES","YES","YES","YES","NO","NO","NO","YES"),
  NEXT_USEFUL_DATE = c("02-01-2024", "02-01-2024" ,"03-01-2024","04-01-2024","05-01-2024","08-01-2024","08-01-2024","08-01-2024","09-01-2024","10-01-2024","11-01-2024","15-01-2024","15-01-2024","15-01-2024","15-01-2024"))
DF_2$NEXT_USEFUL_DATE <- as.Date(DF_2$NEXT_USEFUL_DATE, format = "%d-%m-%Y")

需求条件 #

• 若REQUEST_DATE对应的USEFUL_DATE为“YES”且REQUEST_TIME在07:00:00-19:00:00之间，直接拼接REQUEST_DATE与REQUEST_TIME；
• 若REQUEST_DATE对应的USEFUL_DATE为“YES”且REQUEST_TIME在00:00:00-06:59:59之间，拼接REQUEST_DATE与07:00:00；
• 若REQUEST_DATE对应的USEFUL_DATE为“NO”，找到下一个USEFUL_DATE为“YES”的日期，拼接该日期与07:00:00；
• 若REQUEST_DATE对应的USEFUL_DATE为“YES”且REQUEST_TIME在19:00:01-23:59:59之间，取下一个有效日期拼接07:00:00。

预期结果 #

DF_1 <- DF_1 %>% 
  mutate(REQUEST_DATE_FINAL = c("02/01/2024  07:00:00","03/01/2024  07:00:00","03/01/2024  07:00:00","03/01/2024  15:15:32","04/01/2024  17:10:17","05/01/2024  07:00:12", "08/01/2024  07:00:00","15/01/2024  07:00:00","15/01/2024  08:44:19"))
DF_1$REQUEST_DATE_FINAL <- as.POSIXlt(DF_1$REQUEST_DATE_FINAL, format = "%d-%m-%Y %H:%M:%S")

解决方案 #

无需使用循环，通过left_join关联两个数据框后，用case_when即可批量处理所有条件：

library(dplyr)

# 关联数据框，匹配每条请求对应的有效日期标记和下一个有效日期
DF_combined <- DF_1 %>%
  left_join(DF_2, by = c("REQUEST_DATE" = "DATE"))

# 生成最终日期时间变量
DF_result <- DF_combined %>%
  mutate(
    # 将时间转为可比较的格式
    time_period = as.POSIXct(REQUEST_TIME, format = "%H:%M:%S"),
    time_07 = as.POSIXct("07:00:00", format = "%H:%M:%S"),
    time_19 = as.POSIXct("19:00:00", format = "%H:%M:%S"),
    # 按条件生成最终值
    REQUEST_DATE_FINAL = case_when(
      USEFUL_DATE == "YES" & time_period >= time_07 & time_period <= time_19 ~ 
        as.POSIXlt(paste(REQUEST_DATE, REQUEST_TIME), format = "%Y-%m-%d %H:%M:%S"),
      USEFUL_DATE == "YES" & time_period < time_07 ~ 
        as.POSIXlt(paste(REQUEST_DATE, "07:00:00"), format = "%Y-%m-%d %H:%M:%S"),
      USEFUL_DATE == "YES" & time_period > time_19 ~ 
        as.POSIXlt(paste(NEXT_USEFUL_DATE, "07:00:00"), format = "%Y-%m-%d %H:%M:%S"),
      USEFUL_DATE == "NO" ~ 
        as.POSIXlt(paste(NEXT_USEFUL_DATE, "07:00:00"), format = "%Y-%m-%d %H:%M:%S"),
      TRUE = NA_POSIXlt_
    )
  ) %>%
  select(REQUEST_DATE, REQUEST_TIME, REQUEST_DATE_FINAL)

# 查看结果
print(DF_result)

说明 #

1. left_join直接将DF_2中对应日期的USEFUL_DATE和NEXT_USEFUL_DATE匹配到DF_1的每条记录，避免循环查找；
2. 把时间转为POSIXct格式后，可直接与07:00、19:00两个节点做大小比较；
3. case_when按条件优先级依次判断，生成符合要求的最终日期时间。

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