如何用dplyr按条件汇总列值并生成新行（移除原低价值行）

需要使用dplyr实现以下需求：

• 仅对value列数值小于10的行，汇总其value和percentage列的值
• 将汇总结果作为名为cheap_stuff的新item行
• 移除原低价值（value<10）的行

数据集如下：

df <- data.frame(group=c(rep("A",4), rep("B",4), rep("C",4), rep("D",4)),
                 value=c(1, 23, 15, 5,  3,  45, 7,  21, 4,  8,  26, 30, 3,  9,  37, 68),
                 percentage=c(2.27, 52.27,  34.09,  11.36   ,3.95   ,59.21  ,9.21   ,27.63  ,5.88   ,11.76  ,38.24  ,44.12  ,2.56   ,7.69, 31.62, 58.12),
                 item=c("cheap1","expensive1"   ,"expensive2",  "cheap2",
                 "cheap1",  "expensive1","cheap2","expensive2",
                 "cheap1","cheap2","expensive1","expensive2",
                 "cheap1","cheap2","expensive1","expensive2"))

期望输出：

group value percentage        item
1      A     6      13.64 cheap_stuff
2      A    23      52.27  expensive1
3      A    15      34.09  expensive2
4      B    10      13.16 cheap_stuff
5      B    45      59.21  expensive1
6      B    21      27.63  expensive2
7      C    12      17.65 cheap_stuff
8      C    26      38.24  expensive1
9      C    30      44.12  expensive2
10     D    12      10.26 cheap_stuff
11     D    37      31.62  expensive1
12     D    68      58.12  expensive2

尝试的错误代码：

library(dplyr)
df%>%
  group_by(group) %>%
  mutate(item= replace(item, which(value <10),"cheap_stuff")) %>%
  mutate(value = sum(value[value < 10]))

得到的错误结果：

# A tibble: 16 × 4
# Groups:   group [4]
   group value percentage item       
   <chr> <dbl>      <dbl> <chr>      
 1 A         6       2.27 cheap_stuff
 2 A         6      52.3  expensive1 
 3 A         6      34.1  expensive2 
 4 A         6      11.4  cheap_stuff
 5 B        10       3.95 cheap_stuff
 6 B        10      59.2  expensive1 
 7 B        10       9.21 cheap_stuff
 8 B        10      27.6  expensive2 
 9 C        12       5.88 cheap_stuff
10 C        12      11.8  cheap_stuff
11 C        12      38.2  expensive1 
12 C        12      44.1  expensive2 
13 D        12       2.56 cheap_stuff
14 D        12       7.69 cheap_stuff
15 D        12      31.6  expensive1 
16 D        12      58.1  expensive2 

可以采用分离-汇总-合并的思路，用dplyr实现如下：

library(dplyr)

# 1. 提取高价值行（保留原数据）
high_value_rows <- df %>% filter(value >= 10)

# 2. 对低价值行分组汇总，生成cheap_stuff行
cheap_summary <- df %>%
  filter(value < 10) %>%
  group_by(group) %>%
  summarise(
    value = sum(value),
    percentage = round(sum(percentage), 2), # 保留两位小数对齐期望输出
    item = "cheap_stuff"
  ) %>%
  ungroup()

# 3. 合并两类数据并按group排序，让cheap_stuff排在每组最前
final_result <- bind_rows(cheap_summary, high_value_rows) %>%
  arrange(group, item != "cheap_stuff")

# 查看结果
print(final_result, n = Inf)

也可以用更紧凑的链式分组处理写法：

library(dplyr)

final_result <- df %>%
  group_by(group) %>%
  group_modify(function(sub_data, group_key) {
    # 拆分低/高价值行
    cheap <- sub_data %>% filter(value < 10)
    high <- sub_data %>% filter(value >= 10)
    
    # 生成汇总行并合并
    if (nrow(cheap) > 0) {
      summary_row <- tibble(
        group = group_key$group,
        value = sum(cheap$value),
        percentage = round(sum(cheap$percentage), 2),
        item = "cheap_stuff"
      )
      bind_rows(summary_row, high)
    } else {
      # 无低价值行时直接返回高价值行
      high
    }
  }) %>%
  ungroup() %>%
  arrange(group)

print(final_result, n = Inf)

你之前的代码存在三个核心问题：

1. 使用mutate会保留所有行，无法移除原低价值行
2. mutate(value = sum(value[value < 10]))会把整个分组的value列全部替换成低价值行的总和，直接覆盖了高价值行的数值
3. 仅修改了item列，未对percentage列做汇总处理，也没有过滤掉原低价值行

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