CODEWITHSUNDEEP
rdataframesurvival
Economist_Ayahuasca
Economist_Ayahuasca

Reputation: 1642

Reshape complex time to event data in R

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.

Upvotes: 0

Views: 116

Answers (1)

Wimpel
Wimpel

Reputation: 27732

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

Upvotes: 1

Related Questions