4 Data Cleaning and Preparation

Principles of data cleaning

Data cleaning turns collected data into a consistent, trustworthy form for analysis. It does not mean changing every unusual value: unusual values may still be correct. Preserve the original data, document assumptions, and check that each proposed change is justified.

Handling missing values

A missing value means that a value was not recorded or is unavailable. It is not the same as zero, an empty string, or a category such as “none.” Files may use several markers, such as a blank cell, N/A, or unknown; identify and standardize these markers before counting missing values. In pandas, missing-value markers depend in part on a column’s data type.

For each column, examine how many values are missing and consider why. A few missing values in a nonessential field may be left missing or removed from a particular analysis. For an important field, possible approaches include collecting the value, using imputation, or retaining a missingness indicator.

Imputation choices can distort results, so explain and test them rather than applying them automatically. A median might be used for a skewed numeric column, or the most common category for a categorical column.

Investigating duplicates

Duplicate rows can arise when records are imported more than once, but repeated values do not always indicate repeated records. For example, two sales with the same date and amount may still be separate events.

Define what makes a record unique, such as a transaction ID or a combination of customer ID and order date. Inspect suspected duplicates before removing them, and decide which record to keep using a documented rule. Pandas can find and remove duplicates using all columns or selected columns.

Standardizing formats and values

Equivalent values may appear in different forms, such as NY, N.Y., and New York; dates such as 03/04/2025 and 2025-04-03; or text with extra spaces and inconsistent capitalization. Choose a consistent representation based on the data’s meaning and source.

For dates, confirm whether ambiguous values use month-first or day-first order before parsing. For measurements, verify units before converting, such as centimeters versus inches. Standardizing formats should preserve meaning, not merely make strings look alike.

Validating cleaned data

Validation checks whether cleaned data meets explicit rules. Common checks include:

• Type: Dates parse as dates; quantities are numeric.

• Completeness: Required identifiers are present.

• Range: Age or price values fall within plausible limits.

• Allowed values: A status belongs to a defined set, such as open, closed, or pending.

• Relationships: A delivery date does not precede an order date.

• Uniqueness: Fields defined as unique contain no duplicates.

A failed check is a signal to investigate, not automatic proof that a record is wrong. For example, a negative amount might be an error or a valid refund. Record validation rules and exceptions so results can be reproduced.

A practical workflow in Python

This pandas example assumes a sales file and illustrates checks. Adapt the column names, allowed values, and duplicate policy to the actual data.

Reproducible preparation

A reliable workflow is iterative:

1. Keep a raw copy.

2. Inspect columns, types, ranges, and missingness.

3. Define cleaning and validation rules.

4. Apply transformations while logging changed or excluded rows.

5. Recheck the rules and compare summaries before and after.

6. Save the cleaned data and the steps used.

When preparing data for machine learning, split training and test data before learning imputation or other preprocessing values. Fit transformations on training data only, then apply the same transformations to the test data. Otherwise, data leakage can influence evaluation.