Organizing spreadsheets
/A couple of weeks ago I alluded to ill-formed spreadsheets in my post Murphy's Law for Excel. Spreadsheets are clearly indispensable, and are definitely great for storing data and checking CSV files. But some spreadsheets need to die a horrible death. I'm talking about spreadsheets that look like this (click here for the entire sheet):
This spreadsheet has several problems. Among them:
- The position of a piece of data changes how I interpret it. E.g. a blank row means 'new sheet' or 'new well'.
- The cells contain a mixture of information (e.g. 'Site' and the actual data) and appear in varying units.
- Some information is encoded by styles (e.g. using red to denote a mineral species). If you store your sheet as a CSV (which you should), this information will be lost.
- Columns are hidden, there are footnotes, it's just a bit gross.
Using this spreadsheet to make plots, or reading it with software, with be a horrible experience. I will probably swear at my computer, suffer a repetitive strain injury, and go home early with a headache, cursing the muppet that made the spreadsheet in the first place. (Admittedly, I am the muppet that made this spreadsheet in this case, but I promise I did not invent these pathologies. I have seen them all.)
Let's make the world a better place
Consider making separate sheets for the following:
- Raw data. This is important. See below.
- Computed columns. There may be good reasons to keep these with the data.
- Charts.
- 'Tabulated' data, like my bad spreadsheet above, with tables meant for summarization or printing.
- Some metadata, either in the file properties or a separate sheet. Explain the purpose of the dataset, any major sources, important assumptions, and your contact details.
- A rich description of each column, with its caveats and assumptions.
The all-important data sheet has its own special requirements. Here's my guide for a pain-free experience:
- No computed fields or plots in the data sheet.
- No hidden columns.
- No semantic meaning in formatting (e.g. highlighting cells or bolding values).
- Headers in the first row, only data in all the other rows.
- The column headers should contain only a unique name and [units], e.g. Depth [m], Porosity [v/v].
- Only one type of data per column: text OR numbers, discrete categories OR continuous scalars.
- No units in numeric data cells, only quantities. Record depth as 500, not 500 m.
- Avoid keys or abbreviations: use Sandstone, Limestone, Shale, not Ss, Ls, Sh.
- Zero means zero, empty cell means no data.
- Only one unit per column. (You only use SI units right?)
- Attribution! Include a citation or citations for every record.
- If you have two distinct types or sources of data, e.g. grain size from sieve analysis and grain size from photomicrographs, then use two different columns.
- Personally, I like the data sheet to be the first sheet in the file, but maybe that's just me.
- Check that it turns into a valid CSV so you can use this awesome format.
After all that, here's what we have (click here for the entire sheet):
Maybe the 'clean' analysis-friendly sheet looks boring to you, but to me it looks awesome. Above all, it's easy to use for SCIENCE! And I won't have to go home with a headache.
The data in this post came from this Cretaceous shale dataset [XLS file] from the government of Manitoba. Their spreadsheet is pretty good and only breaks a couple of my golden rules. Here's my version with the broken and fixed spreadsheets shown here. Let me know if you spot something else that should be fixed!