I am trying to move entries in my column around to set them up as start/stop times. The first row for each id is set up fine but I need to shift the rest down and over for this to work as I am trying.
I have tried using dplyr and mutating the entries into new columns but the problem is the time entry is in another column so I am trying to work around that.
#This is what my data looks like
mydata<-data.frame(id=c(rep(1,3),rep(2,2)),baseline=c(rep("2018-07-14",3),
rep("2018-06-16",2)),
date=c("2018-08-23","2018-09-20","2018-10-05","2018-07-04","2018-08-08"))
head(mydata)
expecteddata<-data.frame(id=c(rep(1,3),rep(2,2)),
start=c("2018-07-14","2018-08-23","2018-09-20","2018-06-16","2018-07-04"),
end=c("2018-08-23","2018-09-20","2018-10-05","2018-07-04","2018-08-08"))
head(expecteddata)
This is what I am hoping to get. It also might be nice to increment start times since different rows would belong to different risk sets but that is a different issue. Any help or pointers would be greatly appreciated on how I can proceed.
Ensure that the date variables are the correct class and try:
library(dplyr)
mydata %>%
group_by(id) %>%
mutate(baseline = lag(date, default = first(baseline))) %>%
rename(start = baseline, end = date)
# A tibble: 5 x 3
# Groups: id [2]
id start end
<dbl> <date> <date>
1 1 2018-07-14 2018-08-23
2 1 2018-08-23 2018-09-20
3 1 2018-09-20 2018-10-05
4 2 2018-06-16 2018-07-04
5 2 2018-07-04 2018-08-08