How to select the row with the maximum value in each group

Question

In a dataset with multiple observations for each subject I want to take a subset with only the maximum data value for each record  For example  with a following dataset   ID     lt - c 1 1 1 2 2 2 2 3 3  Value  lt - c 2 3 5 2 5 8 17 3 5  Event  lt - c 1 1 2 1 2 1 2 2 2   group  lt - data frame Subject ID  pt Value  Event Event    Subject 1  2  and 3 have the biggest pt value of 5  17  and 5 respectively    How could I first find the biggest pt value for each subject  and then  put this observation in another data frame  The resulting data frame should only have the biggest pt values for each subject

User · Answer

Here s another data table solution  since which max does not work on characters  library data table  group  lt - data table Subject ID  pt Value  Event Event   group    SD order pt  decreasing   TRUE     1   by   Subject

User · Answer

Another data table option   library data table  setDT group  group group order -pt    I 1L   Subject  V1    Or another  less readable but slightly faster    group group   rn     I  order Subject  -pt         rn c 1L  1L   which diff Subject  gt 0L          timing code   library data table  nr  lt - 1e7L ng  lt - nr 4L set seed 0L  DT  lt - data table Subject sample ng  nr  TRUE   pt 1 nr  rnorm nr   DT2  lt - copy DT    microbenchmark  microbenchmark times 3L      mtd0    a0  lt - DT DT    I which max pt    by Subject  V1        mtd1    a1  lt - DT DT order -pt    I 1L   Subject  V1        mtd2    a2  lt - DT2 DT2   rn     I           order Subject  -pt   rn c TRUE  diff Subject  gt 0L                mtd3    a3  lt - unique DT order Subject  -pt    by  Subject      fsetequal a0 order Subject    a1 order Subject      1  TRUE fsetequal a0 order Subject    a2   rn    NULL  order Subject      1  TRUE fsetequal a0 order Subject    a3 order Subject      1  TRUE   timings   Unit  seconds  expr      min       lq     mean   median       uq      max neval  mtd0 3 256322 3 335412 3 371439 3 414502 3 428998 3 443493     3  mtd1 1 733162 1 748538 1 786033 1 763915 1 812468 1 861022     3  mtd2 1 136307 1 159606 1 207009 1 182905 1 242359 1 301814     3  mtd3 1 123064 1 166161 1 228058 1 209257 1 280554 1 351851     3

User · Answer

The most intuitive method is to use group by and top n function in dplyr      group   gt   group by Subject    gt   top n 1  pt    The result you get is      Source  local data frame  3 x 3      Groups  Subject  3         Subject    pt Event          dbl   dbl   dbl      1       1     5     2     2       2    17     2     3       3     5     2

User · Answer

One more base R solution   merge aggregate pt   Subject  max  data   group   group     Subject pt Event 1       1  5     2 2       2 17     2 3       3  5     2

User · Answer

Another base solution  group sorted  lt - group order group Subject  -group pt    group sorted  duplicated group sorted Subject       Subject pt Event         1  5     2         2 17     2         3  5     2   Order the data frame by pt  descending  and then remove rows duplicated in Subject

User · Answer

Since  dplyr  v1 0 0  May 2020  there is the new slice   syntax which supersedes top n    See also https   dplyr tidyverse org reference slice html  library tidyverse   ID     lt - c 1 1 1 2 2 2 2 3 3  Value  lt - c 2 3 5 2 5 8 17 3 5  Event  lt - c 1 1 2 1 2 1 2 2 2   group  lt - data frame Subject ID  pt Value  Event Event   group   gt      group by Subject    gt      slice max pt    gt    A tibble  3 x 3   gt    Groups    Subject  3    gt    Subject    pt Event   gt       lt dbl gt   lt dbl gt   lt dbl gt    gt  1       1     5     2   gt  2       2    17     2   gt  3       3     5     2  Created on 2020-08-18 by the reprex package  v0 3 0 9001    Session info  sessioninfo  session info     gt  - Session info ---------------------------------------------------------------   gt   setting  value                                         gt   version  R version 4 0 2 Patched  2020-06-30 r78761    gt   os       macOS Catalina 10 15 6                        gt   system   x86 64  darwin17 0                            gt   ui       X11                                           gt   language  EN                                           gt   collate  en US UTF-8                                   gt   ctype    en US UTF-8                                   gt   tz       Europe Berlin                                 gt   date     2020-08-18                                    gt     gt  - Packages -------------------------------------------------------------------   gt   package       version    date       lib source                               gt   assertthat    0 2 1      2019-03-21  1  CRAN  R 4 0 0                        gt   backports     1 1 8      2020-06-17  1  CRAN  R 4 0 1                        gt   blob          1 2 1      2020-01-20  1  CRAN  R 4 0 0                        gt   broom         0 7 0      2020-07-09  1  CRAN  R 4 0 2                        gt   cellranger    1 1 0      2016-07-27  1  CRAN  R 4 0 0                        gt   cli           2 0 2      2020-02-28  1  CRAN  R 4 0 0                        gt   colorspace    1 4-1      2019-03-18  1  CRAN  R 4 0 0                        gt   crayon        1 3 4      2017-09-16  1  CRAN  R 4 0 0                        gt   DBI           1 1 0      2019-12-15  1  CRAN  R 4 0 0                        gt   dbplyr        1 4 4      2020-05-27  1  CRAN  R 4 0 0                        gt   digest        0 6 25     2020-02-23  1  CRAN  R 4 0 0                        gt   dplyr         1 0 1      2020-07-31  1  CRAN  R 4 0 2                        gt   ellipsis      0 3 1      2020-05-15  1  CRAN  R 4 0 0                        gt   evaluate      0 14       2019-05-28  1  CRAN  R 4 0 0                        gt   fansi         0 4 1      2020-01-08  1  CRAN  R 4 0 0                        gt   forcats       0 5 0      2020-03-01  1  CRAN  R 4 0 0                        gt   fs            1 5 0      2020-07-31  1  CRAN  R 4 0 2                        gt   generics      0 0 2      2018-11-29  1  CRAN  R 4 0 0                        gt   ggplot2       3 3 2      2020-06-19  1  CRAN  R 4 0 1                        gt   glue          1 4 1      2020-05-13  1  CRAN  R 4 0 0                        gt   gtable        0 3 0      2019-03-25  1  CRAN  R 4 0 0                        gt   haven         2 3 1      2020-06-01  1  CRAN  R 4 0 0                        gt   highr         0 8        2019-03-20  1  CRAN  R 4 0 0                        gt   hms           0 5 3      2020-01-08  1  CRAN  R 4 0 0                        gt   htmltools     0 5 0      2020-06-16  1  CRAN  R 4 0 1                        gt   httr          1 4 2      2020-07-20  1  CRAN  R 4 0 2                        gt   jsonlite      1 7 0      2020-06-25  1  CRAN  R 4 0 2                        gt   knitr         1 29       2020-06-23  1  CRAN  R 4 0 2                        gt   lifecycle     0 2 0      2020-03-06  1  CRAN  R 4 0 0                        gt   lubridate     1 7 9      2020-06-08  1  CRAN  R 4 0 1                        gt   magrittr      1 5        2014-11-22  1  CRAN  R 4 0 0                        gt   modelr        0 1 8      2020-05-19  1  CRAN  R 4 0 0                        gt   munsell       0 5 0      2018-06-12  1  CRAN  R 4 0 0                        gt   pillar        1 4 6      2020-07-10  1  CRAN  R 4 0 2                        gt   pkgconfig     2 0 3      2019-09-22  1  CRAN  R 4 0 0                        gt   purrr         0 3 4      2020-04-17  1  CRAN  R 4 0 0                        gt   R6            2 4 1      2019-11-12  1  CRAN  R 4 0 0                        gt   Rcpp          1 0 5      2020-07-06  1  CRAN  R 4 0 2                        gt   readr         1 3 1      2018-12-21  1  CRAN  R 4 0 0                        gt   readxl        1 3 1      2019-03-13  1  CRAN  R 4 0 0                        gt   reprex        0 3 0 9001 2020-08-13  1  Github  tidyverse reprex 23a3462     gt   rlang         0 4 7      2020-07-09  1  CRAN  R 4 0 2                        gt   rmarkdown     2 3 3      2020-07-26  1  Github  rstudio rmarkdown 204aa41    gt   rstudioapi    0 11       2020-02-07  1  CRAN  R 4 0 0                        gt   rvest         0 3 6      2020-07-25  1  CRAN  R 4 0 2                        gt   scales        1 1 1      2020-05-11  1  CRAN  R 4 0 0                        gt   sessioninfo   1 1 1      2018-11-05  1  CRAN  R 4 0 2                        gt   stringi       1 4 6      2020-02-17  1  CRAN  R 4 0 0                        gt   stringr       1 4 0      2019-02-10  1  CRAN  R 4 0 0                        gt   styler        1 3 2 9000 2020-07-05  1  Github  pat-s styler 51d5200         gt   tibble        3 0 3      2020-07-10  1  CRAN  R 4 0 2                        gt   tidyr         1 1 1      2020-07-31  1  CRAN  R 4 0 2                        gt   tidyselect    1 1 0      2020-05-11  1  CRAN  R 4 0 0                        gt   tidyverse     1 3 0      2019-11-21  1  CRAN  R 4 0 0                        gt   utf8          1 1 4      2018-05-24  1  CRAN  R 4 0 0                        gt   vctrs         0 3 2      2020-07-15  1  CRAN  R 4 0 2                        gt   withr         2 2 0      2020-04-20  1  CRAN  R 4 0 0                        gt   xfun          0 16       2020-07-24  1  CRAN  R 4 0 2                        gt   xml2          1 3 2      2020-04-23  1  CRAN  R 4 0 0                        gt   yaml          2 2 1      2020-02-01  1  CRAN  R 4 0 0                        gt     gt   1   Users pjs Library R 4 0 library   gt   2   Library Frameworks R framework Versions 4 0 Resources library

User · Answer

If you want the biggest pt value for a subject  you could simply use      pt max   as data frame aggregate pt Subject  group  max

User · Answer

Another data table solution   library data table  setDT group    head  SD order -pt    1   by     Subject

User · Answer

Another option is slice  library dplyr  group   gt        group by Subject    gt        slice which max pt        Subject    pt Event       lt dbl gt   lt dbl gt   lt dbl gt   1       1     5     2  2       2    17     2  3       3     5     2

User · Answer

I wasn t sure what you wanted to do about the Event column  but if you want to keep that as well  how about  isIDmax  lt - with dd  ave Value  ID  FUN function x  seq along x   which max x     1 group isIDmax         ID Value Event   3  1     5     2   7  2    17     2   9  3     5     2   Here we use ave to look at the  Value  column for each  ID   Then we determine which value is the maximal and then turn that into a logical vector we can use to subset the original data frame

User · Answer

In base you can use ave to get max per group and compare this with pt and get a logical vector to subset the data frame   group group pt    ave group pt  group Subject  FUN max       Subject pt Event  3       1  5     2  7       2 17     2  9       3  5     2   Or compare it already in the function   group as logical ave group pt  group Subject  FUN function x  x  max x       group ave group pt  group Subject  FUN function x  x  max x    1    Variant    Subject pt Event  3       1  5     2  7       2 17     2  9       3  5     2

User · Answer

A shorter solution using data table   setDT group     SD which max pt    by Subject       Subject pt Event   1        1  5     2   2        2 17     2   3        3  5     2

User · Answer

do call rbind  lapply split group as factor group Subject    function x   return x which max x pt          Using Base R

User · Answer

A dplyr solution   library dplyr  ID  lt - c 1 1 1 2 2 2 2 3 3  Value  lt - c 2 3 5 2 5 8 17 3 5  Event  lt - c 1 1 2 1 2 1 2 2 2  group  lt - data frame Subject ID  pt Value  Event Event   group   gt       group by Subject    gt       summarize max pt   max pt     This yields the following data frame     Subject max pt 1       1      5 2       2     17 3       3      5

User · Answer

Using dplyr 1 0 2 there are now two ways to do this  one is long hand and the other is using the verb across            create data       ID     lt - c 1 1 1 2 2 2 2 3 3        Value  lt - c 2 3 5 2 5 8 17 3 5        Event  lt - c 1 1 2 1 2 1 2 2 2               group  lt - data frame Subject ID  pt Value  Event Event   Long hand the verb is max   but note the na rm   TRUE which is useful for examples where there are NAs as in the closed question  Merge rows in a dataframe where the rows are disjoint and contain NAs         group   gt            group by Subject    gt            summarise pt   max pt  na rm   TRUE                     Event   max Event  na rm   TRUE    This is ok if there are only a few columns but if the table has many columns across   is useful  The examples for this verb are often with summarise across start with    but in this example the columns don t start with the same characters  Either they could be changed or the positions listed      group   gt            group by Subject    gt            summarise across 1 ncol group -1  max  na rm   TRUE   names    quot   col  quot     Note for the verb across   1 refers to the first column after the first actual column so using ncol group  won t work as that is too many columns  makes it position 4 rather than 3

User · Answer

by is a version of tapply for data frames   res  lt - by group  group Subject  FUN function df  df which max df pt       It returns an object of class by so we convert it to data frame   do call rbind  b    Subject pt Event 1       1  5     2 2       2 17     2 3       3  5     2

User · Answer

Here s a data table solution   require data table     1 9 2 group  lt - as data table group    If you want to keep all the entries corresponding to max values of pt within each group   group group    I pt    max pt    by Subject  V1       Subject pt Event   1        1  5     2   2        2 17     2   3        3  5     2   If you d like just the first max value of pt   group group    I which max pt    by Subject  V1       Subject pt Event   1        1  5     2   2        2 17     2   3        3  5     2   In this case  it doesn t make a difference  as there aren t multiple maximum values within any group in your data

[r] How to select the row with the maximum value in each group

Examples related to r

Examples related to dataframe

Examples related to r-faq