Select the top N values by group

by

This seems more straightforward using data.table as it performs the sort while setting the key.

So, if I were to get the top 3 records in sort (ascending order), then,

require(data.table)
d <- data.table(mtcars, key="cyl")
d[, head(.SD, 3), by=cyl]

does it.

And if you want the descending order

d[, tail(.SD, 3), by=cyl] # Thanks @MatthewDowle

Edit: To sort out ties using mpg column: 

d <- data.table(mtcars, key="cyl")
d.out <- d[, .SD[mpg %in% head(sort(unique(mpg)), 3)], by=cyl]

#     cyl  mpg  disp  hp drat    wt  qsec vs am gear carb rank
#  1:   4 22.8 108.0  93 3.85 2.320 18.61  1  1    4    1   11
#  2:   4 22.8 140.8  95 3.92 3.150 22.90  1  0    4    2    1
#  3:   4 21.5 120.1  97 3.70 2.465 20.01  1  0    3    1    8
#  4:   4 21.4 121.0 109 4.11 2.780 18.60  1  1    4    2    6
#  5:   6 18.1 225.0 105 2.76 3.460 20.22  1  0    3    1    7
#  6:   6 19.2 167.6 123 3.92 3.440 18.30  1  0    4    4    1
#  7:   6 17.8 167.6 123 3.92 3.440 18.90  1  0    4    4    2
#  8:   8 14.3 360.0 245 3.21 3.570 15.84  0  0    3    4    7
#  9:   8 10.4 472.0 205 2.93 5.250 17.98  0  0    3    4   14
# 10:   8 10.4 460.0 215 3.00 5.424 17.82  0  0    3    4    5
# 11:   8 13.3 350.0 245 3.73 3.840 15.41  0  0    3    4    3

# and for last N elements, of course it is straightforward
d.out <- d[, .SD[mpg %in% tail(sort(unique(mpg)), 3)], by=cyl]

More Related Contents:

  1. R transpose and group by
  2. Aggregate / summarize multiple variables per group (e.g. sum, mean)
  3. Collapse / concatenate / aggregate a column to a single comma separated string within each group
  4. Mean per group in a data.frame [duplicate]
  5. Count number of rows within each group
  6. Faster ways to calculate frequencies and cast from long to wide
  7. Aggregating by unique identifier and concatenating related values into a string [duplicate]
  8. Extract row corresponding to minimum value of a variable by group
  9. Group by multiple columns and sum other multiple columns
  10. Add count of unique / distinct values by group to the original data
  11. Select the first and last row by group in a data frame
  12. Summarizing by subgroup percentage in R
  13. Collapsing rows where some are all NA, others are disjoint with some NAs
  14. Aggregate multiple columns at once [duplicate]
  15. Aggregate a dataframe on a given column and display another column
  16. Aggregate a data frame based on unordered pairs of columns
  17. Compute mean and standard deviation by group for multiple variables in a data.frame
  18. aggregate methods treat missing values (NA) differently
  19. Group integer vector into consecutive runs
  20. Returning first row of group
  21. Apply several summary functions (sum, mean, etc.) on several variables by group in one call
  22. Aggregate data in R
  23. aggregate() puts multiple output columns in a matrix instead
  24. data.frame Group By column [duplicate]
  25. R group by aggregate
  26. R use ddply or aggregate
  27. Run a custom function on a data frame in R, by group
  28. Aggregate by week in R
  29. Aggregating all unique values of each column of data frame
  30. How can I count the number of NAs per group?

Leave a Comment