tidytable-core 1.0.0

An ecosystem-style explicit data cleaning framework for Excel and CSV pipelines.

Here is the raw Markdown block. You can copy everything inside this block and paste it directly into your README.md file:

# tidytable-core 🧹

An ecosystem-style, explicit data cleaning framework built for data analysts who bridge the gap between messy, human-formatted Excel/CSV spreadsheets and production-ready Python data structures.

Unlike "black-box" cleaning scripts that automatically change your underlying values, `tidytable` forces an **explicit pipeline paradigm**. Each transformation engine is completely decoupled, granting you absolute control and step-by-step data lineage tracking.

---

## 🚀 Installation & System Configuration

Install `tidytable-core` globally from the Python Package Index (PyPI):

```bash
pip install tidytable-core

Core External System Dependencies

• pandas: Core tabular dataframe manipulation engine.
• openpyxl: Memory-mapped engine for modern .xlsx workbook streams.
• python-dateutil: Dynamic flexible timestamp text resolution matrix parser.
• rapidfuzz: High-performance Levenshtein Distance string similarity evaluation index engine.

---

🏗️ Architectural Execution Pipeline

Data flows sequentially through your explicitly invoked processing domains:

[ Messy Spreadsheet File ]
            │
            ▼
┌───────────────────────┐
│     tidytable.xl      │ ──► Resolves unaligned human-formatted layout grids.
└───────────┬───────────┘
            │ (Tabular Data Stream)
            ▼
┌───────────────────────┐
│  tidytable.structural │ ──► Standardizes variable labels and strips whitespace.
└───────────┬───────────┘
            │
            ▼
┌───────────────────────┐
│    tidytable.parse    │ ──► Casts values safely into clean data types.
└───────────┬───────────┘
            │
            ▼
┌───────────────────────┐
│   tidytable.missing   │ ──► Drops empty panels and applies imputation profiles.
└───────────┬───────────┘
            │
            ▼
┌───────────────────────┐
│    tidytable.dedup    │ ──► Filters duplicate records safely.
└───────────┬───────────┘
            │
            ▼
┌───────────────────────┐
│   tidytable.profile   │ ──► Validates constraints and generates audit ledger logs.
└───────────┬───────────┘
            │
            ▼
  [ Pristine DataFrame ]

---

📚 Complete Sub-Library Blueprint Reference

1. tidytable.xl (The Excel Surgeon)

Extracts pristine data grids from visually stylized worksheets.

xl.load_workbook(file_path: str) -> dict[str, pd.DataFrame]

• Use: Loads an entire workbook into memory.
• Arguments: file_path (str): System destination path pointing to an Excel document.

xl.unmerge_and_fill(sheet_data: pd.DataFrame, strategy: str = "ffill") -> pd.DataFrame

• Use: Flattens merged cells and fills empty fields down or across so rows stay linked.
• Arguments:
• sheet_data (DataFrame): The input sheet table matrix.
• strategy (str): Direction constraint rule. "ffill" (forward fill down) or "lfill" (lateral fill across).

xl.sniff_headers(sheet_data: pd.DataFrame, scan_rows: int = 20) -> tuple[int, list[str]]

• Use: Skips title banners and KPI cards to find where the actual table headers start.
• Arguments: scan_rows (int): Search depth row index limit.

import tidytable as tt

sheets = tt.xl.load_workbook("sales_report.xlsx")
raw_data = sheets["Q2_Leads"]

# Sniff header index location and clean names
header_idx, headers = tt.xl.sniff_headers(raw_data, scan_rows=15)

# Unmerge and prop values down to form a database structure
df = tt.xl.unmerge_and_fill(raw_data, strategy="ffill")

---

2. tidytable.structural (The Text Blacksmith)

Cleans and standardizes column structures and text anomalies.

structural.rename_columns(df: pd.DataFrame, style: str = "snake_case") -> pd.DataFrame

• Use: Converts columns (like "Gross Profit (%)") into clean variables ("gross_profit").
• Arguments: style (str): Re-casing format rules. Default is "snake_case".

structural.strip_whitespace(series: pd.Series) -> pd.Series

• Use: Deep-strips leading/trailing spaces, tab breaks, and hidden non-breaking spaces (\xa0).

structural.standardize_categories(series: pd.Series, mapping: dict = None, auto_cluster: bool = False) -> pd.Series

• Use: Groups manual typos and naming variations into a single target category name.
• Arguments:
• mapping (dict): Manual dictionary rules map (e.g., {"USA": ["usa", "U.S.A.", "us"]}).
• auto_cluster (bool): Uses Levenshtein Distance to merge variations automatically.

df = tt.structural.rename_columns(df, style="snake_case")
df["product_name"] = tt.structural.strip_whitespace(df["product_name"])

# Merge regional text typos automatically using string distance clustering
df["region"] = tt.structural.standardize_categories(df["region"], auto_cluster=True)

---

3. tidytable.parse (The Type Whisperer)

Converts raw string text blocks into strict mathematical datatypes without crashing.

parse.dates(series: pd.Series, dayfirst: bool = False) -> pd.Series

• Use: Parses mixed date format variations in a single column into uniform ISO datetimes.

parse.financials(series: pd.Series) -> pd.Series

• Use: Extracts numeric values from accounting styles like "$ (1,250.00)" or "12K".

parse.repair_identifiers(series: pd.Series, pad_length: int = None) -> pd.Series

• Use: Restores dropped leading zeroes on data codes (e.g., converts float 401.0 back to "00401").

df["invoice_date"] = tt.parse.dates(df["invoice_date"], dayfirst=False)
df["net_revenue"] = tt.parse.financials(df["net_revenue"])
df["zip_code"] = tt.parse.repair_identifiers(df["zip_code"], pad_length=5)
df["roi_metric"] = tt.parse.handle_formula_ghosts(df["roi_metric"], error_strategy="coerce")

---

4. tidytable.missing (The Ghost Hunter)

Identifies and resolves gaps in data matrices.

missing.drop_empty_cols(df: pd.DataFrame, threshold: float = 0.50) -> pd.DataFrame

• Use: Drops column attributes where the missing values ratio exceeds the threshold boundary limit.

missing.flag_absence(df: pd.DataFrame, columns: list[str]) -> pd.DataFrame

• Use: Appends a binary companion indicator column ({column}_is_missing) to keep the data signal before running imputation.

missing.impute(series: pd.Series, strategy: str = "median") -> pd.Series

• Use: Fills null voids based on chosen statistical parameters ("mean", "median", "mode").

df = tt.missing.drop_empty_cols(df, threshold=0.40)
df = tt.missing.flag_absence(df, columns=["customer_age"])
df["customer_age"] = tt.missing.impute(df["customer_age"], strategy="median")

---

5. tidytable.dedup (The Twin Eliminator)

Detects and drops duplicate entries across your rows.

dedup.absolute(df: pd.DataFrame) -> pd.DataFrame

• Use: Drops rows only if they match exactly across every single field.

dedup.partial(df: pd.DataFrame, subset: list[str], keep: str = "latest", timestamp_col: str = None) -> pd.DataFrame

• Use: Resolves record updates by keeping the earliest or latest transaction entry for a unique key.

# Clear absolute identical rows
df = tt.dedup.absolute(df)

# For matching customer IDs, keep only the record with the most recent update timestamp
df = tt.dedup.partial(df, subset=["customer_id"], keep="latest", timestamp_col="updated_at")

---

6. tidytable.merge (The VLOOKUP Bridge)

Joins separate files together even when keys are messy, incomplete, or slightly misspelled.

# Identify mismatched elements before executing joins
pre_flight = tt.merge.join_diagnose(left_df=leads_df, right_df=master_df, left_on="vendor", right_on="v_name")

# Join matching rows even if there are typos (e.g., matches "Apple Inc." to "Apple, Inc.")
joined_df = tt.merge.fuzzy_vlookup(leads_df, master_df, left_on="vendor", right_on="v_name", threshold=0.88)

---

7. tidytable.reconcile (The Ledger Auditor)

Automates version control checks between separate instances of the same file structure.

# Generate structural audits comparing January data against February data
ledger_updates = tt.reconcile.sheet_diff(df_old=jan_df, df_new=feb_df, key_column="transaction_id")

print("New rows added this month:", len(ledger_updates["Added"]))
print("Row modifications captured:", len(ledger_updates["Modified"]))

---

8. tidytable.profile (The Auditor & Schema Guard)

Handles file schema pinning, anomaly detection, and automated audit trails.

# Scan for raw strings masking null values (e.g., "?", "n/a", "-")
anomalies = tt.profile.check_anomalies(df)

# Validate current file structure against last month's blueprint to make sure scripts don't crash
if tt.profile.validate(df, schema_path="schemas/prod_blueprint.json"):
    # Output file pipeline performance audit change log metrics
    print(tt.profile.audit_report(df, output="cli"))

---

🎯 Complete End-to-End Explicit Analyst Workflow

Here is a complete real-world script showing how an analyst runs a detailed cleaning pipeline manually:

import tidytable as tt
import pandas as pd

# Step 1: Layout Normalization
workbook = tt.xl.load_workbook("raw_factory_data.xlsx")
sheet_grid = workbook["Master_Log"]
df = tt.xl.unmerge_and_fill(sheet_grid, strategy="ffill")

# Step 2: Structural Column Cleaning
df = tt.structural.rename_columns(df, style="snake_case")
df["part_name"] = tt.structural.strip_whitespace(df["part_name"])

# Step 3: Type Safe Parsing
df["serial_id"] = tt.parse.repair_identifiers(df["serial_id"], pad_length=6)
df["cost"] = tt.parse.financials(df["cost"])
df["log_date"] = tt.parse.dates(df["log_date"])

# Step 4: Integrity and Row Refinement
df = tt.missing.flag_absence(df, columns=["efficiency_score"])
df["efficiency_score"] = tt.missing.impute(df["efficiency_score"], strategy="mean")
df = tt.dedup.absolute(df)

# Step 5: Verification & Schema Pinning
if tt.profile.validate(df, schema_path="schemas/factory_spec.json"):
    df.to_csv("clean_factory_data.csv", index=False)
    print(tt.profile.audit_report(df, output="cli"))

---

⚖️ License

Distributed under the MIT License. See LICENSE for details.