为特定观测值的计数创建按组的汇总行

Create summary row by group for the count of a particular observation

提问人：PhDavey 提问时间：11/16/2023 更新时间：11/16/2023 访问量：26

问：

我有一个看起来像这样的数据集

df1 <- tibble(ha_name = c("Interior", "Interior", "Interior", "Interior", "Interior", "Interior",
                          "Fraser", "Fraser", "Fraser", "Fraser", "Fraser", "Fraser",
                          "Northern", "Northern", "Northern", "Northern", "Northern", "Northern"),
              facility = c("Facility 1", "Facility 2", "Facility 3", "Facility 4", "Facility 5", "Facility 6",
                           "Facility 1", "Facility 2", "Facility 3", "Facility 4", "Facility 5", "Facility 6",
                           "Facility 1", "Facility 2", "Facility 3", "Facility 4", "Facility 5", "Facility 6"),
              pre_mandate = c("Planning", "Implementation", "Planning", "Planning", "Implementation", "Planning",
                              "Implementation", "Planning", "Planning", "Planning", "Implementation", "Implementation",
                              "Planning", "Planning", "Planning", "Implementation", "Planning", "Planning"),
              current = c("Planning", "Implementation", "Implementation", "Planning", "Implementation", "Planning",
                          "Implementation", "Planning", "Planning", "Implementation", "Implementation", "Implementation",
                          "Planning", "Implementation", "Planning", "Implementation", "Planning", "Implementation")
)

我的目标是为每个ha_name添加一个摘要行，该行计算“实施”在pre_mandate和当前列中的次数。

例如，对于 Interior，该行将是Interior - All Interior - 2 - 3

我正在尝试应用我在其他地方找到的代码来基本执行此操作，但在一列中使用数值

df2 <- df1 %>% group_by(ha_name, fiscal_year, quarter) %>% 
  group_modify(~ bind_rows(., summarise(., count = sum(count)))) %>% 
  ungroup() %>% 
  mutate(facility = coalesce(facility, paste("All", ha_name)),
         ha_name = if_else(startsWith(facility, "All"), "", ha_name))

但是当我尝试修改它时，我有点卡住了如何让它只计算“实现”的实例。

我目前正在研究的是这个

f2 <- df1 %>% group_by(ha_name) %>% 
  group_modify(~ bind_rows(., summarise(., across(pre_mandate:current, `%in%`, "Implementation")))) %>% 
  ungroup() %>% 
  mutate(facility = coalesce(facility, paste("All", ha_name)),
         ha_name = if_else(startsWith(facility, "All"), "", ha_name))

但是 Across With 的使用不会返回单个值。不确定这是否接近，我只需要再添加一个函数来汇总结果，或者我是否偏离了目标。%in%

任何帮助将不胜感激！

r dplyr 横跨

评论

答：

0赞 stefan 11/16/2023 #1

问题是 using 将返回一个布尔向量。但是，由于您想要 true 值的计数，因此您必须将其包装（并将结果转换为字符）：%in%sum

library(dplyr, warn.conflicts = FALSE)

df1 %>%
  group_by(ha_name) %>%
  group_modify(
    ~ bind_rows(., summarise(., across(
      c(pre_mandate, current), ~ as.character(
        sum(.x %in% "Implementation")
      )
    )))
  ) %>%
  ungroup() %>%
  mutate(
    facility = coalesce(facility, paste("All", ha_name))
  )
#> # A tibble: 21 × 4
#>    ha_name  facility   pre_mandate    current       
#>    <chr>    <chr>      <chr>          <chr>         
#>  1 Fraser   Facility 1 Implementation Implementation
#>  2 Fraser   Facility 2 Planning       Planning      
#>  3 Fraser   Facility 3 Planning       Planning      
#>  4 Fraser   Facility 4 Planning       Implementation
#>  5 Fraser   Facility 5 Implementation Implementation
#>  6 Fraser   Facility 6 Implementation Implementation
#>  7 Fraser   All Fraser 3              4             
#>  8 Interior Facility 1 Planning       Planning      
#>  9 Interior Facility 2 Implementation Implementation
#> 10 Interior Facility 3 Planning       Implementation
#> # ℹ 11 more rows

上一个：在不同日期制作分组堆积条形图

下一个：R ggplot：连续变量的大小分隔符