Skip to content

Day 04 Part 1 — Data Cleaning and Preparation: Agenda

Data analysts spend 60–80% of their time cleaning data. Not because they want to — because real data is messy. Values are missing, formats are inconsistent, duplicates sneak in, and outliers skew every metric. This session teaches you to find, understand, and fix these problems systematically — and to document every decision so your work is reproducible.

Why this session matters before everything else

Every analysis you build sits on top of your data. A dashboard fed dirty data shows wrong numbers. A churn model trained on duplicate records is overfit to noise. A revenue report with un-deduplicated orders makes the business look bigger than it is. Data cleaning is not a chore — it is quality control for every decision that follows.

Session Overview

Duration: 3 hours Prerequisite: NumPy and Pandas Tools: Python + Pandas in Jupyter Lab

The Dataset Used Throughout This Session

All examples in Part 1 use a realistic sales/orders dataset with the following columns:

Column Type Description
order_id string Unique order identifier
customer_id string Customer identifier
order_date datetime Date the order was placed
product_name string Name of the product ordered
category string Product category (Electronics, Furniture, Books, etc.)
quantity int Units ordered
unit_price float Price per unit (£)
total_amount float quantity × unit_price
region string Geographic region (North, South, East, West)
sales_rep string Name of the sales representative
status string Order status (completed, pending, cancelled, refunded)

This is the same dataset you will clean in Part 1 and then explore in Part 2.

Learning Objectives

By the end of this session you will be able to:

  • Profile a new dataset before touching it — understand shape, types, missingness, duplicates
  • Detect and handle missing values using the right strategy per column
  • Identify and remove or reconcile duplicate records
  • Detect outliers using statistical and visual methods, and decide how to handle them
  • Standardise data types, formats, and string values
  • Build a reusable data cleaning pipeline with .pipe()
  • Document cleaning decisions for reproducibility

The Cost of Bad Data

Before writing any cleaning code, understand what is at stake.

Wrong revenue reporting: An ERP system exports orders each night. Due to a re-export bug, completed orders appear twice. Revenue looks 12% higher than reality. The CFO presents inflated numbers to the board.

Biased churn model: A data pipeline stores order dates as strings like "2024/01" alongside "January 2024" and "01-2024". When a data scientist groups by month, some months get split into multiple groups. Churn patterns appear seasonal — but the seasonality is an artefact of inconsistent date formatting.

Wrong product decisions: A product ranking dashboard sorts by total_amount. But total_amount has not been cleaned — some rows have "$1,234.00" (string with currency symbol) that were coerced to NaN. 15% of high-value orders disappear from the ranking. The team deprioritises a top-performing product.

The garbage-in, garbage-out principle

A statistical model or dashboard does not know your data is dirty. It will confidently produce wrong output. The analyst is the last line of defence before bad data reaches a decision-maker.

For senior analysts

Quantify the cost of bad data when pitching data quality work. "We found 847 duplicate orders totalling £62,000 in inflated revenue" is a business case — not a technical complaint. Stakeholders fund data quality work when they see its dollar value.

Session Flow

Time Topic File
0:00 – 0:15 Understanding your data first — profile before cleaning 00-agenda (this file)
0:15 – 0:55 Missing values — detect, understand, impute 01-missing-values
0:55 – 1:25 Duplicate records — find and resolve 02-duplicates
1:25 – 1:55 Outliers — detect and decide 03-outliers
1:55 – 2:25 Data transformation — types, formats, encoding 04-data-transformation
2:25 – 2:45 Cleaning workflows — building a pipeline 05-cleaning-workflows
2:45 – 3:00 Case study 06-case-study

Understanding Your Data First — Profile Before You Clean

The single most common mistake beginners make is jumping straight into cleaning. Before you fix anything, spend 10–15 minutes understanding what you have.

import pandas as pd
import numpy as np

df = pd.read_csv("sales_data.csv")

# 1. Shape and column names
print(f"Shape: {df.shape[0]:,} rows × {df.shape[1]} columns")
print(f"Columns: {list(df.columns)}")

Expected output: 

Shape: 1,000 rows × 11 columns
Columns: ['order_id', 'customer_id', 'order_date', 'product_name', 'category',
          'quantity', 'unit_price', 'total_amount', 'region', 'sales_rep', 'status']


# 2. Data types — what pandas thinks each column is
print(df.dtypes)

Expected output: 

order_id        object
customer_id     object
order_date      object     ← should be datetime
product_name    object
category        object
quantity        object     ← should be int (stored as string from CSV)
unit_price      object     ← should be float (has "£" prefix)
total_amount    float64
region          object
sales_rep       object
status          object


# 3. Missing values — full missingness profile
missing = df.isnull().sum()
missing_pct = df.isnull().mean() * 100

missing_report = pd.DataFrame({
    "missing_count": missing,
    "missing_pct": missing_pct.round(1),
}).sort_values("missing_pct", ascending=False)

print(missing_report[missing_report["missing_count"] > 0])

Expected output: 

               missing_count  missing_pct
sales_rep               180         18.0
total_amount             45          4.5
region                   22          2.2
order_date                8          0.8


# 4. Duplicate rows
print(f"Exact duplicate rows: {df.duplicated().sum()}")
print(f"Duplicate order_ids: {df.duplicated(subset=['order_id']).sum()}")

# 5. Cardinality check — how many unique values per column
print(df.nunique().sort_values())

Expected output: 

status             4
category           6
region             4
sales_rep         12
...
order_id        1000


# 6. Descriptive statistics for numeric columns
print(df.describe())

# 7. Descriptive statistics for string columns
print(df.describe(include="object"))

# 8. Sample rows — always look at the data directly
print(df.sample(10))
print(df.head())
print(df.tail())

The 10-minute profiling rule

Before cleaning, spend exactly 10 minutes running these 8 checks. Write down what you find: which columns have nulls, what the dtypes should be, what the cardinality of each categorical column is, and whether the row count matches what you expected from the data source. This list becomes your cleaning checklist.

For mid-level analysts

Use ydata-profiling (formerly pandas-profiling) for a one-line HTML profiling report on large datasets: 

from ydata_profiling import ProfileReport
profile = ProfileReport(df, title="Sales Data Profile")
profile.to_file("profile_report.html")
This generates a full report with distribution plots, missing value heatmaps, correlation matrices, and sample values for every column. Useful for sharing with a data engineering team when opening a data quality ticket.

For senior analysts

Profiling in production should be automated. Run a profiling job after each ETL load and compare key statistics (row count, missing %, column means) against the previous run. A spike in missing values or a 30% drop in row count is a pipeline failure signal — you want to catch it before a stakeholder does.

The Data Quality Checklist

Before writing any cleaning code, audit your dataset:

  • [ ] How many rows and columns?
  • [ ] What are the data types of each column? Do they match what the column contains?
  • [ ] Which columns have missing values, and what percentage?
  • [ ] Are there duplicate rows? Duplicate order IDs?
  • [ ] Do numeric columns have extreme values? Do any seem like data entry errors?
  • [ ] Are dates in a consistent format?
  • [ ] Are string columns consistently cased and formatted?
  • [ ] Do values match the expected domain (e.g., quantity should be a positive integer)?
  • [ ] Are there unexpected values in categorical columns (e.g., "COMPLETED", "complete", "Completed" all meaning the same thing)?

Previous: NumPy & Pandas Interview Questions | Next: Missing Values