3 Data Cleaning and Preparation

Purpose and first steps

Data cleaning aims to make data reliable for its intended use without changing what the data means. Begin by keeping an unchanged copy of the original data, then inspect and document transformations.

First understand what each field and row represents. Look for missing values, duplicates, inconsistent categories, outliers, and format problems. Resolve an issue only when the evidence supports a decision; retain uncertain cases for review rather than forcing a conclusion.

Missing values

A missing value means that no usable value is recorded, but it does not explain the reason. A blank might mean “not collected,” “not applicable,” “unknown,” or a failed measurement. These possibilities can require different treatments, so check the data source or codebook before acting.

Measure how many values are missing and whether missingness is concentrated in particular columns or groups. Choose a treatment that fits the analysis:

• Keep missing values when their absence is meaningful or the analysis can handle them.

• Remove rows or columns when missingness is substantial and the loss of information is acceptable.

• Impute values when a justified estimate is needed. For example, a median may be used for a skewed numeric variable. Record that imputed values are estimates, not observations.

Do not automatically replace every missing number with zero: zero is a real value and can change summaries. In pandas, isna() identifies missing values, while fillna() and dropna() support common treatments.

Duplicates

A repeated row may be an accidental duplicate or a valid repeated event. Before removing it, check what one row is supposed to represent.

Exact duplicate rows can be identified across all columns. Duplicates based on a key, such as a customer ID, may instead indicate repeated records that need review. If records conflict, apply a defined rule—such as retaining the most recent verified record—instead of deleting arbitrarily.

In pandas, duplicated() flags repeated rows and drop_duplicates() removes them; both can use selected columns.

Category inconsistencies

Inconsistent categories can arise from capitalization, extra spaces, abbreviations, or spelling differences. For example, "West", " west ", and "W." might refer to the same region, or they might not.

Inspect distinct values and confirm equivalences using a reliable definition. Map only values known to mean the same thing, and keep an explicit “unknown” or missing value when a category cannot be confidently resolved. For ordered categories such as low, medium, and high, use their meaningful order rather than alphabetical order.

Checking outliers

An Outlier is an observation unusually far from the rest of a dataset. It may be a data-entry or measurement error, but it may also be a valid and important case.

Use summaries and plots to flag unusual values, then check them against domain rules, source records, and related fields. Correct a value only when evidence supports the correction. Otherwise, retain it, flag it, or use an analysis method suitable for extreme values. Do not delete an observation solely because it lies beyond a statistical cutoff.

Formats and values

Check each column against its intended type and valid range. Common problems include numbers stored as text, inconsistent date formats, mixed units, and impossible values—for example, a negative age when ages must be nonnegative.

Convert values using a known format and unit, and review entries that fail conversion. A date such as 03/04/2025 is ambiguous unless the source specifies whether the month or day comes first. In pandas, to_datetime() parses dates. Setting errors="coerce" turns unparseable entries into missing timestamps, which should be inspected before deciding what to do with them.

The code below standardizes region text, applies one confirmed category mapping, parses dates with a specified format, flags exact duplicate rows, and flags unusually high scores without automatically deleting them. The score threshold is only an example; a valid threshold depends on the variable’s definition and context.

Here, casefold() standardizes letter case, and the category mapping applies one confirmed correction. Invalid dates become missing timestamps for review.

Documenting decisions

Maintain a cleaning log that records the date, column or records affected, issue found, action taken, reason, and number of values changed or removed. Note assumptions, unresolved cases, and any rules used to flag outliers.

Preserve the raw data and, when possible, make cleaning steps reproducible in code. Documentation helps others understand how prepared data differs from the original and evaluate whether the choices suit their analysis. Cleaning decisions should be evidence-based, and uncertain cases should remain available for review.