Select first and last row from grouped data

Question

Question  Using dplyr  how do I select the top and bottom observations rows of grouped data in one statement   Data  amp  Example  Given a data frame  df  lt - data frame id c 1 1 1 2 2 2 3 3 3                     stopId c  a   b   c   a   b   c   a   b   c                      stopSequence c 1 2 3 3 1 4 3 1 2     I can get the top and bottom observations from each group using slice  but using two separate statments   firstStop  lt - df   gt     group by id    gt     arrange stopSequence    gt     slice 1    gt     ungroup  lastStop  lt - df   gt     group by id    gt     arrange stopSequence    gt     slice n      gt     ungroup   Can I combine these two statmenets into one that selects both top and bottom observations

User · Answer

Not dplyr  but it s much more direct using data table   library data table  setDT df  df  df order id  stopSequence    I c 1L  N    by id  V1        id stopId stopSequence   1   1      a            1   2   1      c            3   3   2      b            1   4   2      c            4   5   3      b            1   6   3      a            3   More detailed explanation     1  get row numbers of first last observations from each group        basically  we sort the table by id stopSequence  then         grouping by id  name the row numbers of the first last        observations for each id  since this operation produces        a data table         I is data table shorthand for the row number        here  to be maximally explicit  I ve named the variable V1        as row num to give other readers of my code a clearer        understanding of what operation is producing what variable first last   df order id  stopSequence     row num    I c 1L  N     by id  idx   first last row num    2  extract rows by number df idx    Be sure to check out the Getting Started wiki for getting the data table basics covered

User · Answer

Just for completeness  You can pass slice a vector of indices   df   gt   arrange stopSequence    gt   group by id    gt   slice c 1 n       which gives    id stopId stopSequence 1  1      a            1 2  1      c            3 3  2      b            1 4  2      c            4 5  3      b            1 6  3      a            3

User · Answer

I know the question specified dplyr  But  since others already posted solutions using other packages  I decided to have a go using other packages too   Base package   df  lt - df with df  order id  stopSequence  stopId      merge df  duplicated df id             df  duplicated df id  fromLast   TRUE             all   TRUE    data table   df  lt -  setDT df  df order id  stopSequence      SD c 1  N    by id    sqldf   library sqldf  min  lt - sqldf  SELECT id  stopId  min stopSequence  AS StopSequence       FROM df GROUP BY id        ORDER BY id  StopSequence  stopId   max  lt - sqldf  SELECT id  stopId  max stopSequence  AS StopSequence       FROM df GROUP BY id        ORDER BY id  StopSequence  stopId   sqldf  SELECT   FROM min       UNION       SELECT   FROM max     In one query   sqldf  SELECT            FROM  SELECT id  stopId  min stopSequence  AS StopSequence               FROM df GROUP BY id                ORDER BY id  StopSequence  stopId          UNION         SELECT           FROM  SELECT id  stopId  max stopSequence  AS StopSequence               FROM df GROUP BY id                ORDER BY id  StopSequence  stopId      Output     id stopId StopSequence 1  1      a            1 2  1      c            3 3  2      b            1 4  2      c            4 5  3      a            3 6  3      b            1

User · Answer

using which min and which max     library dplyr  warn conflicts   F  df   gt      group by id    gt      slice c which min stopSequence   which max stopSequence       gt    A tibble  6 x 3   gt    Groups    id  3    gt       id stopId stopSequence   gt     lt dbl gt   lt fct gt           lt dbl gt    gt  1     1 a                 1   gt  2     1 c                 3   gt  3     2 b                 1   gt  4     2 c                 4   gt  5     3 b                 1   gt  6     3 a                 3   benchmark  It is also much faster than the current accepted answer because we find the min and max value by group  instead of sorting the whole stopSequence column     create a 100k times longer data frame df2  lt - bind rows replicate 1e5  df  F    bench  mark    mm  df2   gt        group by id    gt        slice c which min stopSequence   which max stopSequence       jeremy   df2   gt       group by id    gt       arrange stopSequence    gt       filter row number    1   row number    n       gt  Warning  Some expressions had a GC in every iteration  so filtering is disabled    gt    A tibble  2 x 6   gt    expression      min   median  itr sec  mem alloc  gc sec    gt     lt bch expr gt   lt bch tm gt   lt bch tm gt       lt dbl gt   lt bch byt gt      lt dbl gt    gt  1 mm           22 6ms     27ms     34 9     14 2MB     21 3   gt  2 jeremy      254 3ms    273ms      3 66    58 4MB     11 0

User · Answer

A different base R alternative would be to first order by id and stopSequence  split them based on id and for every id we select only the first and last index and subset the dataframe using those indices   df sapply with df  split order id  stopSequence   id    function x                      c x 1   x length x             id stopId stopSequence  1  1      a            1  3  1      c            3  5  2      b            1  6  2      c            4  8  3      b            1  7  3      a            3     Or similar using by  df unlist with df  by order id  stopSequence   id  function x                      c x 1   x length x

User · Answer

There is probably a faster way   df   gt     group by id    gt     arrange stopSequence    gt     filter row number    1   row number    n

User · Answer

Something like   library dplyr   df  lt - data frame id c 1 1 1 2 2 2 3 3 3                    stopId c  a   b   c   a   b   c   a   b   c                     stopSequence c 1 2 3 3 1 4 3 1 2    first last  lt - function x      bind rows slice x  1   slice x  n        df   gt     group by id    gt     arrange stopSequence    gt     do first last       gt     ungroup     Source  local data frame  6 x 3           id stopId stopSequence    1  1      a            1    2  1      c            3    3  2      b            1    4  2      c            4    5  3      b            1    6  3      a            3   With do you can pretty much perform any number of operations on the group but  jeremycg s answer is way more appropriate for just this task

User · Answer

Another approach with lapply and a dplyr statement  We can apply an arbitrary number of whatever summary functions to the same statement   lapply c first  last           function x  df   gt   group by id    gt   summarize all funs x      gt    bind rows     You could for example be interested in rows with the max stopSequence value as well and do    lapply c first  last  max  stopSequence             function x  df   gt   group by id    gt   summarize all funs x      gt   bind rows

User · Answer

Using data table     convert to data table setDT df     order  group  filter df order stopSequence      SD c 1   N    by   id      id stopId stopSequence 1   1      a            1 2   1      c            3 3   2      b            1 4   2      c            4 5   3      b            1 6   3      a            3

[r] Select first and last row from grouped data

Examples related to r

Examples related to dplyr