Subtracting Two Columns Consisting of Both Date and Time in R

Question

I am having a problem with subtracting two columns of a table consisting of both date and time in a single cell.

> TimeData

DEPARTURE_TIME      LAB_TIME
1/30/2010 4:18      1/30/2010 0:29
1/30/2010 4:18      1/30/2010 0:29
1/30/2010 6:49      1/30/2010 0:48
1/30/2010 6:49      1/30/2010 0:48
1/30/2010 9:42      1/30/2010 1:29
1/30/2010 9:42      1/30/2010 1:29
1/30/2010 7:25      1/30/2010 1:16

I need to obtain the difference between Departure Time and Lab Time in hours and minutes.

Do I need to separate time and date or is there a way to subtract the data in this way ?

I really appreciate any help.

Answer 1

Try TimeData$DEPARTURE_TIME - TimeData$LAB_TIME ?

It depends on whether your xxx_TIME columns are strings or whether you've converted them to date-times.

Suppose they're strings (they've been read in using read.csv or something similar); then to convert them to date-time objects you can use as.POSIXct (see ?as.POSIXct and strptime):

# convert the string columns to dates
TimeData$DEPARTURE_TIME <- as.POSIXct(TimeData$DEPARTURE_TIME,
                                      format='%m/%d/%Y %H:%M')
TimeData$LAB_TIME       <- as.POSIXct(TimeData$LAB_TIME,
                                      format='%m/%d/%Y %H:%M')

Note the format argument: looks like yours are in month/day/year hours:minutes(25 hr clock) format. See ?strptime for more info on date formats.

Then to calculate the difference, you can do either:

diffs <- TimeData$DEPARTURE_TIME - TimeData$LAB_TIME

which picks the appropriate time units for you, OR to specify hours you can use difftime (see ?difftime):

# like saying DEPARTURE_TIME-LAB_TIME but more control over the output
diffs <- difftime(TimeData$DEPARTURE_TIME,TimeData$LAB_TIME,units="hours")

The resulting object diffs looks like this:

> diffs
Time differences in hours
[1] 3.816667 3.816667 6.016667 6.016667 8.216667 8.216667 6.150000
attr(,"tzone")
[1] ""

To extract just the numeric part, use as.numeric(diffs). To convert this into an hours vector and a minutes vector...well, 60 minutes to a second, etc:

# use as.numeric(diffs) to retrieve just the vector.
# let's convert to hours & minutes...
diffs.hours <- floor(as.numeric(diffs))
diffs.minutes <- (as.numeric(diffs)%%1 * 60)

Giving you:

> diffs.hours
[1] 3 3 6 6 8 8 6
> diffs.minutes
[1] 49 49  1  1 13 13  9