BELSFish Workflow

Craig Meyer
85 steps
7 minutes

About Page

Google Account

Google Docs

Google Sheets

Geo-Locate

All the scripts are available here: https://github.com/BRITorg/Grouper

Download the data

It's probably going to be best to decrease the size of your dataset as far as you can. We'll get into the math in a second, but proabably want to keep this to county-level.

Run pyBELS

**Q: "So why run pyBELS, when we're going to use Grouper later?"** A: "pyBELS significantly reduces the human and machine burden! Grouper's matching compares every record to every other record, so the number of comparisons grows very quickly. Reducing a 27,000 record county with pyBELS to 11,000 representative records reduces the unique pairs from 364 million to 60 million. More importantly, it's a lot faster for the person validating the grouping. Going through a spreadsheet of 11,000 grouped representative locality strings is a lot less work than doing the same with 27,000 records. Removing a bunch of near duplicates with pyBELS also means that bad matches stick out more, so the review process is more accurate as well."

Run Grouper

We're actually going to do two things here! We'll trim the large pyBELS files with BELSFillet.py, and then regroup the BELS groups with Grouper.py.

Navigate to the folder containing the BELSFish scripts. Type "cmd" in the navigation bar to open a command window.

In the command window, type "python BelsFillet.py" and navigate to the pyBels output folder. Note that the script will create a copy of each file with a -trimmed.tsv suffix.

Though I'm advertising it as a "trimmer" it's actually doing a few things: - Checking the core columns exist, substituting nulls when necessary - Placeholder catalogNumber ("ID\_" plus occurrence ID) when null - Adding some spreadsheet formulas - isInstitution flag - latitude/longitude count - "MOOSH" for unique strings of Grouper_ID, lat, lon - "REVIEW" logic - Where's Walter hyperlink

Type "python Grouper.py" and enter the location of the -trimmed.csv you want to group.

We're doing a lot of stuff here too: - Normalizing locality text ("1/2" to "0.5", "NW" to "northwest", etc) - Extracting distance, unit, direction from locality (5, miles, east" - Aliasing rare words to similar common words ("praririe" to "prairie") - Grouping similar normalized & aliased localities together with a "Grouper_ID" - Splitting groups with different distance/unit/directions ("12" to "12.1", "12.2")

Grouper tries to replace rare words (hopefully typos!) with similar, more common words. Without aliasing, Grouper is really sensitive to typos, which will often split localities even when everything else is identical. You can't manually change how things get aliased here, but you can dial that sensitivity up or down based on your dataset.

Now that we've created the files, we're going to set them aside for a minute while we get the BELSFish spreadsheet together. This spreadsheet will be where we do most of the work reviewing and ultimately exporting our grouped localities and coordinates.

Make a copy of the BELSFish template

All we're doing here is making a copy of the BELSFish spreadsheet and using it as a template. This is the easiest way to keep formatting and the script together.

Navigate to <https://docs.google.com/spreadsheets/d/1ziYUi7unDyAI6U0xyuL-UQGF-gYTCYPWbid6JPxEFuk/edit?usp=sharing>

Click "File"

Click "Make a copy"

Click this text field, and rename the template to your project.

Click "Make a copy"

Import the -trimmed file to the "Original" tab.

In this step, we’re putting the "trimmed" data set into the spreadsheet template. This contains every record. Later, we’ll apply our key to the records on this tab so every record has a Grouper_ID and coordinates.

This next part is important and easy to overlook. We need cell A1 to be highlighted before we import data. Trying to fix it later will probably mess up a lot of cell references, and who needs that hassle?

Click cell A1.

Click "File"

Click "Import"

Click "Upload" and select your "-trimmed.csv" file.

Click here.

Click "Replace data at selected cell"

The "Convert text to numbers, dates and formulas" option is pretty important here, since we're importing text we want the spreadsheet to interpret as a formula. Good thing it's on by default!

Click "Import data"

Import the -key file to the "Key" tab

Now we’re going to import the Grouper file we created with the python script. It's a key that links BELS groups to Grouper groups. We'll manually review these later and lump/split Grouper_IDs together.

Remember how we needed to select a specific cell before import earlier? Same thing here - select the red B2 cell before we import data to make sure data goes to the right place.

The A column contains a serial number for the cell shading. The Key's "Serial" A column is hidden for simplicity.

Click "File"

Click "Import"

Click here.

Click "Replace data at selected cell"

Click "Import data"

Update cell shading

We'll be spending a lot of time reviewing the groups here, and shading by Grouper_id can make that much easier. This is a little cumbersome to set up, but worth it.

Hey remember how I hid the A column on this tab earlier? I'm unhiding it now to show you what it's doing.

Click "BELSFish"

Click "Update Cell Shading"

Psych! The script won't work until it's authorized to run.

Click "OK"

Click on your Google account

Yes, there is a terrifying warning here. This script needs a lot of permissions to run, sadly. If you want to review what you're agreeing to, there's a copy of the appscript in "SpreadsheetTools.gs" in the grouper GitHub.

Click this checkbox.

Click here.

Authorizing the script interrupted that function, so we gotta do it again.

Click "BELSFish"

Click "Update Cell Shading"

So now we've updated the formula in the A column. All it does is looks for changes in Grouper_id and add 1 in the row when the values aren't the same. Now we need to copy the conditional formatting from the second row through the rest of the Key tab.

Select the cells on the second row.

There are actually two conditional formatting rules we want to copy over everything. One rule sets the entire row green if the serial# is odd, and the other rule sets the column H red if the value is more than a little different than the surrounding values. The easiest way to get both is to highlight all the cells in row 2.

This isn't a step. It's just reference for what we're copying from the first row through the rest of the key.

Copy the cells with [[ctrl]] + [[c]]

Click here to select the rest of the first row.

Select the cells below.

Right click, select "Paste special", and "Conditional formatting only"

Now each row with different Grouper_IDs are separated by shading. The Grouper_IDs in column H should occasionally be red too. We'll talk about why in a minute.

Update unique_locality

This tool finds “surplus” words that don’t occur in every normalized locality in a single Grouper_ID group. This is a really handy way of seeing differences between localities. You can (and should!) rerun this as you change Grouper_id numbers around.

Click "BELSFish"

Click "Remove Common Words"

Just a cleanup step here to make sure the filter covers the entire data range.

Click "Remove filter", then "Create a filter" to extend the filter across the new data

Validate groups

This is the time intensive part! Here we’re reviewing the groups to make sure the Grouper_ID values actually reflect real groups. The easiest way to validate groups is changing the Grouper_id values on the Key tab. I don't really have steps here - I go through the spreadsheet top to bottom. I do, however, have some tips:

You will almost certainly see some rows with red Grouper_id values. Grouper tries to move singleton groups right after larger groups that are similar. These singletons are similar enough to warrant a review, but not similar enough we'd be confident automatically grouping them together. The intention here is to catch the occasional stray locality that the process missed. The yield on this is low - maybe 20-40%. Don't just rubber stamp these!

It's really important to review all these out-of-order singletons before re-sorting the key. The order is the only thing keeping these singletons near a similar group for review. Changing that order before you review the singletons removes the chance for you to roll them into larger groups.

Grouper's matching only looks at normalized_locality. If you're getting a weird combination, look there first. I've included both the original and normalized locality in the key columns. When Grouper substitutes a word in, it adds a \* prefix in the normalized_locality field.

In addition to unique_locality, this sheet has a couple additonal columns that can help you: Confidence: This is a row-wise calculation of similarity to every other row with the same Grouper_id. Similar localities will have close or identical confidences. A suspiciously low confidence is worth a closer look. Distance_direction: the algorithm Grouper uses is insensitive to word order, so Grouper also tries to extract and compare distances, directions, and units. The script will split apart groups where these aren't identical (that's why some groups have .1, .2, etc suffixes.).

Finalize Grouper_id back to original data

The key we just finished is an index linking BELS groups to larger Grouper groups. Our next step is to propagate Grouper groups ("Grouper_id") to every record using BELS numbers.

Some herbaria don't use catalog numbers. The app script will create a temporary placeholder - which in this case is "id\_" with the symbiota occurence id.

Click "Original"

Click "BELSFish"

Click "Populate Grouper_id from Key"

Now we need to sort the Original tab. The "Review" column relies on Grouper_ID being contiguous. Now, we're going to sort by Grouper_id and isInstitution.

This is a little in the weeds, but if you're troubleshooting you should know how this works: The first row of a Grouper_ID with no coordinates gets set to "NONE", with other records set to "Skip-none." This "NONE" determines which record gets georeferenced. If Grouper_IDs are discontinuous the same Grouper ID will get multiple "NONE" rows and would get georeferenced multiple times. It's slightly less important, but we also want to make sure we're georeferencing our stuff rather than whatever random institution also shares the Grouper_ID, so it's better to have our institutions come first inside a Grouper_ID.

Click here.

Click "Data"

Click "Advanced range sorting options"

Click "Data has header row"

Click "catalogNumber"

Click "Grouper_ID"

Click "Add another sort column"

This one is important. We're sorting isInstitution in Z-A order because we want TRUE (signifying "this is one of ours") to come before FALSE ("not one of ours").

Click here.

Click "Sort"

BELSFish's logic broadly categorizes groups by coordinates: - NONES have no coordinates in the group. - ONES have one coordinate in the group. - TONS have multiple unique coordinates in the group. ONES and TONS need to be manually reviewed, and NONES get sent to CoGe.

Working with ONES & TONS

The "MAP" link in the wheresWalter column pulls data from the spreadsheet: - Latitude & Longitude - Uncertainty - Date - Locality It's intended to be a quick way to map and validate pre-existing coordinates.

Export NONES to CoGe

Everything is sorted the way we want! Here we're just exporting the dataset of NONES out to CoGe. The "To CoGe" tab automatically does some wizardry to look for "NONE" in the "REVIEW" column and values 1 or more in the "InstitutionCount" column. Put in English, it's the first row of Grouper_IDs that don't have any coordinates and have at least one record in our list of institutions.

Click "To CoGe"

Click "File"

Click "Comma Separated Values (.csv)"

Upload the CSV to CoGe

Do CoGe Stuff

Download Data from CoGe

At this point, the two most time consuming steps are done. We've grouped and validated our localities, and we've georeferenced a single representative of each group. Now we need to re-integrate those coordinates back to every record. Home stretch!

Click this button field.

Click the "CSV" field.

Click the "include complex shapes" field. This adds nothing we're going to use, and considerably inflates the file size.

Click "Download"

CoGe seems to love exporting files without an extension. You will likely have to find the downloaded file and add a CSV extension.

Import data from CoGe back to BELSFish spreadsheet

Our representative localities are now all bundled up in a single file. Now we're going to put them back into the spreadsheet, and copy those values across our grouped localities.

Just like the other times we've imported data from an external source, make sure to click the A1 cell. This makes sure everything goes in the right place.

On the "From CoGe" tab, click the A1 cell.

Click "File"

Click "Import"

Click "Create new spreadsheet"

Click "Replace data at selected cell"

Click "Import data"

Review CoGe duplicates

CoGe creates duplicate records that will mess things up. These usually are because CoGe keeps track of revisions, but it also can be CoGe being its lovable, quirky self. We could filter these out automatically, but last is not always best. Whatever the reason, we have to have only one set of geocoordinates.

Click "BELSFish"

Click the somewhat inaccurately-named "Highlight/Count Duplicates"

The script will highlight duplicates based on Verification type and date. - Skipped (red), then later Corrected (green) - Corrected (red), then later Corrected again (green) - Corrected (orange), then Skipped (orange) - Anything else (orange)

But wait, there's more! Scroll over to the right, and...

In addition to highlighting duplicates, we've actually done a few things in this step: - We've replaced "N\\A" values with empty cells. - Added a "Grouper_ID" for every record. We're going to need this in a second. - Added a count of the number of times each catalogNumber shows up on this sheet.

Click "Create a filter"

Click here.

Click "1." This'll hide the CoGe records that only appear once.

Click "OK"

Now you've hidden all the records that don't need attention. Now you can make an informed decision on which records to keep.

If you're feeling brave, however, you can also only show records in red. Click on any colored column.

Click on Filter by colors, and then click "light red 3"

You probably shouldn't do this, but you can clear these lines all at once if you're very confident you want the last record. I recommend just clearing the values with delete, rather than removing the row. It doesn't change the logic and it's a lot faster.

Propagate Coordinates across Nones

It's coming together! We've grouped our records and we've georeferenced a representative from each group. Now it's time to put them all together!

Click "BELSFish"

Click "Populate Data from CoGe"

And that's populated all the "NONE" and "Skip-none" rows with our filter formulas that look things up off the "From CoGe" tab.

Export and split the location data

So we now have a big spreadsheet containing all our records and coordinates. We need to divide them up by institution and collection - if we tried to import this, we'd create new records from other institutions. It's entirely possible to filter the records down to a single collection and export here. It's just as easy, however, to export a single file and chop them up with a python script - that way we get every institution at once.

Click "File"

Click "Comma Separated Values (.csv)"

We're going back to python for the next step. If it's been a hot minute, see [step 2 on how to navigate to a command line in the Grouper folder.](https://scribehow.com/viewer/BELSFish_Workflow__FVs4RRiqS7SS10UjxJp7UQ?scrollToActionId=b40901ea)

In the grouper folder command window, type "python SplitCSVbyInstitution.py"

See how the the "institution count == 0: " count is different than the "total data rows" count? [Earlier we only exported NONES with an institutuioncount of 1 or more. ](https://scribehow.com/viewer/BELSFish_Workflow__FVs4RRiqS7SS10UjxJp7UQ?scrollToActionId=5381d39d) Put another way, we wanted to prioritize our stuff. We never removed those records though, so the total data rows includes these guys. This script filters out records with institution count equal to zero, because we skipped georeferencing these.

This is the end of BELSFish! We've now chopped up the final export by institution, so we can import the records we control, and pass on a clean CSV of the records we don't control.

Thanks for making it this far! I sincerely hope this helps you in your efforts.

BELSFish Workflow

Craig Meyer
85 steps
7 minutes

About Page

Google Account

Google Docs

Google Sheets

Geo-Locate

All the scripts are available here: https://github.com/BRITorg/Grouper

Download the data

It's probably going to be best to decrease the size of your dataset as far as you can. We'll get into the math in a second, but proabably want to keep this to county-level.

Run pyBELS

Run Grouper

We're actually going to do two things here! We'll trim the large pyBELS files with BELSFillet.py, and then regroup the BELS groups with Grouper.py.

Navigate to the folder containing the BELSFish scripts. Type "cmd" in the navigation bar to open a command window.

In the command window, type "python BelsFillet.py" and navigate to the pyBels output folder. Note that the script will create a copy of each file with a -trimmed.tsv suffix.

Type "python Grouper.py" and enter the location of the -trimmed.csv you want to group.

Make a copy of the BELSFish template

All we're doing here is making a copy of the BELSFish spreadsheet and using it as a template. This is the easiest way to keep formatting and the script together.

Navigate to <https://docs.google.com/spreadsheets/d/1ziYUi7unDyAI6U0xyuL-UQGF-gYTCYPWbid6JPxEFuk/edit?usp=sharing>

Click "File"

Click "Make a copy"

Click this text field, and rename the template to your project.

Click "Make a copy"

Import the -trimmed file to the "Original" tab.

Click cell A1.

Click "File"

Click "Import"

Click "Upload" and select your "-trimmed.csv" file.

Click here.

Click "Replace data at selected cell"

The "Convert text to numbers, dates and formulas" option is pretty important here, since we're importing text we want the spreadsheet to interpret as a formula. Good thing it's on by default!

Click "Import data"

Import the -key file to the "Key" tab

Remember how we needed to select a specific cell before import earlier? Same thing here - select the red B2 cell before we import data to make sure data goes to the right place.

The A column contains a serial number for the cell shading. The Key's "Serial" A column is hidden for simplicity.

Click "File"

Click "Import"

Click here.

Click "Replace data at selected cell"

Click "Import data"

Update cell shading

We'll be spending a lot of time reviewing the groups here, and shading by Grouper_id can make that much easier. This is a little cumbersome to set up, but worth it.

Hey remember how I hid the A column on this tab earlier? I'm unhiding it now to show you what it's doing.

Click "BELSFish"

Click "Update Cell Shading"

Psych! The script won't work until it's authorized to run.

Click "OK"

Click on your Google account

Click this checkbox.

Click here.

Authorizing the script interrupted that function, so we gotta do it again.

Click "BELSFish"

Click "Update Cell Shading"

Select the cells on the second row.

This isn't a step. It's just reference for what we're copying from the first row through the rest of the key.

Copy the cells with [[ctrl]] + [[c]]

Click here to select the rest of the first row.

Select the cells below.

Right click, select "Paste special", and "Conditional formatting only"

Now each row with different Grouper_IDs are separated by shading. The Grouper_IDs in column H should occasionally be red too. We'll talk about why in a minute.

Update unique_locality

Click "BELSFish"

Click "Remove Common Words"

Just a cleanup step here to make sure the filter covers the entire data range.

Click "Remove filter", then "Create a filter" to extend the filter across the new data

Validate groups

Finalize Grouper_id back to original data

The key we just finished is an index linking BELS groups to larger Grouper groups. Our next step is to propagate Grouper groups ("Grouper_id") to every record using BELS numbers.

Some herbaria don't use catalog numbers. The app script will create a temporary placeholder - which in this case is "id\_" with the symbiota occurence id.

Click "Original"

Click "BELSFish"

Click "Populate Grouper_id from Key"

Now we need to sort the Original tab. The "Review" column relies on Grouper_ID being contiguous. Now, we're going to sort by Grouper_id and isInstitution.

Click here.

Click "Data"

Click "Advanced range sorting options"

Click "Data has header row"

Click "catalogNumber"

Click "Grouper_ID"

Click "Add another sort column"

This one is important. We're sorting isInstitution in Z-A order because we want TRUE (signifying "this is one of ours") to come before FALSE ("not one of ours").

Click here.

Click "Sort"

Working with ONES & TONS

Export NONES to CoGe

Click "To CoGe"

Click "File"

Click "Comma Separated Values (.csv)"

Upload the CSV to CoGe

Do CoGe Stuff

Download Data from CoGe

Click this button field.

Click the "CSV" field.

Click the "include complex shapes" field. This adds nothing we're going to use, and considerably inflates the file size.

Click "Download"

CoGe seems to love exporting files without an extension. You will likely have to find the downloaded file and add a CSV extension.

Import data from CoGe back to BELSFish spreadsheet

Our representative localities are now all bundled up in a single file. Now we're going to put them back into the spreadsheet, and copy those values across our grouped localities.

Just like the other times we've imported data from an external source, make sure to click the A1 cell. This makes sure everything goes in the right place.

On the "From CoGe" tab, click the A1 cell.

Click "File"

Click "Import"

Click "Create new spreadsheet"

Click "Replace data at selected cell"

Click "Import data"

Review CoGe duplicates

Click "BELSFish"

Click the somewhat inaccurately-named "Highlight/Count Duplicates"

But wait, there's more! Scroll over to the right, and...

Click "Create a filter"

Click here.

Click "1." This'll hide the CoGe records that only appear once.

Click "OK"

Now you've hidden all the records that don't need attention. Now you can make an informed decision on which records to keep.

If you're feeling brave, however, you can also only show records in red. Click on any colored column.

Click on Filter by colors, and then click "light red 3"

Propagate Coordinates across Nones

It's coming together! We've grouped our records and we've georeferenced a representative from each group. Now it's time to put them all together!

Click "BELSFish"

Click "Populate Data from CoGe"

And that's populated all the "NONE" and "Skip-none" rows with our filter formulas that look things up off the "From CoGe" tab.

Export and split the location data

Click "File"

Click "Comma Separated Values (.csv)"

In the grouper folder command window, type "python SplitCSVbyInstitution.py"

This is the end of BELSFish! We've now chopped up the final export by institution, so we can import the records we control, and pass on a clean CSV of the records we don't control.

Thanks for making it this far! I sincerely hope this helps you in your efforts.