Using a spreadsheet to clean up a dataset

Software tools used in the recipe

The spreadsheet software we have used throughout this recipe is part of Libre Office 3, which is free, open source software that works on Windows, Mac and Linux operating systems. Libre Office 3 can be downloaded and installed from the Document Foundation website. For help getting set up have a read of the free installation guides (they’re quite good).

Most of this recipe can be also be used in recent versions of Microsoft Excel and Google Spreadsheet. The screenshots will look different and the steps may not be quite the same though. To help complete some tasks, we will also occasionally dip into a word processor and text editor like Notepad. These will probably already be installed on your computer.

The ‘dirty’ data – what we’re starting with

We will be working with a spreadsheet of data about sales of vast amounts of agricultural land in less developed countries. The data was researched by a non-governmental organisation called GRAIN, which works to support small farmers and social movements in their struggles for community-controlled and biodiversity-based food systems.

We have placed a spreadsheet with the original dataset on the Data Hub, an online library for data. To follow this recipe, you will need to download a copy of this onto your computer.

The ‘clean’ data – the end result

By the end of this recipe, the data will be substantially cleaned up. We’ve put a copy of the original data along with the final, cleaned GRAIN data online too.

Use the cell formatting features

To display the data in a plain and compact way, removing any choices that the original publisher of the spreadsheet made, you can do the following:

1. Select the complete dataset (Linux/Windows shortcut: Ctrl+A).

2. With the data now selected, right click on top of any row label (eg. 1, 2, 3 etc) to bring up the secondary menu. Choose “Show”. This will make sure any rows that are hidden are revealed. Repeat the same steps for the columns.

3. With all the data selected, right click anywhere to show the secondary menu. Select “Clear Direct Formatting” to remove cell colouring and make the text size uniform.

4. Right click again, and go to “Format Cells”. Select the ‘Alignment’ tab and choose the following options to place the text sensibly in the cells:

   

5. With all the data still selected, right click on any row label again and choose “Optimal Row Height”, and select ‘Okay’. This will resize the rows to remove extra vertical space, which results in more data being displayed in-screen.

6. To resize the columns, place the mouse cursor in the line between columns. Left click and hold, and drag the line until you’re happy with the column width.

Use the Find and Replace feature

Both whitespace and new lines can be “seen” by the spreadsheet.

1. Open the find/replace tool (Shortcut: Ctrl-H).

2. Select “More Options” and check “Regular expressions”. This feature enables the spreadsheet to search for patterns, and not just specific characters.

3. In the input area for Find type [:space:]$ and click “Find”. This is a regular expression that searches only for spaces that are at the end of the text in a cell (which is what the $ denotes). It should look like this:

   

4. Running this search will show you the cells in this worksheet that have one or more trailing whitespaces.

5. To remove the trailing whitespace that have been found, click “Find All”. Make sure the input area for ‘Replace with’ is empty. Then click on “Replace All”. Perform this operation until the spreadsheet tells you, “The search key was not found”.

6. To remove the new lines, repeat steps (a) to (e) with n in the Findbox. Remember to keep regular expressions enabled, or this won’t work.

7. Run the auto-filter again to see how the data has changed.

Use the COUNTBLANK function

This will enable you to show the number of blank cells, which helps you figure out the size of the potential problem:

1. Scroll to the end of the dataset. In a row beneath the data, enter the following formula: =COUNTBLANK(A1:A417) and press enter. This will count the number of blank cells in column A so far as row 417, the last entry in the GRAIN dataset.
2. In the same row copy the formula you just created across rows B to I. This will show you a count of the blank entries in the other columns.
3. You can see that by far the most blank cells are in column G, ‘Projected Investment’.

Use the ISBLANK function with the Conditional Formatting feature

Blank cells can also be highlighted using conditional formatting and the ISBLANK function, changing the background colour of blank cells, so you can see where they are:

1. Select the dataset (cells A1 to I417), and open the ‘Conditional Formatting’ menu (Format → Conditional Formatting → Conditional Formatting). This spreadsheet feature allows you to automatically change the formatting (eg. font size, cell style, background colours etc) depending on the criteria you specify.

2. In the conditional formatting window, make the conditions look like the image below. To make the blank cells stand out more clearly, use an existing style or set up a new one by clicking the ‘New Style’ option, which takes you to a window where you can choose font type, size, background colour and so on.

   

3. Check what has happened. When the conditional formatting is applied the blank cells will be highlighted in red. Here’s how it looks when zoomed out a bit (View → Zoom, then enter 75% into the ‘Variable’ option):

   

4. To remove the conditional formatting: repeat steps (a) to (b) above, but define a clear style (or use the ‘default’) instead. Otherwise select the cell or range of cells and select “Clear Direct Formatting”.

Fill in empty values with the Find and Replace feature

In the GRAIN dataset we do not know whether blank cells signify data that has been deliberately or accidentally left out. You may want to actively specify that the data is missing, rather than leaving a blank cell.

1. Select the complete data range (A1 to I417).
2. Open the find/replace dialogue (Shortcut: Ctrl-H). If you have already used this earlier, you will need to disable searching with regular expressions, because this causes the search to work differently. Do this by clicking ‘More Options’ and uncheck regular expressions.
3. Leave the “Search for” input box empty. Type “Missing” into the replace box (without quote marks). Click on ‘replace all’.
4. Every blank cell will now have the value “Missing” recorded in it. You can verify this using the COUNTBLANK function we outlined above.

Filling blank cells isn’t always useful and it’s important to choose the right term to denote a missing value. For example, in the context of ‘Proposed investment’ using the term ‘none’ to signify missing data is confusing. Readers may think this means you know there is no investment, rather than that there is no data.

Use a Pivot Table to find errors and Autofilter to help fix them

1. At this point your, dataset should have the original Projected Investment data in column G, and the cleaned data in column H, which we named Projected Investment (US$ MIllions).

2. Select column G. Go to Data → Pivot Table → Create New. In the window that appears, checked “Current Selection” and click on OK. A strange new window called Pivot Table will appear, which looks like the image below:

   

3. We can use this to find a list of the unique values in column G (Projected Investment), which will help us identify trouble-causing entries. Select the little grey rectangle near the top centre labelled “Projected…” and drag it to the white area called Row Fields. Select it again and drag it to Data fields too. You should see this:

   

   Notice that in the Data Fields, the little grey rectangle is now labelled “Sum – Projected Investments…”. We want to change this to “Count – Projected Investment … ”, so it shows us how many times each of the different values occurs in the dataset. To do this, double click on it. This window will appear:

   

   Select Count. Then click Okay in the pivot table window. A new worksheet will appear, containing a list of unique values from column G, along with the number of times each occurs.

4. This view of the data enables us to quickly scan down the list and see the problems. The count lets us know how much work it is likely to be to fix them. So, with a quick bit of analysis of this pivot table, we can see that of a total 416 rows of data in the GRAIN dataset only 106 values (that is 416 minus the 310 where data are not present) are recorded in the column for Projected investment. Of these 106, only 14 are NOT uniform like “US$34 million” or “US$1,876 million”. Here are the offending entries, which we’ve pulled out into a table:

1. We can fix these in about 5 minutes simply by identifying them in the original data, and changing them by hand so our formula can then process it. Head back to the worksheet you have your clean data in. A quick way to highlight these entries is to use the autofilter selection list that we used above.

   

2. Go through the list and make sure that only the exceptions we have identified are selected. Then click OK. This will change what the spreadsheet shows: only those rows that have in column G the data you’ve selected will be shown. You can now go through them one-by-one, changing the data in column G so the formula we made can work on it. You will see the results in Column H, which will update automatically. For example:

   US$30-35 mil → hand correct into an average: US$32.5 million → Formula returns 32.5

   US$2 billion → hand correct in US$2,000 million → Formula returns 2,000

   …and so on.

   Tip: as you are updating original data, you may wish to keep a note of what you changed. Either create a column called “Notes”, and record the data there. Or, duplicate column G and name this new column “Projected Investment (un-altered)”. Or, where appropriate update the column called “Summary” with the data, which is the approach we have taken.

3. There are a few final steps to take to make the numbers in column H usable. Currently, our data in column G is a calculated value, not a number: in spreadsheet language, this means we can’t add them up yet! We need to replace the formula with its result. This is easily done with the Paste Special feature we noted above.

   • Select the whole of column H (or just H1 to H417 if you prefer).

   • Copy it (Shortcut: Ctrl-C). Put the cursor at the top of column H, selecting Cell H1. Go to Edit → Paste Special. A window will appear, like this:

     

   • We can choose what aspects of the selected data are pasted by checking and unchecking them. Make it so it looks like this, then select OK:

     

   • Edit one of the cells in column H. You should see that the formula is gone, and there is only a number. Sometimes, after a Paste Special operation, when you edit a cell you may also see an apostrophe has been inserted into the number. This is an infamous bug. You can remove the apostrophes by selecting the column, going to Data → Text to Columns. Just select OK, and the problem will be fixed.

   • Finally, format your column of numbers correctly. Select the column (or range H1:H417), right click on the selected area to bring up the secondary menu. Choose “Format Cells”. In Numbers, select the category ‘Numbers’, and choose -1234, and then change the value for Decimal places to read “1”. Then click OK.

     

   • Now your numbers are ready to use.

Use standard filters for a more flexible search

1. Go to Data → Filter → Standard Filter. This opens up a window like the one below. It has a lot more options. Let’s look for deals involving Alfalfa again. Make your standard filter look like this, and select OK:

   

2. So, this searches down Column F, where our data about production is stored, for cells where the word “alfalfa” is written anywhere. It doesn’t matter whether other words are there. The spreadsheet will again be filtered to show four rows.

3. We can build up more complicated queries. For example, try this one, which will filter the data for deals where the land use was thought to be for rice AND bananas, amongst others:

   

4. This returns only two results.

5. To remove the filter, go to Data → Filter → Remove.

Why this is only a part solution

Again, it’s sort-of-useful but quite limited for the same reasons as autofilter: mis-spellings, alternative wording, not having a complete list to choose from. At root this is a very difficult problem to solve with a spreadsheet: data on Production is what we call a repeatable field, in that buyers of land can have many equally important uses for the land. This is a different dimension of data: it’s called a one-to-many relationship. There is no easy way to structure data for a spreadsheet to make this data easy to use with any accuracy.

A common mis-step at this point is to start adding columns to allow multiple data to be recorded. This isn’t any better than recording it all in one cell, because of the way spreadsheet filters work. For example:

In the example above, all three rows have entries for “Rice”. A spreadsheet filter looks only up and down a column, not left and right to other columns. So to build a filter that accurately returned all three rows, you would need to search all three columns for the term “Rice”. This quickly becomes impractical as you begin wanting to find rows with different combinations of production types.

At this point, altering the structure of the GRAIN data for use in a spreadsheet probably isn’t worth it as there would be little gain. However, in this dataset, around 30% of entries in this column have more than a single entry, and there are nearly a 100 types of production. Having the data is important, as it may allow us to ask and answers questions we wouldn’t otherwise be able to. For example, are there certain land uses that go together? Is there a relationship between the land use, the size of the land transacted, or the amount of investment. Exploiting the data effectively requires a database, not a spreadsheet.

However, there are things we can do to improve our ability to analyse the data, which we will go into in the next section.

Get all the categories that appear in the data

As we discussed above in the section on structural problems, different pieces of data about production are held in a single cell. Each item is separated by commas. We can use a feature called “Text to Columns” to separate out each of the items into individual cells.

1. Create a new empty worksheet and name it “Text-to-columns” (or something memorable so you know what’s in it). We will use this worksheet as a ‘scratch pad’ to work on this particular problem.

2. In the GRAIN dataset that you have been working on, select Column F containing the data about production. Copy it (Shortcut: Ctrl-C) and then go to your new “Text-to-Columns” worksheet and paste it into column A. (You can use Paste Special if you like, to remove all the formatting). Rename the column as “Production (original)”

3. If you haven’t already, then you will now have to remove non-printing characters from this data. You can use the combination of CLEAN and TRIM and then Paste-Special that we described above. So, in cell B1 enter =CLEAN(TRIM(A1)). This will print out a version of the data in cell A1 that has no non-printing characters. Copy the formula down the column B to create a completely clean version of column A. Then select column B, copy it, and use Edit → Paste Special, unchecking “formulas”.

4. Now you have clean, usable data in column B. Rename it “Production (cleaned)”.

5. Now the cool bit: Select column B that has your cleaned data, and select Data → Text to Columns. A new window like this will appear:

   

   In “Separator options” area uncheck “tab” and check “comma”, as the items in our cells are separated by commas, and not tabs. In the “Fields” area you can see that for each comma the spreadsheet finds, it will move the data after that comma into a new column:

   

   Select Okay, and watch what happens. Here’s how your data now looks:

   

Do a few quick checks to see that nothing strange has happened. For example, check that the number of rows remains the same: there are 417 rows in the original data – this should not change. Also run your eye across a few of the rows to see things match.

Make one big list from the almighty mess

Why? This helps us find the unique items. There are over 700 individual items and many duplicates (to count them put =COUNTA(B2:P418) into a cell somewhere) and we can’t drill them down easily as they are currently structured. It’s easier if they are in a single column. To do this, we can use a little hack that occurs when you change file formats:

1. Save your spreadsheet. Now delete column A and save it again with a new filename. Now save it again (yawn!), this time as a Text CSV.

2. Start up your favourite text editor (like Notepad on Windows, Gedit on Linux, or TextEdit on Mac) and open the Text CSV you just created. It’ll look something like this when it’s opened:

   

3. Now copy and paste this text into a new document in Libre Office Writer. We will use Writer to process this data as text, before we return it to a spreadsheet for analysis. So, in Writer we will use the Find and Replace tool – it’s the same as in the spreadsheet – to create a single list:

4. Replace all the commas with new lines: open the Find and Replace feature. Make sure “Regular Expressions” are enabled (look in “More Options”). In Find enter a single comma. In Replace enter n. This will replace every comma with a new line. There will now be lots of blank lines – don’t fret, this doesn’t matter.

5. Select all the text (Ctrl-A) and copy it into the clipboard (Ctrl-C). Go back to your spreadsheet in Libre Office Calc. In a new worksheet select cell A1 and paste the text (Ctrl-V). The data will appear, but with lots of blank rows.

6. Select column A and go to Data → Pivot Table. Choose the options like this the below image.

   

7. In a new worksheet a list of unique terms used to describe Production, arranged alphabetically, along with a count of how many times they occur, will appear:

   

Remove duplicates and correct categories that are nearly the same

You can see immediately from the data above that there are many problems:

• “cofee” is mis-spelt, so is “forstry” (which should be ‘forestry’, which could be the same as ‘forest’)
• there are entries for “fruit” and “fruits”: which one is best?

This work is called ‘reconciliation’ and is a process designed to bring clarity to data. It involves looking through a list of terms and:

• identifying terms that mean the same thing and creating a new list
• applying the new list to the dataset.

We’ll go through these one by one.

Bring the data into a form in which it’s easy to do this task

• Copy the list of items from the Pivot Table you made above and add it into a new worksheet. Use CLEAN and TRIM on it, and then sort it alphabetically in ascending order.
• Insert a row at the top. Label column A “Category”, Column B “Issue”, Column C “New Term”.

Identifying terms that mean the same thing

1. Go down the list and look for terms that mean the same thing. Here are some things that should alert your suspicion about a term:

• Spelling mistakes e.g. “bananas” vs “banabas”
• Differences in case: “fruit” vs “Fruit”. Choose your case and stick to it.
• Multiples: “fruits” vs “fruit”. Choose which one you want to use. Adjectives: “sweet sorghum”, “winter barley”. If there’s a similar category, like just “barley”, it may make sense to remove this more specific category.
• Additional terms: “and” in the text eg. “Dairy and Grain farms”; “Citrus and Olives”; “Crops (sorghum)”. The rule is to have only one category in each cell. So delete one of the terms and add it to the list on its own if it doesn’t exist.
• Qualifying terms eg “beef cattle” vs “beef”. “Crops” vs “food crops”. Choose which one.

1. In column B record what you think is the problem e.g. Near match, none, Spelling error. This will just help you keep track of the changes you make.
2. When you’ve gone down the list and identified the problems, then make the changes in column C. Here’s what we did (we also ringed the suspected duplicates as we went along):

1. Once you’ve done this, run Data → Pivot Table on your list of new terms (in Column C). You’ll see a huge difference.
2. By removing duplicates, spelling and grammar differences and so on we have cut down the categories from 149 to 88, which is still quite an extensive list! Anyhow, we have a more useful controlled vocabulary. The next step is to apply this to the data, so it can help us with our analysis.

Edit the data to fit the more accurate list of categories

So in order to create a more accurate analysis of the GRAIN dataset we have narrowed down the categories used to describe the sorts of land use to which transacted land is put. The last step is to bring the data into compliance with these new terms. We can do this using a combination of three useful features of the spreadsheet:

• Conditional formatting: we mentioned this `above`_. It changes the formatting of a cell based on a rule that you give it eg. turn any cell in a given range red if it has the word “Sheep” in it. We can use this to highlight production categories that are not in our new improved list of categories.

• Data validation: this enables you to restrict what data is entered into a cell. So you specify a list of allowed values, and rather than type what you like into a cell you choose from a list. We can use this to make the changes to the data to bring it in line with the new categories, whilst reducing the risk of introducing more errors.
• Concatenation: this merges the contents of cells together. We will use it to put the improved data about production back together

The two things you will need are:

• the spreadsheet in which you used Text to Columns to separate out the terms used to describe land use
• your new, cleaned up list of terms taken from the previous pivot table.

Step 1: Find the items that need correcting

We can use the spreadsheet to find the items that need correcting by comparing the data we have to the new list of categories. Here’s how:

1. In the worksheet where you have the data about production, make sure you have cleaned the data (using CLEAN and TRIM and paste special to remove the formulas).

2. Have the split items in a worksheet called “Split”. Place your new list of categories in a worksheet called “Categories”. Select your list of categories. Go to Data → Define Range. This window will appear, which will enable us to make the list of categories into a “range” against which we can make comparisons:

   

3. You’ve already selected the list of categories, which you can see displayed in the Range area. Type ProductionCategories into the Name area and then select Add. We can now use this range.

4. Select all the data (Shortcut: Ctrl-A) in the worksheet called “Split”, where the data about production use is split across different columns. Go to Format → Conditional Formatting and make it look like the image below.

   

The formula to use is COUNTIF(ProductionCategories,A1)=0. Also, select “New Style”. A new window called “Cell Style” will pop up. In the “Font Effects” tab choose a colour and then select okay. We chose red, which is shown below:

Select OK. This highlights in red the text in cells that are not found in the data range we have called “ProductionCategories”. The effect this has is to highlight the entries that we have to now correct. You spreadsheet will look like this:

Step 2: Correct these entries

Now we know where to look, we can make corrections to the data. The way to do this is to introduce data validation to the spreadsheet. This restricts the data that can be entered into a cell.

1. Select the complete dataset in the worksheet called ‘Split”, where you’ve just highlighted the values that don’t appear on the improved list of categories. Go to Data → Validity.

2. In the window that opens, make the fields look like those in the image below:

   

   What this does is tell the spreadsheet that the only values that it should allow you to enter come from the list to be found in Cells A2:A88 of the worksheet called “Categories”. In others words: your list of categories. We also need to decide what to do when a value that isn’t on the list is entered. Select “Error Alert” and make it look like this to stop any non-list values being entered:

   

   Click OK, and go to a cell with red text in it and click on it. You’ll see that a little drop down selector on the right hand side of the cell.

   

   Click on it to display the list of ‘approved’ terms:

   

You can now go through the data, correcting it to remove the errors and make it more useful for analysis. There are a few things to watch out for:

• As you go through, increasingly you can use keyboard shortcuts and auto-complete and rely on the validation to tell you when you’ve typed a wrong entry.
• When you have changed a value, notice that the text changes to colour to show that it is now a recognised term. When there’s no more red, you’re done.
• With values like “Soyabean and other crops”, you should change it to “Soybean” and then add a new entry for “Crops”. Don’t forget!

Step 3: put it all back together again

We will take the improved data about land use and re-incorporate it into the full dataset.

• In you first worksheet, where you have been progressively cleaning the data, insert 15 columns to the right of Column F. Take the data from the worksheet that you validated the production category data in and copy it across into the new columns. If you didn’t put column heading, remember to paste starting in Row 2. You’ll be able to see the original data in Column F, and the separated about and cleaned data in Columns G to S.
• What we need to do now is put the clean data back into a single cell. We can do this with the CONCATENATE function. This allows you to take data from different cells and blend it into one. For example:

• We can blend data from other cells (which we called ‘referencing’), with other text to make it more readable. The examples above show how this works.
• Now let’s apply it to the data in our spreadsheet. In cell T2 insert the following formula:

• It looks messy, but follow the logic. It looks across row 2 in columns G to S for data, and then prints it with a comma in between. We don’t know which cells have data in them precisely, so there is a list of trailing commas which print where a cell is empty. We can get rid of these easily with the LEFT and SEARCH formulas we used `above`_. In cell U2:

• So, with these two formulas in place, they can be copied down the spreadsheet to complete the operation. The only row that this doesn’t work for is row 94, Temasek’s purchase of land in China. You can correct it by hand :)
• The final step is to make the new data usable. If you’re confident the work is done, insert column to the left of the original column F called Production. Call this new Column G “Production (cleaned)”. Select all the data in Column T (rows 2 to 417), move the cursor and use Paste Special to insert it into Column G.
• You can now either delete or hide columns H to W in which you’ve been working.

Step 4: using the cleaned data about types of production land use

You can repeat the steps that we outlined in Problem 4 above, using the standard filters to build queries on the cleaned data in column G. This time you will not have problems with inconsistencies, mis-spellings and so on.