在R中按设施类型创建多时段排班统计汇总表格

一、生成目标汇总表格 #

通过tidyverse工具链完成数据转换与统计，直接输出符合要求的汇总表格：

library(tidyverse)

# 原始数据
Name <- c("Nick", "Bill", "Maria", "Ben", "Tina")
Type <- c("general", "specialized", "er", "specialized", "general")
MondayDay <- c("yes", "yes", "yes", "yes", "yes")
MondayNight <- c("no", "no", "yes", "no", "no")
TuesdayDay <- c("no", "yes", "yes", "yes", "yes")
TuesdayNight <- c("no", "no", "yes", "no", "no")
WednesdayDay <- c("no", "no", "no", "yes", "no")
WednesdayNight <- c("no", "yes", "yes", "yes", "no")

df <- data.frame(Name, Type, MondayDay, MondayNight, TuesdayDay, TuesdayNight, WednesdayDay, WednesdayNight)

# 宽转长并统一时段中文名称
df_long <- df %>%
  pivot_longer(cols = -c(Name, Type),
               names_to = "时段",
               values_to = "值班状态") %>%
  mutate(
    时段 = recode(时段,
                  "MondayDay" = "周一白天",
                  "MondayNight" = "周一夜间",
                  "TuesdayDay" = "周二白天",
                  "TuesdayNight" = "周二夜间",
                  "WednesdayDay" = "周三白天",
                  "WednesdayNight" = "周三夜间"),
    值班数值 = as.integer(值班状态 == "yes")
  )

# 统计值班数与占比，转回宽格式并拼接总数列
summary_df <- df_long %>%
  group_by(Type, 时段) %>%
  summarise(值班数 = sum(值班数值), .groups = "drop") %>%
  group_by(Type) %>%
  mutate(
    类型总数 = n_distinct(df_long$Name[df_long$Type == cur_group()$Type]),
    占比 = paste0(round(值班数/类型总数*100), "%"),
    显示文本 = paste0(值班数, "(", 占比, ")")
  ) %>%
  pivot_wider(id_cols = Type, names_from = 时段, values_from = 显示文本) %>%
  left_join(df %>% count(Type, name = "总数"), by = "Type") %>%
  rename("设施类型" = Type)

# 输出结果
print(summary_df, row.names = FALSE)

运行后输出的表格结构与预期一致：

二、可视化方案 #

如果需要更直观的展示，可使用以下两种可视化方式：

1. 热力图（直观展示占比分布） #

ggplot(summary_df %>% pivot_longer(cols = -c(设施类型, 总数), names_to = "时段", values_to = "显示文本"),
       aes(x = 时段, y = 设施类型)) +
  geom_tile(aes(fill = str_extract(显示文本, "\\d+(?=%)") %>% as.integer()), color = "white") +
  geom_text(aes(label = 显示文本), size = 4) +
  scale_fill_gradient(low = "#f5f5f5", high = "#27ae60", labels = scales::percent_format(scale = 1)) +
  labs(title = "各类型设施值班占比热力图", x = "时段", y = "设施类型", fill = "值班占比") +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

2. 分组柱状图（清晰展示值班数量） #

summary_long <- df_long %>%
  group_by(Type, 时段) %>%
  summarise(值班数 = sum(值班数值), .groups = "drop") %>%
  group_by(Type) %>%
  mutate(占比 = paste0(round(值班数/n_distinct(df_long$Name[df_long$Type == Type])*100), "%"))

ggplot(summary_long, aes(x = 时段, y = 值班数, fill = Type)) +
  geom_bar(stat = "identity", position = position_dodge(width = 0.8)) +
  geom_text(aes(label = paste0(值班数, "(", 占比, ")")), 
            position = position_dodge(width = 0.8), vjust = -0.3, size = 3.5) +
  labs(title = "各类型设施各时段值班数量", x = "时段", y = "值班数量", fill = "设施类型") +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

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