data-cleaning-skill

<div align="center"> # Tool List **Industrial-grade Data Cleaning Pipeline for AI Agents** [](https://www.python.org/) [](LICENSE) [](https://github.com/lytssaa/data-cleaning-skill) [](https://github.com/lytssaa/data-cleaning-skill) Let AI clean dirty data in one sentence. Automatically identify missing values, outliers, convert full-width to half-width, supporting 15 file formats. </div> --- ## Table of Contents - [Features](#features) - [Quick Start](#quick-start) - [Installation](#installation) - [Pipeline Architecture](#pipeline-architecture) - [Parameter Description](#parameter-description) - [Three-layer API](#three-layer-api) - [Output Structure](#output-structure-data-product-package) - [AI Semantic Output](#ai-semantic-output) - [Usage Examples](#usage-examples) - [Design Principles](#design-principles) - [License](#license) --- ## Features | Capability | Description | |------|------| | 📂 **15 Formats** | CSV / TSV / Excel / JSON / Parquet / Feather / XML / YAML / HTML / SQLite / Pickle | | 🔍 **Semantic Annotation Layer** | `invalid` (→NaN) / `suspicious` (retain + mark) / `sentinel` (placeholder → NaN) three independent processing | | 📊 **AI-readable Output** | Each execution returns `semantic_output`: one-sentence summary + quality score + insights + recommendations | | 🔧 **Seven-stage Pipeline** | Read → Standardize → Type Alignment → Semantic Annotation → Missing Handling → Outlier Suppression → Semantic Output | | 📦 **Data Product Package** | Cleaning results automatically packaged into five-layer structure (data / report / lineage / samples / metadata) | | 🤖 **Multi-platform Compatibility** | WorkBuddy / MiMo Code / Claude Code / AtomCode / Claude Desktop (MCP) | --- ## Quick Start ```python from scripts.clean import DataPipelineCleaner cleaner = DataPipelineCleaner() # Basic usage cleaned_df, audit = cleaner.execute( "data.csv", schema_rules={"age": "int", "salary": "float"}, ) # v2 complete parameters (recommended) cleaned_df, audit = cleaner.execute( "data.csv", schema_rules={"age": "int", "salary": "float"}, semantic_rules={"age": {"invalid": [-5, -1], "suspicious": [150]}}, missing_rules={"salary": {"sentinel": [-999]}}, outlier_rules={"salary": {"method": "iqr"}}, ) # Read AI semantic output so = audit["semantic_output"] print(so["summary"]) # "Cleaning 10000 rows → 9850 rows (98.5% retention)..." print(so["data_quality_score"]) # 89 ``` --- ## Installation ### WorkBuddy ```bash workbuddy skill install https://github.com/lytssaa/data-cleaning-skill ``` ### MiMo Code ```bash git clone https://github.com/lytssaa/data-cleaning-skill.git ~/.config/mimocode/skills/data-cleaning ``` ### Claude Code ```bash git clone https://github.com/lytssaa/data-cleaning-skill.git ~/.claude/skills/data-cleaning ``` ### AtomCode ```bash git clone https://github.com/lytssaa/data-cleaning-skill.git ~/.atomcode/skills/data-cleaning ``` ### Claude Desktop (MCP Mode) **Step 1: Install dependencies** ```bash pip install mcp pandas pyarrow openpyxl xlrd ``` **Step 2: Edit `claude_desktop_config.json`** ```json { "mcpServers": { "data-cleaning": { "command": "python", "args": ["adapters/claude/server.py"], "cwd": "/your/installation/path/data-cleaning" } } } ``` ### General dependencies (non-MCP platforms) ```bash pip install pandas pyarrow openpyxl xlrd ``` --- ## Pipeline Architecture <p align="center"> <img src="assets/pipeline.png" alt="Data Cleaning Pipeline Architecture"> </p> | Phase | Stage | Core Operations | |-------|------|---------| | 0 | Data Reading | CSV/Excel → `dtype=str` (zero inference) · Parquet/Feather retain native types | | 1 | Standardization | Column names → snake_case (NFKC) · Ghost characters (`\u200b` `\ufeff`) removal | | 2 | Type Alignment | `schema_rules`: str → int/float/datetime · `errors='coerce'` → NaN | | 3 | Semantic & Missing Handling | `invalid` → NaN · `suspicious` → retain + mark · `sentinel` → NaN · median/mode imputation | | 4 | Outlier Suppression | per-column: iqr / percentile / zscore / none · Only truncate, not delete rows | | 5 | AI Semantic Output | summary · data_quality_score · insights · recommendations | --- ## Parameter Description ### `execute()` Parameters | Parameter | Type | Description | |------|------|------| | `schema_rules` | dict | Column → type mapping (`"int"` / `"float"` / `"str"` / `"datetime"`) | | `semantic_rules` | dict | Semantic annotation: `invalid` (→NaN) / `suspicious` (retain + mark) | | `missing_rules` | dict | Sentinel value handling: `sentinel` (→NaN, separate from semantic layer) | | `business_rules` | dict | Old version: `replace_values` + `fill` keywords | | `outlier_rules` | dict | per-column strategy: `{"column": {"method": "iqr"}}` | | `outlier_method` | str | Global strategy: `iqr` / `percentile` / `zscore` / `none` | | `iqr_k` | float | IQR sensitivity (default 1.5) | | `ingestion_config` | dict | `db` (table name) / `expand_nested` (expand nested JSON) / `expected_min_rows` | | `engine_kwargs` | dict | SQLite table name selection | ### Semantic Layer Description | Concept | Layer | Handling | Applicable Scenarios | |------|--------|---------|---------| | `invalid` | semantic_rules | Value → NaN (obviously wrong) | `age = -5`, impossible values | | `suspicious` | semantic_rules | Value **retain**, mark for review | `age = 150`, abnormal but not excluded | | `sentinel` | missing_rules | Value → NaN (missing placeholder) | `-999`, `9999` such filling codes | > **Why sentinel must be independent?** sentinel is "no data", not "wrong data", processing path is essentially different: semantic invalid → fix data; missing sentinel → complete blank. ### Outlier Method Comparison | Method | Applicable Scenarios | Parameters | |------|---------|------| | `"iqr"` | Normal/symmetric distribution (height, weight, age) | `iqr_k` (default 1.5) | | `"percentile"` | Moderately long-tailed, truncate 0.5% head and tail | `outlier_threshold` (default 0.995) | | `"zscore"` | Known normal distribution | `zscore_threshold` (default 3.0) | | `"none"` | Long-tailed data (salary, revenue) or ID columns | — | --- ## Three-layer API ```python # Layer 1: Pipeline execution (only cleaning, no file writing) cleaned_df, audit = cleaner.execute("data.csv", schema_rules={...}) # Layer 2: Data product package construction (pure memory, zero I/O) bundle = cleaner.build_artifacts(cleaned_df, audit, source_stem="data") # Layer 3: Disk storage artifacts = cleaner.save_artifacts(bundle, output_dir="cleaned_data/") # Old version integration (internal call build + save) cleaner._save_cleaned_output(subdir, stem, cleaned_df, audit) ``` --- ## Output Structure (Data Product Package) Each dataset cleaning generates a complete **five-layer data product package**: ``` cleaned_data/ ├── fitness/ │ ├── data/ │ │ ├── fitness.csv ← Cleaned data (CSV) │ │ └── fitness.parquet ← Cleaned data (Parquet, industrial standard) │ ├── report/ │ │ ├── audit.json ← Complete audit log │ │ └── data_quality.json ← Quality scorecard (score + insights + recommendations) │ ├── lineage/ │ │ └── transformations.json ← Data lineage (every step transformation record) │ ├── samples/ │ │ ├── before_sample.csv ← Before cleaning first 5 rows │ │ └── after_sample.csv ← After cleaning first 5 rows │ └── metadata.json ← Control layer (row count, retention rate, timestamp) ├── hotels/ │ └── ... (same five-layer structure) └── _summary.json ← Global summary ``` | Layer | File | Purpose | User | |------|------|------|--------| | `data/` | `*.csv` + `*.parquet` | Clean data | Downstream system | | `report/audit.json` | Audit log | Every stage operation details | Development debugging | | `report/data_quality.json` | Quality scorecard | Total score + three-dimensional analysis + AI insights | AI reading report | | `lineage/` | Data lineage | Transformation path record | Data governance / compliance | | `samples/` | Before and after comparison | Cleaning before and after 5 rows each | Human quick verification | | `metadata.json` | Control layer | Source file + row count change + timestamp | Automated pipeline | --- ## AI Semantic Output Each `execute()` will return AI-readable structured summary in `audit["semantic_output"]`: ```json { "summary": "Cleaning 10000 rows → 9850 rows (98.5% retention). Fixing 423 missing values, suppressing 37 outliers.", "data_quality_score": 89, "insights": [ "salary column missing rate is high (12%)", "3% of age values are marked as invalid" ], "actions_taken": [ "Row 5, age: -5 → NaN (age.invalid)", "salary: 12 missing values → median imputation" ], "recommendations": [ "Suggest human review suspicious marked 150 records", "Check salary data collection process, missing rate is abnormally high" ] } ``` | Field | Type | Description | |------|------|------| | `summary` | str | One-sentence summary (AI directly reads to user) | | `data_quality_score` | int 0–100 | Data health score | | `insights` | list[str] | Key findings | | `actions_taken` | list[str] | Specific operation records | | `recommendations` | list[str] | Subsequent suggestions / human review prompts | --- ## Usage Examples ### Basic Cleaning ```python from scripts.clean import DataPipelineCleaner cleaner = DataPipelineCleaner() cleaned_df, audit = cleaner.execute( "employees.csv", schema_rules={"age": "int", "salary": "float", "join_date": "datetime"}, ) print(audit["semantic_output"]["summary"]) ``` ### Complete Parameters (with Semantic Rules) ```python cleaned_df, audit = cleaner.execute( "survey.xlsx", schema_rules={"age": "int", "income": "float"}, semantic_rules={ "age": {"invalid": [-5, -1, 0], "suspicious": [150]}, "rooms": {"invalid": [-1], "suspicious": [0]}, }, missing_rules={ "income": {"sentinel": [-999]}, "membership_years": {"sentinel": [9999]}, }, outlier_rules={ "age": {"method": "iqr"}, "income": {"method": "percentile", "threshold": 0.995}, }, ) ``` ### SQLite Database ```python cleaned_df, audit = cleaner.execute( "records.db", schema_rules={"price": "float"}, ingestion_config={"db": {"table": "orders"}}, ) ``` ### Save Data Product Package ```python cleaned_df, audit = cleaner.execute("data.csv", schema_rules={"age": "int"}) bundle = cleaner.build_artifacts(cleaned_df, audit, source_stem="data") artifacts = cleaner.save_artifacts(bundle, output_dir="cleaned_data/") print(artifacts) # All output file paths ``` --- ## Built-in Scripts | Script | Purpose | |------|------| | `scripts/clean.py` | Core `DataPipelineCleaner` class | | `scripts/profile.py` | Quick data overview (support all 15 formats) | | `scripts/quality_report.py` | Render audit JSON to Markdown quality report | | `adapters/claude/server.py` | Claude Desktop MCP adapter | --- ## Design Principles 1. **AI Decision, Python Execution** — AI responsible for data analysis and rule generation, Python pipeline deterministic execution 2. **No Silent Data Loss** — Every deletion has log records 3. **No Trust Type Inference** — CSV/Excel all read in `str`, then explicit conversion 4. **No Delete Abnormal Rows** — Only truncate abnormal values to fence boundary, retain row integrity 5. **Business Rules Prioritize Statistical Rules** — `height=0` first handled by `replace_values`, then IQR 6. **Semantic Separation** — `invalid`/`suspicious` (semantic layer) and `sentinel` (missing layer) processing paths completely independent 7. **AI Native Output** — Each execution returns AI-readable structured summary 8. **Type-aware Imputation** — Classification columns use mode imputation, numerical columns use rounded median, no random fill `"Unknown"` --- ## License MIT © 2026 [lytssaa](https://github.com/lytssaa)