-
将日期列转换为日期类。
-
对于每个ID use complete从最小值创建日期序列DateStart最多为DateEnd.
-
fill the NA先前的非 NA 值，除非Date > DateEnd.
-
对于每组ID, DateStart and DateEnd保持行与NA每组中的值或行号 1。
library(dplyr)
library(tidyr)
df %>%
mutate(across(-ID, lubridate::mdy)) %>%
group_by(ID) %>%
complete(Date = seq(min(DateStart), max(DateEnd), by = '1 day')) %>%
fill(DateStart, DateEnd) %>%
ungroup %>%
mutate(across(c(DateStart, DateEnd), ~replace(., Date > DateEnd, NA))) %>%
group_by(ID, DateStart, DateEnd) %>%
filter(is.na(DateStart) | row_number() == 1)
# ID Date DateStart DateEnd
# <int> <date> <date> <date>
#1 1 2021-02-11 2021-02-11 2021-02-17
#2 1 2021-02-18 NA NA
#3 1 2021-02-19 2021-02-19 2021-02-21
#4 2 2021-01-15 2021-01-15 2021-01-20
#5 2 2021-01-21 NA NA
#6 2 2021-01-22 NA NA
#7 2 2021-01-23 2021-01-23 2021-01-24
data
df <- structure(list(ID = c(1L, 1L, 2L, 2L), Date = c("2/11/2021",
"2/19/2021", "1/15/2021", "1/23/2021"), DateStart = c("2/11/2021",
"2/19/2021", "1/15/2021", "1/23/2021"), DateEnd = c("2/17/2021",
"2/21/2021", "1/20/2021", "1/24/2021")),
class = "data.frame", row.names = c(NA, -4L))
