在R语言中计算事件间隔天数并标记最近接受记录

针对给定数据表，需完成两项操作：

• 用布尔值mostRecent == TRUE/FALSE标记每条记录是否为该用户最近一次“Accepted”状态的记录
• 计算该用户最近一次“Accepted”记录日期至当前日期的天数

示例数据表 #

user  status_name invitationDate
  <fct> <fct>       <date>        
1 1     Accepted    2021-09-09    
2 1     Declined    2021-09-10    
3 1     Accepted    2021-09-30    
4 4     Accepted    2021-11-10    
5 1     Accepted    2021-11-22    
6 4     Declined    2021-11-29    

数据表重建代码（R） #

df <- tribble(
  ~user, ~status_name, ~invitationDate,
  "1", "Declined", "2021-07-13",
  "4", "Declined", "2021-07-31",
  "1", "Accepted", "2021-09-09",
  "1", "Declined", "2021-09-10",
  "1", "Accepted", "2021-09-30",
  "4", "Accepted", "2021-11-10",
  "3", "Declined", "2021-11-12",
  "2", "Declined", "2021-11-18",
  "1", "Accepted", "2021-11-22",
  "4", "Declined", "2021-11-29"
) %>%
  mutate(
    user = as.factor(user),
    status_name = as.factor(status_name),
    invitationDate = as.Date(invitationDate, format = "%Y-%m-%d")
  ) %>%
  group_by(user) %>%
  mutate(cumsum = cumsum(status_name == "Accepted")) %>%
  filter(cumsum > 0) %>%
  select(-cumsum)

实现代码 #

使用dplyr分组处理，完成需求：

library(dplyr)

result_df <- df %>%
  group_by(user) %>%
  mutate(
    # 获取当前用户最近一次Accepted的日期
    max_accepted_date = max(invitationDate[status_name == "Accepted"], na.rm = TRUE),
    # 标记是否为最近一次Accepted记录
    mostRecent = status_name == "Accepted" & invitationDate == max_accepted_date,
    # 计算最近一次Accepted到当前日期的天数
    days_since_last_accepted = as.integer(Sys.Date() - max_accepted_date)
  ) %>%
  ungroup()

结果说明 #

• mostRecent：仅该用户最近一次的“Accepted”记录值为TRUE，其余均为FALSE
• days_since_last_accepted：每个用户的所有记录均显示其最近一次“Accepted”日期到当前系统日期的间隔天数

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