Add a new date field based of off repeat encounters

Question

I have repeat encounters with animals that have a unique IndIDII and a unique GPSSrl number. Each encounter has a FstCptr date.

dat <- structure(list(IndIDII = c("BHS_115", "BHS_115", "BHS_372", "BHS_372", 
"BHS_372", "BHS_372"), GPSSrl = c("035665", "036052", "034818", 
"035339", "036030", "036059"), FstCptr = structure(c(1481439600, 
1450162800, 1426831200, 1481439600, 1457766000, 1489215600), class = c("POSIXct", 
"POSIXt"), tzone = "")), .Names = c("IndIDII", "GPSSrl", "FstCptr"
), class = "data.frame", row.names = c(1L, 2L, 29L, 30L, 31L, 
32L))

> dat
   IndIDII GPSSrl    FstCptr
1  BHS_115 035665 2016-12-11
2  BHS_115 036052 2015-12-15
29 BHS_372 034818 2015-03-20
30 BHS_372 035339 2016-12-11
31 BHS_372 036030 2016-03-12
32 BHS_372 036059 2017-03-11

For each IndID-GPSSrl grouping, I want to create a new field (NextCptr) that documents the date of the next encounter. For the last encounter, the new field would be NA, for example:

dat$NextCptr <- as.Date(c("2015-12-15", NA, "2016-12-11", "2016-03-12", "2017-03-11", NA))

> dat
   IndIDII GPSSrl    FstCptr   NextCptr
1  BHS_115 035665 2016-12-11 2015-12-15
2  BHS_115 036052 2015-12-15       <NA>
29 BHS_372 034818 2015-03-20 2016-12-11
30 BHS_372 035339 2016-12-11 2016-03-12
31 BHS_372 036030 2016-03-12 2017-03-11
32 BHS_372 036059 2017-03-11       <NA>

I would like to work within dplyr and group_by(IndIDII, GPSSrl).

As always, many thanks!

score 1 · Accepted Answer · answered Jan 03 '18 at 02:04

Group by column IndIDII then use lead to shift FstCptr forward by one:

dat %>% group_by(IndIDII) %>% mutate(NextCptr = lead(FstCptr))

# A tibble: 6 x 4
# Groups:   IndIDII [2]
#  IndIDII GPSSrl             FstCptr            NextCptr
#    <chr>  <chr>              <dttm>              <dttm>
#1 BHS_115 035665 2016-12-11 02:00:00 2015-12-15 02:00:00
#2 BHS_115 036052 2015-12-15 02:00:00                  NA
#3 BHS_372 034818 2015-03-20 02:00:00 2016-12-11 02:00:00
#4 BHS_372 035339 2016-12-11 02:00:00 2016-03-12 02:00:00
#5 BHS_372 036030 2016-03-12 02:00:00 2017-03-11 02:00:00
#6 BHS_372 036059 2017-03-11 02:00:00                  NA

If you need to shift the column in the opposite direction, lag could also be useful, dat %>% group_by(IndIDII) %>% mutate(NextCptr = lag(FstCptr)).

Add a new date field based of off repeat encounters

1 Answers1