I need to update the Date2 column by calculating the next record_date of the ID. (i.e) The Date2 column should be (record_date-1) of next record_date of ID
My dataset is like:
ID Date1
123 05-06-2012
123 06-08-2012
123 09-09-2013
234 05-04-2014
234 06-09-2014
I need cumulative type date format (i.e) Expected results
ID Date1 Date2
123 05-06-2012 05-08-2012
123 06-08-2012 08-09-2013
123 09-09-2013 NA
234 05-04-2014 05-09-2014
234 06-09-2014 NA
You can convert the Date1 to date class and subtract 1 date from the next date for each ID.
library(dplyr)
df %>%
mutate(Date1 = lubridate::dmy(Date1)) %>%
group_by(ID) %>%
mutate(Date2 = lead(Date1) - 1)
# ID Date1 Date2
# <int> <date> <date>
#1 123 2012-06-05 2012-08-05
#2 123 2012-08-06 2013-09-08
#3 123 2013-09-09 NA
#4 234 2014-04-05 2014-09-05
#5 234 2014-09-06 NA
data
df <- structure(list(ID = c(123L, 123L, 123L, 234L, 234L),
Date1 = c("05-06-2012", "06-08-2012", "09-09-2013", "05-04-2014", "06-09-2014")),
class = "data.frame", row.names = c(NA, -5L))
