DIY Big Year: A Geeky Look At Data

Scenic Bixby Creek Bridge in Big Sur California – photo by Greg Miller

If you know me then you know I like numbers. A lot. Actually. I love data. It can be so powerful. But it can also be misleading and confusing.

In an early DIY Big Year post I told you about some eBird data that I have been wrangling with for over a year now. In this post I want to give you a peak at some different ways to look at the data I have collected.

Let’s have some fun with Top 10 lists. I have data rolled up for 299 Counties in the United States. The Counties are found all over. All 50 States are represented. Checklist data is from eBird (http://ebird.org) from 2006-2016 as of September 2016. Area and population data come from census.gov.

There you have it—a preliminary look at the data I am using. The cross section of data is pretty diverse. It has highly populated areas as well as those that are sparsely populated. Some are large in area. Some are small.

But all of these Counties have one thing in common—they are the most checklists submitted in the United States (or for some States, the most checklists in the State. See my criteria in my previous posts).

Do the most populated Counties have the most checklists? Not always. Do the Counties with the most checklists have the highest number of species? Not always. Is there a significant correlation between population and total checklists submitted? Nope. How about between checklists submitted and number of species? No again.

So how does this all figure into planning a Big Year? I’m glad you asked. Because I am going to tell you anyway. We live in an age with dizzying amounts of data. Having data can be powerful. But only if you are able to harness the information to help you in a way that makes sense.

What am I talking about? Our goal for a Big Year is to see as many unique species in one calendar year as possible. And remember I hope to make this affordable and efficient, too. A bigger population means are larger number of checklists submitted, but only up to a point. And a larger number of checklists means a larger number of species, but again, only up to a point.

All of the data above is interesting. But it does not yet answer the questions about where and when to go birding for the greatest number of unique species in the shortest amount of time.

The total number of species listed above is for the whole period from 2006-2016. It encompasses all seasons. So that number really is not a great indicator of where to go and a poor indicator of when to go.

There are 299 Counties. And each County has 4 weeks of data per month. A month is always 4 weeks in eBird. The first week is the 1st through the 7th. The second week is the 8th through the 14th. The third week is the 15th through the 21st. And the fourth (and last) week is the 22nd through the end of the month. So 299 Counties x 48 weeks of data gives one a large number of possibilities of where to go and when. In fact, that number of possibilities is 14,352. (Have I told you how much I like numbers?)

Now you may be asking, out of 14,352 possibilities where does one even begin to guess where to go and when? Oh, you thought that was complicated. Throw in 984 species into the mix. Yep. You will need more than a hand calculator. You could do it in a spreadsheet if you could do a pivot table of 7 million rows and then sort it. Good luck with that.

A database is a perfect solution. It is unparalleled in its power to perform on problems like this. It can make calculations on mind-boggling amounts of data and retrieve the information in a matter of seconds (ok, minutes for some of our questions). And this is what I did. I took all those spreadsheets of downloaded data and loaded them into a database. (I used Sql Server Express)

In my next post I will tell you how I used this data to arrive at some answers to the questions of the best places to go at the best times of year to maximize the total number of species on each trip. You won’t want to miss that one!