Subtract days between dates in subsequent rows of different coloumns

[Marit]

Hi!

I have a dataset where I would like to subtract the column Date1 from the previous row of the column Date2 by a group id. Does anyone have suggestion on how to solve this?

Groupid Date1 Date2 Daycalculation
1 01.01.22 15.01.22
1 27.01.22 16.02.22 12
2 02.03.22 20.03.22
2 02.04.22 18.04.22 13

Regards M

[pathos]

Here is an example

library(lubridate)
library(tidytable)

df = data.frame(Groupid = c(1, 1, 2, 2),
                Date1 = c('01.01.22', '27.01.22', '02.03.22', '02.04.22'),
                Date2 = c('15.01.22', '16.02.22', '20.03.22', '19.04.22'))

df |>
  mutate.(Date1 = dmy(Date1),
          Date2 = dmy(Date2)) |>
  mutate.(Date3 = Date1 - lags.(Date2),
          .by = Groupid)

Output

# A tidytable: 4 × 4
  Groupid Date1      Date2      Date3  
    <dbl> <date>     <date>     <drtn> 
1       1 2022-01-01 2022-01-15 NA days
2       1 2022-01-27 2022-02-16 12 days
3       2 2022-03-02 2022-03-20 NA days
4       2 2022-04-02 2022-04-19 13 days