Seat reserved… for the spreadsheet. Photo by Zoonabar. CC-BY-SA 2.0
For non-techie researchers and investigators like me who work on human rights, spreadsheets are incredibly useful. However, it’s hard to imagine a tool as flexible that is at the same time so deeply frustrating. Spreadsheets can make simple things very difficult. For example, for many years this is what “cleaning data” has meant to me and many other people I work with:
Open file in spreadsheet. Open cell. Position cursor. Correct error. Close cell. Move down a row. Open cell. Position cursor. Correct error. Close cell. Move down a row… repeat to row 53,234 or until you fall asleep at the keyboard (whichever comes first).
To help speed these sorts of tasks up, we’ve written a new School of Data course called A gentle introduction to cleaning data in a spreadsheet. It contains loads of ways to make cleaning data a quicker and less painful experience.
In the course we start with a ‘dirty’ dataset containing lots of common errors. We walk you step-by-step through the process of making it to ‘clean’. We’ll show you how to use a range of common spreadsheet features to find and correct problems such as invisible or inconsistent data, missing values, a bad data structure and so on. By the end of the course, you should leave with a better view of what the spreadsheet can do, a practical process you can repeat on your own datasets and a good idea of how to better find help online about how to use spreadsheets.
The course dataset is interesting too. It’s about ‘land-grabbing’, or the commercial buy-up of agricultural land in the developing world by investment companies and governments to grow biofuel and other commodities, turfing people off land they need for their survival and (some analysts reckon) driving up food prices around the world. The data was produced by GRAIN, an excellent research organisation; I hope they accept our apologies for picking on their data in this course!
This is the first in a series of three ‘basics’ courses. They all use the same dataset about landgrabbing. The next in the series is a course called A gentle introduction to descriptive data analysis, which is about using a spreadsheet to get to grips with what’s in your data. Hot on its heels will be an introduction to visualising networks.
Finally, this course will also illustrate the spreadsheet’s limits. At some point, the time and effort you make pushing a spreadsheet to do something may be better spent looking at tools and techniques specifically designed to tackle the problem. In the case of cleaning data, this might be learning how to use Google Refine.
But until that time, all hail the spreadsheet, king of data cleaning.
Tim Harford, who is both a Times (of London) journalist and a BBC Radio 4 broadcaster, would share the intent to improve clarity, honesty and completeness of information used in public behind this blog note. He spends much time and effort on explaining to a broad public what the real significance is of many of the statistics that are quoted in aid of government policies or that are said to describe different aspects and changes in economic and social life.
What he has yet to explore are the fragilities in the original data sets that underpin the statistical summaries – and the summarised changes – that politicians and others use in aid of their current positions, actions and policies or in support of changes in those stances.
There could be an interesting link here. And if the less than perfect reliability and the absence of completeness of data sets became more familiar to decision makers of all kinds, perhaps some of them might at least delay making their decisions until they felt the ground on which to decide was a little firmer. Forgive my optimism: it reflects the way I was brought up to believe things can be made to be better.