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

hcc

New Member
Joined
Aug 14, 2023
Messages
3
Platform
  1. Windows
  2. MacOS
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.

coyote data.jpg


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!
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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.
 
Upvote 0
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.
 
Upvote 0
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:

1692048198853.png


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:
Excel Formula:
=IF((B3-B2)<1/24,C2,C2+1)
 
Upvote 0
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:
Excel Formula:
=IF((B3-B2)<1/24,C2,C2+1)
Oh yes, that accomplishes the same thing but in a much easier way. Thank you.
 
Upvote 0
You are welcome.
Glad I was able to help!
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top