Data Cleaning Project with Python and Pandas

Data Science project 01

Data Cleaning Project with Python and Pandas

Build a complete data cleaning project workflow with reproducible Python code, traceable calculations, validation checks, and honest limitations.

Explore Data Science training in VizagView all project ideas

Analysis question

How can a raw table be made consistent, auditable, and ready for analysis without silently hiding data-quality problems?

Dataset

A deliberately messy synthetic order table with duplicate IDs, mixed date formats, inconsistent city and category labels, missing values, a comma-formatted amount, and an invalid negative amount.

Requirements

  • Python 3.10 or later
  • A terminal or command prompt
  • python -m pip install pandas
  • About 45-60 minutes to build and review

Method and data checks

Create an explicit quality report, remove duplicate order IDs with a documented rule, parse dates and amounts safely, standardise labels, and preserve unresolved invalid values as missing.

  • Order IDs are unique after cleaning
  • Invalid dates and amounts remain visible in the quality report
  • Source data is copied before transformations
  • Category and city normalisation is deterministic

Complete Python code

Save the program as ds_data_cleaning.py. The dataset generator or loader, analysis functions, output, and reproducibility controls are included.

"""Clean a deliberately messy, synthetic order table."""

from __future__ import annotations

import pandas as pd


def make_messy_orders() -> pd.DataFrame:
    return pd.DataFrame(
        {
            "order_id": [1001, 1002, 1002, 1003, 1004, 1005, 1006],
            "order_date": ["2026-01-02", "03/01/2026", "03/01/2026", "bad date", "2026-01-06", None, "2026-01-08"],
            "city": [" vizag ", "VISAKHAPATNAM", "VISAKHAPATNAM", "Hyderabad", None, "hyderabad ", "VIZAG"],
            "category": ["Books", "electronics ", "electronics ", "BOOKS", "Home", None, "home"],
            "amount": ["499", "1,299", "1,299", "NA", 850, "700.50", -50],
        }
    )


def clean_orders(raw: pd.DataFrame) -> tuple[pd.DataFrame, dict[str, int]]:
    data = raw.copy()
    before = len(data)
    data = data.drop_duplicates(subset="order_id", keep="last")
    data["order_date"] = pd.to_datetime(data["order_date"], errors="coerce", dayfirst=True)
    data["amount"] = pd.to_numeric(data["amount"].astype(str).str.replace(",", "", regex=False), errors="coerce")
    city = data["city"].astype("string").str.strip().str.title().replace({"Vizag": "Visakhapatnam"})
    data["city"] = city.fillna("Unknown")
    data["category"] = data["category"].astype("string").str.strip().str.title().fillna("Unknown")
    data.loc[data["amount"] < 0, "amount"] = pd.NA
    report = {
        "rows_before": before,
        "rows_after": len(data),
        "duplicates_removed": before - len(data),
        "missing_dates": int(data["order_date"].isna().sum()),
        "missing_amounts": int(data["amount"].isna().sum()),
    }
    return data.sort_values("order_id").reset_index(drop=True), report


def main() -> None:
    cleaned, report = clean_orders(make_messy_orders())
    print("Quality report:", report)
    print(cleaned.to_string(index=False))


if __name__ == "__main__":
    main()

Run the project

  1. Create and activate a virtual environment.
  2. Install dependencies with python -m pip install pandas.
  3. Run python ds_data_cleaning.py.
  4. Reconcile row counts and totals before interpreting patterns.
  5. Read the limitations before substituting any real dataset.

Expected analytical output

A cleaned DataFrame plus counts for rows before and after cleaning, duplicates removed, missing dates, and missing amounts.

Interpretation and responsible-use limits

The cleaning rules are examples, not universal business rules. Confirm identifiers, date locale, duplicate precedence, valid ranges, and missing-value policy with the data owner before altering a real dataset.

Ways to extend the project

Add schema validation, column-level lineage, rejection tables, unit tests for new failure cases, and a versioned data dictionary.

Continue learning Data Science

Try the next project, return to the Softenant project library, or explore the Data Science course in Vizag for guided data cleaning, analysis, visualisation, and portfolio feedback.