Case Study: Data Cleaning & Automation Tool

02 Overview

This project is an automated data processing engine engineered to ingest messy, non-standard business spreadsheets and output clean, verified datasets. It replaces repetitive manual spreadsheet sanitization with repeatable, validated Python transformation pipelines.

03 The Problem

Raw spreadsheet files exported from legacy CRMs and external vendors often contain duplicates, missing values, inconsistent formatting (such as multiple date representations like DD/MM/YYYY vs MM-DD-YY), and invalid text strings.

Business impact: Attempting to analyze uncleaned datasets directly leads to skewed calculation totals, inaccurate dashboard metrics, and wasted engineering hours spent manually editing CSV cells.

04 The Solution

I developed a modular Python script and processing workflow that scans raw input files, executes targeted sanitization rules (fuzzy matching for duplicates, regex-based phone/date normalization, and rule-based missing value handling), and exports clean datasets alongside a detailed transformation audit log.

05 Data Pipeline Workflow

01. Upload CSV/XLSX

→

02. Schema Validation

→

03. Clean & Deduplicate

→

04. Format Normalization

→

05. Export Clean Dataset

06 Key Features

Smart Deduplication

Detects and purges both exact duplicate rows and fuzzy matching customer records based on primary identifiers.

Date & Time Normalization

Parses heterogeneous date formats automatically into standard ISO-8601 (YYYY-MM-DD).

Missing Value Imputation

Handles null values through domain-specific median, mode, or placeholder substitution without data loss.

String & Case Cleaning

Strips leading/trailing whitespace, invisible unicode symbols, and enforces title/uppercase standards.

Audit & Error Logging

Generates a summary log specifying exactly how many records were modified, dropped, or flagged.

Multi-format Export

Exports analysis-ready files in `.xlsx`, `.csv`, or SQLite format with preserved column types.

07 Technical Architecture

Input (Raw CSV/Excel) │ ▼ [Ingestion Layer] ── Pandas Dataframe Ingestion & Schema Sniffing │ ▼ [Cleaning Pipeline] ├── Step A: Whitespace & Encoding Sanitization ├── Step B: Deduplication on (Customer_ID, Timestamp) ├── Step C: Regex Pattern Matcher (Email, Phone, Dates) └── Step D: Null Strategy Applicator │ ▼ [Validation Gate] ── Pandera / Assertions (Null % < 0.01, Correct Types) │ ▼ Output (Clean File + JSON Audit Summary)

08 Output Demonstration

pipeline_audit_summary.log

[INFO] Processing dataset: raw_sales_transactions_2025.csv [INFO] Total records ingested: 25,480 [SUCCESS] Dropped 1,420 exact duplicate rows. [SUCCESS] Standardized 3,110 date cells into ISO-8601 format. [SUCCESS] Imputed 412 missing 'Unit_Cost' values using product category medians. [SUCCESS] 100% records passed type and constraint verification. [OUTPUT] Clean dataset saved to /output/clean_sales_transactions_2025.xlsx

09 Challenges & Solutions

Challenge: Ambiguous date representations (e.g., 04/05/2024 vs 05/04/2024)

Solution: Built an automated locale and delimiter detector that samples the entire date column to establish consistent day/month patterns before executing conversion.

Challenge: Memory consumption when loading large 500MB+ CSV files

Solution: Implemented chunk-based processing (`chunksize=10000`) in Pandas to streamline memory overhead and prevent out-of-memory crashes.

10 Results & Impact

Automates repetitive data-cleaning operations and produces a standardized dataset ready for downstream analysis.

Ensures zero formula calculation errors caused by corrupted spreadsheet inputs and establishes a consistent, reproducible pipeline for weekly data updates.

11 Future Improvements

Direct PostgreSQL and MySQL database connection drivers for automated table syncs.
Cloud scheduled cron execution via AWS Lambda or GitHub Actions.
Web-based drag-and-drop user interface for non-technical team members.

Data Cleaning & Automation Tool