How to calculate the order of countries a person visits?

Question

I have a data frame df like below:

id     country_id
__     _______
1         01
1         02
1         03
2         01
2         04
2         05
3         06
3         08
3         09
3         10

This shows how each person travelled to which country.From this I need to have something like this

    id       from      to
   ___      _____    ____
    1         01       02
    1         02       03
    1         03       NA
    2         01       04
    2         04       05  
    2         05       06
    3         06       NA
    3         08       09 
    3         09       10
    3         10       NA

I can make df[-1] which gives the whole vector without top and then cbind it But then how to make NA at each id's last entry? Any help is appreciated .

Answer 1

We can do this by getting the lead of the second column, grouped by 'id'

library(dplyr)
df1 %>% 
   group_by(id) %>% 
   transmute(from = country_id, to= lead(from))
# A tibble: 10 x 3
# Groups: id [3]
#      id  from    to
#   <int> <chr> <chr>
# 1     1    01    02
# 2     1    02    03
# 3     1    03  <NA>
# 4     2    01    04
# 5     2    04    05
# 6     2    05  <NA>
# 7     3    06    08
# 8     3    08    09
# 9     3    09    10
#10     3    10  <NA>

Answer 2

Here is a solution with data.table

library("data.table")
d <- read.table(header=TRUE, stringsAsFactors = FALSE, colClasses = c("integer", "character"), text=
'id     country_id
1         01
1         02
1         03
2         01
2         04
2         05
3         06
3         08
3         09
3         10 ')
setDT(d)[, to:=shift(country_id, type="lead"), by=id][]
#    id country_id to
# 1:  1         01 02
# 2:  1         02 03
# 3:  1         03 NA
# 4:  2         01 04
# 5:  2         04 05
# 6:  2         05 NA
# 7:  3         06 08
# 8:  3         08 09
# 9:  3         09 10
# 10: 3         10 NA