Missing values, duplicates and redundancy

Why this matters

Missing values and duplicates are the two most common data quality problems, and both distort results in ways that look like findings. Missing values shrink or bias the sample an average is taken from. Duplicates inflate counts and sums. Neither produces an error message.

The exam asks you to recognise these problems, choose an appropriate way to handle each, and understand the consequences of the choice. This lesson covers how missing values are represented, why the reason for missingness matters, the options for handling them, how to find exact and near duplicates, and redundant data that has drifted out of step.

The lesson

Blank, NULL, zero and 'N/A': four ways to say missing

Missing data does not look the same everywhere, and the first job is to find every form it takes.

• NULL is a database's formal marker for an unknown or absent value. It is not zero and not an empty string, and it behaves specially in comparisons and aggregates.
• An empty string or blank cell is common in files. Some tools read it as NULL, others as a real, empty text value.
• Placeholder text such as N/A, none, unknown, - or ? is typed by people or exported by systems, and is read as an ordinary value unless it is converted.
• Sentinel numbers such as 0, -1, 999 or 9999 are used by some systems to mean "not recorded". A 0 in an age column is usually missing, not a newborn, and averaging it in drags the mean down.

Profiling should count every one of these per column. A column that appears complete because it has no NULLs may still be 15% missing once N/A and 0 are counted. In the course's raw customer file, 20 of the 314 email values are blank.

Why data goes missing, and why it matters which way

The right way to handle missing values depends on why they are missing, and statisticians distinguish three cases.

• Missing completely at random (MCAR): whether a value is missing has nothing to do with the data. A sensor drops readings because of a random fault unrelated to temperature. Ignoring the missing values loses information but does not bias the result.
• Missing at random (MAR): missingness depends on other recorded data but not on the missing value itself. Older customers are less likely to give an email address; knowing age accounts for it.
• Missing not at random (MNAR): missingness depends on the missing value itself. People with the highest incomes are least likely to report income; dissatisfied customers are least likely to answer a satisfaction survey.

The distinction matters because it decides whether the remaining data is still representative. Under MNAR in particular, analysing only the complete records gives a biased answer, and no amount of careful averaging fixes it: a satisfaction score from people who chose to respond describes the respondents, not all customers. Stating the likely mechanism, even as a judgement, is part of reporting honestly.

Deleting, imputing or flagging a missing value

There are three broad options, and each has a cost.

Deletion removes records with missing values. Listwise deletion drops a record if any needed field is missing; pairwise deletion uses each record for whichever calculations it has data for. Deletion is simple and transparent, and it shrinks the sample, and under MAR or MNAR it biases the result.

Imputation fills the gap with an estimate. Common methods include the mean or median of the column, the mode for categories, a value carried forward from the previous reading in a time series, or a prediction from other fields. Imputation keeps the sample size, and it invents data: mean imputation in particular shrinks the variance, making the data look more consistent than it is, and any imputed value can distort relationships between columns.

Flagging keeps the record, leaves the value marked as missing or fills it, and adds an indicator column saying it was missing. This preserves information and lets later analysis check whether missingness itself matters, which it often does.

Whatever the choice, record it: which columns, how many values, which method, and why. A result that depends on imputed values should say so.

Exact and near duplicates, and finding each

A duplicate is a record that represents the same real-world thing as another record.

Exact duplicates are identical in every field, usually from loading the same file twice or a join that fanned out. They are easy to find: group by all columns and count, or compare the row count before and after removing duplicates.

Near duplicates are the harder, more common kind: the same customer entered twice with small differences. The raw customer file has 14, each a second copy of a customer with a new identifier, the surname in capitals and the email in capitals. A check on the whole row finds nothing, because every row differs, and a check on customer_id finds nothing, because each copy has its own.

Finding near duplicates means standardising first, then comparing: trim spaces, convert to one case, remove punctuation, then compare on the fields that identify the real thing, such as email, or name plus date of birth. Beyond that, fuzzy matching measures how similar two strings are, to catch typing errors such as "Jonh" for "John". Every fuzzy match is a judgement, so the candidate pairs should be reviewed, not merged automatically. Deciding which of two duplicate records to keep, or how to combine them into one, is called survivorship, and the rule should be written down: keep the older identifier, for example, and the most recently updated address.

Redundant columns and derived data that has drifted

Redundancy is the same information stored more than once. Some redundancy is deliberate, as in a denormalised warehouse. The problem arises when copies drift: they started equal and have since diverged.

Common examples are a customer's region stored on both the customer record and every order, where the customer has since moved; a total column stored beside the quantity and price it was calculated from, where the price was later corrected but the total was not; and an age column stored beside a date of birth, correct only on the day it was calculated.

Two checks catch drift. Recalculate derived values from their inputs and count the rows where the stored value disagrees. And compare copies of the same attribute across tables. When they disagree, the question is which is the authoritative source, and the usual answer is to keep the source value and recalculate the derived one, rather than trusting either copy.