How to select the rows with maximum values in each group with dplyr

Question

I would like to select a row with maximum value in each group with dplyr   Firstly I generate some random data to show my question  set seed 1  df  lt - expand grid list A   1 5  B   1 5  C   1 5   df value  lt - runif nrow df     In plyr  I could use a custom function to select this row   library plyr  ddply df    A  B   function x  x which max x value       In dplyr  I am using this code to get the maximum value  but not the rows with maximum value  Column C in this case    library dplyr  df   gt   group by A  B    gt       summarise max   max value     How could I achieve this  Thanks for any suggestion   sessionInfo   R version 3 1 0  2014-04-10  Platform  x86 64-w64-mingw32 x64  64-bit   locale   1  LC COLLATE English Australia 1252  LC CTYPE English Australia 1252     3  LC MONETARY English Australia 1252 LC NUMERIC C                        5  LC TIME English Australia 1252      attached base packages   1  stats     graphics  grDevices utils     datasets  methods   base       other attached packages   1  dplyr 0 2  plyr 1 8 1  loaded via a namespace  and not attached    1  assertthat 0 1 0 99 parallel 3 1 0      Rcpp 0 11 1          4  tools 3 1 0

User · Answer

For me  it helped to count the number of values per group  Copy the count table into a new object  Then filter for the max of the group based on the first grouping characteristic  For example   count table   lt - df   gt                   group by A  B    gt                   count     gt                   arrange A  desc n    count table   gt        group by A    gt       filter n    max n     or  count table   gt        group by A    gt       top n 1  n

User · Answer

Try this  result  lt - df   gt                 group by A  B    gt                filter value    max value     gt                arrange A B C   Seems to work  identical    as data frame result     ddply df    A  B   function x  x which max x value         1  TRUE  As pointed out in the comments  slice may be preferred here as per  RoyalITS  answer below if you strictly only want 1 row per group  This answer will return multiple rows if there are multiple with an identical maximum value

User · Answer

df   gt   group by A B    gt   slice which max value

User · Answer

This more verbose solution provides greater control on what happens in case of duplicate maximum value  in this example  it will take one of the corresponding rows randomly   library dplyr  df   gt   group by A  B    gt     mutate the rank    rank -value  ties method    random      gt     filter the rank    1    gt   select -the rank

User · Answer

More generally  I think you might want to get  top  of the rows that are sorted within a given group    For the case of where a single value is max d out  you have essentially sorted by only one column  However  it s often useful to hierarchically sort by multiple columns  for example  a date column and a time-of-day column       Answering the question of getting row with max  value   df   gt        Within each grouping of A and B values    group by  A  B    gt        Sort rows in descending order by  value  column    arrange  desc value      gt        Pick the top 1 value   slice 1    gt        Remember to ungroup in case you want to do further work without grouping    ungroup      Answering an extension of the question of    getting row with the max value of the lowest  C   df   gt        Within each grouping of A and B values    group by  A  B    gt        Sort rows in ascending order by C  and then within that by      descending order by  value  column    arrange  C  desc value      gt        Pick the one top row based on the sort   slice 1    gt        Remember to ungroup in case you want to do further work without grouping    ungroup

User · Answer

You can use top n  df   gt   group by A  B    gt   top n n 1    This will rank by the last column  value  and return the top n 1 rows   Currently  you can t change the this default without causing an error  See https   github com hadley dplyr issues 426

[r] How to select the rows with maximum values in each group with dplyr?

Examples related to r

Examples related to dplyr

Examples related to plyr

Examples related to greatest-n-per-group