Data Lab · Unit 3 of 8
Unit 3: Cleaning data
Garbage in, garbage out: fixing spaces, spellings, duplicates, missing values and impossible numbers before you analyze.
Opens the first lesson of this unit in Data Lab. Every lesson is open from the start.
Lesson 1: Garbage in, garbage out
Goal: Explain why cleaning comes first and how messy data produces wrong answers.
Real data is messy. People mistype, systems export odd formats and the same thing gets recorded three different ways. Analysts often say they spend more time cleaning data than analyzing it, and it is time well spent.
The phrase garbage in, garbage out means a calculation can only be as good as the data fed into it. A spreadsheet will happily calculate an average that includes a typo of 4,500 instead of 45.00, and it will look completely official.
Worked example: one typo, one wrong decision
- A shop records eight sales: 42, 38, 45, 40, 4500, 39, 44, 41.
- The 4500 should have been 45.00; the decimal point was missed.
- Average with the typo: about 599 a sale. Average without it: about 42.
- A manager reading 599 might stock up on expensive items that nobody actually buys.
One unchecked cell changed the answer by more than ten times.
Good habits: keep the original data untouched and clean a copy, write down every change you make, and check the data before you trust any result.
- Data cleaning
- Finding and fixing errors, inconsistencies and gaps in data before analyzing it.
- Garbage in, garbage out
- The idea that flawed input data produces flawed results, however good the analysis.
Remember: Keep the original, clean a copy, and write down every change you make.
Lesson 2: Fixing text: spaces, capitals and spellings
Goal: Standardize text so that the same thing is always written the same way.
To a computer, North, north, North (with a space at the end) and N. are four different values. Count orders by region and you will get four rows where you expected one.
Common text fixes:
| Problem | Example | Fix (Excel and Google Sheets) |
|---|---|---|
| Extra spaces | " Ava Brooks " | =TRIM(A2) removes spaces at the ends and doubled spaces inside |
| Inconsistent capitals | riverton, RIVERTON | =PROPER(A2) → Riverton; also UPPER and LOWER |
| Different spellings | N., Nrth, North | Find and replace, or a lookup list of the correct values |
Worked example: counting regions correctly
- Raw values:
North,north,NORTH,South,south. - Apply TRIM, then PROPER:
North,North,North,South,South. - Now a count by region gives North: 3 and South: 2, instead of five separate groups.
Prevention beats cure: when you collect data yourself, use dropdown lists instead of free typing for things like region, size or yes/no.
- TRIM
- A spreadsheet function that removes extra spaces from text.
- PROPER
- A spreadsheet function that capitalizes the first letter of each word.
- Standardize
- Make every version of the same value identical, so it groups and counts correctly.
Remember: Same thing, same spelling, no stray spaces. Use dropdowns to stop the mess at the source.
Lesson 3: Duplicates and missing values
Goal: Find duplicates and handle blanks honestly.
Duplicates are records that appear more than once, often because a form was submitted twice or two files were combined. They inflate counts and totals. Look for rows where every field matches, or where an ID that should be unique repeats.
Be careful: not every repeat is a duplicate. Two customers can share a name, and the same person can really buy the same item twice. Use a unique ID, or a combination of fields such as order number plus item, to decide.
Missing values are blank cells. You have three honest options, and the right one depends on why the value is missing:
- Leave it blank and say how many are missing. Functions like AVERAGE skip blanks.
- Fill it only if you truly know the value, such as a zero that was skipped, or a city you can look up from the ZIP code.
- Exclude the row from a particular calculation, and report that you did.
Watch out: Never invent values to make a dataset look complete. And do not silently replace blanks with 0: a missing test score is not a score of zero, and treating it as one drags the average down.
- Duplicate record
- The same real-world record entered more than once.
- Missing value
- A cell where the value was not recorded.
Worked example: cleaning a club sign-up list
A club sign-up sheet has 48 rows.
- Sort by email: 3 emails appear twice with identical names and times. They are double submissions, so remove the 3 extra rows.
- Two "Alex Kim" rows have different emails and grades. They are two real people, so keep both.
- 5 rows have no phone number. Leave them blank and report "5 of 45 have no phone number" instead of inventing numbers.
45 real members, nothing invented, and a note that tells the reader exactly what is missing.
Remember: Use IDs to spot true duplicates. Blank is not zero: report missing values, do not invent them.
Lesson 4: Outliers and sanity checks
Goal: Spot impossible or suspicious values before they distort results.
An outlier is a value far away from the rest. Some outliers are errors: a typo, a wrong unit, a test entry. Others are real and important, such as a record sales day or a rare medical reading. Your job is to investigate, not to delete automatically.
Quick sanity checks catch most problems:
- Min and max of every number column. An age of 212, a negative price or a date in the year 2205 is impossible.
- Counts. Does the number of rows match what you expected?
- Totals. Does the sum match a known figure, like a bank statement?
- Sorted view. Sort each column both ways and look at the top and bottom few rows.
Worked example: a sanity check in action
A table of delivery times in minutes has MIN = 12 and MAX = 1,440.
- 1,440 minutes is exactly 24 hours. Suspicious.
- Looking at that row: the delivery was marked "complete" the next day, after the driver forgot to tap the button.
- Decision: exclude it from the average delivery time, and note why.
Whatever you decide, write it down: "Removed 1 row: delivery logged a day late by mistake." Anyone checking your work should be able to follow every decision.
Hands-on lab in the room: Cleaning lab. A messy export from the shop. Find the six problems before anyone analyzes it.
- Outlier
- A value far from the others. It may be an error or a real, important event.
- Sanity check
- A quick test, like checking minimums, maximums and totals, to catch obvious errors.
Remember: Check min, max and counts first. Investigate outliers; do not delete them automatically.
Practise it in Data Lab
Each lesson ends with two questions that check you can use the idea, not just recognise it. Wrong answers explain why and come back in Review. The unit ends with a 5-question quiz; pass it with 80% or more to master the unit. Hands-on lab: Cleaning lab.
Every lesson is free: no ads, no lives and no paywall.