Finding next date in a sequence for more than one specific condition

Question

Suppose I have the following dataset:

df1 <- data_frame(date = c("2021-01-01", "2021-01-03", "2021-01-05", "2021-01-01", "2021-01-02", "2021-01-03", "2021-01-02", "2021-01-04", "2021-01-06"),
                 group = c("A", "A", "A", "B", "B", "B", "C", "C", "C"),
                 type = c("blue", "blue", "blue", "green", "green", "red", "yellow", "blue", "purple"))

I want to identify, for each group and type, if there's a future date for the same combination of group and type. This would yield me the following df:

df1 <- data_frame(date = c("2021-01-01", "2021-01-03", "2021-01-05", "2021-01-01", "2021-01-02", "2021-01-03", "2021-01-02", "2021-01-04", "2021-01-06"),
                 group = c("A", "A", "A", "B", "B", "B", "C", "C", "C"),
                 type = c("blue", "blue", "blue", "green", "green", "red", "yellow", "blue", "purple"),
                 future_date = c("2021-01-03", "2021-01-05", NA, "2021-01-02", NA, NA, NA, NA, NA))

score 0 · Answer 1 · answered Aug 25 '22 at 20:42

dplyr solution:

df1 |> 
  group_by(group, type) |> 
  mutate(future_date = lead(date, 1)) |>
  ungroup()

Output:

# A tibble: 9 x 4
  date       group type   future_date
  <chr>      <chr> <chr>  <chr>      
1 2021-01-01 A     blue   2021-01-03 
2 2021-01-03 A     blue   2021-01-05 
3 2021-01-05 A     blue   NA         
4 2021-01-01 B     green  2021-01-02 
5 2021-01-02 B     green  NA         
6 2021-01-03 B     red    NA         
7 2021-01-02 C     yellow NA         
8 2021-01-04 C     blue   NA         
9 2021-01-06 C     purple NA

Unfortunately that didn't work. Is it possible that it's because your future_date is class in the example you provided? In my dataset, it's a Date class of data. Thanks! — Gabriel Voelcker, Aug 29 '22 at 18:55

Finding next date in a sequence for more than one specific condition

1 Answers1