Using diff() to find elapsed time when missing values exist

Question

I have data with a grouping variable 'id' and a date column with missing values:

id time    date
a   1   2004-01-13
a   2   2004-05-04
a   3       NA
a   4   2007-03-20
a   5       NA
b   1   2004-01-11
b   2   2004-05-04
b   3       NA
b   4   2006-10-10
b   5       NA
c   1   2004-05-23
c   2   2004-10-14
c   3       NA
c   4       NA
c   5       NA

Within each 'id', I would like to find the difference between each consecutive pair of dates:

id time    date                 difftime
a   1   2004-01-13                 NA
a   2   2004-05-04      (2004-05-04)-(2004-01-13)
a   3       NA                     NA  
a   4   2007-03-20      (2007-03-20)-(2004-05-04)
a   5       NA                     NA
b   1   2004-01-11                 NA
b   2   2004-05-04      (2004-05-04)-(2004-01-11)
b   3       NA                     NA
b   4   2006-10-10      (2006-10-10)-(2004-05-04)
b   5       NA                     NA
c   1   2004-05-23                 NA
c   2   2004-10-14      (2004-10-14)-(2004-05-23)
c   3       NA                     NA
c   4       NA                     NA
c   5       NA                     NA

I tried these codes but none of them got what I want.

data$difftime <- aggregate(date ~ id, data, diff)

library(data.table)
setDT(data)[ , difftime := diff(data$date), by = id] 
  
diff(data$date)

Builiding on your data.table attempt, you may restrict your diff calculation to the non-NA rows (put !is.na(date) in the i slot). Also remember that the length of diff is one less than the original data so you need to pad with one NA: d[!is.na(date), dif := c(NA, diff(date)), id] — Henrik
– Henrik, Commented Feb 26, 2021 at 0:14

ThomasIsCoding · Accepted Answer · 2021-02-26 00:22:08Z

1

Hope this data.table option could help

setDT(df)[
  ,
  difftime := replace(
    rep(NA, .N),
    which(!is.na(date))[-1],
    diff(na.omit(date))
  ),
  id
]

or a shorter one (thank @Henrik)

setDT(df)[!is.na(date), difftime := c(NA, diff(date)), id]

which gives

    id time       date difftime
 1:  a    1 2004-01-13       NA
 2:  a    2 2004-05-04      112
 3:  a    3       <NA>       NA
 4:  a    4 2007-03-20     1050
 5:  a    5       <NA>       NA
 6:  b    1 2004-01-11       NA
 7:  b    2 2004-05-04      114
 8:  b    3       <NA>       NA
 9:  b    4 2006-10-10      889
10:  b    5       <NA>       NA
11:  c    1 2004-05-23       NA
12:  c    2 2004-10-14      144
13:  c    3       <NA>       NA
14:  c    4       <NA>       NA

edited Feb 26, 2021 at 0:22

answered Feb 26, 2021 at 0:01

ThomasIsCoding

106k9 gold badges38 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

B D. Over a year ago

just tried it and got exactly what I need. Thank you so much!

Henrik Over a year ago

Or d[!is.na(date), dif := c(NA, diff(date)), id]

ThomasIsCoding Over a year ago

@Henrik That's smart! Thanks!

Collectives™ on Stack Overflow

Using diff() to find elapsed time when missing values exist

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related