Matching up Columns for bird migration data

[L.steel]

Hi,
I am working with a large data set (20,000 rows) of bird migration data. The data is split between 35 birds. My aim is to map the location and the behaviour of the bird on that day, for each bird. I will be doing this in QGIS, but need to get my data into the right format prior to importation into different software.
This is a sample of my data below: (see sample worksheet attached for larger sample)
bird date flight rest forage Bird Date Latitude Longitude
eb80612 15/07/2012 3 68 22 eb80612 16/07/2012 33.01 -9.35
eb80612 16/07/2012 5 40 22 eb80612 18/07/2012 50.22 -4.44
eb80612 17/07/2012 0 72 24 eb80612 19/07/2012 51.59 -5.67
eb80612 18/07/2012 2 62 31 eb80612 20/07/2012 52.01 -5.15
eb80612 19/07/2012 2 64 30 eb80612 21/07/2012 52.8 -3.89
eb80612 20/07/2012 1 47 39 eb80848 08/08/2012 54.325 -4.905
eb80848 07/08/2012 0 32 65 eb80848 09/08/2012 53.105 -4.375
eb80848 08/08/2012 0 39 58 eb80848 11/08/2012 54.53 -4.95
eb80848 09/08/2012 0 46 52 eb80848 12/08/2012 53.48 -4.98
eb80848 10/08/2012 0 35 62
eb80848 11/08/2012 0 49 47

I need to match the behaviour to the location and bird, as obviously I cannot plot a behaviour onto a map without it having an associated location and bird.
So for example, I would need this data looking like this (see below) - by removing any behaviour days which do not have a corresponding location for that date and that bird; and by removing any location days where behaviour data for that bird has not been recorded.
bird date flight Rest Forage Bird Date Latitude Longitude
eb80612 16/07/2012 5 40 22 eb80612 16/07/2012 33.01 -9.35
eb80612 18/07/2012 2 62 31 eb80612 18/07/2012 50.22 -4.44
eb80612 19/07/2012 2 64 30 eb80612 19/07/2012 51.59 -5.67
eb80612 20/07/2012 1 47 39 eb80612 20/07/2012 52.01 -5.15
eb80848 08/08/2012 0 39 58 eb80848 08/08/2012 54.325 -4.905
eb80848 09/08/2012 0 46 52 eb80848 09/08/2012 53.105 -4.375
eb80848 11/08/2012 0 49 47 eb80848 11/08/2012 54.53 -4.95

This is too time consuming / creates too much human error to do it manually. But I cannot find a way to apply e.g. a formula, to either the entire data set, or each bird’s data.
Any help would be AMAZING!
Thank you!

[AliGW]

Welcome to the forum!

Do you have the Power Query add-in?

Will you please attach a sample Excel workbook?

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[L.steel]

Hi Ali,

Thank you for your message. I have now (I think!) attached a larger sample of my data to my post.

I do not have the Power Query add in, but will download it!

[AliGW]

Thanks for the attachment, but I'm sorry - what are we aiming for? Is what you have provided what you are looking for? If so, what does the source data look like? If not, what do you want the resulting data to look like? I'm not at all clear what the objective is.

[AliGW]

Have a look at this. If this is what you are looking for, or close, let me know and I'll show you how to do it.

Excel 2016 (Windows) 32 bit

	L	M	N	O	P	Q	R
1	Bird	Date	Latitude	Longitude	Table1.flight	Table1.rest_day	Table1.forage
2	eb80612	16/07/2012 00:00	33.01	-9.35	3	68	22
3	eb80612	16/07/2012 00:00	33.01	-9.35	5	40	22
4	eb80612	16/07/2012 00:00	33.01	-9.35	0	72	24
5	eb80612	16/07/2012 00:00	33.01	-9.35	2	62	31
6	eb80612	16/07/2012 00:00	33.01	-9.35	2	64	30
7	eb80612	16/07/2012 00:00	33.01	-9.35	1	47	39
8	eb80612	16/07/2012 00:00	33.01	-9.35	2	41	45
9	eb80612	16/07/2012 00:00	33.01	-9.35	16	53	34
10	eb80612	16/07/2012 00:00	33.01	-9.35	11	79	14
11	eb80612	16/07/2012 00:00	33.01	-9.35	15	62	24
12	eb80612	16/07/2012 00:00	33.01	-9.35	5	65	28
13	eb80612	16/07/2012 00:00	33.01	-9.35	1	74	21
14	eb80612	16/07/2012 00:00	33.01	-9.35	5	51	19
15	eb80612	16/07/2012 00:00	33.01	-9.35	1	40	18
16	eb80612	16/07/2012 00:00	33.01	-9.35	1	54	35
17	eb80612	16/07/2012 00:00	33.01	-9.35	2	48	39
18	eb80612	16/07/2012 00:00	33.01	-9.35	0	79	20
19	eb80612	16/07/2012 00:00	33.01	-9.35	0	69	30
20	eb80612	16/07/2012 00:00	33.01	-9.35	1	63	15
21	eb80612	16/07/2012 00:00	33.01	-9.35	2	55	25
22	eb80612	16/07/2012 00:00	33.01	-9.35	4	40	40
23	eb80612	16/07/2012 00:00	33.01	-9.35	0	76	19
24	eb80612	16/07/2012 00:00	33.01	-9.35	0	77	27
25	eb80612	16/07/2012 00:00	33.01	-9.35	2	72	20
26	eb80612	16/07/2012 00:00	33.01	-9.35	0	68	20
27	eb80612	16/07/2012 00:00	33.01	-9.35	1	66	29
28	eb80612	16/07/2012 00:00	33.01	-9.35	5	32	13
29	eb80612	16/07/2012 00:00	33.01	-9.35	1	45	26
30	eb80612	16/07/2012 00:00	33.01	-9.35	3	25	44
31	eb80612	16/07/2012 00:00	33.01	-9.35	1	68	28

Sheet: Sheet1

[L.steel]

Hi,
Many thanks for your replies! So the sample worksheet I attached is the raw data, not what I am aiming for. The sample worksheet contains two sets of data from the same individual birds over the same time period. The left hand data set shows behaviour of each bird, on a selected day; and the right hand set shows the location (latitude and longitude) of each bird for selected days. The two data sets are separated by the blank column. The data collection is explained below.

Maybe some background on the data would help. The birds were tagged and their location (latitude and longitude) recorded every day, however sometimes the confidence for the readings is not sufficiently high so one needs to delete readings. This leaves one with incomplete sequence of days which one can interpolate between. Simultaneously, the proportion of the day that these same birds spent either: flying, resting or foraging was recorded for the same birds each day over the same time period. Once again, some days readings were deleted due to low confidence (but crucially, not necessarily the same days which were deleted in the location data). This data, with certain low confidence days deleted, is shown in my sample worksheet. My aim is to match the behaviours of the birds to their location to see how their behaviours change over their migration routes. So I need to end up with a location (i.e. latitude and longitude reading) and a behaviour reading (the flight, rest and forage columns) per bird per day. But as mentioned, the dates of the behaviour data and the location data do not match up (normally there are also many more behaviour days recorded than location days). One cannot plot a behaviour onto a map unless it is matched to a location, therefore, I need to match the days for each bird by deleting any rows of data from each of the two data sets shown (shown side by side) which do not have an equivalent date and bird reading in the other data set. (the actual rows of data do not need to be changed, just some deleted).

For example, in the example below, I would need the row for the 15/07/2012 on the Left hand data set to be deleted since there is no location recorded for this bird on this date, so I would not be able to plot it.

bird date flight rest_day forage Bird Date Latitude Longitude
eb80612 15/07/2012 3 68 22 eb80612 16/07/2012 33.01 -9.35
eb80612 16/07/2012 5 40 22 eb80612 17/07/2012 51.03 -3.08
eb80612 17/07/2012 0 72 24

Equally, if there was an extra row of data on the right hand data set (location) which did not match up to the same bird and date in the behaviour (left hand side data set) then this would need to be removed. In this example, 21/07/2012 row on the right hand data set would need to be deleted, as there is no corresponding (individual bird and date) row of data in the left hand set.

bird date flight rest_day forage Bird Date Latitude Longitude
eb80612 19/07/2012 51.59 -5.67
eb80612 19/07/2012 2 64 30 eb80612 20/07/2012 52.01 -5.15
eb80612 20/07/2012 1 47 39 eb80612 21/07/2012 52.8 -3.89


I hope this might make it clearer?

Many thanks!

[L.steel]

Hi,
Many thanks for your replies! So the sample worksheet I attached is the raw data, not what I am aiming for. The sample worksheet contains two sets of data from the same individual birds over the same time period. The left hand data set shows behaviour of each bird, on a selected day; and the right hand set shows the location (latitude and longitude) of each bird for selected days. The two data sets are separated by the blank column. The data collection is explained below.

Maybe some background on the data would help. The birds were tagged and their location (latitude and longitude) recorded every day, however sometimes the confidence for the readings is not sufficiently high so one needs to delete readings. This leaves one with incomplete sequence of days which one can interpolate between. Simultaneously, the proportion of the day that these same birds spent either: flying, resting or foraging was recorded for the same birds each day over the same time period. Once again, some days readings were deleted due to low confidence (but crucially, not necessarily the same days which were deleted in the location data). This data, with certain low confidence days deleted, is shown in my sample worksheet. My aim is to match the behaviours of the birds to their location to see how their behaviours change over their migration routes. So I need to end up with a location (i.e. latitude and longitude reading) and a behaviour reading (the flight, rest and forage columns) per bird per day. But as mentioned, the dates of the behaviour data and the location data do not match up (normally there are also many more behaviour days recorded than location days). One cannot plot a behaviour onto a map unless it is matched to a location, therefore, I need to match the days for each bird by deleting any rows of data from each of the two data sets shown (shown side by side) which do not have an equivalent date and bird reading in the other data set. (the actual rows of data do not need to be changed, just some deleted).

For example, in the example below, I would need the row for the 15/07/2012 on the Left hand data set to be deleted since there is no location recorded for this bird on this date, so I would not be able to plot it.

bird date flight rest_day forage Bird Date Latitude Longitude
eb80612 15/07/2012 3 68 22 eb80612 16/07/2012 33.01 -9.35
eb80612 16/07/2012 5 40 22 eb80612 17/07/2012 51.03 -3.08
eb80612 17/07/2012 0 72 24

Equally, if there was an extra row of data on the right hand data set (location) which did not match up to the same bird and date in the behaviour (left hand side data set) then this would need to be removed. In this example, 21/07/2012 row on the right hand data set would need to be deleted, as there is no corresponding (individual bird and date) row of data in the left hand set.

bird date flight rest_day forage Bird Date Latitude Longitude
eb80612 19/07/2012 2 64 30 eb80612 19/07/2012 51.59 -5.67
eb80612 20/07/2012 1 47 39 eb80612 20/07/2012 52.01 -5.15
--------------------------------- eb80612 21/07/2012 52.8 -3.89


I hope this might make it clearer?

Many thanks!

[JeteMc]

I feel as if I understand what you want and hopefully this will be helpful.
The following proposal doesn't actually delete any rows, however on sheet 2 it keeps only rows matching birds and dates.
There are two added columns on sheet 1:
1) Loc. # is populated using*: =MATCH(A2&B2,G$2:G$1000&H$2:H$1000,0)
2) FRF SH2 # is populated using*: =IF(G2="","",MATCH(G2&H2,Sheet2!A$2:A$1000&Sheet2!B$2:B$1000,0))+1
On sheet 2 column A is populated using:

Please Login or Register  to view this content.

Columns B:E are populated using:

Please Login or Register  to view this content.

Columns G:J are populated using:

Please Login or Register  to view this content.

*Denotes an array entered formula which is confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Note that in the original there were no rows of Lat. and Long. that did not match a row of flight, rest and forage, so I inserted one (row 3) so that you could see the formulas applied.
Let us know if you have any questions.