tidytable-core 1.0.1

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

tidytable

The Explicit Tabular Data Wrangling & Grid Normalization Ecosystem

---

Introduction & Philosophy

tidytable is a production-grade Python package offering fast, flexible, and explicitly isolated data cleaning pipelines. It is designed specifically for data engineering and consumer analytics workflows that regularly ingest chaotic, human-stylized spreadsheets (Excel/CSV) and need to transition them into strict, predictable, machine-ready data structures.

Unlike traditional automated or "black-box" data cleaning scripts that apply hidden transformations under the hood, tidytable enforces an explicit pipeline paradigm. Every logical operation is strictly isolated within single-responsibility sub-libraries. This ensures full algorithmic predictability, reproducibility, and a transparent data lineage from ingestion to audit.

---

Architectural Framework

Data moves through tidytable sequentially. Analysts construct pipelines by invoking specialized domain layers explicitly:

[ Human-Styled Raw Spreadsheet ] │ ▼ ┌───────────────────────┐ │ tidytable.xl │ ──► Normalizes grid layouts & structural offsets └───────────┬───────────┘ │ ▼ ┌───────────────────────┐ │ tidytable.structural │ ──► Standardizes tokens, labels, and text clusters └───────────┬───────────┘ │ ▼ ┌───────────────────────┐ │ tidytable.parse │ ──► Casts values safely into strict mathematical types └───────────┬───────────┘ │ ▼ ┌───────────────────────┐ │ tidytable.missing │ ──► Flags absence signals and isolates data voids └───────────┬───────────┘ │ ▼ ┌───────────────────────┐ │ tidytable.dedup │ ──► Resolves ledger conflicts and duplicate states └───────────┬───────────┘ │ ▼ ┌───────────────────────┐ │ tidytable.profile │ ──► Pins blueprints and executes schema guardrails └───────────┬───────────┘ │ ▼ [ Production-Ready DataFrame ]

---

Core Dependencies & Installation

tidytable relies on a highly optimized, C-accelerated foundational dependency matrix:

• pandas (>= 2.0.0) — Matrix manipulation and DataFrame vectorization.
• openpyxl (>= 3.1.0) — High-performance utility streams for modern .xlsx sheets.
• rapidfuzz (>= 3.0.0) — High-performance C-backed Levenshtein Distance string matching.
• python-dateutil (>= 2.8.2) — Dynamic international timestamp evaluation matrix.

To install the framework:

pip install tidytable-core

---

Deep-Dive Sub-Library Reference & Examples

---

1. Ingestion Grid Normalization (tidytable.xl)

Designed to resolve layout anomalies typical of human-designed worksheets, such as title blocks, KPI matrices, and unaligned data tables.

Functions:

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

• Explanation: Read an entire Excel workbook stream into memory, mapping each sheet to an accessible dictionary frame.

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

• Explanation: Detects merged formatting cell layout flaws and dynamically propagates the core value across the spanned boundaries.

• Parameters: strategy="ffill" (forward fill downwards) or "lfill" (lateral fill across columns).

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

• Explanation: Scans upper data regions to identify where metadata banners clear out and true column headers begin based on row non-null density metrics.

• split_multi_tables(sheet_data: pd.DataFrame) -> list[pd.DataFrame]

• Explanation: Slices complex layouts containing multiple independent data tables isolated by whitespace intervals into individual DataFrames.

Example:

import tidytable as tt

# Load raw spreadsheet
workbook = tt.xl.load_workbook("quarterly_report.xlsx")
sheet_matrix = workbook["Sales_Summary"]

# Locate the table starting index and its headers
header_idx, clean_headers = tt.xl.sniff_headers(sheet_matrix, scan_rows=15)

# Flatten structural cell merges across columns or rows
flattened_df = tt.xl.unmerge_and_fill(sheet_matrix, strategy="ffill")

# Break isolated multi-tables out into standalone blocks
tables_list = tt.xl.split_multi_tables(flattened_df)

---

2. Lexical & Structural Refinement (tidytable.structural)

Standardizes structural text labels and provides error-tolerant sorting.

Functions:

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

• Explanation: Translates irregular human column labels (e.g., Gross Income ($) !!) into clean, programming-friendly variables.

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

• Explanation: Vectorized stripping of leading/trailing padding, trailing tabs, and web-scraping artifacts like non-breaking unicode spaces (\xa0).

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

• Explanation: Maps variant spelling errors and data entry inaccuracies into a common target variable structure using fuzzy string matching.

Example:

import pandas as pd
import tidytable as tt

df = pd.DataFrame({
    "  Product SKU ! ": ["A1", "B2"],
    "Region_Log": ["United States", "U.S.A. "]
})

# Normalize headers into precise snake_case variables
df = tt.structural.rename_columns(df, style="snake_case") # -> ['product_sku', 'region_log']

# Cluster alternative spellings or data typos automatically
df["region_log"] = tt.structural.standardize_categories(df["region_log"], auto_cluster=True)

---

3. Type-Safe Ingestion Parsing (tidytable.parse)

Enforces mathematical type data alignment across mixed string inputs while tracking and preventing operational runtime faults.

Functions:

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

• Explanation: Parses alphanumeric text parameters containing bookkeeping metrics like currency symbols, text-based scales (K, M, B), or negative parenthesis arrays ($ (1,250.00)), turning them into standard float models.

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

• Explanation: Normalizes arbitrary, non-standardized timestamp configurations concurrently into safe ISO datetimes.

• boolean(series: pd.Series) -> pd.Series

• Explanation: Converts arbitrary localized text triggers (Yes/No, Y/N, 1/0, True/False) directly into absolute boolean arrays.

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

• Explanation: Restores dropped leading zeros from structural reference keys (e.g., matching numeric float identifiers like 401.0 back to code strings like "00401").

• handle_formula_ghosts(series: pd.Series, error_strategy: str = "coerce") -> pd.Series

• Explanation: Targets spreadsheet formula evaluation failures (#DIV/0!, #VALUE!, #REF!), replacing them with standard null entries (NaN).

Example:

import pandas as pd
import tidytable as tt

df = pd.DataFrame({
    "cost_basis": ["$ (1,500.50)", "25.5K", "#VALUE!"],
    "opened_at": ["2026-06-11", "Jan 12, 2026", ""],
    "store_id": [402.0, 501.0, np.nan]
})

df["cost_basis"] = tt.parse.handle_formula_ghosts(df["cost_basis"])
df["cost_basis"] = tt.parse.financials(df["cost_basis"])         # -> [-1500.5, 25500.0, NaN]
df["opened_at"] = tt.parse.dates(df["opened_at"])                 # -> Datetime64 structures
df["store_id"] = tt.parse.repair_identifiers(df["store_id"], pad_length=5) # -> ["00402", "00501", ""]

---

4. Missing Data & Signal Isolation (tidytable.missing)

Provides clear tracing methodologies to log missing elements before imputation updates modify the underlying distribution profiles.

Functions:

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

• Explanation: Removes vertical column parameters that exceed specified missing value density boundaries.

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

• Explanation: Appends companion binary trace indicator column paths ({column}_is_missing) to preserve the mathematical truth of missing positions before applying imputation.

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

• Explanation: Imputes empty elements safely using selected statistics ("mean", "median", "mode") or fallback strings.

Example:

import tidytable as tt

# Prune columns with more than 40% missing entries
df = tt.missing.drop_empty_cols(df, threshold=0.40)

# Flag missing data signals before modifying data fields
df = tt.missing.flag_absence(df, columns=["customer_age"])

# Execute targeted statistical profile filling
df["customer_age"] = tt.missing.impute(df["customer_age"], strategy="median")

---

5. Deduplication & Ledger Resolution (tidytable.dedup)

Controls database record aggregation rules to eliminate transaction double-counting across overlapping runs.

Functions:

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

• Explanation: Drops row entries only if they present identical values across all columns.

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

• Explanation: Resolves conflicting records for identical core identifiers by applying historical sorting sequences.

Example:

import tidytable as tt

# Remove absolute identical row twins
df = tt.dedup.absolute(df)

# Resolve multi-entry states by maintaining only the latest ledger item
df = tt.dedup.partial(df, subset=["user_id"], keep="latest", timestamp_col="updated_at")

---

6. Mapping & Joining Mechanics (tidytable.merge)

Executes relational mapping workflows across files even when integration identifiers are imperfect or misspelled.

Functions:

• fuzzy_vlookup(left_df: pd.DataFrame, right_df: pd.DataFrame, left_on: str, right_on: str, threshold: float = 0.85) -> pd.DataFrame

• Explanation: Merges datasets using accelerated Levenshtein string distance scoring arrays to match variant lookup keys.

• join_diagnose(left_df: pd.DataFrame, right_df: pd.DataFrame, left_on: str, right_on: str) -> dict

• Explanation: Evaluates alignment compatibility between key sets, summarizing structural alignment risks before a merge operation.

Example:

import tidytable as tt

# Evaluate join compatibility profile risks
risk_summary = tt.merge.join_diagnose(leads_df, master_vendor_df, left_on="vendor", right_on="v_name")

# Run error-tolerant join matching "Apple Inc." safely to "Apple, Inc."
merged_df = tt.merge.fuzzy_vlookup(leads_df, master_vendor_df, left_on="vendor", right_on="v_name", threshold=0.88)

---

7. File Reconcile & Iteration Tracking (tidytable.reconcile)

Tracks changes across file generations to maintain audit compliance tracking.

Functions:

• sheet_diff(df_old: pd.DataFrame, df_new: pd.DataFrame, key_column: str) -> dict[str, pd.DataFrame]

• Explanation: Extracts row-level changes between separate file generations, breaking mutations into clear tracking metrics.

• align_schemas(df_old: pd.DataFrame, df_new: pd.DataFrame) -> tuple[pd.DataFrame, pd.DataFrame]

• Explanation: Aligns matching column sequences between disparate file versions to ensure safe appending.

Example:

import tidytable as tt

# Compare performance delta tracking sheets
historical_delta = tt.reconcile.sheet_diff(df_old=jan_log, df_new=feb_log, key_column="uid")

print("New rows detected:", historical_delta["Added"])
print("Modified elements captured:", historical_delta["Modified"])

---

8. Schema Pinning & Auditing Guardrails (tidytable.profile)

Establishes structural validations to monitor pipeline transformations and prevent down-stream ingestion failures.

Functions:

• blueprint(df: pd.DataFrame) -> dict

• Explanation: Generates a structural footprint dictionary detailing shapes, precise types, and missing value counts.

• check_anomalies(df: pd.DataFrame) -> list[str]

• Explanation: Scans text fields to locate unhandled missing placeholder strings (e.g., "?", "n/a", "-").

• audit_report(df: pd.DataFrame, output: str = "cli") -> str

• Explanation: Generates comprehensive transformation ledger summaries for production environments.

• pin_schema(df: pd.DataFrame, path: str) -> None

• Explanation: Generates an unchangeable reference JSON structural map representing a pristine dataset configuration.

• validate(df: pd.DataFrame, schema_path: str) -> bool

• Explanation: Compares incoming data frames against pinned reference templates, throwing informative exceptions if layout changes or type drift occur.

Example:

import tidytable as tt

# Check for hidden string anomalies
alerts = tt.profile.check_anomalies(df)

# Validate incoming structural properties against an immutable target layout schema blueprint
if tt.profile.validate(df, schema_path="schemas/target_blueprint.json"):
    # Generate human-readable operational execution audit trails
    print(tt.profile.audit_report(df, output="cli"))

---

Complete End-to-End Operational Pipeline Example

This script illustrates how an analyst can chain tidytable sub-libraries sequentially to create an explicit data cleaning pipeline:

import pandas as pd
import tidytable as tt

def execute_ingestion_pipeline(file_destination: str, operational_schema: str) -> pd.DataFrame:
    # Layer 1: Normalizing messy layouts from an Excel workbook
    sheets = tt.xl.load_workbook(file_destination)
    raw_frame = sheets["Master_Log"]
    df = tt.xl.unmerge_and_fill(raw_frame, strategy="ffill")
    
    # Layer 2: Structural Column Standardizations
    df = tt.structural.rename_columns(df, style="snake_case")
    df["client_name"] = tt.structural.strip_whitespace(df["client_name"])
    
    # Layer 3: Type Safe Processing Boundaries
    df["serial_id"] = tt.parse.repair_identifiers(df["serial_id"], pad_length=6)
    df["net_revenue"] = tt.parse.handle_formula_ghosts(df["net_revenue"])
    df["net_revenue"] = tt.parse.financials(df["net_revenue"])
    df["transaction_date"] = tt.parse.dates(df["transaction_date"])
    
    # Layer 4: Quality Checks & Deduplication Rules
    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)
    
    # Layer 5: Final Schema Validation & Deployment Logs
    tt.profile.validate(df, schema_path=operational_schema)
    print(tt.profile.audit_report(df, output="cli"))
    
    return df

# Run pipeline explicitly
clean_dataset = execute_ingestion_pipeline("raw_factory_data.xlsx", "schemas/production_spec.json")