R: Converting Coordinates into "Trips"

I am working with the R programming language.

I have the following data frame that contains the Latitude and the Longitude of a ordered list of cities :

map_data <- data.frame("Lat" = c(43.5426, 43.2424, 43.6544, 43.6452, 43.6629), "Long" = c(-79.3871, -79.3860, -79.3807, -79.3806,-79.3957 ), id = c(1,2,3,4,5))

map_data$id = as.factor(map_data$id)

      Lat     Long id
1 43.5426 -79.3871  1
2 43.2424 -79.3860  2
3 43.6544 -79.3807  3
4 43.6452 -79.3806  4
5 43.6629 -79.3957  5

I would like to convert this data frame into the following format:

  start_lat start_long end_lat end_long
1   43.5426   -79.3871 43.2424  -79.386
2   43.2424   -79.3860 43.6540  -79.386

In the above data frame:

• the first row represents the "trip" from "city 1 to city 2"
• the second row represents the "trip" from "city 2 to city 3"
• etc.

Currently, I am doing this in Microsoft Excel manually – I only have a few cities so I can manage this manually.

But can someone please show me how to do this for large amounts of data?

Thanks!

>Solution :

Use data.table

library(data.table)

setDT(map_data)[
  , c("end_lat","end_long"):= shift(map_data[,c(1,2)],-1)][
    , .(id, start_lat = Lat, start_long=Long, end_lat,end_long)]

Output:

       id start_lat start_long end_lat end_long
   <fctr>     <num>      <num>   <num>    <num>
1:      1   43.5426   -79.3871 43.2424 -79.3860
2:      2   43.2424   -79.3860 43.6544 -79.3807
3:      3   43.6544   -79.3807 43.6452 -79.3806
4:      4   43.6452   -79.3806 43.6629 -79.3957
5:      5   43.6629   -79.3957      NA       NA