Equation or work around to find independent detections/occurrences from camera trap photos

[hcc]

I need help figuring out an easier way to determine occurrences of species from camera trap photos. I don't need regular occurrences, where you count the number of detections within a designated time period. I need to find "independent occurrences", where the detection starts from the first occurrence and all photos within the designated time period are included in that occurrence. The next occurrence starts whenever the next detection occurs and the time period starts again. It is a little confusing so I have attached a subset of my data and an example.

In this example, we used a 60 minute designated time interval. The first detection event would start at 10:03 on 7/1 and all detections between 10:03 and 11:03 would be considered 1 occurrence. In the above example, that would mean that 7/1/2021 at 10:03 and 7/1/21 at 10:11 are condensed into one occurrence. The next detection event would start at 12:43 and go until 13:43, so the detections at 12:43, 12:44, 13:12, 13:14, and 13:40 are all one occurrence. The next detection event would start at 13:44 and go until 14:44, etc.

Is there a way to automate this process in Excel? Our dataset is very large and we are going through all the occurrences manually right now, which takes a lot of time. Any help would be appreciated, thank you!

 

[Joe4]

Welcome to the Board!

I don't necessarily agree with your method. In that case, you could have the following 3 consecutive date/time records:
7/1/2021 10:03
7/1/2021 11:01
7/1/2021 11:05

If you use your 60 minute from first occurrence rule, records 1 and 2 above would be lumped together as one, while the last record would be a new occurence.
But logically looking at the data, it seem far more likely that records 2 and 3 are from the same occurrence, and record 1 is a separate occurrence.

I think a better method may instead to sort all records, have it create a new occurence after some set period of time passes without another occurence.

 

[hcc]

Welcome to the Board!

I don't necessarily agree with your method. In that case, you could have the following 3 consecutive date/time records:
7/1/2021 10:03
7/1/2021 11:01
7/1/2021 11:05

If you use your 60 minute from first occurrence rule, records 1 and 2 above would be lumped together as one, while the last record would be a new occurence.
But logically looking at the data, it seem far more likely that records 2 and 3 are from the same occurrence, and record 1 is a separate occurrence.

I think a better method may instead to sort all records, have it create a new occurence after some set period of time passes without another occurence.

I agree, but we need it to be standardized. The time interval may be changed depending on the species we're looking at, but we have over 800,000 photos with cameras operating 24 hours/day for over a year so we unfortunately can't go through each individual day and hour to determine which occurrences are most likely to occur together.

 

[Joe4]

I agree, but we need it to be standardized. The time interval may be changed depending on the species we're looking at, but we have over 800,000 photos with cameras operating 24 hours/day for over a year so we unfortunately can't go through each individual day and hour to determine which occurrences are most likely to occur together.

That is actually not what I am suggesting at all.

What I am suggesting is if your sort them, first by species, then by date time, you can subtract each value from the one above it. If it is, say less than 60 minutes, keep the counter from above. If it is more, add one to it.

And I actually think that is a bit easier to do than the original way you asked for. Quite frankly, I don't have an answer for you on that.

So a simplified version of this looks like this:

where I have put a 1 in cell C2, and then the following formula in cell C3 and copied down for the rest of the rows:

=IF((B3-B2)<1/24,C2,C2+1)

 

[hcc]

That is actually not what I am suggesting at all.

What I am suggesting is if your sort them, first by species, then by date time, you can subtract each value from the one above it. If it is, say less than 60 minutes, keep the counter from above. If it is more, add one to it.

And I actually think that is a bit easier to do than the original way you asked for. Quite frankly, I don't have an answer for you on that.

So a simplified version of this looks like this:

View attachment 97159

where I have put a 1 in cell C2, and then the following formula in cell C3 and copied down for the rest of the rows:

=IF((B3-B2)<1/24,C2,C2+1)

Oh yes, that accomplishes the same thing but in a much easier way. Thank you.

 

[Joe4]

You are welcome.
Glad I was able to help!