基于订单结果与日期生成summary列：循环或dplyr实现方案问询

新增疑问 #

现有回复及其他Stack Overflow问题未解答的新问题：能否通过for循环按customerid和order_date遍历订单，或在dplyr中使用elseif等实现需求？

核心需求 #

希望基于order_result和order_date的值创建新列summary，order列代表每个customer的订单编号。

准备数据 #

customer <- c("A", "A", "B", "B", "C", "C", "C", "D", "E", "E", "E", "F", "G", "G", "G", "H", "H", "I", "I", "I", "J", "J", "J", "K", "K", "K", "K", "K")
order <- c("1", "2", "1", "2", "1", "2", "3", "1", "1", "2", "3", "1", "1", "2", "3", "1", "2", "1", "2", "3", "1", "2", "3", "1", "2", "3", "4", "5")
order_result <- c("positive", "lost", "negative", "return", "negative", "lost", "negative", "lost", "lost", "return", "lost", "return", "lost", "negative", "lost", "lost", "positive", "return", "negative", "lost", "lost","positive", "lost", "return", "negative", "lost", "lost", "negative")
order_date <- 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", "2017-09-13", "2018-02-16", "2020-08-21", "2017-05-20", "2018-07-05", "2019-02-15", "2021-11-04", "2017-08-07", "2021-08-05", "2019-07-30", "2020-09-23", "2020-11-23","2017-10-30", "2018-04-09", "2020-04-09", "2019-07-30", "2019-12-04", "2020-04-04", "2020-08-03", "2021-12-24")
df1 <- data.frame(customer, order, order_result, order_date)

生成summary列的规则 #

按每个客户的order_date从早到晚遍历order_result，生成包含Yes/No的summary列：

• 每个客户的首单summary固定为Yes；
• 以当前索引订单为起点，根据order_result决定后续行的summary值：
  • 若order_result为"positive"，该客户后续所有订单的summary均为No；
  • 若order_result为"negative"，后续订单中，order_date与索引订单日期差≤400天的为No，>400天的为Yes；
  • 若order_result为"return"或"lost"，下一订单的summary为Yes；
• 跳转到下一个summary为Yes的订单作为新索引，重复上述流程，各客户独立处理。

期望结果 #

summary <- c("Yes", "No", "Yes", "No", "Yes", "No", "Yes", "Yes", "Yes", "Yes", "Yes", "Yes", "Yes", "Yes", "Yes", "Yes", "Yes", "Yes", "Yes", "No", "Yes", "Yes", "No", "Yes", "Yes", "No", "No", "Yes")

遇到的问题 #

不确定如何基于日期差处理不定行数的No填充，以及如何在同一客户内跳转至下一个Yes行重复流程，尝试lag/lead函数未得到正确结果。

伪代码尝试 #

if (order == 1) {
  summary == 'TRUE'}  #first order for a customer is always TRUE. 
if (order_result[row_number()] == 'positive') {   #If result is positive
  summary[row_number()+ length(?)] == FALSE} #After a positive result, all subsequent rows in summary for that customer are FALSE.
if (order_result[row_number()] == 'negative') {   #If result is negative, there are 2 options based on time difference between that order date and subsequent order dates. 
  if (diff_time(orderdate[row_number()], orderdate[row_number(?)]) <= 400 {  
    summary[row_number()+ length(?)] == FALSE}   #After a negative result, all subsequent rows under summary within 400 days of that order for that customer are FALSE
  else summary[row_number()+ length(?)] == TRUE}  #Otherwise all subsequent rows in summary over 400 days for that customer are TRUE
if (order_result[row_number()] == 'lost' | order_result[row_number()] == 'return')  {
  summary[row_number() + 1] == TRUE}  #If order result is lost or return, the next order for that customer is true under summary.
}

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