基于R语言计算各地点成功率并生成相对表现对比表格

1. 数据准备 #

先定义原始数据框：

df <- data.frame(PersonID = c(1:20),
                 Location = c("B","A","D","C","A","D","C","D","A","D","B","A","D","C","A","D","C","D","A","D"),
                 Success = c("yes","no","yes",NA,"yes","no","no","yes",NA,"yes","no","yes",NA,"yes","no","no","yes",NA,"yes","no"))

2. 统计指标计算 #

用dplyr包完成核心统计，步骤包括：

• 按地点分组，统计有效尝试次数（排除Success为NA的记录）、成功次数（Success为"yes"的记录）
• 计算各地点的成功率（成功次数/有效尝试次数）
• 计算其他地点平均成功率：排除当前地点后，剩余所有地点的成功率平均值
• 计算成功率差值：当前地点成功率减去其他地点平均成功率

代码实现：

library(dplyr)

# 基础统计计算
location_stats <- df %>%
  filter(!is.na(Success)) %>%
  group_by(Location) %>%
  summarise(
    有效尝试次数 = n(),
    成功次数 = sum(Success == "yes"),
    成功率 = 成功次数 / 有效尝试次数
  ) %>%
  ungroup()

# 计算跨地点对比指标
location_stats <- location_stats %>%
  rowwise() %>%
  mutate(
    其他地点平均成功率 = mean(.[.$Location != Location, "成功率"]),
    成功率差值 = 成功率 - 其他地点平均成功率
  ) %>%
  ungroup()

3. 生成样式化表格 #

用gt包生成美观的对比表格，包含百分比格式化、条件高亮等样式：

library(gt)

location_stats %>%
  gt() %>%
  # 设置标题与副标题
  tab_header(
    title = md("**各地点成功率对比分析**"),
    subtitle = "含与其他地点平均成功率的差值"
  ) %>%
  # 格式化百分比列，保留1位小数
  fmt_percent(
    columns = c(成功率, 其他地点平均成功率, 成功率差值),
    decimals = 1
  ) %>%
  # 差值列条件着色：正值标绿，负值标红
  data_color(
    columns = 成功率差值,
    colors = scales::col_numeric(
      palette = c("#d62728", "#ffffff", "#2ca02c"),
      domain = c(-0.3, 0.3)
    )
  ) %>%
  # 调整列宽与对齐方式
  cols_width(
    Location = px(80),
    everything() = px(120)
  ) %>%
  cols_align(align = "center") %>%
  # 添加表格边框样式
  tab_options(
    table.border.top.color = "black",
    table.border.bottom.color = "black",
    column_labels.border.top.color = "black",
    column_labels.border.bottom.color = "black"
  )

运行代码后会生成样式规整的表格，直观展示各地点的尝试情况、成功率及与其他地点的差异。

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