在R中基于时间差和其他列值替换后续行的实现方案

原始数据 #

customerid <- c("A1", "A1", "A2", "A2", "A3", "A3", "A3", "A4")
index <- c("1", "2", "1", "2", "1", "2", "3", "1")
c1 <- c("pos", "neg", "neg", "pos", "neg", "neg", "pos", "neg")
orderdate <- c("2018-09-14", "2020-08-20", "2018-09-15", "2019-08-25", "2017-09-12", "2018-09-16", "2020-08-21", "2018-08-10")
df <- data.frame(customerid, index, c1, orderdate)

需求说明 #

需基于c1和orderdate创建新列c2，每个customerid独立处理，先按orderdate从早到晚排序后遵循以下规则：

• 规则1（优先级最高）：若某行c1为"pos"，该行c2设为NA，该客户所有后续行c2设为"No"。
• 规则2：若某行c1为"neg"或其他非"pos"值，该行c2设为NA，后续行分两种情况：
  • A) 所有orderdate与当前"基准日期"（初始为该客户最早订单日期）相差≤500天的后续行，c2设为"No"。
  • B) 首个orderdate与当前"基准日期"相差>500天的行，c2设为"Yes"，并将该行设为新的"基准日期"，重复规则1-2。

预期结果 #

c2 <- c(NA, "No", NA, "No", NA, "No", "Yes", NA)

扩展测试数据（c1含其他取值） #

customerid <- c("A1", "A1", "A2", "A2", "A3", "A3", "A3", "A4")
index <- c("1", "2", "1", "2", "1", "2", "3", "1")
c1 <- c("pos", "lost", "neg", "return", "neg", "neg", "lost", "lost")
orderdate <- c("2018-09-14", "2020-08-20", "2018-09-15", "2019-08-25", "2017-09-12", "2018-09-16", "2020-08-21", "2018-08-10")
df <- data.frame(customerid, index, c1, orderdate)

解决方案 #

使用dplyr分组结合循环处理，核心是对每个客户的行按日期排序后，逐行跟踪触发状态与基准日期：

library(dplyr)

# 转换日期格式并按客户+日期排序
df <- df %>%
  mutate(orderdate = as.Date(orderdate)) %>%
  arrange(customerid, orderdate)

# 定义单个客户数据的处理函数
process_customer <- function(data) {
  n <- nrow(data)
  c2 <- rep(NA, n)
  trigger_rule1 <- FALSE
  base_date <- data$orderdate[1]
  
  for (i in 1:n) {
    if (trigger_rule1) {
      if (i > 1) c2[i] <- "No"
      next
    }
    
    current_c1 <- data$c1[i]
    current_date <- data$orderdate[i]
    
    if (current_c1 == "pos") {
      c2[i] <- NA
      trigger_rule1 <- TRUE
    } else {
      c2[i] <- NA
      if (i < n) {
        for (j in (i+1):n) {
          if (trigger_rule1) break
          
          days_diff <- as.numeric(difftime(data$orderdate[j], base_date, units = "days"))
          if (days_diff <= 500) {
            c2[j] <- "No"
          } else {
            c2[j] <- "Yes"
            base_date <- data$orderdate[j]
            i <- j - 1
            break
          }
        }
      }
    }
  }
  data$c2 <- c2
  return(data)
}

# 分组处理并还原原始顺序
result <- df %>%
  group_by(customerid) %>%
  group_modify(~process_customer(.x)) %>%
  ungroup() %>%
  arrange(match(customerid, df$customerid), match(index, df$index))

print(result$c2)

运行后原始数据的c2将与预期一致；扩展数据中"lost""return"等取值会按neg逻辑处理。

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