BITC / NBD protocol - 3
0 (0 Likes / 0 Dislikes)
We have now managed to aggregate our point data to the grid IDs of our ½ degree aggregation grid.
Now we will continue with some work in Excel.
We're going to paste the dataset into Excel and call the sheet 'ORIGINAL'.
There it is.
We can get rid of the first column which is inserted by QGIS.
Now we have our time stamp and species.
Both of those are from the original dataset.
And, we have the grid ID from the coarse-grained aggregation grid.
Call this sheet 'Original'.
And, create a second sheet called 'Working'.
Remember, we never change our original data so we always have the option to backtrack if necessary.
Now we have a copy that we can work with.
Add a column called 'Dummy'.
This is populated with 1's so that we can do calculations and summaries.
Okay.
There's working.
Let's refer back to the protocol.
In Working, reduce to 3 columns... Add a column... We've done that.
Now, we want to estimate N.
We will do this using a pivot table.
Highlight the entire area in 'Working'.
Data --> Pivot Table
Sometimes 'Pivot Table' is under 'Insert' in other versions of Excel.
Here we go.
Put it into a new sheet.
Bring the grid ID down to here.
Bring Dummy down to here.
You can see that we have the grid ID from our aggregation grid
and we have the sum of our marker, Dummy.
That gives us exactly what we wanted — sample sizes per grid cell.
Paste that into a new sheet called 'Summary'.
Change this to 'Grid ID'.
And, this to 'N'.
Now, we have the first piece of the picture:
the sample size for each grid cell in our map.