Module 5 · Data Analytics Foundations
Data cleaning
The problems real data arrives with, how they mislead you, and a safe way to fix them.
About 30 minutes
The 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.
The concept
The most common problems:
| Problem | Example | What it breaks |
|---|---|---|
| Duplicates | The same customer listed twice | Counts and totals come out too high |
| Inconsistent labels | Lagos, LAGOS, lagos, Lagos | Groups split into several |
| Extra spaces | " Ada Superstore" | Matching and lookups fail silently |
| Mixed formats | 2023-07-11, 11/07/2023, 11-Jul-2023 | Dates sort and group wrongly |
| Numbers stored as text | "₦1,200,000" | Sums return 0 or an error |
| Missing values | A blank credit limit | Averages and totals change meaning |
| Outliers | A quantity of 10,000 when most are under 40 | Averages are dragged up; could be a typo |
Rules for cleaning safely
- Never edit the original. Keep the raw file untouched; work on a copy.
- Keep a cleaning log. Write down each change: what, why, how many rows. Anyone can then repeat or question your work.
- Fix the cause when you can. If the old system allowed free-typed regions, the new one should use a drop-down list.
- Don't guess silently. If a value is missing, decide on a rule (leave blank, mark "Unknown") and state it.
Example
Four rows from the raw export:
| Customer Name | Region | Date Joined | Credit Limit |
|---|---|---|---|
kayode distributors | LAGOS | 22/10/2023 | ₦2,050,000 |
ADA SUPERSTORE | Lagos | 2023-07-11 | 1,200,000 |
Hajia Amina Superstore | north central | 21/04/2023 | 850000 |
peace mart | South-West | 01/09/2022 | 1500000 |
After cleaning:
| Customer Name | Region | Date Joined | Credit Limit |
|---|---|---|---|
| Kayode Distributors | Lagos | 2023-10-22 | 2050000 |
| Ada Superstore | Lagos | 2023-07-11 | 1200000 |
| Hajia Amina Superstore | North Central | 2023-04-21 | 850000 |
| Peace Mart | South West | 2022-09-01 | 1500000 |
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.
Walkthrough
A cleaning plan for this file, in the order you'd do it:
- Save a copy of the raw file and work only on the copy.
- Trim spaces and fix capitals in names (spreadsheets have
TRIMandPROPERfunctions for this; the Excel course shows them). - Standardise regions to six agreed spellings: Lagos, South West, South East, South South, North Central, North West.
SW,South-Westandsouth westall becomeSouth West. - Remove duplicates after steps 2 and 3. Before trimming,
ADA SUPERSTOREandAda Superstorelook different, so duplicate removal would miss them. - Convert dates to one format, reading each as day/month/year.
- Turn money into numbers: remove
₦, commas and.00. - Log what you changed and how many rows it affected.
Practice
Practice
How many data rows does the raw export customer_list_raw.csv contain (not counting the header)?
Practice
After trimming spaces, ignoring differences in capital letters, and removing duplicate customer names, how many different customers are in the list?
Check your understanding
Answer every question to check.