🧹 Universal Data Preprocessing System

A generic, reusable Python pipeline that automatically profiles, cleans, transforms, and validates ANY tabular CSV or Excel dataset — making it ML-ready in one command.

---

📌 What It Does

ANY RAW CSV / EXCEL DATASET
        ↓
  Auto Column Type Detection
        ↓
  Profiling & Missing Value Analysis
        ↓
  Cleaning & Normalization
        ↓
  Outlier Detection (IQR)
        ↓
  Date Feature Engineering
        ↓
  Boolean & Categorical Encoding
        ↓
  Scikit-Learn Pipeline (Scale + Encode)
        ↓
  Validation (PASS / FAIL)
        ↓
CLEANED CSV  +  ML-READY MATRIX  +  REPORTS  +  CHARTS

---

🚀 Quick Start

1. Clone the Repository

git clone https://github.com/imsurya26/universal-data-preprocessing.git
cd universal-data-preprocessing

2. Install Dependencies

pip install -r requirements.txt

3. Run on Your Dataset

# CSV file
python preprocess.py --input your_dataset.csv

# Excel file (first sheet by default)
python preprocess.py --input your_dataset.xlsx

# Excel — choose specific sheet
python preprocess.py --input your_dataset.xlsx --sheet "Sheet2"

---

⚙️ All Command-Line Options

Argument	Short	Default	Description
--input	-i	dataset.csv	Path to input CSV or Excel file
--output	-o	processed_data/	Output directory for all results
--sheet	-s	0	Excel sheet name or index (ignored for CSV)
--handle-outliers		False	Cap extreme outliers using IQR bounds
--no-scaling		True	Disable StandardScaler on numerical columns
--no-date-features		True	Skip date feature extraction
--include-identifiers		False	Include ID columns in ML matrix
--include-free-text		False	Include free-form text in ML matrix

Examples

# Standard run
python preprocess.py --input sales_data.csv

# Cap outliers + custom output folder
python preprocess.py --input data.csv --output results/ --handle-outliers

# Disable scaling (e.g. for tree-based models)
python preprocess.py --input data.csv --no-scaling

# Include identifiers in ML matrix
python preprocess.py --input data.csv --include-identifiers

---

📂 Output Files

After running, you get:

processed_data/
├── cleaned_dataset.csv          ← Human-readable cleaned data
├── ml_ready_dataset.csv         ← Encoded + scaled ML-ready matrix
├── preprocessing_report.csv     ← Per-column transformation log
├── preprocessing_summary.txt    ← Full before/after text summary
└── reports/figures/
    ├── missing_values.png        ← Missing values before vs after
    ├── numerical_distributions.png
    ├── numerical_boxplots.png    ← Outlier diagnostic
    └── correlation_heatmap.png

Also saved directly to the project root:

• cleaned_dataset.csv
• ml_ready_dataset.csv
• preprocessing_report.csv
• preprocessing_summary.txt

---

🔍 Column Type Auto-Detection

The script automatically detects column types using value-based heuristics — not just pandas dtype:

Detected Type	Logic
identifier	Column name contains id/email/name, email pattern, EMP001-style codes, or high uniqueness
boolean	Values are subset of {yes, no, true, false, y, n, 0, 1}
datetime	70%+ of values parse successfully as dates
numerical	Numeric or numeric strings ($75,000, 50%) after cleaning
categorical	Short, low-cardinality string values
text	Long strings / avg word count > 3 (e.g. reviews, notes)

---

🧠 Preprocessing Pipeline

Missing Value Standardization

Detects and converts all common missing representations to NaN:

NaN, None, NULL, null, N/A, na, ?, -, "", whitespace-only strings

Imputation Strategy

Column Type	Strategy
Numerical	Median imputation
Categorical	Mode imputation
Boolean	Mode imputation
Datetime	Forward fill → Backward fill
Identifier / Text	"Missing" placeholder

Outlier Detection (IQR Method)

For every numerical column, computes: Q1, Q3, IQR, Lower Bound, Upper Bound, Outlier Count

Outlier capping is off by default — use --handle-outliers to enable it.

Scikit-Learn Pipeline

Numerical  →  SimpleImputer(median)  →  StandardScaler
Categorical →  SimpleImputer(most_frequent)  →  OneHotEncoder(handle_unknown='ignore')
Boolean    →  0 / 1 conversion
Datetime   →  year, month, day, day_of_week, quarter features

---

✅ Validation Report

After every run the script prints a full validation summary:

========== VALIDATION ==========
Rows before:            1000
Rows after:             987

Duplicates before:      13
Duplicates after:       0

Missing values before:  45
Missing values (Clean): 0
Missing values (ML):    0

Infinite values:        0
Invalid values:         0

Validation status:      PASS
================================

---

📊 Diagnostic Visualizations

Automatically saved to reports/figures/:

Plot	Description
missing_values.png	Bar chart: missing counts before vs after
numerical_distributions.png	KDE histograms for each numerical column
numerical_boxplots.png	Boxplots for outlier inspection
correlation_heatmap.png	Pearson correlation matrix heatmap

---

🔧 Configuration (No CLI Needed)

You can also edit the defaults at the top of preprocess.py:

DATA_PATH         = "dataset.csv"
OUTPUT_DIR        = "processed_data"
HANDLE_OUTLIERS   = False
EXTRACT_DATE_FEATURES = True
EXCLUDE_IDENTIFIERS   = True
EXCLUDE_FREE_TEXT     = True
SCALE_NUMERICAL       = True

---

📦 Requirements

pandas>=2.0.0
numpy>=1.24.0
scikit-learn>=1.2.0
matplotlib>=3.7.0
seaborn>=0.12.0
openpyxl           # for .xlsx Excel files
xlrd               # for legacy .xls Excel files

Install all at once:

pip install -r requirements.txt
pip install openpyxl xlrd

---

🗂️ Project Structure

universal-data-preprocessing/
├── preprocess.py               ← Main preprocessing pipeline (all-in-one)
├── dataset.csv                 ← Sample test dataset with edge cases
├── requirements.txt            ← Python dependencies
├── README.md                   ← This file
├── cleaned_dataset.csv         ← Generated output
├── ml_ready_dataset.csv        ← Generated output
├── preprocessing_report.csv    ← Generated output
├── preprocessing_summary.txt   ← Generated output
└── processed_data/
    └── reports/figures/        ← Generated diagnostic charts

---

🤝 Contributing

Pull requests are welcome! If you find a bug or want to add a new feature (e.g. a new column type detector or imputation strategy), feel free to open an issue.

---

📄 License

MIT License — free to use, modify, and distribute.

---

👤 Author

Surya — @imsurya26