Identify. Not too much. Mostly collections.

This week I’ve been using one of the tools I learned about at VALA Tech Camp to clean up membership data as part of a migration we’re doing at MPOW from the aging Amlib system to Koha ILS. Open Refine is a powerful data cleaning tool that can be thought of as Excel with regex. As we’ve been working on getting data migration right, we hit a bit of a snag. In Koha, each discrete part of a member’s address is in a different field:

• street number
• street address1
• street address2
• city (suburb)
• state
• postcode

etc...

This is quite typical of most databases storing address data. Amlib, however, is a fairly old system with some data architecture that is ...interesting. For whatever reason, the address data coming out of Amlib is all stored in a single field. Combine that with multiple possibilities for how addresses can appear, and literally twenty years of data entry into a single free text field, and I’m sure you can imagine how consistent the data is. Working out how to split it out in a sane way that minimises work later has been time consuming. Part of the problem is having to consider all the possibly ways an address may have been entered. Even when library staff have followed procedures correctly, the data is still inconsistent - for example some records list a phone number first, then the street address, whereas other include no phone number. Consider all of the following possibilities, which are all ‘correct’:

0401 234 567, 1 Main St, Booksville, 3020 0401234567, 1 Main St, Booksville, 3020 1 Main St, Booksville, 3020 90123456, 1 Main St, Booksville, 3020 9012 3456, 1 Main St, Booksville, 3020 9012-3456, 1 Main St, Booksville, 3020

There are thousands of examples of all these types of records within our 130,000 member records. Initially, it looked like these were the major differences. Urban and suburban Australia tends to have very few street numbers above 999, partially because streets often change their name when they hit a new suburb. I wrote a quick regex query in OpenRefine to find every record where the first four characters didn’t include a space, and created a new column with the part before the first comma for records matching that query. That was fine until I realised that “P.O. Box 123” would appear to be a phone number under this rule, so I adjusted to exclude anything with a space or a full stop. That was the easy bit. Addresses aren’t as simple as you might think:

1 Main St, Booksville, 3020 Unit 1, 10 Main St, Booksville, 3020 1/10 Main St, Booksville, 3020 F1/10 Main St, Booksville, 3020 F1 10 Main St, Booksville, 3020 Unit 1, The Mews, 1 Main St, Booksville, 3020 1 Main St, Booksville, Vic, 3020

Welcome to regex hell. After a bit of trial and error, I eventually split out the ‘number’ from each address. There are some edge cases where the address information somehow ended up with no commas at all or was incomplete, that we will need to clean up manually, but that’s probably about 4000 out of 130,000, which isn’t so bad. I’ll post something on GitHub at some point with some of the formulas I used to clean the data up for import - for when all you Amlib libraries move over to Koha amirite?

A need to know basis

Going through this process has helped me to keep top of mind something that all librarians, indeed everyone working with any database of personal information, needs to constantly question:

What data are we storing about people, and do we need to store it?

For example, public libraries generally record a member’s sex or gender (no distinction is generally made, though usually it’s labelled as ‘sex’ but actually means ‘gender’). Why? Do I need to know the gender of a member in order to provide information, advice or assistance? The only real argument I’ve heard about this is that it assist in finding members in the database when they do not have their membership card, but that seems to be a fairly weak argument for storing a sometimes intensely personal data point that isn’t always readily ascertained, and can change over time. Of course, most public libraries, in Australia at least, aren’t necessarily able to make decisions like this alone. National, state and local government standards about ‘minimum data sets’ define what must or at least should be collected, sometimes seemingly in contradiction of privacy standards. Once we ask this question of whether we need to store certain data at all, however, another one pops up, in some ways just as important.

“How are we storing this data about people?”