R语言统计字符串实例并聚合：解决计数、排序及大小写问题

原始数据 #

aa <- data.frame(
  group = c("asdf", "asdf", "asdf", "asdf", "asdf", "asdf", "asdf", "asdf", "asdf", "asdf", "asdf", "asdf", "asdf", "asdf", "fdsa", "fdsa", "fdsa", "fdsa", "fdsa", "fdsa", "fdsa", "fdsa", "fdsa", "fdsa", "fdsa", "fdsa", "fdsa", "fdsa", "fdsa", "fdsa", "fdsa", "fdsa", "fdsa", "fdsa", "fdsa", "fdsa", "fdsa", "fdsa", "fdsa"),
  id = c(1, 2, 2, 2, 3, 3, 3, 4, 5, 6, 7, 8, 9, 10, 1, 2, 3, 4, 5, 6, 7, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 9, 9, 9, 9, 9, 9, 9, 10),  
  Fr_1 = c(NA, "Apple", "Apple", "Apple", "Blueberry", "Blueberry", "Blueberry", NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, "Mango", "mango", "Mango", "Mango", "Mango", "Mango", "mango", "Mango", "Mango", "Mango", "Watermelon", "Watermelon", "Watermelon", "Watermelon", "Watermelon", "Watermelon", "Watermelon", NA),
  Fr_2 = c("\bBanana\b", "Apple", "Apple", "Apple", "Blueberry", "Blueberry", "Blueberry", "Orange", "Strawberry", "Grapes", "Pineapple", "Mango", "Watermelon", "Kiwi", "\bBanana\b", "Apple", "Blueberry", "Orange", "Strawberry", "Grapes", "Pineapple", "Mango", "Mango", "Mango", "Mango", "Mango", "Mango", "Mango", "Mango", "Mango", "Mango", "Watermelon", "Watermelon", "Watermelon", "Watermelon", "Watermelon", "Watermelon", "Watermelon", "Kiwi")
)

需求说明 #

• 新增cnt列，统计Fr_1中元素的实例数量：单词相同仅大小写不同的（如Mango和mango）需合并计数，同时保留原单元格的大小写显示
• NA对应的cnt值设为0
• id保持1、2、3…的自然顺序，最终数据按group和id排序（参考预期结果结构）

尝试的代码及问题 #

尝试代码：

cc <- aa |>
  dplyr::group_by(group, Fr_1, Fr_2, id) |>
  dplyr::count(Fr_1, group, name = "cnt", sort = F)

遇到的问题：

• id未按自然顺序排列
• NA被计为1（需求应为0）
• 大小写不同的相同单词（如Mango和mango）计数未合并，无法统一显示正确的总数

思路 #

1. 先对原数据按group和id去重，匹配预期结果的单行id结构
2. 创建统一大小写的辅助列，实现大小写不敏感的分组计数
3. 按group和辅助列分组统计总数，关联回原数据
4. 将NA对应的cnt替换为0，最后按group和id排序

完整代码 #

library(dplyr)

result <- aa |>
  # 按group、id去重，保留每个id的唯一行
  distinct(group, id, .keep_all = TRUE) |>
  # 创建统一小写的辅助列，用于大小写不敏感的分组
  mutate(Fr_1_lower = tolower(Fr_1)) |>
  # 按group和小写列分组，统计每组的总数量
  group_by(group, Fr_1_lower) |>
  mutate(cnt = n()) |>
  ungroup() |>
  # 将NA对应的cnt设为0
  mutate(cnt = ifelse(is.na(Fr_1), 0, cnt)) |>
  # 按group和id自然排序
  arrange(group, id) |>
  # 移除辅助列，匹配预期结果的列结构
  select(-Fr_1_lower)

# 查看结果
print(result)

效果验证 #

• id按1、2、3…自然顺序排列，且按group分组排序
• NA对应的cnt值为0
• Mango和mango因统一小写后分组，计数合并为10，同时保留原单元格的大小写显示
• 结果结构与预期的bb数据一致

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