如何在R数据框中合并重复字符串并求和数值、移除指定字符串？

我需要对数据框中的字符串列进行处理：合并相同的字符串对象并对其附带的数值求和，同时移除所有"HQ"字符串。以下是示例数据及期望输出：

示例数据框 #

branch <- c("OL", "CA", "PL", "OR", "FL")
perf <- c("Mattheu (12), Jessica (32), Mattheu (22), Tom (10), HQ", 
          "Tobias (13), Kurt (22), Mathias (44), HQ, Tobias (55)",
          "Tom (30), HQ, Giti (88), Patel (54), Tom (12), Tom (10)",
          "Harry (1), Potter (32), Harry (2)",
          "Timothy (3), HQ, Sara (44), HQ"
          )
performance <- data.frame(branch, perf)

# 查看原数据
print(performance)

原数据输出：

branch                                                    perf
1     OL  Mattheu (12), Jessica (32), Mattheu (22), Tom (10), HQ
2     CA   Tobias (13), Kurt (22), Mathias (44), HQ, Tobias (55)
3     PL Tom (30), HQ, Giti (88), Patel (54), Tom (12), Tom (10)
4     OR                       Harry (1), Potter (32), Harry (2)
5     FL                          Timothy (3), HQ, Sara (44), HQ

期望输出 #

处理后perf列的结果应为：

[1] "Mattheu (34), Jessica (32), Tom (10)"
[2] "Tobias (68), Kurt (22), Mathias (44)"
[3] "Tom (52), Giti (88), Patel (54)"     
[4] "Harry (3), Potter (32)"              
[5] "Timothy (3), Sara (44)"

解决方案 #

方法一：使用tidyverse工具链（dplyr + stringr + tidyr） #

逻辑清晰，适合处理复杂数据变换：

library(dplyr)
library(stringr)
library(tidyr)

result <- performance %>%
  # 拆分每行字符串为单个条目
  mutate(perf_items = str_split(perf, ", ")) %>%
  unnest(perf_items) %>%
  # 过滤所有"HQ"条目
  filter(perf_items != "HQ") %>%
  # 提取姓名和对应数值
  mutate(
    name = str_trim(str_extract(perf_items, "^[^(]+")), # 去除姓名前后空格
    value = as.integer(str_extract(perf_items, "\\d+"))
  ) %>%
  # 按分支和姓名分组求和
  group_by(branch, name) %>%
  summarise(total = sum(value), .groups = "drop") %>%
  # 组合成"姓名 (总和)"格式
  mutate(combined_entry = str_glue("{name} ({total})")) %>%
  # 按分支合并为目标字符串
  group_by(branch) %>%
  summarise(perf_processed = str_c(combined_entry, collapse = ", ")) %>%
  # 保留原数据分支顺序
  right_join(performance, by = "branch") %>%
  select(branch, perf_processed)

# 查看处理结果
print(result$perf_processed)

方法二：Base R实现 #

无需加载额外包，轻量简洁：

# 定义单行字符串处理函数
process_single_row <- function(perf_str) {
  # 拆分字符串并过滤HQ
  items <- strsplit(perf_str, ", ")[[1]]
  items <- items[items != "HQ"]
  
  # 提取姓名和数值
  names <- sub("\\s*\\(\\d+\\)", "", items)
  values <- as.integer(sub(".*\\((\\d+)\\)", "\\1", items))
  
  # 按姓名求和
  sum_values <- tapply(values, names, sum)
  
  # 重组为目标格式字符串
  paste0(names(sum_values), " (", sum_values, ")", collapse = ", ")
}

# 应用函数到每行
performance$perf_processed <- sapply(performance$perf, process_single_row)

# 查看结果
print(performance$perf_processed)

两种方法均可得到期望输出，可根据个人习惯选择。

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