[plutarco] ETL + endpoint afluencia por estación — Metro CDMX (2010-2026)

[galigaribaldi]

Contexto

Hallazgo durante la implementación de #40: el Portal de Datos Abiertos de la CDMX publica afluencia por estación para el STC Metro — 16 años de datos diarios (enero 2010 - abril 2026, 56 MB CSV).

De todos los sistemas de transporte, solo el Metro tiene granularidad por estación en datos abiertos. Los demás sistemas (Metrobús, Trolebús, Cablebús, Tren Ligero) solo publican por línea.

Valor

Para VFT

• El Node Score usa âᵢ (demanda observada por estación). Hoy VFT tiene que prorratear la afluencia de la línea entre estaciones con un modelo estimado
• Con estos datos, VFT tendría la demanda real observada por estación del Metro — sin estimación
• Metro = 49% de la afluencia total del sistema, así que esto cubre el sistema más importante

Para Apimetro

• Primer dataset de serie temporal larga (16 años) del esquema plutarco
• Habilita un nuevo endpoint: GET /movilidad/analitico/afluencia-estacion
• ~37,000 registros (agregado mensual) o ~1.1M (diario) — manejable para PostgreSQL

Fuente

Dataset: Afluencia Diaria del Metro (Simple)
URL: https://datos.cdmx.gob.mx/dataset/afluencia-diaria-del-metro-cdmx
Publicador: STC Metro / SEMOVI
Licencia: CC BY 4.0
Actualización: Mensual
Columnas: fecha, linea, estacion, afluencia (por confirmar con diccionario de datos)

También existe versión Desglosada con tipo de pago (tarjeta, boleto, gratuidad).

Implementación propuesta

DDL

CREATE TABLE IF NOT EXISTS plutarco.afluencia_estacion (
    id            SERIAL PRIMARY KEY,
    estacion_id   INTEGER REFERENCES public.estacions(id),
    linea_id      INTEGER REFERENCES public.lineas(id),
    anio          SMALLINT NOT NULL,
    mes_num       SMALLINT NOT NULL,
    mes           VARCHAR(20),
    afluencia     BIGINT NOT NULL,
    fuente        TEXT,
    created_at    TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(estacion_id, anio, mes_num)
);

Archivos a crear

• ETL/DataCharge/LoadAfluenciaEstacion.py — ETL (descarga CSV, agrega a mensual, mapea estación)
• db/migrations/v3.0_afluencia_estacion.sql — DDL + índices
• cmd/pkg/controller/geojson/AfluenciaEstacionController.go — query
• cmd/pkg/routes/RoutesAfluenciaEstacion.go — handler + Swagger
• cmd/pkg/routes/Routes.go — wire en grupo analítico

Endpoint

GET /movilidad/analitico/afluencia-estacion

Filtros: linea_id, estacion_id, anio, mes_num

Dependencias

• 195 estaciones de Metro en public.estacions
• Catálogo de homologación de nombres de estación (CSV usa nombres que pueden diferir de public.estacions.nombre)

Estimación de volumen

• ~195 estaciones × 16 años × 12 meses = ~37,440 registros (mensual)
• ~195 estaciones × 16 años × 365 días = ~1,138,800 registros (diario)

Recomendación: almacenar a nivel mensual (consistente con afluencia_linea) con opción de agregar tabla diaria después si se requiere.

[galigaribaldi]

Análisis completo — Mapeo CSV vs BD (2026-06-21)

CSV fuente descargado y analizado

Archivo: afluenciastc_simple_04_2026.csv (56 MB)
Estructura: fecha,anio,mes,linea,estacion,afluencia
Volumen: 732,351 filas diarias / ~37K agregado mensual
Cobertura: enero 2010 — abril 2026 (17 años)
Encoding: Latin-1 (requiere decode a UTF-8 en ETL)

Resultado del mapeo

Métrica	Valor
Combinaciones estación+línea en CSV	197
Estaciones en BD (public.estacions METRO)	195
Match exacto	186 (94.4%)
Requieren normalización	11 (5.6%)

Fixes aplicados a la BD (esta sesión)

3 correcciones en public.estacions:

1. Penón Viejo → Peñón Viejo (id=12120, ortografía)
2. Viveros y Derechos Humanos → Viveros y Derechos Humanos (id=12032, doble espacio)
3. Niños Héroes y Poder Judicial CDMX → Niños Héroes y Poder Judicial CDMX (id=12024, doble espacio)

11 diferencias restantes → 3 reglas de normalización

No se necesita catálogo de homologación. Las diferencias se resuelven con funciones de normalización en el ETL:

Regla 1 — / → y (7 de 11)

Etiopía/Plaza de la Transparencia   → Etiopía y Plaza de la Transparencia (id=12027)
Ferrería/Arena Ciudad de México     → Ferrería y Arena Ciudad de México (id=12062)
Garibaldi/Lagunilla                 → Garibaldi y Lagunilla (id=12084, 12127)
La Villa/Basílica                   → La Villa y Basílica (id=12068)
Viveros/Derechos Humanos            → Viveros y Derechos Humanos (id=12032)
Zócalo/Tenochtitlan                 → Zócalo (id=12003, truncar en /)

Regla 2 — Normalizar acentos y formato (3 de 11)

Gómez Farias       → Gómez Farías (id=11988, acento faltante en CSV)
Peñón viejo        → Peñón Viejo (id=12120, capitalización)
UAM-Azcapotzalco   → UAM Azcapotzalco (id=12061, guión → espacio)

Regla 3 — Nombre extendido (1 de 11)

Niños Héroes → Niños Héroes y Poder Judicial CDMX (id=12024, match parcial)

Líneas en el CSV: doble nomenclatura

El CSV tiene 24 nombres de línea porque cambiaron de Linea (sin acento) a Línea (con acento) a lo largo del tiempo. Ambas variantes mapean al mismo linea_id:

"Linea 1" y "Línea 1"   → linea_id=191 (num_comercial='1')
"Linea 12" y "Línea 12" → linea_id=301 (num_comercial='L12')

Propuesta de implementación simplificada

Dado que el 94.4% matchea directo y el resto se resuelve con normalización:

1. No crear catálogo de homologación — usar funciones de normalización en el ETL
2. Agregar a nivel mensual — consistente con afluencia_linea
3. Lookup directo en el ETL: normalizar nombre → buscar en BD por (nombre, linea_id)
4. Log de descartados — si alguna estación no matchea, logear y no insertar (no romper)

Archivos a crear (actualizado)

Archivo	Descripción
db/migrations/v3.0_afluencia_estacion.sql	DDL tabla + índices (sin catálogo)
ETL/DataCharge/LoadAfluenciaEstacion.py	ETL con normalización inline
cmd/pkg/models/AfluenciaEstacion.go	Struct respuesta
cmd/pkg/controller/geojson/AfluenciaEstacionController.go	Query JOIN
cmd/pkg/routes/RoutesAfluenciaEstacion.go	Handler + Swagger

[galigaribaldi]

Handoff sesión 2026-06-21 (sesión 2) — Pasos 1 y 2 completados

Lo hecho

• Paso 1 (DDL): Tabla plutarco.afluencia_estacion creada

  • Migración: db/migrations/v3.0_afluencia_estacion.sql
  • DDL agregado a db/init/init_plutarco.sql
  • Tabla creada en Docker DEV via docker cp + psql -f

• Paso 2 (ETL): ETL/DataCharge/LoadAfluenciaEstacion.py — ejecutado exitosamente

  • 38,219 registros mensuales cargados
  • 195/195 estaciones mapeadas (100%)
  • 12/12 líneas, cobertura 2010–2026
  • Afluencia acumulada: 22,845,843,248 viajes
  • CSV fuente: afluenciastc_simple_04_2026.csv (56MB, 1,162,980 filas diarias)

Fixes de encoding aplicados al ETL

El CSV tiene doble mojibake (mezcla de Latin-1 puro y UTF-8 leído como Latin-1 dos veces):

1. _fix_latin1() ahora itera hasta 3 veces para deshacer múltiples capas
2. _normalizar_linea() usa regex (\d+|[A-B])$ — robusto ante cualquier variante de encoding
3. Dict: "Niños Héroes" → "Niños Héroes y Poder Judicial CDMX"
4. Regla UAM: solo UAM-Azcapotzalco → UAM Azcapotzalco, UAM-I se queda igual

Archivos sin commit (rama DEV-fix-plutarco-extension)

• db/migrations/v3.0_afluencia_estacion.sql (nuevo)
• db/init/init_plutarco.sql (modificado)
• ETL/DataCharge/LoadAfluenciaEstacion.py (nuevo)

Pendiente

• Paso 3: Endpoint Go GET /movilidad/analitico/afluencia-estacion (modelo, controller, route)
• Paso 4: Swagger + Postman
• Paso 5: Pruebas curl en Docker DEV

Para re-ejecutar el ETL

cd ETL
PYTHONPATH=. DB_HOST=localhost DB_PORT=5433 DB_USER=admin_apimetro DB_PASSWORD='Dev_Adm1nP4ss!2024' DB_NAME=db_apimetro python3 DataCharge/LoadAfluenciaEstacion.py