dplyr select top 10 values for each category

user_9067780 0
Viewed 7002

I am trying to filter a data frame that contains n rows for n categories. I want that each category dimension values are sorted by another column revenues and then, the top 10 values of each dimension are selected and get rid of the rest.

I did an attempt with the following snipper, but it does not seem to achieve what I want to:

data <-  tbl_df(data) %>%
  arrange(revenues) %>%
  group_by(dimension) %>%
  top_n(10)
dplyr r
3 Answers
data <-  tbl_df(data) %>%
  group_by(dimension) %>%
  arrange(revenues, .by_group = TRUE) %>%
  top_n(10)
user_50240090

We can test it with an example data:

set.seed(100)
data = data.frame(revenues=rnbinom(100,mu=1000,size=1),
dimension=sample(letters[1:2],100,replace=TRUE))

Firstly as @DarrenTsai correctly pointed out, you need to specify the column to do top_n(). Secondly, when you use top_n, it goes by descending order and takes the entries with rank 1-10:

data %>% top_n(10,revenues)
   revenues dimension
1      4191         b
2      1916         a
3      2397         b
4      1895         a
5      2013         a
6      2351         b
7      3889         b
8      2503         a
9      3909         a
10     2779         b

This means you don't need to arrange your data, and I am not sure whether you intend to take it in descending or ascending. Let's assume it is descending, :

data %>%  group_by(dimension) %>% top_n(10,revenues)

Note, this code above will take the top 10 values, meaning in events of ties (say you have 2 ranked 1st), you will get more than 10. For example in this data: 

# A tibble: 21 x 2
# Groups:   dimension [2]
   revenues dimension
      <dbl> <fct>    
 1     1663 a        
 2     1663 a        
 3     1753 a        
 4     1849 a        
 5     1856 a        
 6     1869 a        
 7     1895 a        
 8     1916 a        
 9     2013 a        
10     2503 a        
# … with 11 more rows

We can see whether the results are correct, this is what we expect:

unlist(tapply(data$revenues,data$dimension,function(i)-sort(-i)[1:10]))
  a1   a2   a3   a4   a5   a6   a7   a8   a9  a10   b1   b2   b3   b4   b5   b6 
3909 2503 2013 1916 1895 1869 1856 1849 1753 1663 4191 3889 2779 2397 2351 1479 
  b7   b8   b9  b10 
1414 1340 1327 1274

And using the group_by + top_n() :

 data %>%  group_by(dimension) %>% top_n(10,revenues) %>% 
arrange(dimension,desc(revenues)) %>% pull(revenues)
 [1] 3909 2503 2013 1916 1895 1869 1856 1849 1753 1663 1663 4191 3889 2779 2397
[16] 2351 1479 1414 1340 1327 1274

You can see 1663 is taken twice, giving 21 values in total.

If you need absolutely 20 (10 each):

data %>% arrange(desc(revenues)) %>%
group_by(dimension) %>% do(head(.,10))
user_122584590 
data <-  tbl_df(data) %>%
  group_by(dimension) %>%
  arrange(desc(revenues),.by_group=TRUE) %>%
  top_n(10,revenues)
user_132339790
Related