如何在R-Studio中使用dplyr将长格式数据转换为指定宽格式？

以下是实现需求的R代码，结合dplyr和tidyr完成数据转换：

library(dplyr)
library(tidyr)

# 示例数据（替换为你的实际数据）
df <- tibble(
  ID = c(1,1,1,1,1,1,2,2,2,2,2,2),
  risk = c("A","A","A","B","B","B","B","B","B","C","C","C"),
  severity = c("green","amber","amber","amber","amber","amber","green","green","amber","green","red","red")
)

# 定义严重程度的逻辑顺序（green < amber < red），确保min/max计算正确
severity_order <- c("green", "amber", "red")
df <- df %>% 
  mutate(severity = factor(severity, levels = severity_order, ordered = TRUE))

# 按ID和risk分组统计：数量、最小/最大严重程度
summary_data <- df %>%
  group_by(ID, risk) %>%
  summarize(
    count = n(),
    min_sev = as.character(min(severity)),
    max_sev = as.character(max(severity)),
    .groups = "drop"
  )

# 补全每个ID缺失的risk类别，填充0和NA
summary_data <- summary_data %>%
  complete(ID, risk = c("A", "B", "C"), fill = list(count = 0)) %>%
  mutate(
    min_sev = ifelse(count == 0, NA, min_sev),
    max_sev = ifelse(count == 0, NA, max_sev)
  )

# 转换为宽格式并调整列顺序和命名
final_data <- summary_data %>%
  pivot_wider(
    id_cols = ID,
    names_from = risk,
    values_from = c(count, min_sev, max_sev),
    names_glue = "{.value}_{risk}"
  ) %>%
  select(
    ID,
    count_A, min_sev_A, max_sev_A,
    count_B, min_sev_B, max_sev_B,
    count_C, min_sev_C, max_sev_C
  ) %>%
  rename(
    A = count_A,
    "min_sev (A)" = min_sev_A,
    "max_sev (A)" = max_sev_A,
    B = count_B,
    "min_sev (B)" = min_sev_B,
    "max_sev (B)" = max_sev_B,
    C = count_C,
    "min_sev (C)" = min_sev_C,
    "max_sev (C)" = max_sev_C
  )

# 查看结果
final_data

代码说明 #

1. 定义严重程度顺序：将severity转为有序因子，避免按字母顺序计算min/max（比如"amber"字母顺序早于"green"，但逻辑上严重程度更低）。
2. 分组统计：对每个ID和risk组合，计算条目数量、最小和最大严重程度。
3. 补全缺失类别：确保每个ID都包含A、B、C三类risk，缺失的类别填充0和NA。
4. 转换宽格式：将长数据转为宽数据，调整列顺序和命名（R不允许重复列名，因此给min_sev/max_sev添加risk后缀区分）。

输出结果与需求结构一致，数据值完全匹配：

• ID=1：A=3，min_sev=green，max_sev=amber；B=3，min_sev/max_sev=amber；C=0，min_sev/max_sev=NA
• ID=2：A=0，min_sev/max_sev=NA；B=3，min_sev=green，max_sev=amber；C=3，min_sev=green，max_sev=red

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