difference in time dependent on columns

Question

enter link description herefor a dataframe with a column 'Time' in the format %Y-%m-%d %H:%M:%S I am trying to calculate the difference in minutes between rows when those rows meet certain column requirements. Only calculating difftime when the data is from the same site, camera, and for one species. rows are observations and there are columns for: SpeciesID, Site, Plot, Camera, Time.

I have tried:

site.list<-unique(data2$Site) #site list made
  species.list<-unique(data2$SpeciesID)  #species list made
  Time<-as.POSIXlt(data2$Time)
  Time<-rev (Time)

      difftime <- NULL
      for( Site in site.list ){  
        for( Camera in paste('C', 1:4, sep='') ){
                      index <- which( data2$Site == Site & data2$Camera == Camera & data2$SpeciesID==SpeciesID) 
          index2 <- order( data2[index,'Camera'], data2[index, 'Date'], data2[index, 'SpeciesID'])
          small.data <- data2[index, ][index2, ]

          i <- 2
          while( i<- dim(small.data)[1]){

           if ( small.data[i, 'SpeciesID'] == small.data[i-1, 'SpeciesID'] & 
                             small.data[i, 'Site'] == small.data[i-1,'Site'] &
                             small.data[i, 'Camera'] == small.data[i-1,'Camera']{
                               small.data<-difftime(Time[1:(length(Time)-1)] , Time[2:length(Time)])}
          foo<- rbind(difftime, small.data)
        }
      }

Can you provide a small sample of your data? You will have a greater chance of getting a response. Have a look [here](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example). If you can't provide a sample just a dummy example would do. You have also ways to create example from private data [here](http://stackoverflow.com/questions/10454973/how-to-create-example-data-set-from-private-data-replacing-variable-names-and-l) — DJJ, Mar 02 '15 at 17:50
@DJJ I provided a link to a subset of data. I hope that helps — Michelle Reilly, Mar 02 '15 at 19:34

DJJ · Answer 1 · 2015-03-03T22:18:28.440

0

In case you don't already have data.table

install.packages("data.table","http://cran.us.r-project.org")
library (data.table)

I've imported the data in this way: dt <- fread("pathToData.csv",key='Camera,Site,SpeciesID')

but I'm putting a sample of 10 observations on SO. Feel free to say so if you want them removed.

df <- structure(list(Individual = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L), Date = c(15543L, 15543L, 15543L, 15554L, 15554L, 15554L, 
15554L, 15554L, 15543L, 15543L), Image1 = c("544.1.P5_", "544.1.P5_", 
"544.1.P5_", "544.7.I1_2", "544.7.I1_2", "544.7.I1_2", "544.7.I1_2", 
"544.7.I1_2", "544.1.P5_", "544.1.P5_"), Site = c("544", "544", 
"544", "544", "544", "544", "544", "544", "544", "544"), Camera = c(1L, 
1L, 1L, 7L, 7L, 7L, 7L, 7L, 1L, 1L), Plot = c(1L, 1L, 1L, 5L, 
5L, 5L, 5L, 5L, 1L, 1L), Plot_Type = c("OnTrail", "OnTrail", 
"OnTrail", "OffTrail", "OffTrail", "OffTrail", "OffTrail", "OffTrail", 
"OnTrail", "OnTrail"), CameraID = c("P5", "P5", "P5", "I1", "I1", 
"I1", "I1", "I1", "P5", "P5"), Time = c("2012/07/22 00:31:00", 
"2012/07/22 00:31:00", "2012/07/22 00:31:00", "2012/08/02 09:09:00", 
"2012/08/02 09:09:00", "2012/08/02 09:09:00", "2012/08/02 09:09:00", 
"2012/08/02 09:09:00", "2012/07/22 00:31:00", "2012/07/22 00:31:00"
), Hour = c(0L, 0L, 0L, 9L, 9L, 9L, 9L, 9L, 0L, 0L), Minute = c(31L, 
31L, 31L, 9L, 9L, 9L, 9L, 9L, 31L, 31L), Second = c(17L, 18L, 
23L, 18L, 20L, 22L, 24L, 26L, 34L, 36L), SpeciesID = c(2L, 2L, 
2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L), Common = c("Mule deer", "Mule deer", 
"Mule deer", "Mule deer", "Mule deer", "Mule deer", "Mule deer", 
"Mule deer", "Mule deer", "Mule deer"), Scientific = c("Odocoile", 
"Odocoile", "Odocoile", "Odocoile", "Odocoile", "Odocoile", "Odocoile", 
"Odocoile", "Odocoile", "Odocoile"), SpeciesID.1 = c(2L, 2L, 
2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L)), .Names = c("Individual", "Date", 
"Image1", "Site", "Camera", "Plot", "Plot_Type", "CameraID", 
"Time", "Hour", "Minute", "Second", "SpeciesID", "Common", "Scientific", 
"SpeciesID.1"), row.names = c(NA, 10L), class = "data.frame")

dt = data.table(df,key='Camera,Site,SpeciesID')

# 1) Count the number of obs per category (SpeciesID,Cameraand Site)
# 2) Convert Time into time variable (the format is optional here)
# 3) Perform the time difference where there were at least 2 observations per category
#     (SpeciesID,Cameraand Site) and compute the time difference for each category.
#     (in seconds)

dt[,n:=.N,list(SpeciesID,Camera,Site)][
,Time:=as.POSIXct(Time,format="%Y/%m/%d %H:%M:%S")][
     n>2,diffSec:=filter(Time,c(1,-1),sides=1),list(Camera,Site,SpeciesID)]

Other Solution

 dt[,n:=.N,list(SpeciesID,Camera,Site)][
 ,Time:=as.POSIXct(Time,format="%Y/%m/%d %H:%M:%S")][
      n>2,diffSec:=c(NA,diff(Time,1)),list(Camera,Site,SpeciesID)]

You may want to check your variable Time format as well. Is it the right time? I've noticed that seconds were not included.

edited Mar 03 '15 at 22:18

answered Mar 02 '15 at 18:23

DJJ

2,481
2
28
53

I get an error message indicating that 'filter' cannot be used for an object of class POSIXct or POSIXt. Did you mean to use some type of subsetting? Thanks! – Michelle Reilly Mar 03 '15 at 17:44
That's strange. I just copied the whole code in a fresh console and no errors on my side. I'm still not sure that the code do what you want it to do. For now it is taking the difference of consecutive rows for each (SpeciesID, Camera and Site) category. And yes it uses a kind of subsetting. Can you tell your R version? – DJJ Mar 03 '15 at 17:52
R GUI 64bit 3.1.1 . I tried installing data.table in R Studio and get an error that previous version cannot be uninstalled. Looking into the issue now. What I am looking for the code to do is calculate difference in time IF the rows are for the same SpeciesID, and then IF its the same Site and then IF its the same Camera. when those stipulations are met then I want to know the difference in time (in minutes) – Michelle Reilly Mar 03 '15 at 18:38
same message received when running the code in newest version of R. Error in UseMethod("filter_") : no applicable method for 'filter_' applied to an object of class "c('POSIXct', 'POSIXt')" – Michelle Reilly Mar 03 '15 at 21:54
@MichelleReilly I've posted an update to the solution. May be this one will work. – DJJ Mar 03 '15 at 22:21
1

Thanks! will give it a try in the a.m. Running models currently – Michelle Reilly Mar 04 '15 at 23:00
well done. It seemed to work. I open the dataframe in notepad and seconds are included. I have never use data.frame before so thanks for introducing me to it. One additional question, would there be any way to alter the code you provided to get the difference in time between to species of interest? same, by being able to choose SpeciesID <- CommonName1=="Bobcat" & CommonName2== 'MountainLion' and then calculating the difference in time ? – Michelle Reilly Mar 05 '15 at 14:52
No worries, it's `data.table`. Have a look at `?data.table` or [here](https://github.com/Rdatatable/data.table/wiki). May be this will do. `dt[,n:=.N,list(SpeciesID,Camera,Site)][ ,Time:=as.POSIXct(Time,format="%Y/%m/%d %H:%M:%S")][(Common=="Bobcat" | Common == "MountainLion") & n>2,diffSec:=c(NA,diff(Time,1)),list(Camera,Site)]` – DJJ Mar 05 '15 at 15:04
sorry, I meant data.table not data.frame...will try this and see what happens – Michelle Reilly Mar 05 '15 at 15:24
I didn't properly explain the 2nd question above. The code you provide did exactly what the question read as. So thanks for that! What I meant to ask about is how to alter so that the code is calculating difference between an entry for a mountain lion to the next entry for a bobcat. – Michelle Reilly Mar 05 '15 at 19:10
feel free to ask another question on Stack Overflow. In general it's one questions per thread :-). You might have a faster answer from someone much more competent. You can of course to put a link to this post so other people can follow. And to maximize your chances of getting an answer really try to provide some sample of your data and the desired output so that people can help easily. – DJJ Mar 05 '15 at 21:15
Thanks. Will do. I think I figured it out last night. Will give it another try and then ask if I still don't get it. Thanks again for all your help! – Michelle Reilly Mar 06 '15 at 15:20

difference in time dependent on columns

1 Answers1