3

I would like to calculate time difference (in hours) between tab$date[i + 1] and tab$date[i] by group. Here my code:

setDT(tab)
system.time(tab <- tab[ , diffTime := c(0,diff(date, units="hours")), by="ID"])
tab$diffTime <- round(tab$diffTime)

The problem is that I obtain both hours, minutes, and seconds:

Date                   DiffTime
2012-03-05 01:00:36    0
2012-03-05 03:00:35    2
2012-03-05 05:01:05    2
...
2010-01-29 21:01:00    0
2010-01-29 22:01:01    60
2010-01-29 23:01:12    60
...
2012-02-13 05:00:34    0
2012-02-13 16:01:06    39632
2012-02-14 03:00:47    39581

The dates are POSIXct data. How can I obtain only hours ?

Arun
  • 116,683
  • 26
  • 284
  • 387
Nell
  • 559
  • 4
  • 20
  • I tried with your code and the small data provided, but couldn't reproduce the problem in `R 3.2.2`. Can you try with `difftime`? – akrun Oct 02 '15 at 16:56

1 Answers1

4

We could use difftime and specify the units as 'hours'.

library(data.table)
setDT(tab)[, DiffTime := c(0, round(difftime(date[-1L], date[-.N], 
                                 units='hours'))), by= ID]
tab
#   ID                date DiffTime
#1:  1 2012-03-05 01:00:36        0
#2:  1 2012-03-05 03:00:35        2
#3:  1 2012-03-05 05:01:05        2
#4:  2 2010-01-29 21:01:00        0 
#5:  2 2010-01-29 22:01:01        1
#6:  2 2010-01-29 23:01:12        1

data

tab <- data.frame(ID= rep(1:2, each=3),
  date= as.POSIXct(c('2012-03-05 01:00:36', '2012-03-05 03:00:35', 
  '2012-03-05 05:01:05', '2010-01-29 21:01:00', '2010-01-29 22:01:01',
  '2010-01-29 23:01:12'), format='%Y-%m-%d %H:%M:%S'))
Community
  • 1
  • 1
akrun
  • 874,273
  • 37
  • 540
  • 662
  • Thank you very much akrun for your answer. Is it possible to use difftime for time difference between `tab$date[i + 1]` and `tab$date[i]`. For example, in your `tab`, diffTime = 2 (i.e., difference between `2012-03-05 05:01:05` and `2012-03-05 03:00:35`) instead of 4 and diffTime = 1 (i.e., difference between `2010-01-29 23:01:12` and `2010-01-29 22:01:01`) instead of 2 ? – Nell Oct 02 '15 at 16:56
  • @Nell Perhaps `setDT(tab)[, DiffTime := c(0, round(difftime(date[-1L], date[-.N], units='hours'))), by= ID]` – akrun Oct 02 '15 at 17:02
  • what do `date[-1L]` and `date[-.N]` do? – wwl Aug 29 '17 at 15:36
  • 1
    @wwl the `date[-1L]` removes the first observation and `date[-.N]` remove the last observation of 'date' so that we can do the subtraction of dates between adjacent elements – akrun Aug 29 '17 at 17:50