Create a new column in the data which has value from Value
column wehre Col1 = 'Start.Date'
or NA
otherwise. For each ID
we can fill the NA
value from the previous dates and remove the rows with 'Start.Date'
.
library(dplyr)
library(tidyr)
df %>%
mutate(Start.Date = as.Date(replace(Value, Col1 != 'Start.Date', NA))) %>%
group_by(ID) %>%
fill(Start.Date) %>%
ungroup() %>%
filter(Col1 != 'Start.Date')
# ID Col1 Value Start.Date
# <int> <chr> <chr> <date>
#1 123 Stem A1 2011-06-18
#2 123 Stem_1 A6 2011-06-18
#3 123 Stem_2 NA 2011-06-18
#4 321 Stem C1 2014-08-05
#5 321 Stem_1 C4 2014-08-05
#6 321 Stem_2 NA 2014-08-05
#7 677 Stem NA NA
#8 677 Stem_1 NA NA
#9 677 Stem_2 NA NA
data
df <- structure(list(ID = c(123L, 123L, 123L, 123L, 321L, 321L, 321L,
321L, 677L, 677L, 677L, 677L), Col1 = c("Start.Date", "Stem",
"Stem_1", "Stem_2", "Start.Date", "Stem", "Stem_1", "Stem_2",
"Start.Date", "Stem", "Stem_1", "Stem_2"), Value = c("2011-06-18",
"A1", "A6", NA, "2014-08-05", "C1", "C4", NA, NA, NA, NA, NA)),
class = "data.frame", row.names = c(NA, -12L))
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…