Identify discrete events based on a time difference of 30 minutes or more in R

user_7376851 0
Viewed 56

I have a dataframe of timestamps when an individual animal (ID) is detected in specific locations. Here is an example of the data:

timestampUTC    location    ID
2017-10-02 19:23:27 JB12    A69-1601-47272
2017-10-02 19:26:48 JB12    A69-1601-47272
2017-10-02 19:27:23 JB12    A69-1601-47272
2017-10-02 19:31:46 JB12    A69-1601-47272
2017-10-02 23:52:15 JB12    A69-1601-47272
2017-10-02 23:53:26 JB12    A69-1601-47272
2017-10-02 23:55:13 JB12    A69-1601-47272
2017-10-03 19:53:50 JB13    A69-1601-47272
2017-10-03 19:55:23 JB13    A69-1601-47272
2017-10-03 19:58:26 JB13    A69-1601-47272
2017-10-04 13:15:13 JB12    A69-1601-47280
2017-10-04 13:16:42 JB12    A69-1601-47280
2017-10-04 13:21:39 JB12    A69-1601-47280
2017-10-04 19:34:54 JB12    A69-1601-47280
2017-10-04 19:55:28 JB12    A69-1601-47280
2017-10-04 20:08:23 JB12    A69-1601-47280
2017-10-04 20:21:43 JB12    A69-1601-47280
2017-10-05 04:55:48 JB13    A69-1601-47280
2017-10-05 04:57:04 JB13    A69-1601-47280
2017-10-05 05:18:40 JB13    A69-1601-47280
2017-10-07 21:24:19 JB13    A69-1601-47280
2017-10-07 21:25:36 JB13    A69-1601-47280
2017-10-07 21:29:25 JB13    A69-1601-47280

My real dataframe is almost 200,000 lines long and has 4 different locations and 13 different IDs.

I want to sort these into discrete events (ID at location) with start and end times based on the timestampUTC column, with the events ending at the timestampUTC when the next detection for that ID in that location is more than half an hour later. The next event begins at the next datetime.

Using the example data above, I would want to generate another dataframe that looks something like this:

ID             location event start         event end
A69-1601-47272  JB12    2017-10-02 19:23:27 2017-10-02 19:31:46
A69-1601-47272  JB12    2017-10-02 23:52:15 2017-10-02 23:55:13
A69-1601-47272  JB13    2017-10-03 19:53:50 2017-10-03 19:58:26
A69-1601-47280  JB12    2017-10-04 13:15:13 2017-10-04 13:21:39
A69-1601-47280  JB12    2017-10-04 19:34:54 2017-10-04 20:21:43
A69-1601-47280  JB13    2017-10-05 04:55:48 2017-10-05 05:18:40
A69-1601-47280  JB13    2017-10-07 21:24:19 2017-10-07 21:29:25

If an ID was detected at a location it gives the ID, location, and the start and end of its time there.

For example, you can see that there are 2 discrete events for ID 47272 at location JB12 that occur on the same day (2017-10-02) but the difference between the end of the first event and the start of the second is >30 min (~4 hrs and 20 mins) so they're separate events.

I would add what code I have tried but I don't know where to start.

Thanks in advance!

r datetime
1 Answers

Here is an option

library(tidyverse)
df %>%
    mutate(
        timestampUTC = as.POSIXct(timestampUTC),
        diff = c(0, diff(timestampUTC) / 60),
        grp = cumsum(diff > 30)) %>%
    group_by(grp) %>%
    summarise(
        ID = first(ID),
        location = first(location),
        `event start` = first(timestampUTC),
        `event end` = last(timestampUTC))
## A tibble: 7 x 5
#    grp ID             location `event start`       `event end`
#  <int> <fct>          <fct>    <dttm>              <dttm>
#1     0 A69-1601-47272 JB12     2017-10-02 19:23:27 2017-10-02 19:31:46
#2     1 A69-1601-47272 JB12     2017-10-02 23:52:15 2017-10-02 23:55:13
#3     2 A69-1601-47272 JB13     2017-10-03 19:53:50 2017-10-03 19:58:26
#4     3 A69-1601-47280 JB12     2017-10-04 13:15:13 2017-10-04 13:21:39
#5     4 A69-1601-47280 JB12     2017-10-04 19:34:54 2017-10-04 20:21:43
#6     5 A69-1601-47280 JB13     2017-10-05 04:55:48 2017-10-05 05:18:40
#7     6 A69-1601-47280 JB13     2017-10-07 21:24:19 2017-10-07 21:29:25

I've kept some some of the intermediate steps (columns) to help with readability and understanding. In short, we convert timestamps to POSIXct, then calculate time differences in minutes between successive timestamps with diff, create groups of observations based on whether the next timestamp is > 30 minutes away. The rest is grouping by grp and summarising entries from relevant columns.

The same, more succinct (perhaps at the expense of readability)

df %>%
    group_by(grp = cumsum(c(0, diff(as.POSIXct(timestampUTC)) / 60) > 30)) %>%
    summarise(
        ID = first(ID),
        location = first(location),
        `event start` = first(timestampUTC),
        `event end` = last(timestampUTC)) %>%
    select(-grp)

Sample data

df <- read.table(text =
    "timestampUTC    location    ID
'2017-10-02 19:23:27' JB12    A69-1601-47272
'2017-10-02 19:26:48' JB12    A69-1601-47272
'2017-10-02 19:27:23' JB12    A69-1601-47272
'2017-10-02 19:31:46' JB12    A69-1601-47272
'2017-10-02 23:52:15' JB12    A69-1601-47272
'2017-10-02 23:53:26' JB12    A69-1601-47272
'2017-10-02 23:55:13' JB12    A69-1601-47272
'2017-10-03 19:53:50' JB13    A69-1601-47272
'2017-10-03 19:55:23' JB13    A69-1601-47272
'2017-10-03 19:58:26' JB13    A69-1601-47272
'2017-10-04 13:15:13' JB12    A69-1601-47280
'2017-10-04 13:16:42' JB12    A69-1601-47280
'2017-10-04 13:21:39' JB12    A69-1601-47280
'2017-10-04 19:34:54' JB12    A69-1601-47280
'2017-10-04 19:55:28' JB12    A69-1601-47280
'2017-10-04 20:08:23' JB12    A69-1601-47280
'2017-10-04 20:21:43' JB12    A69-1601-47280
'2017-10-05 04:55:48' JB13    A69-1601-47280
'2017-10-05 04:57:04' JB13    A69-1601-47280
'2017-10-05 05:18:40' JB13    A69-1601-47280
'2017-10-07 21:24:19' JB13    A69-1601-47280
'2017-10-07 21:25:36' JB13    A69-1601-47280
'2017-10-07 21:29:25' JB13    A69-1601-47280", header = T)
user_65309700
Related