Track 14 · Practical Data Analysis · Lesson 4 of 11
Duplicates and invalid values
Find exact and near duplicates, inconsistent categories, impossible values and unit errors in a raw table, and count how many records each problem affects.
A retailer's export says the average customer spent over 12 000 SAR last year. The real figure is under half that: a handful of spends were typed in halalas, some customers appear twice, and a few refunds were netted into negative “spends”. None of these errors is visible in a summary — you only find them by looking for them deliberately.
Missing values announce themselves (see the previous lesson). The problems in this lesson are quieter: every cell holds a value, the value has the right type, and yet it is wrong. There are four families to check for in every new table.
- Duplicates. The same record appears more than once. An exact duplicate is identical in every column. A near duplicate is the same real-world entity recorded twice with small differences (a typo in a name, a different capitalisation, a later update).
- Inconsistent categories. One category spelled several ways —
Riyadh,RIYADH,Riyadh(with a trailing space), or an alias such asAl KhobarforKhobar. Software treats each spelling as a different group. - Impossible values. Values that cannot be true: an age of 230, a negative annual spend, a percentage above 100. Some are codes in disguise (−1 or 999 meaning “not recorded”).
- Unit errors. A value recorded in the wrong unit — halalas instead of riyals (×100), grams instead of kilograms, minutes instead of hours. They look like extreme outliers but follow a pattern.
Included with a Formula Quest membership
It has 8 graded exercises with feedback, hints and worked solutions. After it you will be able to:
- Detect exact duplicates by comparing the row count with the number of distinct ids, and remove only the extra copies
- Spot inconsistent category spellings in a frequency table and count the records affected
- Find impossible values with range filters and recognise codes such as −1 used for “not recorded”
- Recognise a unit error by its round factor and correct it instead of deleting it
- Explain why an outlier is not automatically an error