Grouping variables select first row (keep one column), last row (keep different column)

Question

I have the following table:

id  origin destination price
 1     A      B          2
 1     C      D          2
 2     A      B          3
 3     B      E          6
 3     E      C          6
 3     C      F          6

Basically what I want to do is to group it by id, select the first element from origin, and keep the last element from destination resulting in this table.

id  origin destination price
 1     A      D          2
 2     A      B          3
 3     B      F          6

I know how to select the first and last row, but not to do what I want.

df %>%
group_by(id) %>%
slice(c(1, n())) %>%
ungroup()

Is it possible to do this with dplyr or even with data.table?

Answer 1

A solution with library(data.table):

unique(setDT(df)[, "origin" := origin[1] , by = id][, "destination" := destination[.N], by = id][, "price" := price[1] , by = id][])

A shortcut suggested by Imo:

setDT(df)[, .(origin=origin[1], destination=destination[.N], price=price[1]), by=id]

Answer 2

A base R approach using split:

do.call(rbind, lapply(split(df, df$id), 
                      function(a) with(a, data.frame(origin=head(origin,1), destination=tail(destination,1), price=head(price,1)))))

#  origin destination price
#1      A           D     2
#2      A           B     3
#3      B           F     6