🧹 Data-Processing-API

A Tkinter desktop UI for pre-processing CSV datasets — inspect, clean, impute and encode your data, then run quick model experiments on the cleaned result.

Despite the "API" in the name, this is a desktop application. Uploaded CSV files are registered in a SQL Server database together with a per-file checklist of cleaning tasks. Each cleaning step is applied interactively through the UI, cleaned datasets are versioned on disk under datasets/<file-id>/, and once the checklist is complete a feature-selection / model-comparison screen is unlocked. A standalone one-shot cleaner (main.py) that needs no database is also included.

✨ Features

• File management — upload CSV files, track them in SQL Server (Process.DataFiles) with size, row/column counts and a per-file cleaning-task checklist; soft-delete files from the grid
• Data statistics — side-by-side "before cleaning" vs "after cleaning" view: row/column counts, empty columns, constant-value columns, all-unique (ID-like) columns, columns with negative values, numeric columns with missing values, categorical columns
• One-click cleanups — drop empty columns, drop constant-value columns, split off all-unique columns into a separate CSV (datasets/<id>/splitted/)
• Missing-value handling — per column: mean, median, zero, or model-based prediction with a RandomForestRegressor (trained on up to the first 5000 complete rows)
• Negative-value handling — per column: non-negative mean, non-negative median, zero, predicted value, or no change
• Categorical encoding — label-encode all object columns; the fitted LabelEncoders are pickled to datasets/<id>/label_encoders/merged_label_encoders.pkl
• Feature selection — pick a target column to see linear-regression feature weights, a lower-triangle correlation heatmap (seaborn), and a quick model comparison (Linear Regression vs Decision Tree Regressor, MSE and R² printed to the console)
• Standalone cleaner (main.py) — no database needed: drops all-empty columns, median-fills missing values, removes duplicate rows, drops all-unique columns, suggests candidate classification/regression target columns, and saves the result as <name>_Cleaned.csv

📋 Requirements

• Python 3 with Tkinter
• Microsoft SQL Server plus the SQL Server ODBC driver (for the Home.py workflow; developed for Windows)
• Third-party packages (no requirements.txt; derived from imports) — install:

pip install pandas numpy scikit-learn seaborn matplotlib pyodbc

🛠 Installation

git clone https://github.com/mkamranr/Data-Processing-API.git
cd Data-Processing-API
pip install pandas numpy scikit-learn seaborn matplotlib pyodbc

Database setup (needed for Home.py / mainform.py only):

1. Create a database named DataProcessingAI and run DataProcessingAPI.sql against it. It creates the Gen and Process schemas, the tables Gen.SerialGenerator, Gen.Tasks, Process.DataFiles, Process.DataFilesTasks, Process.DataFilesTemp, and the stored procedures Process.SaveDataFilesTasks and Process.UpdateDataFileTask.
2. Seed the lookup rows the app reads (the script creates schema only), for example:

INSERT INTO Gen.SerialGenerator (Attribute, Value) VALUES ('DATASET_ID', 1);
INSERT INTO Gen.Tasks (TaskID, Description) VALUES
  (1, 'Drop empty columns'),
  (2, 'Drop all-unique columns'),
  (3, 'Handle missing values'),
  (8, 'Encode categorical columns'),
  (9, 'Handle negative values');

(Task IDs 1, 2, 3, 8 and 9 are the ones referenced by the code; the app marks a file as ready for feature selection once tasks 1, 2, 3 and 8 are processed or not applicable.) 3. Adjust the connection string hardcoded in DBHelper.py (SERVER=DB1;DATABASE=DataProcessingAI;UID=dataprocessing;PWD=dataprocessing) to match your environment.

🚀 Usage

Full database-backed app

python Home.py

• File → Upload — select a .csv file; it is copied to datasets\<id>\, registered in Process.DataFiles and given its task checklist via EXEC Process.SaveDataFilesTasks.
• Each file row in the grid offers:
  • Data Cleaning — opens the Data Statistics window with before/after stats and action buttons (drop empty / constant / all-unique columns, handle missing values, handle negative values, label-encode categoricals, show data, describe dataset). Cleaned output is written to datasets\<id>\cleaned\<filename>.
  • Feature Selection — appears once the file's StatusID reaches 2 (cleaning checklist complete). Choose a target feature to see feature weights and the correlation heatmap, and click Predict and Display Accuracy to compare Linear Regression and Decision Tree Regressor (MSE / R² printed to the console).
  • Delete — soft delete (IsActive = 0).

Standalone one-shot cleaner (no database)

python main.py

Click Upload File, pick a CSV, and the app cleans it in one pass (drops all-empty columns, median-fills missing values, removes duplicates, drops all-unique columns), prints candidate classification/regression target columns to the log pane, and prompts you to save the result as <name>_Cleaned.csv.

Legacy variant

mainform.py (with detailform.py) is an earlier version of Home.py/FileDetails.py and can still be run with python mainform.py.

⚙️ How it works

Home.py lists the active rows of Process.DataFiles in a Tkinter grid; uploading a CSV allocates an ID from Gen.SerialGenerator, copies the file under datasets\<id>\ and creates one Process.DataFilesTasks row per task in Gen.Tasks. The Data Statistics window (FileDetails.py) profiles the dataset with pandas and exposes targeted fixes; each fix saves a new cleaned CSV to datasets\<id>\cleaned\ and calls Process.UpdateDataFileTask to mark the corresponding task as processed (or not applicable when the issue is absent). When tasks 1, 2, 3 and 8 are all resolved, the stored procedure flips the file's StatusID to 2, which unlocks the Feature Selection window (FeatureSelection.py): it fits a LinearRegression to rank feature weights, renders a seaborn correlation heatmap, and benchmarks Linear Regression against a Decision Tree Regressor on an 80/20 train/test split.

📁 Project structure

File	Purpose
Home.py	Main window: file grid, CSV upload, launches child forms
FileDetails.py	"Data Statistics" window: profiling + cleaning actions
MissingData.py	Per-column missing-value strategies (mean/median/zero/RandomForest prediction)
NegativeData.py	Per-column negative-value strategies
FeatureSelection.py	Feature weights, correlation heatmap, model comparison
DBHelper.py	pyodbc helper (connection string + generic CRUD)
DataProcessingAPI.sql	SQL Server schema: tables and stored procedures
main.py	Standalone one-shot CSV cleaner (no database)
mainform.py, detailform.py	Earlier versions of the main/detail forms
LICENSE	GPL-3.0

⚠️ Notes / Limitations

• Windows-oriented: dataset paths use backslashes (datasets\...), the ODBC driver string is DRIVER={SQL Server}, and windows are maximised with root.state("zoomed").
• Database credentials are hardcoded in DBHelper.py.
• The checkboxes in main.py (Clean dataset, Handle outliers, Encode dataset) are displayed but not wired up — the same cleaning pipeline runs regardless.
• Feature selection and prediction expect fully numeric data; label-encode categorical columns first.
• Only regression models are wired in (Linear Regression, Decision Tree Regressor); metrics are printed to the console rather than shown in the UI.
• DataProcessingAPI.sql creates schema only — the Gen.SerialGenerator and Gen.Tasks seed rows must be inserted manually.

📄 License

This project is licensed under the GNU General Public License v3.0 — see LICENSE.