整洁的宇宙(Tidyverse)：在分组数据框中筛选n个最大的组

Question

我想根据数量筛选出前n个最大组，并在筛选后的数据框上进行一些计算。

这里是一些数据

Brand <- c("A","B","C","A","A","B","A","A","B","C")
Category <- c(1,2,1,1,2,1,2,1,2,1)
Clicks <- c(10,11,12,13,14,15,14,13,12,11)
df <- data.frame(Brand,Category,Clicks)

|Brand | Category| Clicks|
|:-----|--------:|------:|
|A     |        1|     10|
|B     |        2|     11|
|C     |        1|     12|
|A     |        1|     13|
|A     |        2|     14|
|B     |        1|     15|
|A     |        2|     14|
|A     |        1|     13|
|B     |        2|     12|
|C     |        1|     11|

这是我期望的输出结果。我希望筛选掉两个数量最大的品牌，然后找出每个品牌/类别组合中的平均点击次数。

|Brand | Category| mean_clicks|
|:-----|--------:|-----------:|
|A     |        1|        12.0|
|A     |        2|        14.0|
|B     |        1|        15.0|
|B     |        2|        11.5|

我原以为可以用这样的代码实现（但实际上不行）

df %>%
  group_by(Brand, Category) %>%
  top_n(2, Brand) %>% # Largest 2 brands by count
  summarise(mean_clicks = mean(Clicks))

编辑：理想的答案应该适用于数据库表以及本地表格

- Shinobi_Atobe

---

你可能需要明确你打算使用哪个DBMS，因为并非所有DBMS都支持窗口函数，而dbplyr在某些翻译中可能需要它们。我相信@Ronak的答案应该适用于支持窗口函数的DB，但由于add_count，在其他DB上会失败，解决方法是在子查询中进行分组和计数，将其加入到原始查询中并继续进行。 - moodymudskipper

实际上，@Paul的解决方案可能立即奏效，因为它似乎就是这样做的。 - moodymudskipper

Answer 1

使用 join 过滤数据框的另一种 dplyr 解决方案:

library(dplyr)

df %>%
  group_by(Brand) %>%
  summarise(n = n()) %>%
  top_n(2) %>% # select top 2
  left_join(df, by = "Brand") %>% # filters out top 2 Brands
  group_by(Brand, Category) %>%
  summarise(mean_clicks = mean(Clicks))

# # A tibble: 4 x 3
# # Groups:   Brand [?]
#   Brand Category mean_clicks
#   <fct>    <dbl>       <dbl>
# 1 A            1        12  
# 2 A            2        14  
# 3 B            1        15  
# 4 B            2        11.5

- Paul

---

+1 是因为即使它比某些解决方案更长，我相信这是唯一适用于 dbplyr 支持的所有 DBMS 的解决方案。arrange(desc(n)) %>% head(2) 可以通过使用 top_n 来替换。 - moodymudskipper

Answer 2

一种不同的dplyr解决方案：

df %>%
  group_by(Brand) %>%
  mutate(n = n()) %>%
  ungroup() %>%
  mutate(rank = dense_rank(desc(n))) %>%
  filter(rank == 1 | rank == 2) %>%
  group_by(Brand, Category) %>%
  summarise(mean_clicks = mean(Clicks))

# A tibble: 4 x 3
# Groups:   Brand [?]
  Brand Category mean_clicks
  <fct>    <dbl>       <dbl>
1 A           1.        12.0
2 A           2.        14.0
3 B           1.        15.0
4 B           2.        11.5

或者一个简化版本（基于@camille的建议）：

df %>%
  group_by(Brand) %>%
  mutate(n = n()) %>%
  ungroup() %>%
  filter(dense_rank(desc(n)) < 3) %>%
  group_by(Brand, Category) %>%
  summarise(mean_clicks = mean(Clicks))

- tmfmnk

---

我唯一建议的改进是，由于您想要前两个排名，所以可以使用rank <= 2或rank < 3代替rank == 1 | rank == 2，这样您还可以轻松跳过 mutate 行并在 filter 调用内创建一个排名。 - camille

Answer 3

编辑

根据更新后的问题，我们可以先添加一个计数列，仅筛选出前 n 组计数，然后按 Brand 和 Category 进行分组，找到每个组的 mean。

df %>%
  add_count(Brand, sort = TRUE) %>%
  filter(n %in% head(unique(n), 2)) %>%
  group_by(Brand, Category) %>%
  summarise(mean_clicks = mean(Clicks))


#   Brand Category mean_clicks
#   <fct>    <dbl>       <dbl>
#1 A            1        12  
#2 A            2        14  
#3 B            1        15  
#4 B            2        11.5

原始回答

我们可以按照 Brand 进行分组，并对每个组进行所有计算，然后通过 top_n 过滤出前几个组。

library(dplyr)
df %>%
  group_by(Brand) %>%
  summarise(n = n(), 
            mean = mean(Clicks)) %>%
  top_n(2, n) %>%
  select(-n)

#  Brand  mean
#  <fct> <dbl>
#1  A      12.8
#2  B      12.7

- Ronak Shah

---

谢谢你的回答Ronak，但我可以看到这种情况不会起作用。我将编辑我的问题。 - Shinobi_Atobe

使用 filter(n %in% head(unique(n), 2)) 是一个非常聪明的想法。但我不禁在想，一定有更快/更优雅的解决方案吧？ - Shinobi_Atobe

@Shinobi_Atobe 我同意。我也感觉应该有更好的东西，不过现在想不出来。 - Ronak Shah

如果你基于dense_rank进行过滤（就像@tmfmnk所做的那样），你可以跳过add_count中的排序，并使用df％>% add_count（Brand）％>% filter（dense_rank（desc（n））％in％1：2）。不知道这是否更快/更优雅..;) - Henrik

@Henrik 是的，绝对没错。但这并不会减少任何步骤，只是用另一个替换了一个。 - Ronak Shah

Answer 4

这个问题怎么样，使用基本的R中的table函数呢？

df %>%
  filter(Brand %in% names(tail(sort(table(Brand)), 2))) %>%
  group_by(Brand, Category) %>%
  summarise(mean_clicks = mean(Clicks))

# A tibble: 4 x 3
# Groups:   Brand [?]
  Brand Category mean_clicks
  <chr>    <dbl>       <dbl>
1 A         1.00        12.0
2 A         2.00        14.0
3 B         1.00        15.0
4 B         2.00        11.5

- Shree

---

为了更加简洁，可以使用 names(tail(sort(table(x)),2))。如果想要重复使用，可以编写一个函数 is_in_top_group <- function(x,n) x %in% names(tail(sort(table(x)),n))。 - moodymudskipper

@Moody_Mudskipper 谢谢！不知道我怎么会错过那个。我编辑了我的答案。 - Shree

Answer 5

一个data.table的好主意是通过品牌将计数分组，并过滤掉前两个（按降序排序）。然后，我们将其与原始数据框合并，并按(品牌，类别)进行分组找到平均值。

library(data.table)

#Convert to data.table
dt1 <- setDT(df)

dt1[dt1[, .(cnt = .N), by = Brand][
             order(cnt, decreasing = TRUE), .SD[1:2]][,cnt := NULL], 
                   on = 'Brand'][, .(means = mean(Clicks)), by = .(Brand, Category)][]

这将会给出：

   Brand Category means
1:     A        1  12.0
2:     A        2  14.0
3:     B        2  11.5
4:     B        1  15.0

- Sotos

Answer 6

与上面略有不同。只是因为我不喜欢在大型数据集中使用join。有些人可能不喜欢我创建和删除小数据框，抱歉:(

df %>% count(Brand) %>% top_n(2,n) -> Top2
df %>% group_by(Brand, Category) %>% 
filter(Brand %in% Top2$Brand) %>% 
summarise(mean_clicks = mean(Clicks))
remove(Top2)

- turqoisehat