在R中按User和Video分组创建Value新列的实现方案

问题需求 #

基于给定数据表新增Value列，规则如下：

• 按User和Video分组，每组第一行Value赋值为0；
• 组内其余行，根据之前所有行的Happy值判断：
  • 之前仅出现No → 赋值1
  • 之前仅出现Yes → 赋值2
  • 之前同时出现No和Yes → 赋值3

R代码实现 #

library(tidyverse)

# 原始数据
df <- tibble(
  User = c("a", "a", "a", "a", "a", "a", "a", "a", "a", "b", "b", "b", "b", "b", "b"),
  Video = c("V1", "V1", "V1", "V2", "V3", "V3", "V4", "V4", "V4", "V1", "V1", "V5", "V5", "V5", "V5"),
  Comment = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15),
  Happy = c("No", "Yes", "No", "Yes", "No", "No", "Yes", "No", "No", "No", "No", "Yes", "Yes", "No", "No")
)

# 生成目标结果
df_result <- df %>%
  group_by(User, Video) %>%
  mutate(
    # 累积记录当前行之前出现的所有唯一Happy值
    prev_happy = accumulate(Happy, ~ if(.y %in% .x) .x else c(.x, .y), .init = character(0)) %>% tail(-1),
    # 根据规则赋值Value
    Value = case_when(
      row_number() == 1 ~ 0,
      all(prev_happy == "No") ~ 1,
      all(prev_happy == "Yes") ~ 2,
      TRUE ~ 3
    )
  ) %>%
  select(-prev_happy) %>%
  ungroup()

结果展示 #

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