Kable/KableExtra表格：隐藏低价行且保留汇总均值+合并单行子集

核心需求实现说明 #

1. 隐藏低价格明细行但保留均值计算参与度：所有层级的均值计算基于完整的diamonds数据集，确保price<3000的原始数据纳入cut、color层级的均值统计；仅在clarity层级的明细行中过滤掉均值<3000的条目。
2. 合并单行子集与超集：统计各层级的子集数量，若某超集下仅含1个子集，将子集的名称和价格直接合并到超集行中，避免冗余行显示。

完整代码 #

library(tidyverse)
library(knitr)
library(kableExtra)

df <- diamonds

# 1. 计算各层级均值（基于全量数据）
# clarity层级：过滤掉均值<3000的行
clarity_level <- df %>% 
  group_by(cut, color, clarity) %>% 
  summarize(price = mean(price, na.rm = TRUE), .groups = "drop") %>%
  filter(price >= 3000) %>%
  mutate(level = "clarity")

# color层级
color_level <- df %>% 
  group_by(cut, color) %>% 
  summarize(price = mean(price, na.rm = TRUE), .groups = "drop") %>%
  mutate(clarity = "", level = "color")

# cut层级
cut_level <- df %>% 
  group_by(cut) %>% 
  summarize(price = mean(price, na.rm = TRUE), .groups = "drop") %>%
  mutate(color = "", clarity = "", level = "cut")

# 合并数据并添加排序键
combined <- bind_rows(clarity_level, color_level, cut_level) %>%
  mutate(sort_key = case_when(
    level == "cut" ~ 1,
    level == "color" ~ 2,
    level == "clarity" ~ 3
  )) %>%
  arrange(cut, color, clarity, sort_key)

# 2. 统计子集数量，处理单行合并
cut_color_count <- combined %>% filter(level == "color") %>% count(cut, name = "color_count")
color_clarity_count <- combined %>% filter(level == "clarity") %>% count(cut, color, name = "clarity_count")

combined_with_count <- combined %>%
  left_join(cut_color_count, by = "cut") %>%
  left_join(color_clarity_count, by = c("cut", "color")) %>%
  mutate(
    # 合并cut与唯一color
    cut_merged = ifelse(level == "cut" & color_count == 1, paste0(cut, " (", color[level == "color"], ")"), cut),
    # 合并color与唯一clarity
    color_merged = ifelse(level == "color" & clarity_count == 1, paste0(color, " (", clarity[level == "clarity"], ")"), color),
    # 合并价格显示
    price_merged = case_when(
      level == "cut" & color_count == 1 ~ paste0(round(price, 2), " / ", round(price[level == "color"], 2)),
      level == "color" & clarity_count == 1 ~ paste0(round(price, 2), " / ", round(price[level == "clarity"], 2)),
      TRUE ~ as.character(round(price, 2))
    )
  )

# 过滤被合并的冗余行，整理最终表格
final_table <- combined_with_count %>%
  filter(
    !(level == "color" & color_count == 1),
    !(level == "clarity" & clarity_count == 1)
  ) %>%
  select(cut = cut_merged, color = color_merged, clarity, price = price_merged) %>%
  mutate(across(c(color, clarity), ~ ifelse(. == "", NA, .)))

# 生成格式化表格
final_table %>%
  kbl(
    caption = "钻石价格分层均值表（隐藏价格<3000的明细行）",
    longtable = TRUE,
    na.strings = ""
  ) %>%
  kable_paper(full_width = FALSE) %>%
  column_spec(1, bold = TRUE) %>%
  column_spec(3, italic = TRUE) %>%
  collapse_rows(columns = 1:2, valign = "top") %>%
  add_header_above(header = c("钻石信息" = 4))

关键步骤解析 #

• 均值计算逻辑：所有层级的均值均基于原始全量数据计算，保证cut、color层级的均值不受明细行过滤影响；仅在clarity层级执行过滤，隐藏低价格条目。
• 单行合并逻辑：通过统计各层级子集数量，识别出仅含1个子项的超集，将子项的名称和价格拼接至超集行，同时移除被合并的子项行，实现紧凑显示。

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