What we do
About the TEAPeople
Membership / Donate
About insectsInsects of Ontario
Endangered sp. / Laws
For Ontario Nature
Submitting Butterfly Records for the
TEA Seasonal Summaries and Atlas
by Rick Cavasin. For comments, contact Alan Macnaughton (email@example.com)
BackgroundOver the years, the number of Butterfly observations that have been submitted on citizen science platforms like eButterfly and iNaturalist has ballooned. We are now getting 10s of thousands of observations every year (over 50,000 for each of 2020 and 2021). It's no longer feasible to process these observations "by hand". The only way we can ensure timely and accurate conversion of the data into the Atlas format is to perform this operation using software. In order to make this possible, the input to that software has to be in a consistent format.
For the citizen science platforms, this is straightforward, as we receive the data in a standardised format from each platform. Although the TEA published a spreadsheet template in the past, it allowed considerable latitude in how observers could fill it out, and some people went beyond that and customised the spreadsheet. As a result, previous Atlas compilers were required to spend a huge amount of their personal time converting these diverse formats into the format required by the Atlas. Not only is this process time consuming (and maddening), but it is also error prone. Even with all that work, we were still left with certain inconsistencies.
Many people are submitting their photo observations to iNaturalist, and the Ontario Butterfly Atlas periodically retrieves observations from iNaturalist for inclusion. For a number of reasons, we do not use observations from iNaturalist that do not include photographs (ie. "sight" records). If you do not want to use iNaturalist (or eButterfly), or if you want to submit your "sight" observations to the Atlas, we offer the option of submitting your observations in spreadsheet format.
The New Spreadsheet Template
Observers who wish to submit their observations in spreadsheet format must use the new template provided (see link above) and follow the guidelines outlined below. Most of it is fairly self explanatory and the template contains some sample entries. There is a small amount of flexibility - chiefly in terms of which columns you choose to use. Because the data is going to be processed using software, there is limited flexibility on what goes into those columns. You can't put just anything in there and expect the software to "understand". For example, you can ONLY use decimal latitude/longitude. While you can use either a Common Name or a Scientific Name, you must use the names in the TEAs published list (please pick either Common or Scientific and stick with it). I will ONLY accept subspecies names for Limenitis arthemis, and for Erynnis persius. You can include genus level observations, but for the most part, these will be discarded as the Atlas does not record genus level observations (with the exception of Celastrina sp. and larval/pupal Limenitis observations). For those who prefer to use Scientific names, and who prefer to use the most up-to-date taxonomy, some allowance will be made. You may use a more "up-to-date" scientific name if that name is currently used on iNaturalist. I don't have time to track down and verify every alternative name that's out there. If I don't recognize the scientific name you're using, your spreadsheet will be returned to you.
You will see that there are 3 sheets in the template - they should appear as tabs along the bottom of the spreadsheet. The first one is the actual submission template. The second sheet is the rules/suggestions about what can go in the various columns. The third sheet is the list of acceptable species names (Common and Scientific).
The column order can be changed, but the column names must NOT be altered, as the software needs those names in order to figure out what's in each column.
The following are either/or options: You can enter the observation date either in the Date column, or you can use the Year, Month, Day columns. Please don't use both, or alternate. Pick which column(s) you want to use, and stick with it. The sample data in the sheet shows a mix for illustrative purposes. You can enter either the Common Name or the Scientific Name but NOT BOTH. (the sample data has a mix, but you should pick one and stick with it). Again, you must use the names that are in our published list.
Note that information that repeats for multiple observations (things like the date, location, latitude, longitude, etc.) MUST be copied down the columns for all entries to which they apply. You can't leave cells blank and assume that the software is going to "understand" that the date/location from the previous line will apply. The software reads each row of the spreadsheet one by one, and treats each one as an independent observation (even if you are grouping them together as a "checklist" with a common date/location). There are easy shortcuts for copying multiple cells down the columns (some are outlined in a video linked further down this email).
For each column the following guidelines apply (you can see these guidelines in the guidelines tab at the bottom of the template):
Coordinate Accuracy (optional)
Survey Remarks - (optional)
- if you choose to specify species using its Scientific Name, it must be the Scientific Name the TEA is currently using, or the Scientific Name currently use on iNaturalist (see the list in the 3rd sheet)
Also reported on
iNat Observation ID - (optional)
eB Observation ID - (optional)
Note that the order of the columns can be changed, and if there's an optional column that you don't think you will ever use, you can delete it from your spreadsheet (though hiding it is probably safer). For example, if you are always going to enter your observation dates using the Year, Month and Day columns, feel free to delete the Date column. The one thing you absolutely cannot change is the spelling of the column headings. The software used by the TEA will read in the spreadsheet header, and use the column names to figure out what's in each column. It will do this by searching for the original column names as they appear in the template. If an optional column is completely missing, our software will handle it. If you delete a non-optional column, things aren't going to work, so it might be safer to just "hide" the columns you don't want to use.
As a general rule, we want to discourage third party reporting. More often than not, the observation gets reported by the original observer (or multiple third parties!), with a different set of Lat/Long coordinates, resulting in duplicate observations. Even if this doesn't happen, it is very difficult to verify anything about these observations, or to ask any questions if something doesn't look quite right. As far as we're concerned, third party reports are just hearsay, and the Atlas already contains enough dubious and unverifiable observations. As a result, we're going to ignore any observations that appear to have been reported on behalf of someone else.
Some of you who are spreadsheet savvy may decide you want to fancy up my simple spreadsheet. That's fine, as long as you don't change the heading names. If you add formulae or anything like that, please strip them out before you submit your spreadsheet, or simply export your spreadsheet in CSV format before sending it in.
If these restrictions seem draconian, there are good reasons for all of them which we would be happy to explain.
The following is a suggestion for how to speed up your data entry (if you have trouble understanding this, feel free to contact me for clarification): The order of the columns is set up to facilitate chronological data entry. The idea is that you enter all the observations for an outing (or survey) at the same time. The very best time to do this is soon after your outing, rather than at the end of the season. The idea is similar to the "checklist" model used in eButterfly, where you report a series of observations for one location/date together as a group. You enter your date and location information (the info that is common to all the observations you want to group into a checklist) into one row, and then copy that information down for as many rows as you have observations to report. Then you fill in the observation specific information (the pink columns) on a row by row basis. The coordinate accuracy is just an estimate of how far off the Lat/Long are from where the observations were made (ie. the maximum error - use one value for all the observations at that location). Take a look at the sample data in the template to get an idea.
The way I fill this spreadsheet in is to group together observations that were recorded within a reasonable distance of each other - as one would for a checklist on eButterfly. If I observe 10 species along a 5km hike, I enter the Lat/Long coordinates of the approximate midpoint of the hike, and copy them for all 10 species I observed, and I set the Coordinate accuracy to something like 2500, which would be the maximum distance any individual observation might be from the midpoint of my hike. Think of the Accuracy as the radius of a circle around the Lat/Long coordinates that encompasses all your observations. This "checklist" mode of entering observations cuts down the amount of time required to enter your observations into the spreadsheet since you only have to enter the values in the yellow columns once for all the observations you made for a particular date/location (and then you copy those values for the rest of the observations in the checklist). For tips on how to quickly copy the content from one row of a spreadsheet onto a number of rows, have a look at this video: https://www.youtube.com/watch?v=u1rp9nzLQw8 There are probably lots of other videos on youtube that explain this in other ways, along with other tips on spreadsheet data entry.
If you prefer to enter more exact coordinates for each individual observation, you can do that. Just be aware that the software that will process your observations will check the proximity of observations for the same species, and multiple observations of the same species reported within a few hundred metres of each other may be represented by a single observation in the Atlas. Also, keep in mind that observations with an accuracy figure greater than 10km will be excluded from the Atlas altogether. We recommend grouping together observations that were made a short distance apart, but please keep it within a radius of a few kilometres maximum.
Another suggestion for those who report observations made over extended distances - you might want to take Atlas square boundaries into account, and split your checklists so that your observations get reported for the correct Atlas square. There are a number of ways you can do this, and detailed instructions are beyond the scope of this set of suggestions.
If you are unsure of whether or not you are filling in the template properly, feel free to submit a sample after you have filled in a few entries, and we will let you know if there are any problems with what you're doing.
For the 2022 year, we'd like to get as many submissions as possible BEFORE Dec. 1 so that we can put out the 2022 seasonal summary in a more timely fashion. But you can submit your observations (including observations from past years) at any time for inclusion in the Atlas.