Data Wrangling with eBird – Part 3

Please.  Oh, please.  Not another post with geeky, nerdy, techno-garble!  Yes.  Another one.  Relax.  I will get back to different material soon.  I just want to finish what I started here–and that is how to use eBird to help plan the birding portion of my trip to Valley Verde Birding Festival next spring.  Oh, sure.  I can just print off last year’s bird list.  But that won’t tell me much about which of those birds is the most likely to be seen.  That is where eBird is an absolute treasure trove of information!

In the first tutorial I showed you how to get data out of eBird and into a spreadsheet.  In Part 2 I showed you how to clean up the data and highlight the expected species for the 4th week of April in Yavapai County, Arizona.  In Part 3 I want to show you how to list the 25 most common birds for my upcoming trip.  Ready?  Here we go.

First, open up the spreadsheet we’ve been working on.  Now add a new sheet to the workbook.  I am using the spreadsheet LibreOffice Calc.  Just click on the little “+” sign at the bottom:

01 spreadsheet insert a new sheet

Highlight all the species (including the column title “Species”).  The from the main menu select Edit > Copy:

02 spreadsheet highlight and copy species list

Go to the bottom of the page and select the tab, “Sheet2”.  On the new sheet single click on the top left cell, “A1”.  Then from the main menu select Edit > Paste.

03 spreadsheet past species into new sheet

Boom!  There’s all the species.  Now move your cursor so it’s exactly between column A and column B.  The arrow should turn into a little vertical bar with arrow pointing both left and right.  Double-click.  Bam!  You just “auto-fit” the width of your column.  Ok.  Maybe you’re already a spreadsheet genius.  Thanks for being patient with the rest of us.

04 spreadsheet widen column

Now go back to “Sheet1”.  Find the column with data for “Apr_wk4”.  Highlight all the data including the column title, “Apr_wk4”.  Select Edit > Copy from the main menu of the spreadsheet.  Then go to “Sheet2”.

05 spreadsheet highlight and copy Apr_wk4 data

In “Sheet2” click on the cell, “B1”.  This is where we want to paste the data for April week 4.  Choose Edit > Paste from the main menu.  Note that the yellow highlighting is gone.  Don’t worry about that for now.

06 spreadsheet paste Apr_wk4 data in new sheet

Click on cell, “A1” again.  Select Insert > Columns from the main menu.

07 spreadsheet insert a column

This column is for taxonomical sequence so we can put the list back into its original order after we’re done fiddling with the data.  Name the column something descriptive.  I am using “Tax_Seq” in this example.  Enter 1 into the first cell below the title.  In the next cell down, type in the formula “=A2+1”.  This will add one to the previous cell.

08 spreadsheet create a tax_seq column

We want to copy that formula for the remainder of the cells.  Click cell, “A3” (the cell where we just added the formula).  Press ctrl+c (2 keys simultaneously).  This is the same action as Edit > Copy from the main menu.  Then highlight the rest of the cells below it.  Press ctrl+v (or Edit > Paste from the main menu) to paste formulas into the remaining cells.  Voila!  You have sequenced the species list!

09 spreadsheet populate tax_seq column

But, these formulas will get messed up when we sort this data into a different order.  To preserve their values we need to convert the formulas to numbers.  How do we do that?  Highlight all the data in this column again.  Press ctrl+c to copy the data.  Now the fun part.  Do NOT use ctrl+p!  This will not change anything.  Use the main menu on this one.  Select Edit > Paste Special…

11 spreadsheet menu paste special

A dialog box for Paste Special will appear.  In the Selection region UNCHECK everything except for Numbers.  If you paste formulas again, it will not change anything.  Click on “Ok” to continue.

12 spreadsheet paste special dialog box

Now you should see a warning message asking if you want to overwrite data.  Yes.  We want to do this.  This will take the displayed values–the numbers–and overwrite the formulas.  Click the “Yes” button.

13 spreadsheet paste message

Make sure your latest active cell is somewhere in the data.  If you are not sure, just single click on cell, “A1”.  Go to the main menu and select Data > Sort.

14 spreadsheet set up data sort

We want to sort this data by descending (largest to smallest) frequency of checklists–the data we have in the column for Apr_wk4.  Remember in eBird that Frequency of Checklists is the percentage of checklists on which a bird is recorded out of the total number of checklists submitted to eBird.  Some of them may be ties, so we are going to set up the second sort criteria as Tax_Seq ascending (smallest to largest).

15 spreadsheet sort dialog box

The raw data we see are percentages in decimal form.  Let’s make them easier to read by converting them to a percent format for our spreadsheet.  Highlight all the data for Apr_wk4.  Click on the little “%” sign from the top menu of the spreadsheet.

16 spreadsheet convert Apr_wk4 data to percents

Let’s color code the values so they will stand out in a list.  Highlight all the numbers in column C–for Apr_wk4.  From the main menu select Format > Conditional Formatting > Color Scale…

17 spreadsheet set up color scale for Apr_wk4

And up pops another dialog box.  This one is to set up your color scale.  You can choose any colors you want.  In this example I want the rare birds to show up with some light blue highlighting (light cyan here) and the really common birds to be highlighted using Orange 2.  The in between-ers will be light gray.  Once you’ve finished with your artsy design, click “Ok” to continue.

18 spreadsheet conditional formatting dialog box

Wow!  Look at this.  There is our list of the Top 25 Most Reported Species for Yavapai County, Arizona during the last week of April. (I took liberty and highlight my column titles in yellow and drew borders around the cells, too).

Looking at this list almost half of them are of serious interest to this Ohio birder.  There are quite a few birds that are either pretty hard to find in Ohio or have never been seen there at all.  Cool.

19 spreadsheet most common species

Now, back to losing our yellow highlighting and not being able to see our expected species–those over the 2% threshold that we used.  Want to flag those species again?  Ok.  But we don’t wanna ruin our pretty work of art with all the color highlighting.  So in the column D, add the formula =if(c2>=0.02,1,””)  This says if the value for Apr_wk4 is greater than or equal to 2% then flag it with the number 1, else leave column D blank.  Once you have done this you can copy that formula down through the cells in column D.

20 spreadsheet flag target species

Now pat yourself on the back!  Nice job being patient enough to get this far!  You are now a seasoned eBird travel planner!  Good luck with your next trip!!!


Leave a Reply

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