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 & Example

Given a data frame:

df <- 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 statements:

firstStop <- df %>%
  group_by(id) %>%
  arrange(stopSequence) %>%
  slice(1) %>%
  ungroup

lastStop <- df %>%
  group_by(id) %>%
  arrange(stopSequence) %>%
  slice(n()) %>%
  ungroup

Can I combine these two statements into one that selects both top and bottom observations?

See also [How to select the first and last row within a grouping variable in a data frame?](https://stackoverflow.com/questions/8203818/how-to-select-the-first-and-last-row-within-a-grouping-variable-in-a-data-frame) — Henrik, Nov 28 '18 at 08:04

score 323 · Accepted Answer · answered Jul 21 '15 at 01:48

323

There is probably a faster way:

df %>%
  group_by(id) %>%
  arrange(stopSequence) %>%
  filter(row_number()==1 | row_number()==n())

answered Jul 21 '15 at 01:48

jeremycg

24,657
5
63
74

91

`rownumber() %in% c(1, n())` would obviate the need to run vector scan twice – MichaelChirico Sep 04 '16 at 12:20
20

@MichaelChirico I suspect you omitted an `_`? i.e. `filter(row_number() %in% c(1, n()))` – Eric Fail Oct 11 '17 at 11:03

score 138 · Answer 2 · answered Jul 21 '15 at 17:11

138

Just for completeness: You can pass slice a vector of indices:

df %>% arrange(stopSequence) %>% group_by(id) %>% 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

answered Jul 21 '15 at 17:11

Frank

66,179
8
96
180

might even be faster than `filter` - have not tested this, but see [here](https://stackoverflow.com/a/27766197/7941188) – tjebo Jul 03 '18 at 16:42
1

@Tjebo Unlike filter, slice can return the same row multiple times, eg `mtcars[1, ] %>% slice(c(1, n()))` so in that sense the choice between them depends on what you want returned. I'd expect the timings to be close unless `n` is very large (where slice might be favored), but haven't tested either. – Frank Jul 03 '18 at 16:47

MichaelChirico · Answer 3 · 2023-03-27T16:25:38.450

18

Not dplyr, but it's much more direct using data.table:

library(data.table)
setDT(df)
df[
  df[order(id, stopSequence), .(rows = .I[c(1L,.N)]), by=id]$rows
]
    #  rows 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 rows
#      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), .(rows = .I[c(1L,.N)]), by=id]
idx = first_last$rows

# 2) extract rows by number
df[idx]

Be sure to check out the Getting Started wiki for getting the data.table basics covered

edited Mar 27 '23 at 16:25

answered Jul 21 '15 at 02:05

MichaelChirico

33,841
14
113
198

1

Or `df[ df[order(stopSequence), .I[c(1,.N)], keyby=id]$V1 ]`. Seeing `id` appear twice is weird to me. – Frank Jul 21 '15 at 17:22
You can set keys in the `setDT` call. So an `order` call no need here. – Artem Klevtsov Feb 01 '17 at 07:52
3

@ArtemKlevtsov - you may not always want to set the keys, though. – SymbolixAU Apr 23 '17 at 22:57
2

Or `df[order(stopSequence), .SD[c(1L,.N)], by = id]`. See [here](https://stackoverflow.com/a/14155807/4241780) – JWilliman Jul 11 '17 at 09:33
@JWilliman that won't necessarily be _exactly_ the same, since it won't reorder on `id`. I think `df[order(stopSequence), .SD[c(1L, .N)], keyby = id]` should do the trick (with the minor difference to the solution above that the result will be `key`ed – MichaelChirico Oct 11 '17 at 11:13
@MichaelChirico how would u do this if u want the last instead of first? – Keweik Nov 18 '20 at 07:51
@Keweik I don't follow -- the question is about first _and_ last. My answer shows how to do both simultaneously. Just use `.N` alone for last only, or `1L` alone for first only. – MichaelChirico Nov 18 '20 at 08:32
@MichaelChirico, i see, sorry my bad. – Keweik Nov 18 '20 at 08:35
@keweik don't sweat it :) – MichaelChirico Nov 18 '20 at 14:45

moodymudskipper · Answer 4 · 2020-01-31T17:32:37.583

using which.min and which.max :

library(dplyr, warn.conflicts = F)
df %>% 
  group_by(id) %>% 
  slice(c(which.min(stopSequence), which.max(stopSequence)))

#> # A tibble: 6 x 3
#> # Groups:   id [3]
#>      id stopId stopSequence
#>   <dbl> <fct>         <dbl>
#> 1     1 a                 1
#> 2     1 c                 3
#> 3     2 b                 1
#> 4     2 c                 4
#> 5     3 b                 1
#> 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 <- bind_rows(replicate(1e5, df, F)) 
bench::mark(
  mm =df2 %>% 
    group_by(id) %>% 
    slice(c(which.min(stopSequence), which.max(stopSequence))),
  jeremy = df2 %>%
    group_by(id) %>%
    arrange(stopSequence) %>%
    filter(row_number()==1 | row_number()==n()))
#> Warning: Some expressions had a GC in every iteration; so filtering is disabled.
#> # A tibble: 2 x 6
#>   expression      min   median `itr/sec` mem_alloc `gc/sec`
#>   <bch:expr> <bch:tm> <bch:tm>     <dbl> <bch:byt>    <dbl>
#> 1 mm           22.6ms     27ms     34.9     14.2MB     21.3
#> 2 jeremy      254.3ms    273ms      3.66    58.4MB     11.0

score 9 · Answer 5 · answered Jul 21 '15 at 01:48

9

Something like:

library(dplyr)

df <- 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 <- function(x) {
  bind_rows(slice(x, 1), slice(x, n()))
}

df %>%
  group_by(id) %>%
  arrange(stopSequence) %>%
  do(first_last(.)) %>%
  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.

answered Jul 21 '15 at 01:48

hrbrmstr

77,368
11
139
205

1

Hadn't considered writing a function - certainly a good way of doing something more complex. – tospig Jul 21 '15 at 01:57
1

This seems overcomplicated compared to just using `slice`, like `df %>% arrange(stopSequence) %>% group_by(id) %>% slice(c(1,n()))` – Frank Jul 21 '15 at 05:01
5

Not disagreeing (and I pointed to jeremycg's as a better answer _in_ the post) but having a `do` example here might help others when `slice` won't work (i.e. more complex operations on a group). And, you shld post your comment as an answer (it's the best one). – hrbrmstr Jul 21 '15 at 11:44

mpalanco · Answer 6 · 2015-07-22T06:50:35.720

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 <- df[with(df, order(id, stopSequence, stopId)), ]
merge(df[!duplicated(df$id), ], 
      df[!duplicated(df$id, fromLast = TRUE), ], 
      all = TRUE)

data.table:

df <-  setDT(df)
df[order(id, stopSequence)][, .SD[c(1,.N)], by=id]

sqldf:

library(sqldf)
min <- sqldf("SELECT id, stopId, min(stopSequence) AS StopSequence
      FROM df GROUP BY id 
      ORDER BY id, StopSequence, stopId")
max <- 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

s_baldur · Answer 7 · 2019-09-24T07:36:10.723

3

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

edited Sep 24 '19 at 07:36

answered Sep 24 '18 at 09:24

s_baldur

29,441
4
36
69

score 3 · Answer 8 · answered Aug 24 '22 at 14:51

This works fine:

df %>%
  group_by(id) %>%
  arrange(stopSequence) %>%
  slice(1,n())

# A tibble: 6 × 3
# Groups:   id [3]
#     id stopId stopSequence
#  <dbl> <chr>         <dbl>
#1     1 a                 1
#2     1 c                 3
#3     2 b                 1
#4     2 c                 4
#5     3 b                 1
#6     3 a                 3

Sahir Moosvi · Answer 9 · 2018-12-20T18:30:16.147

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 %>% group_by(id) %>% summarize_all(funs(x))) %>% 
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 %>% group_by(id) %>% summarize_all(funs(x))) %>%
bind_rows()

score 1 · Answer 10 · answered Apr 02 '19 at 01:39

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)])))), ]

Select first and last row from grouped data

10 Answers10

Linked

Related