db

When you’re working with a spreadsheet that requires more complex filtering/sorting (i.e. based on conditions that are satisified (or not) across multiple columns/rows) than what’s generally available in Microsoft Excel/Libre Office/Google Sheets, it turns out that SQLite3 (which comes installed with Ubuntu) is a pretty easy/quick way to approach complicated filtering. Additionally, this can all be done from the shell (command line).

The spreadsheet I’m working with in this example contains data for abalone samples that have been evaluated for infection levels of the withering syndrome bacterium in various tissues.

Before using SQLite:

Change the names of any columns that are duplicate names.

Export the spreadsheet as a comma-separated file (.csv)

The remaining steps are all performed using the shell. Additionally, none of the commands below actually alter your file in any way. So, you can play around all you want without worrying about modifying/destroying the source data set.