R中用户连续接受事件间的累计求和实现需求

需求说明 #

需要在给定的R数据表中新增一列，计算每个用户连续Accepted事件之间的累计天数，规则如下：

• 按用户分组计算，每次遇到status_name为Accepted的记录时，重置天数计数器
• 每个用户的首次Accepted记录对应值为0
• 后续的Accepted记录不显示0（隐含重置逻辑，天数从该记录开始重新累计）
• 非Accepted记录的天数为与上一次Accepted记录的日期差

示例数据 #

输入数据示例（已过滤掉用户首次Accepted之前的记录）：

# 复现代码生成的输入数据
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)

df_result <- df %>%
  group_by(user) %>%
  # 为每个Accepted周期生成分组ID，每次遇到Accepted则分组ID递增
  mutate(accepted_group = cumsum(status_name == "Accepted")) %>%
  group_by(user, accepted_group) %>%
  # 计算当前日期与组内首个日期（即该周期的Accepted日期）的天数差
  mutate(days_since_last_accepted = as.integer(invitationDate - first(invitationDate))) %>%
  # 仅保留每个周期首个Accepted的0值，后续Accepted记录设为NA（隐含重置）
  mutate(days_since_last_accepted = ifelse(status_name == "Accepted" & row_number() != 1, NA, days_since_last_accepted)) %>%
  ungroup() %>%
  select(-accepted_group)

# 查看结果
print(df_result)

运行结果 #

# A tibble: 6 × 4
  user  status_name invitationDate days_since_last_accepted
  <fct> <fct>       <date>                     <int>
1 1     Accepted    2021-09-09                     0
2 1     Declined    2021-09-10                     1
3 1     Accepted    2021-09-30                    NA
4 4     Accepted    2021-11-10                     0
5 1     Accepted    2021-11-22                    NA
6 4     Declined    2021-11-29                    19

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