4

In a given data.table what would be the syntax to select column V1 where V2 = max(V2), grouped by V3.

For example: In the mtcars dataset, I would like to find out what is the hp that corresponds to the observation equal to max(disp), grouped by cyl

Here is my ungraceful solution, using which:

mtcars <- data.table(mtcars)
mtcars[which(mtcars$disp %in% mtcars[, max(disp), by = .(cyl)]$V1), .(cyl,hp)]
   cyl  hp
1:   6 110
2:   4  62
3:   8 205

Is there a more "data.table" way of achieving the same result?

Ivan P.
  • 480
  • 2
  • 5
  • 10
  • 1
    Also take a look here: https://stackoverflow.com/questions/16573995/subset-by-group-with-data-table – talat Feb 25 '16 at 08:39

3 Answers3

7

We can try the join 

mtcars[mtcars[, list(disp=max(disp)), by = cyl], 
    on = c('cyl', 'disp')][, c('cyl', 'hp'), with=FALSE]
#   cyl  hp
#1:   6 110
#2:   4  62
#3:   8 205

Or here is a shorter version to get the expected output.

mtcars[, .SD[disp==max(disp), .(hp)], by = cyl]
#   cyl  hp
#1:   6 110
#2:   4  62
#3:   8 205
akrun
  • 874,273
  • 37
  • 540
  • 662
7

You could use .I:

mtcars[mtcars[, .I[which.max(disp)], by = cyl]$V1, .(cyl, hp)]
#   cyl  hp
#1:   6 110
#2:   4  62
#3:   8 205

Or

mtcars[, hp[disp == max(disp)], by=cyl]
#   cyl  V1
#1:   6 110
#2:   4  62
#3:   8 205

And your own approach could be slightly shortened to:

mtcars[disp %in% mtcars[, max(disp), by = .(cyl)]$V1, .(cyl,hp)]
talat
  • 68,970
  • 21
  • 126
  • 157
2
mtcars[,ismax:=disp==max(disp),by=cyl][ismax==T, .(cyl, hp)]
   cyl  hp
1:   6 110
2:   4  62
3:   8 205

I keep arriving at near-identical solutions to others. Ah well...

mtcars[,.SD[disp == max(disp)], by = cyl, .SDcols = c("disp", "hp")][,.(cyl, hp)]
statespace
  • 1,644
  • 17
  • 25
  • This method unnecessarily create new column in the original data – akrun Feb 25 '16 at 08:39
  • 1
    Good point, it just seemed like a short and easy solution though. Let me think how to make the same output without creating new column. – statespace Feb 25 '16 at 08:40