Making a List of Mid-Atlantic Rarities

Well, the final end-goal of this project is yet to be realized (will post here in a few weeks about it), BUT I just wanted to share this spreadsheet in case others were interested. It is a list of all 508 species seen in Maryland, Delaware, Pennsylvania, New Jersey and Virginia along with their ebird frequency in each state. I also added a 'weighted' summary column in order to average all numbers together. My goal in creating this was to see if ebird could help me draw the line at what is considered a 'regional' rarity. I'm still deciding exactly where to draw the line. I'll likely pick somewhere between #200 and #250 and then manually add/subtract a few.  Another use for this, would be to quickly check which nearby states have records of which species. For example, if you were creating a "next top 10 list" for potential first state records, something like this would be extremely handy. Long term, I will probably add a few more states to this or maybe even make a "East of US spreadsheet"


Link to excel spreadsheet


If anyone is curious - this was the key excel formula in lining up checklist percentages from one state to another.

=IF(ISNUMBER(INDEX(R:R,MATCH($A1,Q:Q,0))),INDEX(R:R,MATCH($A1,Q:Q,0)),0)

The formula gathers the species name from A1 and then looks for a match in Column Q. If it finds a match, then it returns the adjacent value in column R.

Map Your eBird History

Well I don't think I'm the only one that has ever wished eBird could give you more personalized maps. One of the things I have wanted most is to be able to easily see all the locations where I have submitted a checklist. Not only that, but what if it could also tell you how your birding patterns have changed spatially over time? Well this ended up being relatively easy and straightforward, and I think the end product is pretty cool. Obviously, the ebird personal data download was a great starting point. Included in the file you download are the full details of every species you have ever submitted to ebird. However, this is way too much information for this project, so the idea is to first parse down the info. With a few sorts and deletes, I transformed the spreadsheet into exactly what I wanted to know. 5 columns: Name of location, latitude, longitude, date of first checklist, and # of checklists. I then took that spreadsheet and uploaded it to geocommons, a free online mapping tool (sign-up required). Geocommons is pretty intuitive, but it might take a little bit to understand how to get started and style your map. For anyone wanting to give this a try, I've outlined some instructions below. You might need a bit of patience, but I don't think it is too difficult. This process is also pretty adaptable. Simple changes to the excel steps could give you an animated map of all your life birds, or display every location where you have ever seen a blue jay. One more note -  by default, geocommons makes all data uploaded to their site publicly available. You will have to change the settings for your data & map if you want to keep it private. 

If you want to take a look at my birding history map before giving it a try with your data - here is the link http://geocommons.com/maps/251520 

Click the play button to watch the animation of locations over time and zoom in on Maryland to see how the weighting by number of checklists works. 

Steps:

1) Download you ebird data 

2) Open the csv file and paste all contents into this excel macro workbook. 

3) Run the 'setupmap' macro embedded in the workbook

4) Save the result as a CSV file

Go to Geocommons

1) Sign up for a new account if you don't have one

2) Start a new map

3) Click "Upload Data" and upload your csv file (it will automatically map your locations)

4) Under the style menu, select "Adjust Icon Size" 

5) Choose Graduated and choose 'Checklists' as the attribute variable

7) Select interval breaks that match your data - Likely to be skewed. Breaks of 3, 10, 20, and 30 worked best for me. 

8) If you want, you can also add time animation based on 'Date'

Update - the excel macro has been updated from a previous version that did not extend properly. It now can handle 25,000 checklists. Try it out and let me know if the new version works. If you are still having trouble - here are the excel steps that you could do to replicate the work of the macro. 

1) First "remove duplicates" based on the submission ID column - this gets you down to only your unique checklists

2) Delete extraneous columns - leave only: location name, latitude, longitude, date

3) Sort by date - oldest at the top

4) In the first free column use the formula =Countif(A:A,A2) where A is the column with location name. Fill this formula down all rows. This sums your total checklists for each location. 

5) Copy and paste this checklist column to keep it fixed - Use paste values only 

6) "Remove duplicates" based on location name. 

7) Sort by checklist # - you want your most birded locations at the top

8) Save as a csv file

Update 2 - Since I posted this a few weeks ago, it has been picked up by Birdventure Birding and Thermal Birding. Check out those links for more example maps. 

Test

This site is a replacement for https://sites.google.com/site/birdingfiles/home. Expect new posts ~ monthly.