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:
Highlight all the species (including the column title “Species”). The from the main menu select Edit > Copy:
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.
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.
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”.
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.
Click on cell, “A1” again. Select Insert > Columns from the main menu.
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.
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!
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…
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.
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.
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.
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).
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.
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…
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.
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.
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.
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!!!