Metadata-Version: 2.4
Name: tidytable-core
Version: 1.0.2
Summary: An ecosystem-style explicit data cleaning framework for Excel and CSV pipelines.
Author-email: Aayush Vijay <aayushvj8699@gmail.com>
Project-URL: Homepage, https://github.com/aayushvijay/tidytable
Classifier: Programming Language :: Python :: 3
Classifier: License :: OSI Approved :: MIT License
Classifier: Operating System :: MacOS :: MacOS X
Classifier: Topic :: Scientific/Engineering :: Information Analysis
Requires-Python: >=3.9
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: pandas>=2.0.0
Requires-Dist: openpyxl>=3.1.0
Requires-Dist: python-dateutil>=2.8.2
Requires-Dist: rapidfuzz>=3.0.0
Dynamic: license-file


<div align="center">
  <img src="https://raw.githubusercontent.com/allisonhorst/palmerpenguins/main/man/figures/logo.png" alt="tidytable logo" width="160px">
  <h1>tidytable</h1>
  <h3><i>The Explicit Tabular Data Wrangling & Grid Normalization Ecosystem</i></h3>

[![PyPI version](https://img.shields.io/pypi/v/tidytable-core.svg?color=blue)](https://pypi.org/project/tidytable-core/)
[![Supported Python Versions](https://img.shields.io/pypi/pyversions/tidytable-core.svg)](https://pypi.org/project/tidytable-core/)
[![Code style: black](https://img.shields.io/badge/code%20style-black-000000.svg)](https://github.com/psf/black)
[![Testing Suite](https://img.shields.io/badge/tests-passing-brightgreen.svg)]()

</div>

---

##  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.

---

##  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")

```

