2

How would you calculate time difference of two consecutive rows of timestamps in minutes and add the result to a new column.

I have tried this:

data$hours <- as.numeric(floor(difftime(timestamps(data), (timestamps(data)[1]), units="mins")))

But only get difference from time zero and onwards.

Added example data with 'mins' column that I want to be added

timestamps                        mins
2013-06-23 00:00:00               NA
2013-06-23 01:00:00               60
2013-06-23 02:00:00               60
2013-06-23 04:00:00              120
Nicolai Jørgensen
  • 91
  • 1
  • 10
  • 4
    I would suggest that you post some sample data to make your example reproducible. Your code alludes to making use of the `difftime` which I would use when calculating the difference. Jumping across pairs of values would be a matter of indexing. You could then do whatever desired with the results. Also have a look at [this answer](http://stackoverflow.com/questions/15213228/get-the-time-between-consecutive-dates-stored-in-a-single-column) which touches on a similar issue. – Konrad Dec 17 '15 at 11:49

2 Answers2

7

The code that you're using with the [1] is always referencing the first element of the timestamps vector.

To do what you want, you want to look at all but the first element minus all but the last element.

mytimes <- data.frame(timestamps=c("2013-06-23 00:00:00",
                                   "2013-06-23 01:00:00",
                                   "2013-06-23 02:00:00",
                                   "2013-06-23 04:00:00"),
                      mins=NA)
mytimes$mins <- c(NA, difftime(mytimes$timestamps[-1],
                               mytimes$timestamps[-nrow(mytimes)],
                               units="mins"))

What this code does is:

  1. Setup a data frame so that you will keep the length of the timestamps and mins the same.
  2. Within that data frame, put the timestamps you have and the fact that you don't have any mins yet (i.e. NA).
  3. Select all but the first element of timestamps mytimes$timestamps[-1]
  4. Select all but the last element of timestamps mytimes$timestamps[-nrow(mytimes)]
  5. Subtract them difftime (since they're well-formatted, you don't first have to make them POSIXct objects) with the units of minutes. units="mins"
  6. Put an NA in front because you have one fewer difference than you have rows c(NA, ...)
  7. Drop all of that back into the original data frame's mins column mytimes$mins <-
Bill Denney
  • 766
  • 1
  • 6
  • 21
2

Another option is to calculate it with this approach:

# create some data for an MWE
hrs <- c(0,1,2,4)

df <- data.frame(timestamps = as.POSIXct(paste("2015-12-17", 
                                            paste(hrs, "00", "00", sep = ":"))))

df
# timestamps
# 1 2015-12-17 00:00:00
# 2 2015-12-17 01:00:00
# 3 2015-12-17 02:00:00
# 4 2015-12-17 04:00:00

# create a function that calculates the lag for n periods
lag <- function(x, n) c(rep(NA, n), x[1:(length(x) - n)])

# create a new column named mins
df$mins <- as.numeric(df$timestamps - lag(df$timestamps, 1)) / 60

df
# timestamps mins
# 1 2015-12-17 00:00:00   NA
# 2 2015-12-17 01:00:00   60
# 3 2015-12-17 02:00:00   60
# 4 2015-12-17 04:00:00  120
David
  • 9,216
  • 4
  • 45
  • 78