Difference of timestamp per ID in R

Question

@Akrun this is the output when i run code...and when i type View(df) i get the old dataset.

> df %>%
+   group_by(ID) %>%
+   mutate(difftime=c(15, format(.POSIXct(diff(TIMESTAMP)), '%M:%S')))
Source: local data frame [192 x 9]
Groups: ID

      TIMESTAMP             ID             STATUS         ACTIVE       PSET
1  2014-08-05 00:00:00     28808            DOWN          67.410        70
2  2014-08-05 00:15:00     28808            DOWN          67.410        70
3  2014-08-05 00:30:00     28808            DOWN          67.410        70
4  2014-08-05 00:45:00     28808            DOWN          67.410        70
5  2014-08-05 01:00:00     28808            DOWN          67.410        70
..                 ...       ...                 ...             ...                     ...
Variables not shown: STAT (chr), PIN (chr), ONOFF (chr), difftime (chr)

I need to find the timestamp difference for each ID making the first timestamp for each ID as NA. I have tried diff but i am not able to find difference for each ID. this can be made a little simpler by order but how make R's diff function know that ID's can be different Below is the sample data for your reference. Here is the code i am using as of now as requested.

foreach(i=1:length(xyzID)) %dopar%{
  xyz$timediff<-c(15,diff(xyz$TIMESTAMP))
    }

TIMESTAMP           ID      timediff
07/29/2014 10:15 AM 189252  NA
07/29/2014 10:45 AM 189252  0:30
07/29/2014 11:00 AM 189252  0:15
07/29/2014 11:15 AM 189252  0:15
07/29/2014 11:30 AM 302859  NA
07/29/2014 11:45 AM 302859  0:15
07/29/2014 04:00 PM 302859  04:15
07/29/2014 12:15 PM 189252  01:00
07/29/2014 12:30 PM 189252  0:15
07/29/2014 01:00 PM 189252  0:30
07/29/2014 01:15 PM 189252  0:15
07/29/2014 01:30 PM 178502  NA
07/29/2014 01:45 PM 178502  0:15

Could you post a new example as it is not clear from the description. — akrun, Nov 21 '14 at 16:41
Assign it to a new `object`. ie. `df1 <- df %>%group_by(ID) %>% mutate(difftime=c(15, format(.POSIXct(diff(TIMESTAMP)), '%M:%S')))` and check the `str(df1)`. `as.data.frame(df1)` — akrun, Nov 21 '14 at 17:04
Awesome..it works!!! i am a novice in R. any suggested pdf's?? — Kaushal, Nov 21 '14 at 17:24
Please check this link http://stackoverflow.com/questions/1744861/how-to-learn-r-as-a-programming-language — akrun, Nov 21 '14 at 18:04

score 3 · Answer 1 · answered Nov 20 '14 at 12:49

Try

 library(dplyr)
 df$TIMESTAMP <- as.POSIXct(df$TIMESTAMP, format='%m/%d/%Y %I:%M %p')

 df %>%
     group_by(ID) %>% 
     mutate(timediff=c(NA, format(.POSIXct(diff(TIMESTAMP)), '%M:%S')))

 #             TIMESTAMP     ID timediff
 #1  2014-07-29 10:15:00 189252       NA
 #2  2014-07-29 10:45:00 189252    00:30
 #3  2014-07-29 11:00:00 189252    00:15
 #4  2014-07-29 11:15:00 189252    00:15
 #5  2014-07-29 11:30:00 302859       NA
 #6  2014-07-29 11:45:00 302859    00:15
 #7  2014-07-29 16:00:00 302859    04:15
 #8  2014-07-29 12:15:00 189252    01:00
 #9  2014-07-29 12:30:00 189252    00:15
 #10 2014-07-29 13:00:00 189252    00:30
 #11 2014-07-29 13:15:00 189252    00:15
 #12 2014-07-29 13:30:00 178502       NA
 #13 2014-07-29 13:45:00 178502    00:15

data

df <- structure(list(TIMESTAMP = c("07/29/2014 10:15 AM", "07/29/2014 10:45 AM", 
"07/29/2014 11:00 AM", "07/29/2014 11:15 AM", "07/29/2014 11:30 AM", 
"07/29/2014 11:45 AM", "07/29/2014 04:00 PM", "07/29/2014 12:15 PM", 
"07/29/2014 12:30 PM", "07/29/2014 01:00 PM", "07/29/2014 01:15 PM", 
"07/29/2014 01:30 PM", "07/29/2014 01:45 PM"), ID = c(189252L, 
189252L, 189252L, 189252L, 302859L, 302859L, 302859L, 189252L, 
189252L, 189252L, 189252L, 178502L, 178502L)), .Names = c("TIMESTAMP", 
"ID"), class = "data.frame", row.names = c(NA, -13L))

@Kaushal Sharma It chains together multiple arguments together ie. the `LHS` and `RHS` in `dplyr` — akrun, Nov 21 '14 at 15:31
and it will work only data frame has all columns with same datatype? What if we had different datatypes. — Kaushal, Nov 21 '14 at 16:31
@Kaushal Sharma I am sorry, could you elaborate a bit more in detail. Better would be to update with a new example in your post. — akrun, Nov 21 '14 at 16:32

score 1 · Accepted Answer · answered Nov 20 '14 at 13:04

Or with data.table (otherwise the same as with dplyr):

# data
df <- structure(list(TIMESTAMP = structure(c(1406621700, 1406623500, 
1406624400, 1406625300, 1406626200, 1406627100, 1406642400, 1406628900, 
1406629800, 1406631600, 1406632500, 1406633400, 1406634300), class = c("POSIXct", 
"POSIXt"), tzone = ""), ID = c(189252L, 189252L, 189252L, 189252L, 
302859L, 302859L, 302859L, 189252L, 189252L, 189252L, 189252L, 
178502L, 178502L)), .Names = c("TIMESTAMP", "ID"), row.names = c(NA, 
-13L), class = "data.frame")
# set to data.table
require(data.table)
setDT(df)
# calculate timedifference
df[, timediff := c(NA, format(.POSIXct(diff(TIMESTAMP)), '%M:%S')), by=ID]
df
#             TIMESTAMP     ID timediff
#  1: 2014-07-29 10:15:00 189252       NA
#  2: 2014-07-29 10:45:00 189252    00:30
#  3: 2014-07-29 11:00:00 189252    00:15
#  4: 2014-07-29 11:15:00 189252    00:15
#  5: 2014-07-29 11:30:00 302859       NA
#  6: 2014-07-29 11:45:00 302859    00:15
#  7: 2014-07-29 16:00:00 302859    04:15
#  8: 2014-07-29 12:15:00 189252    01:00
#  9: 2014-07-29 12:30:00 189252    00:15
# 10: 2014-07-29 13:00:00 189252    00:30
# 11: 2014-07-29 13:15:00 189252    00:15
# 12: 2014-07-29 13:30:00 178502       NA
# 13: 2014-07-29 13:45:00 178502    00:15

whenever i run the timediff part my R session is getting aborted abnormally. Any idea why? — Kaushal, Nov 21 '14 at 16:34

Difference of timestamp per ID in R

2 Answers2

data