0

I have this sample:

data <- structure(list(timestamp = c(1401581040991, 1401581230769, 1401581410907, 
1401581591597, 1401581960830, 1401582002091, 1401582140958, 1401582330515, 
1401585071017, 1401585432174, 1401585641225, 1401586011911, 1401587120695, 
1401588721173, 1401589081689, 1401581041819, 1401585363131, 1401586083812, 
1401586983743, 1401588785148), timestamp_pretty = structure(c(1L, 
3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 12L, 13L, 14L, 17L, 18L, 20L, 
2L, 11L, 15L, 16L, 19L), .Label = c("01/06/2014 00:04:00", "01/06/2014 00:04:01", 
"01/06/2014 00:07:10", "01/06/2014 00:10:10", "01/06/2014 00:13:11", 
"01/06/2014 00:19:20", "01/06/2014 00:20:02", "01/06/2014 00:22:20", 
"01/06/2014 00:25:30", "01/06/2014 01:11:11", "01/06/2014 01:16:03", 
"01/06/2014 01:17:12", "01/06/2014 01:20:41", "01/06/2014 01:26:51", 
"01/06/2014 01:28:03", "01/06/2014 01:43:03", "01/06/2014 01:45:20", 
"01/06/2014 02:12:01", "01/06/2014 02:13:05", "01/06/2014 02:18:01"
), class = "factor"), mmsi = c(205477000L, 205477000L, 205477000L, 
205477000L, 205477000L, 205477000L, 205477000L, 205477000L, 205477000L, 
205477000L, 205477000L, 205477000L, 205477000L, 205477000L, 205477000L, 
205482000L, 205482000L, 205482000L, 205482000L, 205482000L), 
    diff_time_seconds = c(NA, 2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
    2L, 1L, 1L, 3L, 1L, 2L, NA, 9L, 4L, 1L, 3L)), .Names = c("timestamp", 
"timestamp_pretty", "mmsi", "diff_time_seconds"), row.names = c(NA, 
-20L), class = c("data.table", "data.frame"), .internal.selfref = <pointer: 0x00000000001e0788>, sorted = "mmsi")

I would like to have the difference of times (in seconds) between each rows for each factors. For example for the first occurence of a factor, the time difference is 0.

I could do it with the column timestamp (epoch time). but when I try with timestamp_pretty, it gets nuts. I have been looking around and I can not find the solution but I remember I got it few days ago...

Here is the example of the output I have: you can clearly see that diff_time_seconds_timestamp_pretty is not right...

structure(list(timestamp = c(1401581040991, 1401581230769, 1401581410907, 
1401581591597, 1401581960830, 1401582002091, 1401582140958, 1401582330515, 
1401585071017, 1401585432174, 1401585641225, 1401586011911, 1401587120695, 
1401588721173, 1401589081689, 1401581041819, 1401585363131, 1401586083812, 
1401586983743, 1401588785148), timestamp_pretty = structure(c(1L, 
3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 12L, 13L, 14L, 17L, 18L, 20L, 
2L, 11L, 15L, 16L, 19L), .Label = c("01/06/2014 00:04:00", "01/06/2014 00:04:01", 
"01/06/2014 00:07:10", "01/06/2014 00:10:10", "01/06/2014 00:13:11", 
"01/06/2014 00:19:20", "01/06/2014 00:20:02", "01/06/2014 00:22:20", 
"01/06/2014 00:25:30", "01/06/2014 01:11:11", "01/06/2014 01:16:03", 
"01/06/2014 01:17:12", "01/06/2014 01:20:41", "01/06/2014 01:26:51", 
"01/06/2014 01:28:03", "01/06/2014 01:43:03", "01/06/2014 01:45:20", 
"01/06/2014 02:12:01", "01/06/2014 02:13:05", "01/06/2014 02:18:01"
), class = "factor"), mmsi = c(205477000L, 205477000L, 205477000L, 
205477000L, 205477000L, 205477000L, 205477000L, 205477000L, 205477000L, 
205477000L, 205477000L, 205477000L, 205477000L, 205477000L, 205477000L, 
205482000L, 205482000L, 205482000L, 205482000L, 205482000L), 
    diff_time_seconds_timestamp_pretty = c(NA, 2L, 1L, 1L, 1L, 
    1L, 1L, 1L, 1L, 2L, 1L, 1L, 3L, 1L, 2L, NA, 9L, 4L, 1L, 3L
    ), diff_time_seconds_timestamp = c(NA, 189778, 180138, 180690, 
    369233, 41261, 138867, 189557, 2740502, 361157, 209051, 370686, 
    1108784, 1600478, 360516, NA, 4321312, 720681, 899931, 1801405
    )), .Names = c("timestamp", "timestamp_pretty", "mmsi", "diff_time_seconds_timestamp_pretty", 
"diff_time_seconds_timestamp"), row.names = c(NA, -20L), class = c("data.table", 
"data.frame"), .internal.selfref = <pointer: 0x00000000001e0788>, sorted = "mmsi")

I used the following code:

data <- data[,c("timestamp", "timestamp_pretty","mmsi")] 
data <- data[order(data$mmsi, data$timestamp_pretty),] 
library("data.table")
data<-data.table(data)
setkey(data,mmsi)
data[,diff_time_seconds_timestamp_pretty:=c(NA,diff(timestamp_pretty)),by=mmsi]
options(digits=12)
data[,diff_time_seconds_timestamp:=c(NA,diff(timestamp)),by=mmsi]

I think there is something wrong with the format of diff_time_seconds_timestamp_pretty but I am stuck with this!

Thank you!

Floni
  • 475
  • 2
  • 13

1 Answers1

1

diff(timestamp_pretty) works on the numeric values of the factor, not on the dates (and it won't work on characters anyway).

Convert or create a POSIXct column like (I did choose the second way):

data[, pos_time:=as.POSIXct( timestamp_pretty, format="%d/%m/%Y %H:%M:%S", tz="UTC" )]

And now the diff will work:

data[,diff_time_seconds_timestamp_pretty:=c(NA,diff(pos_time)),by="mmsi"]

Which gives:

> head(data)
      timestamp    timestamp_pretty      mmsi diff_time_seconds            pos_time diff_time_seconds_timestamp_pretty
1: 1.401581e+12 01/06/2014 00:04:00 205477000                NA 2014-06-01 00:04:00                                 NA
2: 1.401581e+12 01/06/2014 00:07:10 205477000                 2 2014-06-01 00:07:10                                190
3: 1.401581e+12 01/06/2014 00:10:10 205477000                 1 2014-06-01 00:10:10                                180
4: 1.401582e+12 01/06/2014 00:13:11 205477000                 1 2014-06-01 00:13:11                                181
5: 1.401582e+12 01/06/2014 00:19:20 205477000                 1 2014-06-01 00:19:20                                369
6: 1.401582e+12 01/06/2014 00:20:02 205477000                 1 2014-06-01 00:20:02                                 42
Tensibai
  • 15,557
  • 1
  • 37
  • 57