Geek Answers Handbook

R is trying to create start and stop times from one column

I am new to R and am stuck with this seemingly simple problem. I have a large data frame with 4 columns; id, observation date, value (alb) and end date. One identifier can have from 1 to 15 observations on different dates. The end date is the time of the event or censorship with a single identifier.

    id        date  alb         end
  1143  2010-03-23   41  2010-12-15
  1143  2010-06-29   39  2010-12-15
  1144  2008-01-01   34  2009-08-06 
  1145  2010-03-23   42  2012-10-25 
  1145  2011-01-12   45  2012-10-25

To analyze survival using alb as a time-varying covariate, I try to create an episode for each observation using the start and end time column. I am trying to create a column where the stop time is the start time of the next alb observation or the end time if there are no additional alb observations for this identifier. For instance:

    id        date  alb         end       start        stop
  1143  2010-03-23   41  2010-12-15  2010-03-23  2010-06-29
  1143  2010-06-29   39  2010-12-15  2010-06-29  2010-12-15
  1144  2008-01-01   34  2009-08-06  2008-01-01  2009-08-06
  1145  2010-03-23   42  2012-10-25  2010-03-23  2011-01-12
  1145  2011-01-12   45  2012-10-25  2011-01-12  2012-10-25

. , if else. - ? !

r2evans, data.frame, dplyr 1970-01-01. ( 130 000 ).

  id       date  alb        end
1143 2010-03-23 41.0 1996-08-10
1143 2010-06-29 39.0 1996-08-10
1143 2011-01-12 42.0 1996-08-10
1143 2010-09-28 47.0 1996-08-10
1143 2011-07-19 40.0 1996-08-10
1143 2012-06-12 41.0 1996-08-10
1143 2013-06-25 40.0 1996-08-10
1143 2013-12-26 40.0 1996-08-10
1143 2014-06-15 40.0 1996-08-10
1143 2014-12-26 39.9 1996-08-10
1144 2008-01-01 34.0 2015-04-28
1145 2010-03-23 42.0 2015-04-28
1145 2012-01-13 44.0 2015-04-28
1145 2012-06-15 41.0 2015-04-28
+4
2

:

data.frame(
    id=c(        1143,         1143,         1144,         1145,         1145 ),
  date=c("2010-03-23", "2010-06-29", "2008-01-01", "2010-03-23", "2011-01-12" ),
   alb=c(          41,           39,           34,           42,           45 ),
   end=c("2010-12-15", "2010-12-15", "2009-08-06", "2012-10-25", "2012-10-25" )
)

dplyr:

library(dplyr)
df %>%
    group_by(id) %>%
    mutate(start=date, stop=lead(start, default=end[1]))
## Source: local data frame [5 x 6]
## Groups: id
## 
##     id       date alb        end      start       stop
## 1 1143 2010-03-23  41 2010-12-15 2010-03-23 2010-06-29
## 2 1143 2010-06-29  39 2010-12-15 2010-06-29 2010-12-15
## 3 1144 2008-01-01  34 2009-08-06 2008-01-01 2009-08-06
## 4 1145 2010-03-23  42 2012-10-25 2010-03-23 2011-01-12
## 5 1145 2011-01-12  45 2012-10-25 2011-01-12 2012-10-25

base:

do.call('rbind', by(df, df$id, function(x) {
    cbind(x, start=x$date, stop=lead(x$date, default=x$end[1]))
}))
##          id       date alb        end      start       stop
## 1143.1 1143 2010-03-23  41 2010-12-15 2010-03-23 2010-06-29
## 1143.2 1143 2010-06-29  39 2010-12-15 2010-06-29 2010-12-15
## 1144   1144 2008-01-01  34 2009-08-06 2008-01-01 2009-08-06
## 1145.4 1145 2010-03-23  42 2012-10-25 2010-03-23 2011-01-12
## 1145.5 1145 2011-01-12  45 2012-10-25 2011-01-12 2012-10-25
+3

:

dat<-read.table(text="
    id        date  alb         end
  1143  2010-03-23   41  2010-12-15
  1143  2010-06-29   39  2010-12-15
  1144  2008-01-01   34  2009-08-06 
  1145  2010-03-23   42  2012-10-25 
  1145  2011-01-12   45  2012-10-25", header=TRUE, stringsAsFactors=FALSE)

:

dat$start <- dat$date
dat$stop[!duplicated(dat$id, fromLast = TRUE)] <- dat$end[!duplicated(dat$id, fromLast = TRUE)]
dat$stop[duplicated(dat$id, fromLast = TRUE)] <- dat[duplicated(dat$id), "date"]

dat
#    id       date alb        end      start       stop
#1 1143 2010-03-23  41 2010-12-15 2010-03-23 2010-06-29
#2 1143 2010-06-29  39 2010-12-15 2010-06-29 2010-12-15
#3 1144 2008-01-01  34 2009-08-06 2008-01-01 2009-08-06
#4 1145 2010-03-23  42 2012-10-25 2010-03-23 2011-01-12
#5 1145 2011-01-12  45 2012-10-25 2011-01-12 2012-10-25
+1

More articles:


All Articles