5. Cleaning and Preparing Datasets

Why Data Preparation Matters

Cleaning and preparing data means examining its structure, correcting or documenting problems, and transforming it into a form that is accurate, consistent, valid, and appropriate for a particular purpose. The aim is not to make every value appear perfect. The aim is to preserve the meaning of the original observations while making errors, assumptions, and limitations visible.

A useful way to judge Data quality is to consider accuracy, completeness, consistency, reliability, relevance, timeliness, accessibility, and fitness for purpose. These characteristics are connected: a complete dataset can still be inaccurate, and an accurate dataset can still be unsuitable for the question being asked.

Takeaway: Good preparation improves the usefulness and transparency of data without hiding uncertainty or changing observations without justification.

Understanding Dataset Structure

A dataset is an organized collection of observations. It may be stored in a spreadsheet, a comma-separated values (CSV) file, a database table, or a specialized format such as Parquet.

In a tabular dataset:

• A field, column, or variable describes one characteristic, such as score, date, or program.

• A record, row, or observation contains the values associated with one case, event, or entity.

• A header identifies the fields.

• A data type states what kind of value a field should contain, such as numeric, text or categorical, Boolean, or date and time.

Each field should have one clear meaning and one consistent representation. A field named age, for example, should not mix ages, birth years, and notes such as unknown.

An identifier distinguishes one record from another. A primary key is an identifier that is unique for every valid record. Identifiers help detect duplicates and join related tables, but an identifier such as customer ID 1047 is not a ranking or a meaningful measurement.

Before changing values, describe the dataset in a data dictionary. Record:

1. The source and collection date.

2. The unit of observation, such as one person, purchase, or measurement.

3. The meaning and expected type of every field.

4. Units, permitted values, and coding conventions.

5. Relationships between tables.

6. The intended use and important limitations.

Takeaway: Cleaning decisions are safer when the unit of observation, field meanings, identifiers, and permitted values are documented first.

A Repeatable Cleaning Workflow

A repeatable workflow separates the unchanged raw data from prepared versions. Use the following sequence:

1. Preserve the raw data. Keep an unchanged copy of the source file.

2. Profile the dataset. Count rows, inspect field names and data types, and calculate missing-value and duplicate counts.

3. Standardize representation. Normalize dates, capitalization, whitespace, units, and category labels where appropriate.

4. Resolve errors. Investigate missing, inconsistent, duplicated, and extreme values.

5. Validate the result. Test whether the prepared data satisfies documented rules.

6. Document transformations. Record what changed, why it changed, and how many values were affected.

7. Review consequences. Check whether cleaning changed the sample, removed particular groups, or altered conclusions.

For large datasets, process only needed columns, use efficient data types, or work in smaller chunks rather than loading everything into memory at once. Scripted or database-based workflows are often easier to reproduce than manual editing because the same rules can be applied to future data and preserved in an audit trail.

Takeaway: A reliable process is preserved, profiled, standardized, investigated, validated, documented, and reviewed.

Missing Values and Imputation

A missing value may mean that a value was not recorded, is unavailable, is not applicable, or was intentionally withheld. It may appear as an empty cell, NA, N/A, null, -, or a special number such as 999. These representations are not automatically equivalent. A blank cell may mean that no measurement was made, while a measured quantity of zero may be a legitimate observation.

Possible responses include:

• Correct the source: Recover the value from the original record when possible and document the correction.

• Leave it missing: This is often preferable when guessing would introduce more error than it removes.

• Remove records or fields: Exclude a row or column only when the amount of missing information justifies it and the effect is assessed.

• Impute a value: Estimate a replacement using a justified method, such as a group median or interpolation between nearby time measurements.

• Add a missingness indicator: Record whether a value was originally missing in a separate field.

An imputed value is an estimate, not an observed fact. Replacing a missing income value with an overall average can hide differences between groups; a median within a relevant group may be more appropriate, but it remains an estimate.

Takeaway: First determine what missingness means, then choose a documented response that does not conceal uncertainty or unequal patterns of missing data.

Consistency and Duplicate Records

Inconsistent values represent the same thing in different ways. Examples include California, CA, and calif.; Yes, yes, Y, and 1; dates such as 09/15/2026 and 2026-09-15; mixed Celsius and Fahrenheit temperatures; accidental spaces; and numeric fields containing $1,250 alongside 1250.

Define a standard representation, such as an approved list of category codes or the ISO date format YYYY-MM-DD. Standardization should not erase meaningful distinctions. Lowercasing text may help match categories, but it may be inappropriate for personal names, product codes, or case-sensitive identifiers.

A duplicate is a record repeated unnecessarily, either exactly or through a repeated identifier. Duplicates can cause observations to be counted more than once and distort totals, averages, and trends. However, two purchases by the same customer may be valid separate records, while two rows with the same order number may indicate a repeated import.

To investigate possible duplicates:

1. Identify the fields that should uniquely identify a record.

2. Search for repeated identifiers or repeated combinations of fields.

3. Compare repeated records with the original information.

4. Keep, merge, correct, or remove records according to documented rules.

5. Recalculate row counts and key statistics after the change.

Takeaway: Standardize only when values mean the same thing, and investigate duplicates according to the unit of observation rather than deleting them automatically.

Outliers and Unusual Values

An outlier is a value unusually far from most other values. It may be a data-entry error, measurement error, unit-conversion mistake, valid but rare observation, sign of changed conditions, or indication that different populations were combined.

For example, a height of 170 may be reasonable in centimeters but implausible in meters. A sale worth $1 million may be an error or a genuine transaction. Unusual does not mean incorrect.

Potential outliers can be identified by:

• Sorting values and inspecting the smallest and largest observations.

• Using a box plot or histogram.

• Comparing values with domain limits.

• Applying interquartile-range or standard-deviation rules.

• Checking unusual values against the original record.

Flag and investigate an outlier before deciding what to do. Possible actions include correcting an error, retaining the value, analyzing it separately, using a robust statistic such as the median, or reporting results with and without the observation as a sensitivity check.

Takeaway: An outlier is a prompt for investigation, not an automatic reason for deletion.

Validation Rules in Practice

Data validation checks whether data satisfies predefined rules. Validation should occur both during data entry and after cleaning. Record the rules used, the number of failures, and the action taken.

Useful checks include:

• Type checks: Confirm that score is numeric, date_of_birth is a valid date, and is_active uses an approved Boolean representation.

• Range checks: Confirm that a percentage is normally between $0\%$ and $100\%$, a probability is between $0$ and $1$, and a month is between $1$ and $12$.

• Format checks: Confirm that postal codes, email addresses, product codes, and dates follow required patterns.

• Completeness checks: Confirm that required fields, such as an order ID and transaction date, are present.

• Uniqueness checks: Confirm that fields expected to be unique actually are unique.

• Relationship checks: Confirm that related fields make sense together, such as a return date not preceding a purchase date, a student marked graduated having a graduation date, and a foreign key matching an existing related record.

A compact worked example can be checked without assuming that every unusual value is an error:

• Student 101 has score 82, attendance 95%, and program A.

• Student 102 has score eighty, attendance 0.88, and program a.

• Student 102 appears again with score 80, attendance 0.88, and program A.

• Student 103 has score 140, attendance 92%, and program B.

• Student 104 has a missing score, attendance 87%, and program B.

The review should identify text in the score field, mixed attendance representations, inconsistent program labels, a repeated student ID, a score outside a presumed $0$–$100$ range, and a missing score. A documented plan might standardize attendance to a proportion between $0$ and $1$, convert valid score text to numbers, standardize program labels, investigate the repeated ID, verify the score of 140, and report the missing score rather than silently replacing it.

Takeaway: Validation turns preparation rules into explicit tests and makes failures visible before analysis.

Tools, Ethics, and Accountability

Spreadsheets are useful for small and medium-sized datasets because they support sorting, filtering, searching, validation rules, summaries, and charts. A practical workflow is to:

1. Freeze and label the header row.

2. Convert the range into a table.

3. Filter for blanks, unexpected categories, and values outside permitted ranges.

4. Use conditional formatting to highlight duplicates and unusual values.

5. Create pivot tables to compare groups.

6. Plot distributions and trends before and after cleaning.

Visual inspection can reveal patterns that simple counts miss. A chart may show that missing values are concentrated in one region, one category has several spellings, or a sudden spike coincides with a change in data collection. Scripts and database workflows are usually easier to reproduce for large datasets and can preserve an audit trail.

Cleaning decisions can affect people and communities. Protect privacy by collecting and retaining only necessary data, removing direct identifiers when they are not needed, restricting access, and considering whether combinations of fields could re-identify individuals. Respect consent and purpose limitation: data collected for one purpose should not automatically be reused for an unrelated purpose.

Assess representation and bias by asking:

• Who is represented and who is missing?

• Were some groups more likely to have missing values?

• Could a cleaning rule disadvantage a group?

• Are categories respectful, necessary, and clearly defined?

• Can people challenge or correct important data about them?

Document the source, transformations, assumptions, exclusions, and remaining limitations. Preserve raw data when legally and ethically possible, and clearly distinguish estimated values from observed values.

Takeaway: Tools reveal different problems, while ethical documentation helps ensure that preparation is reproducible, fair, transparent, and aligned with the intended purpose.