eBird hero to zero

There is a new eBird hero on the block. Me. And I deserve some serious kudos, for I've just uploaded some 2500 UK checklists. For those not familiar with the concept, a checklist is a defined list of birds seen in a certain location on a certain date - either actual counts or an indication that a species was present but without a count. The theory is that other users also enter their lists, and thus you have a gigantic database that grows exponentially and in time becomes an incredible scientific resource. I had a lot of lists going back years, all stored in a huge Excel spreadsheet. Thousands of lists with every bird I've seen. Counts too in some cases, not simply that I'd seen a species, but that I'd counted twenty three of them. Not many birders will have that kind of electronic record. Faced with that many lists in a series of notebooks for example, how many birders would attempt to digitize them? Some of the earliest lists date from 2003, and there is a glut from 2009-2011 when I found myself gleefully unemployed and able to go birding the length and breadth of the land. UK birding has of course rather tailed off now that I don't twitch, but I still dutifully record each and every outing on the patch when I get home. I submitted 997 lists from Wanstead alone.

EBird has the facility for bulk uploads, so I painstakingly created a template that would allow me to transfer my Excel records in large batches. At this point I'd like to apologise to all the volunteer bird list checkers out there, graciously giving their time to try and keep eBird free from nonsensical data. It's not that my lists are complete garbage, but there has been a teensy weensy problem with dates. My spreadsheet is a thing of beauty, but I will be the first to admit that spreadsheet design is not my forte. It appears that about fifteen years ago i made the momentous decision to combine the location and the date in one cell. Apparently I also decided that consistency was something that needn't concern me too much. Anyway, many years later I have an exceptional record of sightings which are almost impossible to extract a date for, despite the fact that it is there in black and white.

"Wanstead Flats, Sep 2nd", with the year recorded in a separate cell.

Looks simple, but it isn't. 

What about "Wanstead Flats. London, 2nd Sept"? 

You get the idea. How do I get from myriad versions of this to a simple US date format of mm/dd/yyyy? It was very nearly beyond me. Numerous eBird volunteers may suggest that it was definitely beyond me. Eventually I worked out a formula that converted vast majority of the dates correctly, removing commas, splitting the location, days and months, and then concatenating them back up. It was so nearly perfect....ahem.

Many of the dates are correct. Many however seem completely random, and where these concern rarities, as many do, this is confounding the volunteers. Some are close, for instance I was only ten days out on the Portland Brunnich's Guillemot, my translation formula having produced 12/19/2012 rather than 12/29/2012. But when the bird was not found until the 26th..... Others are just rubbish, with no element of the day, month or year bearing any resemblance to when I actually saw a bird. Puffins on the Isle of May in December, Pink-footed Geese in high summer, that kind of thing. When you order my lists by date this results in my being in Scotland, on Scilly and at home in Wanstead more or less simultaneously, or at the very least hints at some rather crazy driving.

Correcting these foul ups is taking some time, and in many cases the vols are get to them first. I think they have some sort of auto exception flagging, but nonetheless it must be rather irritating, especially when these records are from 2008 and so on. Mostly they are being very helpful, suggesting what the actual dates was based on weekends etc, or telling me the period during which the bird was present. Others are more curt, just saying my list is wrong and to please check it. Fair enough really, and so that is what I am doing, helped by the original spreadsheet, this blog, and also good old fashioned notebooks - essential for working out which list is which where it involves a pace I've visited many times. Norfolk seems particularly screwed up, along with Scottish records excluding Shetland. I'll get there, but so much for the efficiency of the batch upload. Then again without it I likely would not have bothered.

Hassle aside it is a great trip down memory lane. I didn't half get about in those days. Every weekend there is something. A juicy mega, a long sea watch, a day of migrant quality on the Norfolk coast and, in between, hundreds of patch visits. I birded a huge amount. It makes me want to do so again.