如何用R创建按周统计多项目活跃率与非活跃率的透视表

按周统计每个项目的活跃率和非活跃率（1 - 活跃率），某周未出现的项目对应指标值填NA，最终输出指定格式的数据框。

样本数据集 #

sample <- structure(list(Week  = c(1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2),
                         Project = c("A", "A", "A",  "A",  "B",  "B",  "B",  "B",  "C",  "C",  "C",  "A", "A",   "A" ), 
                         Status= c( "Active","Rescheduled","Active", "Cancelled", "Active", "Cancelled",  "Cancelled",
                                    "Rescheduled",  "Active",  "Active",   "Rescheduled", "Cancelled", "Cancelled", "Active")),
                    .Names = c("Week","Project","Status"),
                    class = "data.frame" , row.names = c(NA, -14L))

查看数据集输出：

> sample
   Week Project      Status
1     1       A      Active
2     1       A Rescheduled
3     1       A      Active
4     1       A   Cancelled
5     1       B      Active
6     1       B   Cancelled
7     1       B   Cancelled
8     1       B Rescheduled
9     2       C      Active
10    2       C      Active
11    2       C Rescheduled
12    2       A   Cancelled
13    2       A   Cancelled
14    2       A      Active

预期输出 #

Week A_active_rate A_Non_active_rate B_active_rate B_Non_active_rate C_active_rate C_Non_active_rate
1    1          0.50              0.50          0.25              0.75            NA                NA
2    2          0.33              0.67            NA                NA          0.67              0.33

解决方案 #

使用dplyr和tidyr包实现，代码如下：

library(dplyr)
library(tidyr)

result <- sample %>%
  # 按周和项目分组，计算活跃率与非活跃率
  group_by(Week, Project) %>%
  summarise(
    active_rate = mean(Status == "Active"),
    Non_active_rate = 1 - active_rate,
    .groups = "drop"
  ) %>%
  # 转换为宽格式，缺失项目填充NA
  pivot_wider(
    names_from = Project,
    values_from = c(active_rate, Non_active_rate),
    names_glue = "{Project}_{.value}",
    values_fill = list(active_rate = NA, Non_active_rate = NA)
  ) %>%
  # 数值保留两位小数
  mutate(across(where(is.numeric), ~round(., 2))) %>%
  # 调整列顺序匹配预期输出
  select(
    Week,
    A_active_rate, A_Non_active_rate,
    B_active_rate, B_Non_active_rate,
    C_active_rate, C_Non_active_rate
  )

print(result)

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