Data Wrangling With eBird – Part 1

I am planning a trip to Cottonwood, Arizona during the last week of April, 2014 for the Verde Valley Birding Festival. This is an area that is north of Phoenix and south of Flagstaff. It is not included in the American Birding Association’s Birdfinding Guide to Southeast Arizona. Can eBird help me with what kind of birds to expect then? The answer is “Yes”. This post is a tutorial of how to get eBird data into a spreadsheet so you can do just that for the last week of April in Yavapai County, Arizona. In Part 1 we will just get the data into the spreadsheet. In future Parts we will explore some of the possibities of what can be done with this data.

By now I am sure many of you have at least heard about eBird, a Citizen Science project. It is an online database of bird sightings from around the world contributed by birders of all skills. Last year, eBird reached a milestone of over 100 million sightings!!!

If you don’t already contribute to this effort, you should consider it. It is quite easy. You can contribute at their website at or through an app like BirdLog or you can convert data from your existing sightings database with whatever listing software you are using. A lot of online help for this is available.

Ebird has a surprising amount of research value on their website. A birder can get “live” maps for a specific species, a bar chart of abundance for a list of species for a number of different geographical areas, or even line graphs showing abundance of a species by season. It is fun stuff!

But eBird data wrangling? What is that? Basically it is just a fun term for getting data out of eBird into a format that you can use for research on your own. But can’t you just get the bird list for Yavapai County, Arizona from eBird without using a spreadsheet? Well, sort of. What you actually get will be a list of species with bar charts for the entire month of April. The online resource does not drill down any further. But you can get every week of the year inside the spreadsheet we’re about to create!

This post assumes you are somewhat familiar with 1) Microsoft Excel (or some spreadsheet that can read Excel—I am using LibreOffice Calc in this example) and 2) eBird.

Ready? Ok. Here we go. Get online with your favorite browser and go to the website, Click on the tab that says “Explore Data”.

01 eBird Explore tab

Click on “Bar Charts”.

02 eBird View and Explore Data

Select “Arizona” for our target state and then click on the “Counties in Arizona” radio button. After you have done this, click on the “Continue” button.

03 eBird Choose a Location

Here you could click on multiple counties. For now all we need is one. Click on “Yavapai” to select Yavapai County. Then click on the “Continue” button to proceed.

04 eBird Choose up to 15 counties

Bam! You can now see “Bird Observations”. This is a list of all the birds that have been recorded in eBird for Yavapai County, Arizona for all years (1900 to present) and all months. As of the writing of this post, 342 species have been recorded in eBird.

The bar charts actually represent something eBird calls “Frequency of Checklists”. It is simply the percent of checklists which have a particular species recorded. For instance, if a bird like Red-faced Warbler occurred on 3 checklists and a total of 300 checklists were submitted, then the value shown here would be 3 divided by 300 or 1%. It will be displayed in the final spreadsheet as 0.01. Got it?

Page down on this web page until you get to the bottom.

05 eBird Bird Observations

Click on “Download Histogram Data” at the bottom right.

06 eBird Download Histogram Data

Click on the “Browse” button to see a list of available programs.

07 eBird Opening BarChart

Select your spreadsheet of choice.

08 eBird Choose Helper Application

Click the “Ok” button to continue.

09 eBird Opening BarChart 2

Click on “Tab” and “Comma” under “Separated by” (Excel may be different). If you are using Libreoffice like I am you will definitely want to click the check box, “Detect special numbers”. This will make sure that numbers in scientific notation will be read correctly. Click “Ok” to continue.

10 LibreOffice Text Import

Shazam! Data in what looks like a spreadsheet! Note that you cannot do anything with it yet. It is in read-only format.

11 LibreOffice BarChart-2.xls (read-only)

Now let’s save it as a spreadsheet so we can do magic with it later. Click on “File > Save As” from the top spreadsheet menu.

12 LibreOffice File Save As

Save the file as a spreadsheet. It currently is in CSV format (comma separated values).

13 LibreOffice Save As Dialog

Select the directory where you want to save the file and then type in the filename. Click on the “Save” button. You now have a spreadsheet with eBird data in it.

14 LibreOffice Save As Dialog 2

There you have it!  eBird data in your very own spreadsheet!  I hope you have found this tutorial easy to follow.

In the next post I will show you how to use this data.


Leave a Reply

Your email address will not be published. Required fields are marked *