基于另一列值统计多列特定值出现次数的R语言实现

方法一：长格式转换法（适合多主题场景） #

先处理数据中的空字符串（转为NA避免干扰统计），再通过格式转换实现分组汇总：

library(dplyr)
library(tidyr)

# 预处理：将空字符串转为NA
df_clean <- df_test %>%
  mutate(across(c1:c3, ~ ifelse(.x == "", NA, .x)))

# 生成汇总结果
result <- df_clean %>%
  # 宽表转长表，将所有主题列合并为一列
  pivot_longer(cols = c1:c3, names_to = "column", values_to = "theme") %>%
  # 筛选出需要统计的三个主题
  filter(theme %in% c("Traffic", "Social", "Health")) %>%
  # 按姓名+主题分组，统计出现次数
  group_by(Names, theme) %>%
  summarize(count = n(), .groups = "drop") %>%
  # 长表转宽表，补全未出现主题的0值
  pivot_wider(names_from = theme, values_from = count, values_fill = 0) %>%
  # 保持姓名顺序与预期一致
  arrange(factor(Names, levels = c("Benjamin Blue", "Sarah Red", "Mark Black", "Leonie White")))

print(result)

方法二：分组直接统计法（适合少主题场景） #

无需转换格式，直接分组后用across批量统计：

library(dplyr)

# 预处理：将空字符串转为NA
df_clean <- df_test %>%
  mutate(across(c1:c3, ~ ifelse(.x == "", NA, .x)))

# 直接按姓名分组统计
result <- df_clean %>%
  group_by(Names) %>%
  summarize(
    Traffic = sum(across(c1:c3, ~ .x == "Traffic"), na.rm = TRUE),
    Social = sum(across(c1:c3, ~ .x == "Social"), na.rm = TRUE),
    Health = sum(across(c1:c3, ~ .x == "Health"), na.rm = TRUE)
  ) %>%
  arrange(factor(Names, levels = c("Benjamin Blue", "Sarah Red", "Mark Black", "Leonie White")))

print(result)

原代码问题说明 #

1. 未按姓名汇总：原代码仅按行统计单条记录的主题次数，没有对同一姓名的多行数据合并计算
2. 笔误错误：第一个mutate中误将Traffic的统计条件写成了== "Social"
3. 未合并结果：三次分开处理统计逻辑，没有将三个主题的结果整合到同一个数据框中

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