5

I'm trying to calculate the time difference between two timestamps in two adjacent rows using the dplyr package. Here's the code:

    tidy_ex <- function () {

    library(dplyr)

    #construct example data
    data <- data.frame(code = c(10888, 10888, 10888, 10888, 10888, 10888, 
                                    10889, 10889, 10889, 10889, 10889, 10889,
                                    10890, 10890, 10890),
                           station = c("F1", "F3", "F4", "F5", "L5", "L7", "F1",
                                       "F3", "F4", "L5", "L6", "L7", "F1", "F3", "F5"),
                           timestamp = c(1365895151, 1365969188, 1366105495,
                                           1367433149, 1368005216, 1368011698,
                                           1366244224, 1366414926, 1367513240,
                                           1367790556, 1367946420, 1367923973,
                                           1365896546, 1365907968, 1366144207))

    # reformat timestamp as POSIXct
    data$timestamp <- as.POSIXct(data$timestamp,origin = "1970-01-01")

    #create tbl_df
    data2 <- tbl_df(data)

    #group by code and calculate time differences between two rows in timestamp column 
    data2 <- data2 %>%
            group_by(code) %>%
            mutate(diff = c(difftime(tail(timestamp, -1), head(timestamp, -1))))

    data2

    }

The code produces an error message:

 Error: incompatible size (5), expecting 6 (the group size) or 1

I guess that's because the difference for the last row produces an NA (since there is no further adjacent row). The difftime/head-tails method however works with the plyr package instead of dplyr (see this StackOverflow post)

How can I get it to work using dplyr?

Community
  • 1
  • 1
Timm S.
  • 5,135
  • 6
  • 24
  • 38
  • 2
    You can use `lag()` instead of diff, probably. If you use `lag()`, number of rows is N, not N-1. – jazzurro Sep 11 '14 at 13:37
  • `diff` will be of length N-1 try `mutate(diff = c(0, difftime(tail(timestamp, -1), head(timestamp, -1))))` – Victorp Sep 11 '14 at 13:38

2 Answers2

6

Thanks to Victorp for the suggestion. I changed the mutate line to:

mutate(diff = c(difftime(tail(timestamp, -1), head(timestamp, -1)),0))

(The 0 I placed at the end so the difference calculation would start in the first row).

Timm S.
  • 5,135
  • 6
  • 24
  • 38
  • 14
    `difftime(timestamp, lag(timestamp))` would be a little simpler – hadley Sep 13 '14 at 14:10
  • @hadley let's imagine we had `difftime(lead(timestamp), lag(timestamp))`. using `tail` and `head` would be more efficient or not? – M-- Apr 25 '19 at 18:42
1

Alternatively, you can simply try:

... %>%
mutate(diff = c(0,diff(timestamp)))

Or, if you want to explicitly assign the unit and convert the column to numeric for other calculations:

... %>%
mutate(diff = c(0,as.numeric(diff(timestamp), units="mins")))
Robyn
  • 19
  • 2