A Gentle Introduction to Data Cleaning

Expand all sectionsCollapse all sections

Introduction
In this section, learn about the “horror stories” of where data errors in spreadsheets have led to real consequences and how you can avoid them yourself.
1
- 1.1
  Course Outline
Section 1: Nuts and chewing gum
In this section, you’ll gain practical knowledge of common formatting and layout features of spreadsheets and some ideas about how to present your raw data to others so they will love you!
4
Section 2: The Invisible Man is in your spreadsheet, messing with your data
In this section, you’ll learn how even characters which you cannot see can cause havoc in data analysis. The section will teach you to ghostbust white space and other non-printable characters such as carriage returns, spaces and tabs. Once you have removed them, you can get on with your analysis in peace.
4
Section 3: Your data is a witch’s brew
Three is not a number. Nor is a million. At least not when they are typed in as text in a cell in a spreadsheet. Your spreadsheet is pedantic and needs everything to be precise and consistent. Read on to learn more.
4
Section 4: Did you bring the wrong suitcase (again)?
All our spreadsheet wants from us is each cell of data to be organised and neatly packed. In this section, you will analyse data to highlight problems in the way it has been structured so as to avoid them in your own work with data.
4

Some work

To do both the quick and longer tasks below you’ll need:

Download and open this sample spreadsheet on your computer. In column A is the data from above, with different sorts of non-printable characters. In columns B-E are four easy methods of removing non-printable characters from your data using: * the TRIM function (in column B) * the CLEAN function (in column C) * the TRIM and CLEAN functions …. together! (in column D) * the “Paste Special” feature (in column E)
Apply an “AutoFilter” to the data (Data → Filter → AutoFilter). Click on the little downwards-pointing arrows and a selection list will pop up displaying the number of distinct entries in each column. If you click on the autofilter for each of columns A through to E you can see the effect of the different methods of removing non-printable characters.
Double click on cells to view the formulas see how the functions work in practice. There is more information on each of them in the ‘Study’ section below.
Now try it yourself. Create or find a single column of data on your own. Apply these four methods in the same format as our sample the your spreadsheet.

Use the discussion area below to share your work and any observations about this task.

From the Data Wrangling Handbook Recipe on Data Cleaning, run through Problem 2: Whitespace and new lines – data that shouldn’t be there using the GRAIN dataset. This goes into more detail about the CLEAN and TRIM functions.

Introduction