R语言按组条件过滤与数据转换：保留首个NotActive行并新增状态与实例列

需求规则 #

需要对数据集按以下规则处理：

• 按UserID分组处理Cluster列：如果值为NotActive，仅保留连续出现的第一行，丢弃后续连续的NotActive行，直至出现非NotActive值
• 新增Status列：每段活动序列的第一个值标记为Start，序列后第一个NotActive值标记为Complete，中间所有值标记为Ongoing
• 新增Instance列：每个活动段内的行从Start对应的1开始顺序编号，直至Complete对应最大序号

示例原始数据 #

rawdata<-structure(list(DateTime = c("20/02/2021 13:00", "20/02/2021 14:00", 
                                     "20/02/2021 15:00", "20/02/2021 16:00", "20/02/2021 17:00", "20/02/2021 18:00", 
                                     "20/02/2021 19:00", "20/02/2021 20:00", "20/02/2021 21:00", "20/02/2021 22:00", 
                                     "20/02/2021 23:00", "21/02/2021 00:00", "01/03/2021 00:00", "01/03/2021 01:00", 
                                     "01/03/2021 02:00", "01/03/2021 03:00", "01/03/2021 04:00", "01/03/2021 05:00", 
                                     "01/03/2021 06:00", "01/03/2021 07:00", "01/03/2021 08:00", "01/03/2021 09:00", 
                                     "01/03/2021 10:00", "01/03/2021 11:00", "01/03/2021 12:00", "01/03/2021 13:00", 
                                     "20/02/2021 13:00", "20/02/2021 14:00", "20/02/2021 15:00", "20/02/2021 16:00", 
                                     "20/02/2021 17:00", "20/02/2021 18:00", "20/02/2021 19:00", "20/02/2021 20:00", 
                                     "20/02/2021 21:00", "20/02/2021 22:00"), Cluster = c("Cluster 3", 
                                                                                          "Cluster 3", "Cluster 3", "Cluster 3", "NotActive", "NotActive", 
                                                                                          "NotActive", "Cluster 2", "Cluster 1", "Cluster 3", "NotActive", 
                                                                                          "NotActive", "NotActive", "Cluster 5", "Cluster 5", "Cluster 4", 
                                                                                          "NotActive", "NotActive", "NotActive", "NotActive", "Cluster 2", 
                                                                                          "Cluster 2", "Cluster 3", "NotActive", "NotActive", "NotActive", 
                                                                                          "NotActive", "NotActive", "NotActive", "NotActive", "NotActive", 
                                                                                          "NotActive", "Cluster 1", "Cluster 2", "NotActive", "NotActive"
                                     ), UserID = c("AAA", "AAA", "AAA", "AAA", "AAA", "AAA", "AAA", 
                                                   "AAA", "AAA", "AAA", "AAA", "AAA", "AAA", "BBB", "BBB", "BBB", 
                                                   "BBB", "BBB", "BBB", "BBB", "BBB", "BBB", "BBB", "BBB", "BBB", 
                                                   "BBB", "DDD", "DDD", "DDD", "DDD", "DDD", "DDD", "DDD", "DDD", 
                                                   "DDD", "DDD")), class = "data.frame", row.names = c(NA, -36L))

实现代码（基于tidyverse生态） #

# 加载依赖包
library(dplyr)
library(lubridate)

# 数据处理流程
processed_data <- rawdata %>%
  # 转换DateTime为标准时间格式
  mutate(DateTime = dmy_hm(DateTime)) %>%
  # 按用户ID分组
  group_by(UserID) %>%
  # 标记需要删除的连续重复NotActive行
  mutate(drop_flag = Cluster == "NotActive" & lag(Cluster, default = "") == "NotActive") %>%
  filter(!drop_flag) %>%
  # 过滤掉用户数据开头连续的无效NotActive段
  filter(!cumall(Cluster == "NotActive")) %>%
  # 生成活动段ID：每次从NotActive切换到活跃状态时段ID+1
  mutate(segment_id = cumsum(lag(Cluster, default = "NotActive") == "NotActive" & Cluster != "NotActive")) %>%
  # 按用户+活动段分组
  group_by(UserID, segment_id) %>%
  # 生成要求的Status和Instance字段
  mutate(
    Instance = row_number(),
    Status = case_when(
      row_number() == 1 ~ "Start",
      Cluster == "NotActive" ~ "Complete",
      TRUE ~ "Ongoing"
    )
  ) %>%
  # 清理辅助字段，输出结果
  ungroup() %>%
  select(-drop_flag, -segment_id)

结果说明 #

运行上述代码得到的processed_data与提供的期望输出完全一致，字段顺序、数值、标记规则均匹配要求。

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