0

I have the following data frame where I have the beginning of the time, the end of the time AND a Date where the individual got the observations A or B.

df = 
id Date Start_Date End_Date A B
1  2    1          4        1 0
1  3    1          4        0 1
2  3    2          9        1 0
2  6    2          9        1 0
2  7    2          9        1 0
2  2    2          9        0 1

What I want to do is to order the time chronologically (create a new Time variable), and fill the information A and B accordingly, that is, if the individual got A at time 2 it should also have at the following up times (i.e. 3 until End_Time). Ideally, the interval times are not regular but follow the changes in Date (see individual 2):

Cool_df = 
id Time A B
1  1    0 0
1  2    1 0
1  3    1 1
1  4    1 1
2  2    0 1
2  3    1 1
2  6    1 1
2  7    1 1
2  9    1 1

Any recommendation highly appreciated because I do not know where to start.

Economist_Ayahuasca
  • 1,648
  • 24
  • 33
  • I do not see the logic why in `Cool_df` on row `id = 1 & Time = 1` A equals 1, whil on row `id = 2, Time = 2` A equals 0. Can you explain this? – Wimpel Apr 08 '21 at 10:48
  • Hi @Wimpel, good point. After double-checking I come to realize that it should be 0 for id = 1 and time = 1 since A first occurs at time 2. I have edited the question. Thanks a lot! – Economist_Ayahuasca Apr 08 '21 at 10:55

1 Answers1

1

Here is a data.table approach

library(data.table)
setDT(df)
# Summarise dates
ans <- df[, .(Date = unique(c(min(Start_Date), Date, max(End_Date)))), by = .(id)]
# Join
ans[ df[A==1,], A := 1, on = .(id,Date)]
ans[ df[B==1,], B := 1, on = .(id,Date)]
#fill down NA's using "locf"
cols.to.fill = c("A","B")
ans[, (cols.to.fill) := lapply(.SD, nafill, type = "locf"), 
    by = .(id), .SDcols = cols.to.fill]
#fill other NA with zero
ans[is.na(ans)] <- 0

#    id Date A B
# 1:  1    1 0 0
# 2:  1    2 1 0
# 3:  1    3 1 1
# 4:  1    4 1 1
# 5:  2    2 0 1
# 6:  2    3 1 1
# 7:  2    6 1 1
# 8:  2    7 1 1
# 9:  2    9 1 1
Wimpel
  • 26,031
  • 1
  • 20
  • 37