Data cleaning

01The problem

Kolanut is moving to a new system, and IT has exported the customer list from the old one. Open it and something is off straight away: some names are in capitals, some have spaces before them, Lagos is written four different ways, and a few customers appear twice.

If you counted customers in this file, you'd get the wrong number. If you totalled sales by region, "Lagos" and "LAGOS" would appear as two different regions. Data cleaning is fixing problems like these before you analyse.

02The concept

Data cleaning means finding and fixing the problems in a dataset that would make your analysis wrong. It's not glamorous, and it's often most of the work. A beautiful chart built on dirty data is a confident wrong answer.

What "good data" means

Data quality has several sides. It helps to name them, because each is checked differently:

The common problems

Step 1: profile before you fix

Profiling means measuring the problems before changing anything. For Kolanut's raw customer export of 102 rows:

Profiling tells you how big each problem is, which ones matter most, and gives you numbers to check against afterwards. When you've finished, run the same checks again: duplicates 0, region spellings 6, blanks still 3 (unless you found the real values).

Standardising categories

The Region column is a classic consistency problem. Twenty-three spellings, six regions:

The reliable way to fix this is a mapping table: a two-column list of every raw spelling and the clean value it should become. Clean the raw value a little first (remove spaces, make it lower case), then look it up. Any value not in the table is flagged, so nothing slips through, and adding a new spelling is one new row.

Missing values

A blank is not the same as zero. A blank credit limit means "we don't know"; a zero would mean "this customer gets no credit". Treating one as the other changes your totals and averages.

Whatever you choose, write it down and report how many values it affected.

Outliers

An outlier is a value far from the rest. It can be:

• an error: a quantity of 3,000 typed instead of 30;
• real and important: a genuinely huge order from a new wholesaler.

Don't delete outliers automatically. Check the largest and smallest values in each number column first. Kolanut's quantities run from 1 to 30 packs, so a 3,000 would stand out at once. Then decide case by case, and if you exclude one, report the result with and without it.

Rules for cleaning safely

1. Never edit the original. Keep the raw file untouched; work on a copy.
2. Profile first. Count the problems before fixing them.
3. Fix in a sensible order. Trim and standardise text before removing duplicates, or near-duplicates will survive.
4. Keep a cleaning log. Write down each change: what, why, how many rows. Anyone can then repeat or question your work.
5. Don't guess silently. If a value is missing, decide on a rule and state it.
6. Fix the cause when you can. If the old system allowed free-typed regions, the new one should use a drop-down list.

A cleaning log can be as simple as this:

03Example

Four rows from the raw export:

After cleaning:

Look at 01/09/2022. Is that 1 September or 9 January? Nigeria writes day first, so it's 1 September 2022, but a spreadsheet set to US format would read it as 9 January. Mixed date formats are one of the most dangerous cleaning problems, because the wrong answer still looks like a date. Writing the clean dates as 2022-09-01 (year-month-day) removes the doubt for everyone who reads them later.

04Walkthrough

A cleaning plan for this file, in the order you'd do it:

1. Save a copy of the raw file and work only on the copy.
2. Profile it: count rows, distinct names, region spellings, blanks. Write the numbers down.
3. Trim spaces and fix capitals in names (spreadsheets have TRIM and PROPER functions for this; the Excel course shows them).
4. Standardise regions to six agreed spellings: Lagos, South West, South East, South South, North Central, North West. SW, South-West and south west all become South West.
5. Remove duplicates after steps 3 and 4. Before trimming, ADA SUPERSTORE and Ada Superstore look different, so duplicate removal would miss them.
6. Convert dates to one format, reading each as day/month/year.
7. Turn money into numbers: remove ₦, commas and .00.
8. Profile again and compare with step 2: 90 customers, 6 regions.
9. Log what you changed and how many rows it affected.

Summary

05Practice

06More practice

Optional drills. They don't count towards the certificate, but they're the fastest way to make this lesson stick. Several use a different dataset from the lesson on purpose: if you can do the same thing on unfamiliar data, you've really learned it.

07Check your understanding