Capstone: End-to-End Data Cleaning

Reviewed & published by Brayan K

A data cleaning pipeline is the ordered sequence of pandas steps that turns a messy raw file into an analysis-ready table — reading the data, inspecting it, fixing strings and dates, handling missing values, fixing dtypes, deriving new columns, filtering, summarising, and exporting the result. This capstone combines every skill from the course into one realistic workflow.

Part of the free Pandas course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

What You'll Practice in This Capstone

• Reading CSV text with pd.read_csv and io.StringIO
• Inspecting with head, info, describe, isna
• Cleaning strings with the .str accessor
• Parsing dates with pd.to_datetime
• Handling missing data with fillna / dropna
• Deriving columns, grouping, and exporting with to_csv

1 Step 1 — Load and Inspect the Mess

Always look before you clean. head() shows the shape of the data, info() reveals dtypes and null counts, and isna().sum() tells you exactly where the gaps are. Notice the messy strings, mixed casing, and missing values below.

import pandas as pd
import io

raw = """order_id,customer, signup_date ,amount,country
1001,  Alice  ,2023-01-05,49.99,uk
1002,bob,2023/02/11,,US
1003,CAROL,not a date,19.50,uk
1004,  dan,2023-03-20,75.00,
1005,Alice,2023-01-05,49.99,UK
"""

df = pd.read_csv(io.StringIO(raw))

print(df.head())
print("--- info ---")
df.info()                 # dtypes + non-null counts (amount has a missing value)
print("--- missing per column ---")
print(df.isna().sum())    # amount: 1 missing, country: 1 missing
# Also note: messy whitespace and casing in 'customer' and 'country',
# and a bad value 'not a date' in signup_date.

# ✅ Expected output:
#    order_id   customer  signup_date   amount country
# 0      1001    Alice      2023-01-05   49.99      uk
# 1      1002        bob    2023/02/11     NaN      US
# 2      1003      CAROL    not a date   19.50      uk
# 3      1004        dan    2023-03-20   75.00     NaN
# 4      1005      Alice    2023-01-05   49.99      UK
# --- info ---
# <class 'pandas.core.frame.DataFrame'>
# RangeIndex: 5 entries, 0 to 4
# Data columns (total 5 columns):
#  #   Column         Non-Null Count  Dtype  
# ---  ------         --------------  -----  
#  0   order_id       5 non-null      int64  
#  1   customer       5 non-null      object 
#  2    signup_date   5 non-null      object 
#  3   amount         4 non-null      float64
#  4   country        4 non-null      object 
# dtypes: float64(1), int64(1), object(3)
# memory usage: 332.0+ bytes
# --- missing per column ---
# order_id         0
# customer         0
#  signup_date     0
# amount           1
# country          1
# dtype: int64

2 Step 2 — Clean Strings & Parse Dates

Strip whitespace and normalise casing with the .str accessor. Parse dates with pd.to_datetime(..., errors="coerce") so unparseable values become NaT instead of crashing.

import pandas as pd
import io

raw = """order_id,customer, signup_date ,amount,country
1001,  Alice  ,2023-01-05,49.99,uk
1002,bob,2023/02/11,,US
1003,CAROL,not a date,19.50,uk
"""
df = pd.read_csv(io.StringIO(raw))

# Tidy column names (strip surrounding spaces)
df.columns = df.columns.str.strip()

# Clean the customer text: strip spaces, Title Case
df["customer"] = df["customer"].str.strip().str.title()

# Normalise country codes to uppercase
df["country"] = df["country"].str.strip().str.upper()

# Parse dates; bad strings -> NaT (Not a Time) instead of an error
df["signup_date"] = pd.to_datetime(df["signup_date"], errors="coerce")

print(df)
print(df.dtypes)
# 'Alice', 'Bob', 'Carol'; 'UK'/'US'; signup_date is datetime64 with one NaT.

# ✅ Expected output:
#    order_id customer signup_date  amount country
# 0      1001    Alice  2023-01-05   49.99      UK
# 1      1002      Bob         NaT     NaN      US
# 2      1003    Carol         NaT   19.50      UK
# order_id                int64
# customer               object
# signup_date    datetime64[ns]
# amount                float64
# country                object
# dtype: object

3 Step 3 — The Complete Pipeline

Here is the whole workflow end to end: load → clean → handle missing → fix dtypes → derive a column → filter → summarise → export. This is the kind of script you would run on a real messy export.

import pandas as pd
import io

# ----- 0) Raw messy data (in real life: a file path or URL) -----
raw = """order_id,customer, signup_date ,amount,country
1001,  Alice  ,2023-01-05,49.99,uk
1002,bob,2023/02/11,,US
1003,CAROL,2023-02-28,19.50,uk
1004,  dan,2023-03-20,75.00,
1005,Alice,2023-01-05,49.99,UK
1005,Alice,2023-01-05,49.99,UK
"""

df = pd.read_csv(io.StringIO(raw))
print("Loaded:", df.shape, "rows x cols")

# ----- 1) Tidy column names -----
df.columns = df.columns.str.strip()

# ----- 2) Clean string columns with the .str accessor -----
df["customer"] = df["customer"].str.strip().str.title()
df["country"] = df["country"].str.strip().str.upper()

# ----- 3) Parse dates (bad values -> NaT) -----
df["signup_date"] = pd.to_datetime(df["signup_date"], errors="coerce")

# ----- 4) Fix numeric dtype and handle missing values -----
df["amount"] = pd.to_numeric(df["amount"], errors="coerce")
df["amount"] = df["amount"].fillna(df["amount"].median())  # impute missing amount
df["country"] = df["country"].fillna("UNKNOWN")            # placeholder for missing

# ----- 5) Drop exact duplicate rows (order 1005 appears twice) -----
df = df.drop_duplicates()

# ----- 6) Derive a new column -----
df["is_high_value"] = df["amount"] >= 50

# ----- 7) Downcast for memory; country is low-cardinality -----
df["order_id"] = df["order_id"].astype("int32")
df["country"] = df["country"].astype("category")

# ----- 8) Filter: keep only valid, high-value orders -----
clean = df[df["is_high_value"] & df["signup_date"].notna()].copy()

# ----- 9) Summarise: total amount per country -----
summary = (df.groupby("country", observed=True)["amount"]
             .agg(["count", "sum", "mean"])
             .round(2))

print("--- Cleaned data ---")
print(df)
print("--- High-value orders with a valid date ---")
print(clean[["order_id", "customer", "amount"]])
print("--- Summary by country ---")
print(summary)

# ----- 10) Export the cleaned result to CSV text -----
out = io.StringIO()
df.to_csv(out, index=False)
print("--- Exported CSV (first lines) ---")
print(out.getvalue()[:160])

# EXPECTED HIGHLIGHTS:
#  - Shape becomes 5 rows after the duplicate 1005 is dropped.
#  - Order 1002's missing amount is filled with the median (49.99).
#  - Order 1004's missing country becomes 'UNKNOWN'.
#  - 'is_high_value' is True for the 49.99 and 75.00 orders.
#  - Summary shows count/sum/mean of amount per country.

# ✅ Expected output:
# Loaded: (6, 5) rows x cols
# --- Cleaned data ---
#    order_id customer signup_date  amount  country  is_high_value
# 0      1001    Alice  2023-01-05   49.99       UK          False
# 1      1002      Bob         NaT   49.99       US          False
# 2      1003    Carol  2023-02-28   19.50       UK          False
# 3      1004      Dan  2023-03-20   75.00  UNKNOWN           True
# 4      1005    Alice  2023-01-05   49.99       UK          False
# --- High-value orders with a valid date ---
#    order_id customer  amount
# 3      1004      Dan    75.0
# --- Summary by country ---
#          count     sum   mean
# country                      
# UK           3  119.48  39.83
# UNKNOWN      1   75.00  75.00
# US           1   49.99  49.99
# --- Exported CSV (first lines) ---
# order_id,customer,signup_date,amount,country,is_high_value
# 1001,Alice,2023-01-05,49.99,UK,False
# 1002,Bob,,49.99,US,False
# 1003,Carol,2023-02-28,19.5,UK,False
# 100

🎯 Your Turn: Fill the Missing Prices

Replace the missing prices with 0 so the column has no gaps. Fill in the blank method.

import pandas as pd
import io

raw = """item,price
Pen,1.20
Pad,
Eraser,0.50
Ruler,
"""
df = pd.read_csv(io.StringIO(raw))

# 🎯 YOUR TURN: replace every missing price with 0
df["price"] = df["price"].___(0)
print(df)
print("Missing left:", df["price"].isna().sum())

# EXPECTED OUTPUT:
#      item  price
# 0     Pen   1.20
# 1     Pad   0.00
# 2  Eraser   0.50
# 3   Ruler   0.00
# Missing left: 0

📋 The Data Cleaning Checklist

🏆 Final Challenge: Clean a Survey Export

Apply the whole pipeline to a small messy survey: clean the names, normalise the rating column, drop rows with no rating, then report the average rating per city.

import pandas as pd
import io

raw = """name, city ,rating
 Alice ,London,5
bob,london,
CAROL, LEEDS ,4
dan,London,3
 eve , leeds ,5
"""

df = pd.read_csv(io.StringIO(raw))

# 1) Tidy headers + string columns
df.columns = df.columns.str.strip()
df["name"] = df["name"].str.strip().str.title()
df["city"] = df["city"].str.strip().str.title()   # 'london' -> 'London'

# 2) Make rating numeric; blanks -> NaN, then drop those rows
df["rating"] = pd.to_numeric(df["rating"], errors="coerce")
df = df.dropna(subset=["rating"])

# 3) Average rating per city
result = df.groupby("city")["rating"].mean().round(2)
print(df)
print(result)
# EXPECTED: London avg = (5 + 3) / 2 = 4.0, Leeds avg = (4 + 5) / 2 = 4.5
# (Bob's blank-rating row was dropped.)

# ✅ Expected output:
#     name    city  rating
# 0  Alice  London     5.0
# 2  Carol   Leeds     4.0
# 3    Dan  London     3.0
# 4    Eve   Leeds     5.0
# city
# Leeds     4.5
# London    4.0
# Name: rating, dtype: float64

❓ Frequently Asked Questions

Capstone complete — you finished the Pandas course!

You took raw, messy data and ran it through a full professional pipeline: load, inspect, clean strings and dates, handle missing values, fix dtypes, derive columns, filter, summarise, and export. Combined with everything earlier — Series, DataFrames, selection, filtering, aggregation, merging, reshaping, MultiIndex, and performance — you now have the complete toolkit of a working data analyst.

🎓 Congratulations! Take these skills to a real dataset and keep practising.

Practice quiz

Continue this course

• Previous: Checkpoint: Time Series & I/O
• Next: Scaling with Dask — Go beyond memory with Dask's partitioned, parallel, pandas-like DataFrame
• Quick reference: Pandas cheat sheet

Frequently asked questions

What is io.StringIO and why use it in these examples?

io.StringIO wraps an in-memory string so it behaves like a file object. That lets pd.read_csv read CSV text defined right in your script — perfect for self-contained, runnable examples and tests without needing a real file on disk. In production you would pass an actual file path or URL to read_csv instead.

Should I use fillna or dropna for missing values?

It depends on the column and your goal. Use dropna to remove rows or columns that are unusable without the value (for example a missing primary key). Use fillna when a sensible default exists — 0 for missing counts, the median for a skewed numeric column, or a placeholder like 'Unknown' for a category. Decide per column rather than globally.

Why convert columns to the right dtype during cleaning?

Raw CSV data often arrives as strings. Converting with to_datetime, to_numeric, or astype lets pandas do date math, numeric aggregation, and efficient storage. It also surfaces bad values early (with errors='coerce' turning unparseable entries into NaN/NaT) so you can handle them deliberately rather than having them silently break later steps.