How can to create a lag and lead column, grouped by ID and arranged by date?

Question

I need to complete what feels like a relatively simple task, but I'm not really sure where to begin.

I have a data frame(DF1) that has 2 relevant columns. One is an ID and the other is a date. I want to have two new cols that give me the date of the previous event and the date of the next event by group. This will require me to group by ID and arrange by date.

I tried using dplyr and it seemed buggy. What I attempted first:

ID<-c("A","A","B","A","B","B","B","A")
date <- c("1/1/2021"
          , "1/5/2021"
          , '2/1/2021'
          , "1/7/2021"
          , "2/2/2021"
          , "2/5/2021"
          , "2/8/2021"
          , "1/9/2021")

DF1 <- data.frame(ID, date)

DF1
ID     date
A      1/1/2021
A      1/5/2021
B      2/1/2021
A      1/7/2021
B      2/2/2021
B      2/5/2021
B      2/8/2021
A      1/9/2021

DF2 <- DF1 %>% group_by(ID) %>%arrange(date) %>%
  mutate(nextdate= dplyr::lead(date, n = 1, default = NA))

DF2
ID    date     nextdate  
A     1/1/2021 1/5/2021
A     1/5/2021 1/7/2021
A     1/7/2021 1/9/2021
A     1/9/2021 2/1/2021
B     2/1/2021 2/2/2021
B     2/2/2021 2/5/2021
B     2/5/2021 2/8/2021
B     2/8/2021 NA

This gets close, but the 4th row should be na, so it seems like it's dropping the grouping. When I drop the arrange then it gives me something else odd (DF3):

DF3 <- DF1 %>% group_by(ID) %>%
  mutate(nextdate= dplyr::lead(date, n = 1, default = NA))

DF3
  ID    date     nextdate
1 A     1/1/2021 1/5/2021
2 A     1/5/2021 2/1/2021
3 B     2/1/2021 1/7/2021
4 A     1/7/2021 2/2/2021
5 B     2/2/2021 2/5/2021
6 B     2/5/2021 2/8/2021
7 B     2/8/2021 1/9/2021
8 A     1/9/2021 NA

I saw a suggestion on a few other posts to try and group by ID then arrange using "order_by" with the mutate command. This is what I get (DF4) when I do that:

DF4 <- DF1 %>% group_by(ID) %>%
  mutate(nextdate= dplyr::lead(date, order_by=date, n = 1, default = NA))

DF4
  ID    date     nextdate
1 A     1/1/2021 1/5/2021
2 A     1/5/2021 1/7/2021
3 B     2/1/2021 2/2/2021
4 A     1/7/2021 1/9/2021
5 B     2/2/2021 2/5/2021
6 B     2/5/2021 2/8/2021
7 B     2/8/2021 NA      
8 A     1/9/2021 2/1/2021

So, I guess the question is, how can I achieve the following (DF5), perhaps without using dplyr:

DF2
ID    date     nextdate prevdate  
A     1/1/2021 1/5/2021 NA
A     1/5/2021 1/7/2021 1/1/2021
A     1/7/2021 1/9/2021 1/5/2021
A     1/9/2021 NA       1/7/2021
B     2/1/2021 2/2/2021 NA
B     2/2/2021 2/5/2021 2/1/2021
B     2/5/2021 2/8/2021 2/5/2021
B     2/8/2021 NA       2/5/2021

That might be because you have `plyr` loaded. Try using `dplyr::mutate` to call the function explicitly from `dplyr`. — Ronak Shah, Oct 19 '21 at 01:45

score 0 · Accepted Answer · answered Oct 18 '21 at 23:00

I don't get it for me it seems to work fine

library(tidyverse)

ID<-c("A","A","B","A","B","B","B","A")
date <- c("1/1/2021"
          , "1/5/2021"
          , '2/1/2021'
          , "1/7/2021"
          , "2/2/2021"
          , "2/5/2021"
          , "2/8/2021"
          , "1/9/2021")

DF1 <- data.frame(ID, date)

DF1 |>
  arrange(ID,date) |> 
  group_by(ID) |> 
  mutate(nextdate = lead(date),
         lastdate = lag(date))
#> # A tibble: 8 x 4
#> # Groups:   ID [2]
#>   ID    date     nextdate lastdate
#>   <chr> <chr>    <chr>    <chr>   
#> 1 A     1/1/2021 1/5/2021 <NA>    
#> 2 A     1/5/2021 1/7/2021 1/1/2021
#> 3 A     1/7/2021 1/9/2021 1/5/2021
#> 4 A     1/9/2021 <NA>     1/7/2021
#> 5 B     2/1/2021 2/2/2021 <NA>    
#> 6 B     2/2/2021 2/5/2021 2/1/2021
#> 7 B     2/5/2021 2/8/2021 2/2/2021
#> 8 B     2/8/2021 <NA>     2/5/2021

^{Created on 2021-10-18 by the reprex package (v2.0.1)}

I just tried to run this on a different machine and it seems to work. Could it be that another package is overriding my dplyr functions? Because specifying dplyr:: seems to fix the problem? Or could dplyr be acting up in some way? — Tham v, Oct 18 '21 at 23:51

How can to create a lag and lead column, grouped by ID and arranged by date?

1 Answers1