Split data frame by conditional interval

user_11372268 0
Viewed 54

I have a dataframe containing an animal ID and a timestamp (this is simplified GPS data). The df is ordered by date/time. I want to create a column that determines the trip number. Trips are split if the interval between one time and the next is >28800 seconds. 

#some sample data
timestamp <- as.POSIXct(c("18/01/2020 06:43:38", "18/01/2020 06:44:14", "18/01/2020 16:45:07" ,"18/01/2020 16:46:07"), tz = "UTC", format = "%d/%m/%Y %H:%M:%S")
    data <- data.frame("ID" = c("a","b","c","d"), "timestamp" = timestamp)

#ORIGINAL DATAFRAME
#   ID           timestamp
#1  a 2020-01-18 06:43:38
#2  b 2020-01-18 06:44:14
#3  c 2020-01-18 16:45:07
#4  d 2020-01-18 16:46:07

data$interval <- data$timestamp - lag(data$timestamp, n = 1L) #calculates time difference between points
data$trip <- c(1,1,2,2) # THIS IS THE LINE I NEED HELP WITH

#DATAFRAME I WANT IN THE END
#ID           timestamp   interval trip
#1  a 2020-01-18 06:43:38    NA secs    1
#2  b 2020-01-18 06:44:14    36 secs    1
#3  c 2020-01-18 16:45:07 36053 secs    2
#4  d 2020-01-18 16:46:07    60 secs    2

I would also be ok with subsetting the data (see example below). 

$`1`
  ID           timestamp interval 
1  a 2020-01-18 06:43:38  NA secs    
2  b 2020-01-18 06:44:14  36 secs    

$`2`
  ID           timestamp   interval 
3  c 2020-01-18 16:45:07 36053 secs    
4  d 2020-01-18 16:46:07    60 secs

I am struggling to explain myself, I hope this makes sense!

dataframe r
2 Answers

Another way of doing this in data.table:

library(data.table)

setDT(data)[, interval := difftime(timestamp, shift(timestamp), units = "secs")][
            ,     trip := 1 + cumsum(ifelse(is.na(interval > 28800), 0, interval > 28800))][]

#>    ID           timestamp   interval trip
#> 1:  a 2020-01-18 06:43:38    NA secs    1
#> 2:  b 2020-01-18 06:44:14    36 secs    1
#> 3:  c 2020-01-18 16:45:07 36053 secs    2
#> 4:  d 2020-01-18 16:46:07    60 secs    2

split(data, by=c("trip"), keep.by = FALSE)

#> $`1`
#>    ID           timestamp interval
#> 1:  a 2020-01-18 06:43:38  NA secs
#> 2:  b 2020-01-18 06:44:14  36 secs
#> 
#> $`2`
#>    ID           timestamp   interval
#> 1:  c 2020-01-18 16:45:07 36053 secs
#> 2:  d 2020-01-18 16:46:07    60 secs
user_64614620

You could use diff and cumsum

data$interval <- c(NA, diff(data$timestamp))
data$trips <- cumsum(c(TRUE,  data$interval[-1] >28800))
data

#  ID           timestamp trips interval
#1  a 2020-01-18 06:43:38     1       NA
#2  b 2020-01-18 06:44:14     1       36
#3  c 2020-01-18 16:45:07     2    36053
#4  d 2020-01-18 16:46:07     2       60

You can use split to split the data based on trips. 

split(data, data$trips)

Using the same logic in dplyr

library(dplyr)

data %>%
  mutate(interval = difftime(timestamp, lag(timestamp), "secs"),
         trips = cumsum(c(TRUE, interval[-1] > 28800))) %>%
  #To split the data
  #%>% group_split(trips)
user_39629140
Related