untanglepy

Transform irregular Pandas tables with explicit rules and positional lineage. Move embedded subheaders into columns, combine header rows, join split records, and propagate known groups without silently discarding conflicting values.

import pandas as pd
from untanglepy import untangle

raw = pd.DataFrame(
    {"item": ["REGION: NORTH", "Tea", "REGION: SOUTH", "Cocoa"], "value": [None, 10, None, 20]}
)
result = untangle(raw, group_column="item", group_pattern=r"^REGION:", group_name="region")
print(result.frame)  # NORTH / Tea / 10; SOUTH / Cocoa / 20
assert result.source_rows == ((0, 1), (2, 3))

Install

Python 3.11+ and Pandas 2.2+ are required. Until the initial PyPI publication is verified, install from a checkout:

python -m pip install .
# After the first verified PyPI release:
python -m pip install untanglepy

The package has no runtime network calls, telemetry or dependency on sibling projects.

Composable API

All functions accept a DataFrame or an earlier TransformResult, and return TransformResult. Pass the wrapper through a pipeline to preserve lineage; passing only .frame starts a new positional source. Input frames are never changed. Results use a fresh RangeIndex; original index labels can be arbitrary or duplicated. Keep the original input if you need to look up source values.

result.frame contains the output table. result.source_rows contains the original zero-based input row positions contributing to each output row, including context subheaders and group donors. result.header_sources records selected original heading-row positions for each output column, separately from record lineage. result.lineage() returns a table of output positions and source tuples. Source tuples are unique and sorted in original source order. Treat the exposed frame and provenance as snapshots; external frame mutation can invalidate their correspondence.

Rules and ambiguity

Structural blanks are scalar missing values or empty/whitespace-only strings. Zero and False are values. Subheaders match from the start of a string using Python re.match. A pattern with one capture extracts exactly that capture. Without a capture, the matched prefix is removed and the remainder stripped. More than one capture or an empty extracted group raises StructureError. Rows matching a subheader must have blank other fields, preventing accidental payload loss. Group column collisions also raise.

extract_subheaders drops fully blank rows by default; drop_empty=False retains them with lineage. Data preceding the first subheader gets group None; require_group=True rejects it. Matching context rows are removed intentionally. propagate_groups preserves leading blanks and all nonblank original values.

merge_headers accepts current positional rows in the order supplied. It omits blank pieces; horizontal propagation across each heading row requires explicit fill_horizontal=True. Every output column needs a nonblank heading. Generated duplicate names raise unless duplicate_names="suffix" is chosen. Suffixes are globally unique and reserve natural base names: A, A, A_2 becomes A, A_3, A_2. Input column labels must already be unique and flat; duplicate or MultiIndex labels raise with instructions to rename them explicitly.

join_broken_rows requires one actual boolean per current row. A leading continuation is rejected. Only the named text columns concatenate two occupied strings, with the exact separator supplied. Elsewhere, a value can fill a blank or repeat an equal scalar; incompatible occupied values raise before returning a result. Outputs use object dtype for joined/propagated columns to avoid rounding large integers or coercing original objects when a neighbor is missing. No original value is converted to text except explicitly merged heading pieces and selected string concatenation.

Five worked irregular tables

examples/irregular_tables.py contains executable input tables, transformation pipelines and expected outputs:

python examples/quickstart.py
python examples/irregular_tables.py

Existing tools and limitations

Pandas and pyjanitor already supply header promotion, filling, reshaping and cleaning primitives. untanglepy adds composable context provenance and explicit occupied-value conflict handling for irregular tables. It is inspired by unheadr, whose broader functionality includes spreadsheet annotation. See RESEARCH.md for the capability comparison and licenses.

Version 0.1.0 accepts in-memory Pandas tables. CSV, Excel and PDF readers remain the caller's responsibility. There is no automatic layout detection, OCR, rich cell formatting support or Polars backend. Rules assume the caller knows which rows are headers or continuations; sparse data is not guessed into a structure. Lineage records row contribution, not a complete cell-level edit history. Blank rows intentionally dropped and unused trailing subheaders do not create output records. Dtypes can become object while original scalar values are retained.

Validation and benchmark

python -m pip install -e ".[dev]"
python -m pytest --cov=untanglepy --cov-report=term-missing
python -m ruff check .
python -m mypy src
python benchmarks/benchmark.py --rows 20000 --repeat 5

Tests include five worked tables, duplicate indices, compound provenance, extension missing values, large integers, ambiguous conflicts, and generated order/value invariants. The benchmark checks output equivalence to an explicit Pandas subheader recipe, then measures five repetitions after warmup and a separate traced Python memory peak. Raw measurements contain environment and dispersion; methodology explains the additional validation/lineage cost. No performance advantage is assumed.

See CONTRIBUTING.md for development and release checks. MIT licensed original code. Proposed 0.2.0 work: explicit per-column conflict resolvers, per-cell lineage and bounded provenance for very large imports.