datakit

Toolkit for pandas + DuckDB ETL pipelines on messy administrative data: read raw xlsx/csv the safe way, clean and pin a schema, validate, write zstd parquet for BigQuery or DuckDB, match names across files, draw maps — with every value lost or coerced reported in your log file.

Built for Peruvian education and registry data (DNI, código modular, padrón), with Peru-friendly defaults (dayfirst=True, SI/NO flags, 8-digit DNI checks) that are all overridable.

En español: instala con pip install alvaro-datakit (trae todo lo necesario; para importar se usa import datakit). La guía completa en español, docs/GUIA.md, viene en el código fuente: en esta página, Download files → Source Distribution (.tar.gz), o con el comando de User guide / Guía de uso más abajo. El Quickstart es un script completo (comentarios en inglés). Ayuda de cualquier función: import datakit; help(datakit.clean_id_col). El asistente de Matcher pregunta en inglés: y = sí, n = no, s = saltar (¡no es "sí"!), q = terminar y guardar.

Install

Published on PyPI as alvaro-datakit; the import name stays datakit (the PyPI name datakit belongs to an unrelated package, so never pip install datakit bare). Requires Python 3.10+.

pip install alvaro-datakit

That is all: pip installs every library datakit needs, so Excel files, Google Sheets, name matching and maps work right away.

In your own project, pin it like any other dependency, e.g. a requirements.txt line alvaro-datakit>=2.1.0. Old install lines with extras ([excel], [gsheet], [matching], [maps]) still work and add nothing; [dev] adds pytest, for the test suite.

import datakit                     # the distribution name is only for pip
from datakit import clean_str_cols, read_raw

Upgrading from 2.0.0 (installed from git or editable as datakit)? Run pip uninstall datakit before installing alvaro-datakit: both distributions own the datakit/ folder. Check with pip show datakit; if import datakit fails after removing one of them, repair it with pip install --force-reinstall --no-deps alvaro-datakit.

User guide / Guía de uso

A recipe-style walkthrough of every module ships in the source archive: docs/GUIDE.md (English) and docs/GUIA.md (español). Get it from this page, Download files → Source Distribution (alvaro_datakit-2.1.0.tar.gz), or from a terminal:

pip download alvaro-datakit==2.1.0 --no-deps --no-binary alvaro-datakit -d .
tar -xzf alvaro_datakit-2.1.0.tar.gz

The guides land in alvaro_datakit-2.1.0/docs/.

Every public function also documents itself, with its arguments and an example: help(datakit.clean_id_col).

Design principles

• Cleaners coerce and report, never halt — bad values become NA and are logged; use the assert_* guards when you want a hard stop. I/O helpers (read_raw, require_files) do raise early, with a readable message, on a missing or wrong file.
• Mutate and return — DataFrame cleaners modify in place and return the frame, so both imperative and df.pipe(...) styles work.
• Diagnostics through logging — call setup_logging() once per script and every datakit warning/report lands in your console and your persistent log file. datakit itself never prints (except the Matcher wizard's prompt).
• One set of text rules — column names, values, boolean tokens and join keys share one Unicode normalization (accents, invisible spaces). Null tokens like 'N/A' / 'S/D' are counted by junk_report and turned into NA by canonicalize_str / canonicalize_cols=; plain clean_str_cols keeps them as text, so nothing disappears unseen.

Quickstart

A complete script: one attendance export, cleaned, checked against the padrón, and written to parquet + xlsx.

from pathlib import Path
import pandas as pd
from datakit import (
    setup_logging, step_timer, require_files, latest_file, read_raw,
    clean_col_names, clean_str_cols, clean_id_col, cast_date_cols,
    assert_unique_key, fk_report, reconcile_counts, write_outputs,
    duckdb_dup_report, assert_union_compatible,
)

RAW_DIR = Path('data/raw')                             # <- your paths
OUT_DIR = Path('data/processed')
PADRON_FILE = OUT_DIR / 'padron.parquet'
log = setup_logging(__name__, log_file=OUT_DIR / '_diagnostics/etl.log')

require_files({'padron': PADRON_FILE, 'raw dir': RAW_DIR},
              hint='Run the padron pipeline first.')
RAW_FILE = latest_file(RAW_DIR, 'asistencia_*.xlsx')   # newest snapshot of ONE source
# The greatest NAME wins: date your files yyyy-mm-dd, or pass by='mtime'.
# Several sources (one export per UGEL)? Loop over them instead: see the guide.
with step_timer('load + clean'):
    df = read_raw(RAW_FILE)            # every column as text: leading zeros survive
    # (title rows above the header, as in some reports? read_raw(RAW_FILE, header=N))
    clean_col_names(df)                # ' DNI ' -> 'dni', 'Cod Mod' -> 'cod_mod'
    clean_str_cols(df, lower_cols='correo')            # emails lower, the rest UPPER
    df = df.dropna(how='all')                          # blank spreadsheet rows
    df['dni'] = clean_id_col(df['dni'], 8)             # digits + zfill + zeros->NA
    df['cod_mod'] = clean_id_col(df['cod_mod'], 7, max_pad=1)   # 2+ lost zeros: warned
    cast_date_cols(df, 'fecha', format='%d/%m/%Y')     # typed dd/mm text; Excel date cells too

padron = pd.read_parquet(PADRON_FILE)
assert_unique_key(padron, ['cod_mod', 'anexo'])        # raises if the merge could fan out
fk_report(df['cod_mod'] + '-' + df['anexo'],           # orphans, on the same key as the merge
          padron['cod_mod'] + '-' + padron['anexo'], 'cod_mod+anexo')
final = df.merge(padron, on=['cod_mod', 'anexo'], how='left')
reconcile_counts(len(df), len(final), 'enriched rows') # a 1:1 merge keeps the row count

write_outputs(final, parquet=OUT_DIR / 'asistencia_2026.parquet',
              xlsx=OUT_DIR / 'asistencia_2026.xlsx')
# Before stacking yearly outputs in DuckDB / BigQuery:
duckdb_dup_report(OUT_DIR / 'asistencia_2026.parquet', ['dni', 'fecha'])
prev = OUT_DIR / 'asistencia_2025.parquet'             # last year's output, if any
if prev.exists():
    assert_union_compatible(prev, OUT_DIR / 'asistencia_2026.parquet')
log.info('done: %s rows', f'{len(final):,}')

Paths are relative to the folder you run the script from. A run leaves data/processed/asistencia_2026.parquet (zstd, IDs as text), asistencia_2026.xlsx (a copy to look at) and appends to data/processed/_diagnostics/etl.log the same lines it shows in the console, for example:

2026-03-31 09:00:00 — INFO — read_raw: asistencia_2026-03-31.xlsx (xlsx, calamine) -> 5 rows x 6 cols
2026-03-31 09:00:00 — INFO — [dni] 1 garbage values (no digits / all zeros) -> NA.
2026-03-31 09:00:00 — INFO — [fecha] 3 values were ISO text (Excel date cells), accepted alongside format='%d/%m/%Y'.
2026-03-31 09:00:00 — WARNING — [cod_mod+anexo] 1 of 4 keys (25.0%) have no match in the parent (0 null). Top orphans: {'0999999-0': 1}
2026-03-31 09:00:00 — INFO — Reconciliation OK: 4 enriched rows.

An error inside a step_timer block is logged with its traceback; one outside it (a require_files failure, say) only reaches the console.

Module map

Everything imports flat from the package root (from datakit import read_raw) except maps: from datakit.maps import MapConfig, map_clustered. The root also exports the defaults you may extend: COMPACT_NULL_SENTINELS, JUNK_SENTINELS, FEATURES, DEFAULT_WEIGHTS, INITIAL_CREDIT, INITIAL_CAP.

Name matching, dedup and record linkage

datakit.matching covers fuzzy person/institution matching (rapidfuzz, installed with datakit, speeds it up without changing any score):

from datakit import dedupe_names, match_names, link_records, Matcher

# legacy, bridge: any two DataFrames with a 'nombre' column (e.g. last year's
# roster and this year's export; legacy also has 'centro_educativo' and 'dpto'
# for step 3); padron: one with 'cen_edu' and 'd_dpto'.

# 1) Standardize variants within ONE column
#    'John Smith' / 'Jon Smith' / 'Smith, John' -> one cluster, one canonical
#    (an initial, 'J. Smith', scores at most 0.89 and stays out at 0.9: by design)
clusters = dedupe_names(legacy['nombre'], threshold=0.9)
legacy['nombre_std'] = clusters['canonical']

# 2) Fuzzy-match names across two files (token blocking, never all N x M)
matches = match_names(legacy['nombre'], bridge['nombre'], threshold=0.85)
# -> left_idx, right_idx, left, right, score, margin (small margin = near tie: review it)
legacy.loc[matches['left_idx'], 'nombre_bridge'] = (
    bridge.loc[matches['right_idx'], 'nombre'].to_numpy())

# 3) Record linkage with exact constraints (same dpto must agree when present)
links = link_records(legacy, padron,
                     left_col='centro_educativo', right_col='cen_edu',
                     exact_cols=[('dpto', 'd_dpto')])

# 4) The training wizard — learns "same person?" from your y/n answers
#    (active learning: asks about the most uncertain pairs first)
matcher = Matcher()
matcher.train_interactive(legacy['nombre'], bridge['nombre'],
                          save_path='matcher_nombres.json')
#    answer y = same, n = different, s = SKIP (not "si"), q = stop and save
assert matcher.trained          # needs at least 2 'y' and 2 'n' answers
matches = match_names(legacy['nombre'], bridge['nombre'],
                      matcher=matcher, threshold=0.5)   # trained scores = probabilities

# Next session: load it and pass it as matcher=, or call train_interactive again
# to add answers (pairs already labeled are not asked again).
matcher = Matcher.load('matcher_nombres.json')

Built-in handling, no rules to write: typos (JON/JOHN), initials (J SMITH), 'Smith, John' comma order, token order, missing middle names — while JANE SMITH vs JOHN SMITH stays under the default cut. Caveat: initials are inherently ambiguous (J SMITH fits both John and Jane), so a match that relies on one is capped below auto-accept; the wizard learns how much your data should trust them.

Scale: comfortable in the tens of thousands of names. For millions of rows or multi-field probabilistic linkage, step up to splink or dedupe — this module covers the everyday cases without their setup cost.

Stability

2.0.0 was the first shared release; 2.1.0 is the first on PyPI: compatible with 2.0.0 (one new option, clean_id_col(max_pad=)), bug fixes, the distribution rename, and every library now a required dependency. Two date fixes change results: cast_date_cols without format= now reads ISO date text without swapping day and month, and text that mixes UTC offsets (or offsets and naive values) now has its offset-carrying values converted to UTC and is stored without time zone, instead of raising or, on pandas 2.x, giving naive values a neighbour's offset — see the changelog. Everything exported from datakit is the public contract: signatures, return keys, output dtypes, default values and log-vs-raise behaviour change only with a major version bump and a CHANGELOG.md entry (in the source archive). Names that start with _ are internal. Migrating from datakit's internal predecessor, mytools: see the 1.0.0 entry of the changelog.

Argument shapes

Every parameter that names columns or tokens — cols, lower_cols, true_set, sentinels, exact_cols, … — accepts a single string or any collection of strings (list, tuple, set, df.columns). A bare string is one name, never a sequence of characters. One exception: in link_records(exact_cols=) a tuple is always one (left_col, right_col) pair, so pass several same-named columns as a list: exact_cols=['dpto', 'prov'].

Tests

From the unpacked source archive (see User guide above), with the dependencies installed. With pytest (pip install "alvaro-datakit[dev]"):

python -m pytest

Or the same suite without pytest (log lines during the run are expected; the last line is the verdict):

python tests/run_tests.py