How to group the similar row, and queries some row column in R?

Question

I am new to the R language. My problems are

1. I want to manage the origin-destination location with multiple ids. Here is an example of my raw data (csv file)

vehicle_id	location	time
0111111111	13_100.27	13.58
0111111111	13_140.87	17.38
0222222222	12_445.78	02.15

Ps. there are many vehicle_id data which some are same id and differs.

2. I would like to sum vehicle_id into groups and create the start and finish location based on time like this;

vehicle_id	location of origin	start time	location of destination	finish time
0111111111	13_100.27	13.58	13_140.87	17.38
0222222222	13_140.87	17.38	13_102.99	23.57
0333333333	12_445.78	02.15	10.589.58	05.69

How can I code it to get the result like above? Thanks for your help in advance.

Answer 1

Does this work:

library(dplyr)
df %>% group_by(vehicle_id) %>% summarise('location origin' = location, 'start time' = time, 
                                           'location destination' = lead(location), 'finish time' = lead(time)) %>% na.omit()
`summarise()` regrouping output by 'vehicle_id' (override with `.groups` argument)
# A tibble: 2 x 5
# Groups:   vehicle_id [2]
  vehicle_id `location origin` `start time` `location destination` `finish time`
       <dbl> <chr>                    <dbl> <chr>                          <dbl>
1  111111111 13_100.27                13.6  13_140.87                       17.4
2  222222222 12_445.78                 2.15 13_102.99                       23.6

Data used:

df
# A tibble: 4 x 3
  vehicle_id location   time
       <dbl> <chr>     <dbl>
1  111111111 13_100.27 13.6 
2  111111111 13_140.87 17.4 
3  222222222 12_445.78  2.15
4  222222222 13_102.99 23.6 

Answer 2

Using SQL we can extract the start time and location for each trip into start_data and similarly the end time and location into end_data and then join them. This makes use of the fact that SQLite will automatically pick out the location on the minimizing row if min is used and similarly for max.

library(sqldf)

sqldf("
 with start_data as (
   select vehicle_id, location start_location, min(time) start_time 
   from Trips 
   group by vehicle_id),
 end_data as (
   select vehicle_id, location end_location, max(time) end_time
   from Trips 
   group by vehicle_id)
 select * from start_data left join end_data using(vehicle_id)")

giving:

  vehicle_id start_location start_time end_location end_time
1  111111111      13_100.27      13.58    13_140.87    17.38
2  222222222      12_445.78       2.15    12_445.78     2.15

Note

The input in reproducible form.

Trips <- structure(list(vehicle_id = c(111111111L, 111111111L, 222222222L
), location = c("13_100.27", "13_140.87", "12_445.78"), time = c(13.58, 
17.38, 2.15)), class = "data.frame", row.names = c(NA, -3L))