基于User和Video分组创建含Comment_type的新数据表

按User和Video分组处理评论数据，生成包含Comment_type字段的汇总表，规则如下：

• 若组内仅存在一种类型的评论（Happy列全为"Yes"或全为"No"），Comment_type标记为None
• 若组内同时存在两种类型的评论，取组内首条评论的Happy值：
  • 首条为"Yes"则标记Happy
  • 首条为"No"则标记Sad

原始数据表 #

目标数据表 #

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")
)

# 按规则生成目标表
result_df <- df %>%
  group_by(User, Video) %>%
  mutate(
    # 判断组内是否存在两种不同情绪类型
    has_both_types = n_distinct(Happy) == 2,
    # 获取组内第一条评论的情绪值
    first_comment_happy = first(Happy)
  ) %>%
  # 根据规则生成Comment_type
  mutate(Comment_type = case_when(
    !has_both_types ~ "None",
    first_comment_happy == "Yes" ~ "Happy",
    first_comment_happy == "No" ~ "Sad"
  )) %>%
  # 保留每组唯一记录
  distinct(User, Video, Comment_type) %>%
  ungroup()

# 输出结果
print(result_df)

代码说明 #

1. 分组：通过group_by(User, Video)按用户和视频维度分组
2. 判断类型多样性：用n_distinct(Happy)统计组内不同情绪的数量，判断是否同时存在两种类型
3. 获取首条评论情绪：first(Happy)直接提取组内第一条记录的情绪值
4. 生成评论类型：case_when按规则映射Comment_type字段
5. 去重汇总：distinct保留每组唯一的用户-视频-评论类型组合，取消分组后得到目标表

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