Who's reading the spreadsheet?

There are two classes of 'thing' that use spreadsheets:

1. People

2. Computers

Once there is data in a spreadsheet, there are some jobs that only the person can do (e.g. type in new data), and some that only the computer can do (e.g. apply a formula automatically). The things that both can do are the ones to pay attention to when designing data structures. That's where the person's care for the computer's limitations have the most impact.

Here are some examples of things both can do. The computer can autosum a string of numbers. The person, if they really want to, can add up the numbers on a piece of paper (or in their head if they're amazing). Similarly, the computer can sort by a designated column. The person, if they really want to, can do that too - it involves a lot of cutting and pasting, but it can be done.

Here's what the computer can't do:

Arrange things so they're easier for the person. 

Here's what the person can do:

Arrange things so they're easier for the computer. 

At the most fundamental level, the single most helpful thing you can do for the computer is:

Arrange your data so the computer can sort by any column.

And by implication:

Keep your data in the smallest nodes possible.

The computer isn't as smart as the person. It's not smart at all, it has no intelligence. It can only deal with thing it recognises, and that's not actually very much. Similarly, it cannot read for meaning. As far as it's concerned, it only deals with random strings of symbols. So, to make life easier for the computer, here are some tips:

1. Colours are meaningless to the computer, and you can't sort by them. Avoid colour whenever possible, or supplement it (even an extra column with the words 'red' and 'green' in it will improve things).

2. Seperate, seperate, seperate. Never have a column with 'John Smith' in it. 'John' and 'Smith are semantic chunks - one is the chunk 'first name' and the other is the chunk 'Last name'. So seperate them.

3. Use unique ids wherever you can. If all else fails and the data gets messy, you can always sort by this column to get back to the original shape.

4. Never, ever, ever write 'same as above'. Once you sort, that little piece of information becomes a) false and b) misleading. If it's the same, then copy and paste from that cell.

5. Don't merge cells, even if it looks nicer. It's a little bit nicer for people, infinitely worse for computers. Merged cells prevent sort from working.

6. The less formatting, the better. The simpler your spreadsheet is, the better the computer will be able to deal with it. 

4. Regular expressions: introduction

'Regular expressions' is a difficult name for a useful tool. Basically, they're very small computer programs. A single regular expression (or 'regex') is a little sequence of special characters that match a pattern in a file.

What we're dealing with here is 'find' and 'replace'.

Consider this little piece of text:

I have a cat because I like cats. I can never catch my cat.

If you wanted to change that to 'dog', in Excel, or Word, you could. You find 'cat' and replace it with 'dog'.

Find: cat

Replace: dog

Output: I have a dog because I like cats. I can never catch my dog.

Excel and Word will skip the word 'cats'. These programs know that you're unlikely to want to change every sequence of c-a-t, so they assume you mean "find 'cat', but only when it's definitely one word and not part of another word, like 'catch' or 'replicate' or 'subcategory'". Fair enough. Easy, too - after all, you can just do a second find-replace for 'cats'.

Textpad isn't that generous. It assumes that you mean exactly what you say, every time, with no exceptions.

In textpad, this would happen:

Find: cat

Replace: dog

Output: I have a dog because I like dogs. I can never dogch my dog.

The obvious response to that example is: That's really stupid.

Absolutely it's stupid. But here's the thing: Finding 'cat' and replacing it with 'dog' is not a regex. (As a general rule, the less text there is in a regex, the better). Regexes are not about text, they are about patterns, and the patterns are expressed in metacharacters.

A metacharacter is a single keyboard-stroke that refers to a type of character, not the character itself.

Here are some examples:

. means 'any character'

? means 'zero or one of the character before the ?

Don't bother trying to understand the next bit. The point of this example is not to show how it's done, only that it can be done. (The red is just to show which bits are doing the work).

Find: cat\(s?\)\>

Replace: dog\1

Output: I have a dog because I like dogs. I can never catch my dog.

And there we go. All the changes have been made accurately and cleanly in one move.

3. Tab separated values

Metadata is usually stored in Tab Seperated Value (tsv) format.

Don't be worried by that - you've already been using it for years. You may never have known it, but MS Excel is in tsv format.

All tsv means is that the fields of information are seperated by tabs. If you're in an excel spreadsheet, and you press 'tab', you move across by one cell - the software knows that 'tab' means 'the next field'.

There are other ways to seperate units of information: commas, semi-colons, colons, all sorts of things. The handy thing about tabs is that, unlike commas, semi-colons, and colons, they tend not to be used in natural language. You might have a book title like 'My life: the story of my life', but you'll never have 'My life (TAB) the story of my life'. That makes tsv very handy.

('Natural language' is any language that isn't a computer language. English, Spanish, Sign language... anything that people use to understand other people. Computers are terrible with natural language, which causes big headaches for the regex user. We'll cover that in more detail later.)

Look at this excel spreadsheet:

...and this textpad document:

The textpad document looks unintelligible, but it isn't. It is exactly the same document as the excel spreadsheet. In fact, all I did was copy & paste between the two - there are no changes at all. This means two things:

1. The tsv format is easy

2. If you ever get lost in textpad, you can pop your data back into excel to see what's going on.

(Sharp eyes will have spotted that this is a terrible, terrible piece of metadata. All I've done is pull some books off my bookshelf and pop them in a list. It's not standardised, the first name and surname are in the same column, the capitals are all over the shop, and my id column is based on the order I grabbed them. There's a reason for this - we're going to fix it.)

1. What Regular Expressions do

These tutorials are intended as a guide for people who wrangle metadata.

More specifically, they're aimed at people who wrangle tab-separated metadata and have noticed that excel has it's limits.

The name 'regular expressions' (or 'regex') isn't particularly intuitive, and at first sight, regular expressions themselves look a lot like goobledegook.

They do make sense though, and with a little practice, can be very, very powerful.