get first and last values in group – dplyr group_by with last() and first()

15
votes

The code below should group the data by year and then create two new columns with the first and last value of each year.

library(dplyr)

set.seed(123)

d <- data.frame(
    group = rep(1:3, each = 3),
    year = rep(seq(2000,2002,1),3),
    value = sample(1:9, r = T))

d %>% 
    group_by(group) %>%
    mutate(
        first = dplyr::first(value),
        last = dplyr::last(value)
    )

However, it does not work as it should. The expected result would be

  group  year value first  last
  <int> <dbl> <int> <int> <int>
1     1  2000     3     3     4
2     1  2001     8     3     4
3     1  2002     4     3     4
4     2  2000     8     8     1
5     2  2001     9     8     1
6     2  2002     1     8     1
7     3  2000     5     5     5
8     3  2001     9     5     5
9     3  2002     5     5     5

Yet, I get this (it takes the first and the last value over the entire data frame, not just the groups):

  group  year value first  last
  <int> <dbl> <int> <int> <int>
1     1  2000     3     3     5
2     1  2001     8     3     5
3     1  2002     4     3     5
4     2  2000     8     3     5
5     2  2001     9     3     5
6     2  2002     1     3     5
7     3  2000     5     3     5
8     3  2001     9     3     5
9     3  2002     5     3     5
2
rdplyrdata-manipulation
It works for me: I get a column with the first value by group and one with the last value by group. – Jaap
Could you show the version of dplyr – akrun
Do you want summarize instead of mutate? – Bishops_Guest
My guess is a duplicate of this, that you are inadvertently using plyr::mutate instead of dplyr::mutate. However "does not work as intended" is so vague of a description that it's impossible to know... – Gregor Thomas
thanks all! @Gregor that solved the issue! also, i've updated the question to be more precise wrt expected result vs. actual result. – phillyooo

2 Answers

33
votes

dplyr::mutate() did the trick

d %>% 
    group_by(group) %>%
    dplyr::mutate(
        first = dplyr::first(value),
        last = dplyr::last(value)
    )
4
votes

You can also try by using summarise function within dpylr to get the first and last values of unique groups

 d %>% 
    group_by(group) %>% 
        summarise(first_value = first(na.omit(values)),
            last_value = last(na.omit(values))) %>% 
               left_join(d, ., by = 'group')