ahenaor/colombia-congreso-2026-escrutinio

Consolidación del archivo de escrutinio – Congreso de Colombia 2026

Documento técnico que describe, de forma granular, el origen de los datos, el problema de tamaño/manejabilidad que representaban, y el proceso de ingeniería de datos aplicado para consolidarlos en un formato compacto y eficiente.

Fecha del ejercicio: 2026-09-19

1. Origen de los datos

Los datos utilizados en este proyecto provienen del Observatorio Electoral de la Registraduría Nacional del Estado Civil de Colombia:

• —URL: https://observatorio.registraduria.gov.co/views/electoral/historicos-resultados.php
• —Sección específica: "Visor histórico de resultados electorales"

Según la propia descripción del visor:

"Este visor muestra las estadísticas de los resultados electorales, preconteo, escrutinio y los archivos planos de las votaciones con información detallada por departamento, municipio, zona, puesto, mesa y candidato o agrupación política para su descarga. Para la lectura de este histórico se ha puesto a su disposición una guía que le permitirá conocer la información a la que puede acceder en cada uno de los botones habilitados."

Desde este visor se descargó el paquete de datos correspondiente a las elecciones de Congreso de Colombia 2026, el cual viene compuesto por múltiples archivos con distintos niveles de granularidad y propósito:

1.1. Archivos básicos (metadata / catálogos de referencia)

Ubicados en data/MMV_CONGRESO_2026/ArchivosBasicos_Dia electoral_congreso2026/:

1.2. Archivos de preconteo

Ubicados en data/MMV_CONGRESO_2026/mmvPRECONTEOCongreso2026/: SEN_MMV_9999.txt (Senado), CAM_MMV_9999.txt (Cámara), CTP_MMV_9999.txt, CNS_MMV_9999.txt, más su respectivo HASH.txt. Son los resultados preliminares reportados el día de la elección, mesa a mesa.

1.3. Archivo de escrutinio (el foco de este ejercicio)

Ubicado en data/MMV_CONGRESO_2026/mmvESCRUTINIOCongreso2026/MMV_9999.csv, junto con su HASH_MMV_9999.txt de verificación. Este es el archivo de resultados oficiales y definitivos (escrutinio), con el máximo nivel de detalle posible: un registro por cada combinación de mesa de votación × partido/lista × candidato, para las corporaciones de Senado y Cámara, a nivel nacional.

Adicionalmente se descargó el documento Estructuras Basicas (1808).pdf, que especifica de forma oficial el layout de ancho fijo (posición y longitud exacta de cada campo) de todos los archivos anteriores. Este documento fue la fuente de verdad para escribir los parsers, en lugar de inferir las posiciones por ensayo y error.

2. El problema: un archivo demasiado grande y granular para trabajar directamente

El archivo MMV_9999.csv (escrutinio) tiene las siguientes características, verificadas directamente sobre el archivo:

• —Tamaño en disco: 9.08 GB (un único archivo .csv plano).
• —180,592,545 filas (más de 180 millones de registros).
• —41,679,780 votos totales sumados en todo el archivo (línea base usada para validar que no se perdiera ni duplicara información durante el proceso).
• —Contiene todas las corporaciones, circunscripciones, partidos, candidatos, departamentos, municipios, zonas, puestos y mesas del país en un solo archivo, separado por ;, sin encabezado, de ancho fijo por campo mezclado con delimitador.

Con este tamaño y esta granularidad (registro a nivel de mesa × partido × candidato), el archivo es:

• —Difícil de transportar: 9+ GB no es trivial de compartir, subir a un repositorio, adjuntar o mover entre sistemas.
• —Difícil de cargar en memoria: intentar leerlo completo con pandas.read_csv() de forma ingenua puede agotar la RAM de una máquina de escritorio típica.
• —Difícil de manipular/analizar: cualquier filtro o agregación sobre un .csv plano de este tamaño implica leer el archivo completo línea por línea cada vez, sin índices, sin compresión columnar y sin tipos de datos explícitos.
• —Poco expresivo por sí solo: solo trae códigos numéricos (departamento, partido, candidato, etc.), sin nombres ni descripciones — para interpretarlo hay que cruzarlo manualmente contra los archivos básicos cada vez.

Esto motivó el ejercicio de ingeniería de datos: transformar ese archivo plano, pesado y codificado, en un dataset consolidado, enriquecido y liviano, apto para análisis repetidos sin tener que reprocesar el CSV crudo cada vez.

3. Objetivo del proceso de consolidación

Construir, en un notebook dedicado (consolidar_escrutinio.ipynb), un pipeline que:

1. 1.Lea el archivo de escrutinio completo, sin filtrar por partido o candidato (a diferencia del análisis exploratorio inicial en dev.ipynb, que se enfocaba en una sola candidata).
2. 2.Lo enriquezca con los archivos básicos oficiales, agregando nombres de departamento, municipio, puesto, partido, candidato, corporación y circunscripción.
3. 3.Valide matemáticamente que el enriquecimiento no altera la información original (mismo número de filas, misma suma de votos).
4. 4.Optimice el tipado de las columnas numéricas (mesa, votos, potenciales) a los enteros sin signo más pequeños que las representan sin truncarlas.
5. 5.Exporte el resultado a Parquet particionado (Hive, por corporación y departamento) y comprimido, reduciendo drásticamente el tamaño en disco sin perder ni un solo registro.

4. Librerías y herramientas utilizadas

Motor de almacenamiento de salida: formato Apache Parquet, con compresión ZSTD, generado íntegramente por DuckDB.

5. Técnicas de ingeniería de datos aplicadas

5.1. Parseo de archivos de ancho fijo según especificación oficial

En lugar de adivinar las posiciones de los campos, se extrajo el texto completo del PDF Estructuras Basicas (1808).pdf (usando pypdf) para obtener el layout exacto de cada archivo. A partir de esa especificación se construyeron funciones de parseo (parse_divipol, parse_indicadores, parse_partidos, parse_corporacion, parse_circunscripcion, parse_candidatos), cada una recortando la línea de texto en las posiciones correctas. Por ejemplo, para CANDIDATOS el layout verificado fue:

corporacion(3) + circunscripcion(1) + cod_departamento(2) + cod_municipio(3) +
cod_comuna(2) + cod_partido(5) + cod_candidato(3) + preferente(1) +
nombre(50) + apellido(50) + cedula(15) + genero(1) + sorteo(2)   = 138 caracteres

Cada layout fue además verificado empíricamente contra líneas reales de cada archivo antes de darlo por válido (por ejemplo, confirmando que el candidato "017" de la fila de Lopera coincidía exactamente con el corte de bytes calculado).

5.2. Normalización de códigos para permitir el cruce (JOIN) entre archivos

El archivo de escrutinio y los archivos básicos no usan exactamente el mismo formato para los mismos códigos, porque fueron diseñados como archivos independientes:

• —Zona (`cod_zona`): en el escrutinio tiene 3 dígitos, mientras que en DIVIPOL tiene 2. Se normalizó con LPAD(CAST(cod_zona AS INTEGER)::VARCHAR, 2, '0').
• —Partido (`cod_partido`): en el escrutinio tiene 4 dígitos, mientras que en PARTIDOS y CANDIDATOS tiene 5. Se normalizó con LPAD(CAST(cod_partido AS INTEGER)::VARCHAR, 5, '0').
• —Departamento en `CANDIDATOS`: vale '00' para corporaciones de circunscripción nacional (Senado, listas únicas para todo el país) y el código real del departamento para corporaciones territoriales (Cámara, que elige listas por departamento). El JOIN contempla ambos casos con la condición (cand.cod_departamento = '00' OR cand.cod_departamento = e.cod_departamento).

Estas normalizaciones son puramente de formato (representación de texto), no alteran el valor numérico ni pierden información.

5.3. Verificación de unicidad de llaves antes de unir (evitar duplicación de filas)

Antes de ejecutar cualquier JOIN, se verificó con DataFrame.duplicated(subset=llaves) que cada tabla de metadata (divipol, indicadores, partidos, corporacion, circunscripcion, candidatos) tuviera llaves únicas. Esto es crítico: si una tabla de la derecha tuviera llaves repetidas, el LEFT JOIN multiplicaría filas del escrutinio (fan-out), corrompiendo silenciosamente los totales. Resultado de la verificación: 0 duplicados en las 6 tablas.

5.4. Clasificación de tipos de registro especiales

Se identificaron y etiquetaron, sin inventar su significado exacto, tres categorías de cod_candidato mediante una columna nueva tipo_registro:

• —'000' → `SOLO_PARTIDO`: el elector votó por el partido/lista sin marcar un candidato específico (en este caso, el cruce contra CANDIDATOS sí trae un nombre, correspondiente al nombre de la lista).
• —'996', '997', '998' → `ESPECIAL`: códigos que no existen en el catálogo de candidatos (votos en blanco, nulos o tarjetas no marcadas); quedan con las columnas de candidato en NULL en vez de forzar una interpretación no verificada.
• —Cualquier otro valor → `CANDIDATO`: voto a un candidato específico.

Adicionalmente se agregó una columna tipo_voto_especial que traduce esos tres códigos especiales a la convención habitual de la Registraduría (996 = Tarjetas No Marcadas, 997 = Votos Nulos, 998 = Voto en Blanco). Esta convención no está corroborada dentro de Estructuras Basicas (1808).pdf (que no documenta el significado de esos tres códigos), por lo que la columna se dejó explícitamente como una ayuda de lectura y no como un hecho verificado; debe contrastarse contra un boletín oficial de la Registraduría antes de usarse como fuente de verdad en un análisis publicable.

5.5. Procesamiento out-of-core (sin cargar todo en RAM)

Todo el flujo de lectura del CSV, JOIN contra la metadata y escritura a Parquet se ejecutó dentro del motor de DuckDB, usando su API SQL (con.execute(...)) sobre una consulta con CTE (WITH escrutinio AS (...)). En ningún punto se materializaron los 180.6 millones de registros como un DataFrame de pandas en memoria; DuckDB los procesó en streaming, leyendo, transformando y escribiendo por bloques. Solo al final se hizo una consulta de LIMIT 10 para previsualizar el resultado en pandas, lo cual es seguro porque son solo 10 filas.

5.6. Doble validación matemática (antes y después de exportar)

Se calcularon dos métricas de control sobre el CSV original, sin ningún join (línea base):

• —Total de filas: 180,592,545
• —Total de votos: 41,679,780

Y se recalcularon las mismas métricas después del `JOIN` (antes de exportar) y después de exportar a Parquet (leyendo el propio archivo .parquet con read_parquet), usando aserciones (assert) que detendrían el notebook si algo no cuadraba:

assert check_filas == baseline_filas
assert check_votos == baseline_votos

Los tres conteos (CSV crudo → resultado del JOIN → archivo Parquet final) coincidieron exactamente, confirmando que el proceso de enriquecimiento y exportación no agregó, eliminó ni duplicó un solo registro.

5.7. Exportación directa a Parquet comprimido y particionado

La escritura del resultado final se hizo con la sentencia nativa de DuckDB:

COPY (<consulta_consolidada>)
TO 'escrutinio_congreso_2026_consolidado' (
    FORMAT PARQUET,
    PARTITION_BY (cod_corporacion, cod_departamento),
    COMPRESSION ZSTD,
    OVERWRITE_OR_IGNORE TRUE
)

Esto permite que DuckDB transforme y escriba el archivo en un solo paso, aprovechando:

• —Almacenamiento columnar (Parquet), mucho más eficiente que texto plano para datos con muchas columnas repetitivas (códigos, nombres de partidos, departamentos, etc.).
• —Compresión ZSTD, que comprime especialmente bien columnas con baja cardinalidad (pocos valores distintos que se repiten millones de veces, como departamento, partido, corporacion).
• —Codificación por diccionario automática de Parquet para columnas de texto repetidas.
• —Particionado Hive (cod_corporacion=.../cod_departamento=...): el resultado ya no es un único archivo .parquet, sino un directorio con subcarpetas por corporación y departamento. Cada subcarpeta contiene únicamente las filas de esa combinación, lo que permite a motores como DuckDB, Polars o Spark descartar carpetas completas (en vez de solo row groups) cuando se filtra por cod_corporacion o cod_departamento, sin necesidad de leer ni descomprimir esos archivos.

5.8. Tipado estricto de columnas numéricas

Por defecto, read_csv y las operaciones aritméticas de DuckDB asignan tipos amplios (INTEGER/BIGINT) a cualquier columna numérica. Antes de exportar, se verificó el rango real de cada columna cuantitativa (SELECT MAX(...)) y se hizo un CAST explícito al entero sin signo más pequeño que la representa sin truncarla:

Los códigos con ceros a la izquierda (cod_departamento, cod_municipio, cod_partido, cod_candidato, etc.) se dejaron como VARCHAR, ya que son identificadores categóricos (no cantidades) y Parquet los comprime eficientemente por diccionario de todas formas.

5.9. Clustering físico: de ORDER BY global a particionado Hive

La primera versión de la exportación intentó un ORDER BY cod_corporacion, cod_departamento, cod_municipio justo antes del COPY, para agrupar físicamente filas similares dentro de los mismos row groups del Parquet. Sin embargo, ordenar las 180.6M filas ya enriquecidas (27+ columnas) requiere un external sort, y en la máquina usada para este ejercicio (16 GB de RAM, ~28 GB de disco libre) esa operación agotó el directorio temporal de DuckDB y falló con:

OutOfMemoryException: Out of Memory Error: failed to offload data block of size 256.0 KiB (18.1 GiB/18.1 GiB used).

En vez de forzar el ORDER BY (aumentando límites de memoria/disco que esta máquina no tiene disponibles), se optó por el particionado Hive descrito en la sección 5.7, que logra el mismo objetivo de clustering físico —y un predicate pushdown incluso más fuerte, a nivel de carpeta— sin requerir un ordenamiento global: DuckDB enruta cada fila a su partición en streaming, sin materializar ni ordenar el dataset completo en memoria o disco. Esta decisión queda documentada como ejemplo de cómo adaptar una técnica de optimización a las restricciones reales de hardware disponibles, en vez de aplicarla de forma dogmática.

6. Estructura y dimensionalidad del dataset consolidado

El dataset resultante conserva una fila por cada combinación mesa × partido × candidato, igual que el archivo original, pero con 28 columnas (frente a las 12 columnas puramente numéricas/codificadas del CSV crudo):

Dimensiones:

• —Filas: 180,592,545 (idéntico al CSV original, verificado)
• —Columnas: 28 (vs. 12 columnas de códigos en el CSV crudo)
• —Votos totales: 41,679,780 (idéntico al CSV original, verificado)
• —Particiones físicas: el directorio de salida queda organizado como cod_corporacion=.../cod_departamento=.../*.parquet (Hive partitioning), en vez de un único archivo.

Metadata auxiliar utilizada para el enriquecimiento (todas con llave única verificada):

7. Resultado: tamaño del archivo antes y después

El resultado final quedó guardado como un directorio particionado (no un único archivo):

data/MMV_CONGRESO_2026/consolidado/escrutinio_congreso_2026_consolidado/
├── cod_corporacion=001/
│   ├── cod_departamento=01/*.parquet
│   ├── cod_departamento=02/*.parquet
│   └── ...
└── cod_corporacion=002/
    ├── cod_departamento=01/*.parquet
    └── ...

Se lee como un solo dataset lógico con read_parquet('.../escrutinio_congreso_2026_consolidado/**/*.parquet', hive_partitioning=true).

8. Conclusión

Este ejercicio ilustra un flujo típico de ingeniería de datos: partir de un archivo fuente masivo, plano y difícil de manipular (9.08 GB, 180+ millones de filas, sin descripciones legibles), y transformarlo —sin perder ni un solo dato— en un artefacto ~70 veces más liviano (0.13 GB), auto-descriptivo (con nombres en lugar de solo códigos), con tipos numéricos ajustados a su rango real, y físicamente organizado (particionado Hive) para lecturas selectivas rápidas.

Los puntos clave que hicieron posible esta reducción sin pérdida de información fueron:

1. 1.Usar el layout oficial de campos (extraído del PDF de la Registraduría) en lugar de inferirlo.
2. 2.Verificar la unicidad de llaves de cada tabla de metadata antes de cruzarla, evitando duplicación silenciosa de filas.
3. 3.Procesar el archivo completo con un motor out-of-core (DuckDB) en vez de cargarlo entero en memoria con pandas.
4. 4.Validar matemáticamente (conteo de filas y suma de votos) en tres puntos del proceso: CSV crudo, resultado del JOIN, y archivo Parquet final.
5. 5.Exportar a un formato columnar comprimido (Parquet + ZSTD), ideal para datos con muchas columnas de baja cardinalidad repetidas millones de veces.
6. 6.Ajustar cada columna numérica al entero sin signo mínimo que la representa (USMALLINT/UINTEGER) en vez de usar BIGINT por defecto.
7. 7.Reemplazar el clustering por ORDER BY global (inviable en esta máquina por límites de RAM/disco) por particionado Hive (cod_corporacion, cod_departamento), logrando un clustering físico equivalente —o más fuerte— sin necesidad de un external sort, y documentando explícitamente esa decisión de ingeniería y su motivo.