Merging BORIS behavioural data with GPS data based on the time the data were recorded

[RMM84]

Hi,

I have recorded thousands of behavioural events of animals that were carrying the camera. At the same time, these animals were being tracked so I also have lat and long data approximately every 3 seconds for a duration of about 4 hours of these animals movements during the same time period the animals were recording the video footage.

I can merge these two sets of data for an individual animal and sort them so they merge in a chronological order. All of my BORIS observations in this data set are without the lat and long data however.

My goal is to associate the recorded BORIS behaviours with as accurate a GPS position as possible. The animals were moving underwater so the accuracy of this is limited as only as accurate as the GPS position data I have.

I want to populate my BORIS behaviours in my spreadsheet with lat long data that is associated with the closest 'timestamp' of my GPS data with lat long coordinates. There are some large gaps in this data also (of over 30 minutes in a couple of occasions). If possible I want to assign the coordinates to the closest timestamp I have of GPS data rather than just the last known position of the animal from the GPS data. I'm investigating autopopulate in EXCEL, but don't know how to set it for the 'nearest' value based on multiple cells with different values throughout the dataset with thousands of different comparisons to make.

My intention is to map the BORIS behaviours in a GIS. Or do I just include the behaviours in a layer that maps the track of the animal and would lump all the behaviours to the 'most accurate' position from the GPS data that informs this layer. Both results in the GIS would amount to the same, but I would prefer to assign GPS data to my BORIS behaviours is possible.

Can anyone help?

Has anyone tried to combine BORIS behaviours with GPS data before and have any advice?

Many thanks!

An example of this problem is attached. Rows 4 and 5 would be autofilled with the 'closest time' to them which is the lat and long data from row 3. At the same time row 6 is closer to the time in row 7 and where therefore be filled with the lat and long data from there. There are hundreds of these gaps throughout my data sets of about 16 animals so I would like a formula to apply to each data set.

[RMM84]

Problem solved! I received this answer on another forum. Hopefully it can help anyone combining positional with non-positional data.

'I would keep the two datasets separate and use a combination of formulas to connect each time with the nearest GPS. In the example below I separated your table into two datasets, GPS readings and BEHAVIOR readings.

The formula that I placed in cell E10 is,

=INDEX($D$2:$F$5,MATCH(MIN(ABS($D$2:$D$5-D10)),ABS($D$2:$D$5-D10),0),2)
The formula in cell F10 is,

=INDEX($D$2:$F$5,MATCH(MIN(ABS($D$2:$D$5-D10)),ABS($D$2:$D$5-D10),0),3)

Essentially MATCH is used to find the time in the GPS readings that is closest to the BEHAVIOR (time) reading. MIN is the function that finds the smallest time difference. ABS is necessary to remove the difference that negative numbers would cause. MATCH returns the row number, which INDEX uses to then return the cell value at the row and column specified. Longitude is column 3 (in relation to the Array in the formula) and Latitude is column 2.'