r语言 - 汇总并计算dplyr组df中唯一值的个数

Question

我有这个df:

structure(list(CN = c("BR", "BR", "BR", "PL", "PL", "PL", 
"BR", "BR", "BR", "BR", "PL", "PL", "PL"), Year = c(2019, 
2019, 2019, 2019, 2019, 2019, 2020, 2020, 2020, 2020, 2020, 2020, 
2020), Squad = c("A", "B", "C", "A", "B", "C", "C", "F", "G", 
"I", "D", "E", "F"), X = c(1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2, 3, 
1), Y = c(1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2, 3, 1)), row.names = c(NA, 
-13L), class = c("tbl_df", "tbl", "data.frame"))

我想总结(x+y和小队计数的总和)按CN和Year分组;并在相同的结构中添加一个列，其中包含仅按CN分组的小队的唯一/不同值的计数。

它看起来像这样:

structure(list(CN = c("BR", "BR", "PL", "PL"), Year = c(2019, 
2020, 2019, 2020), Sum = c(12, 14, 12, 12), n_squad = c(3, 4, 
3, 3), n_squad_distinct = c(6, 6, 6, 6)), row.names = c(NA, -4L
), class = c("tbl_df", "tbl", "data.frame"))

感谢

Answer 1

我们可以创建按'CN"分组的'n_squad_distinct'列;通过在'Squad'上应用n_distinct，然后添加'Year'和'n_squad_distinct'也作为分组变量，并执行summarise

library(dplyr)
df %>%
group_by(CN) %>%
mutate(n_squad_distinct = n_distinct(Squad)) %>% 
group_by(n_squad_distinct, Year, .add = TRUE) %>%
summarise(Sum = sum(X + Y), n_squad = n_distinct(Squad), .groups = 'drop')

与产出

# A tibble: 4 × 5
CN    n_squad_distinct  Year   Sum n_squad
<chr>            <int> <dbl> <dbl>   <int>
1 BR                   6  2019    12       3
2 BR                   6  2020    14       4
3 PL                   6  2019    12       3
4 PL                   6  2020    12       3