How to extract the row with min or max values?

Question

With a dataframe like this one:

        ID  Year    Temp    ph
1       P1  1996    11.3    6.80
2       P1  1996    9.7     6.90
3       P1  1997    9.8     7.10
...
2000    P2  1997    10.5    6.90
2001    P2  1997    9.9     7.00
2002    P2  1997    10.0    6.93

if I want to know where the max value is I type:

which.max(df$Temp)

and R print the index of the row, for example 665.

So, if I want to read and extract the column with all the related values, I have to type:

df[665, ]

Isn't there a simpler way to know which ID is related to the max value of a specific column of the df?

`df[which.max(df$Temp),]`? Or are you asking something more complicated? — Joshua Ulrich, Oct 18 '13 at 12:26
Ahrrhrh.. I've tried with a lots of methods, but I didn't thought that it could be so simple. Thanks!! — matteo, Oct 18 '13 at 12:31
Also consider `subset(df, Temp == max(Temp))` which gives all the rows whose `Temp` is the maximum (whereas `which.max` solutions give one such row). — G. Grothendieck, Oct 18 '13 at 12:49

score 90 · Accepted Answer · answered Oct 18 '13 at 12:32

90

You can include your which.max call as the first argument to your subsetting call:

df[which.max(df$Temp),]

answered Oct 18 '13 at 12:32

Joshua Ulrich

173,410
32
338
418

what if the vector is bimodal? – Jay Schyler Raadt Apr 25 '18 at 14:53
2

@JaySchylerRaadt: see [Gabor's comment](https://stackoverflow.com/questions/19449615/how-to-extract-the-row-with-min-or-max-values/19449769?noredirect=1#comment28839525_19449615). Though you might need to be [careful if `Temp` is a double](https://stackoverflow.com/q/9508518/271616). – Joshua Ulrich Apr 25 '18 at 14:58

symbolrush · Answer 2 · 2020-10-15T12:55:17.427

A (relatively new) alternative is to use slice_max (or slice_min) from the tidyverse. Using mtcars as example:

library(tidyverse)
mtcars %>% slice_max(mpg)
#                 mpg cyl disp hp drat    wt qsec vs am gear carb
# Toyota Corolla 33.9   4 71.1 65 4.22 1.835 19.9  1  1    4    1

mtcars %>% slice_min(mpg)
#                      mpg cyl disp  hp drat    wt  qsec vs am gear carb
# Cadillac Fleetwood  10.4   8  472 205 2.93 5.250 17.98  0  0    3    4
# Lincoln Continental 10.4   8  460 215 3.00 5.424 17.82  0  0    3    4

Note that slice_max and slice_min give you all the rows, which have a max or min value in the specified column (like the call mtcars[mtcars$mpg == min(mtcars$mpg), ]. So if you only want the first row (like in the call mtcars[which.min(mtcars$mpg), ]), you need to slice once again like:

mtcars %>% slice_min(mpg) %>% slice(1)
#                     mpg cyl disp  hp drat   wt  qsec vs am gear carb
# Cadillac Fleetwood 10.4   8  472 205 2.93 5.25 17.98  0  0    3    4

Moreover, slice also offers a few more helper functions for common use cases like slice_head, slice_tail and slice_sample.

Using the `n =` argument, `slice_max` can also show you rows corresponding to the top n values in the specified column. — its.me.adam, Aug 09 '21 at 20:21

score 11 · Answer 3 · answered Feb 04 '19 at 14:53

11

You could also use a subset and max function to call the row:

df[df$Temp == max(df$Temp),]

answered Feb 04 '19 at 14:53

Derwin Brennan

424
5
5

score 0 · Answer 4 · answered Jun 14 '22 at 15:43

If you are interested in finding the min/max for certain groups, then you can combine group_by and slice_max in tidyverse. In this example, say you wanted to know the max Temp within each year, then you would do the following:

df %>% group_by(Temp) %>% slice_max(Temp)

Just another way to maximize the tidyverse packages.

How to extract the row with min or max values?

4 Answers4

Linked

Related