Si te gusta el tema de los datos pero no tienes un trabajo en el que desarrollar proyectos y hacer currículo profesional, puedes usar algunas de las muchas fuentes de datos abiertos que hay por la red.

Si encima le quieres dar un matiz serio al asunto puedes usar fuentes públicas.

Como datos.canarias.es, donde podemos encontrar entre muchas cosas interesantes un CSV con los contratos adjudicados y formalizados por los distintos niveles institucionales en el ámbito de la Comunidad Autónoma de Canarias.

Y de eso va este diario de trabajo, del proceso de análisis con ese CSV de casi 175.000 registros donde cada fila representa la adjudicación de algún contrato durante ese periodo de 5 años que incluye, vaya por dios, los años del bicho y de los vergonzosos policías de balcón.

Cinismo e ironía apartes, comienza aquí una aventura similar pero multiplicada en complejidad a la que ya divulgué con los precios de las frutas y verduras en MercaTenerife.

(Si eres capaz de leer el proceso, salta al día 9 y al cuadro de mandos publicado al final de este diario.)

Diario de viaje

1. Primeros pasos

(21 noviembre 2024)

Lo primero fue instalar un escritorio remoto en mi viejo Mac por lo que tuve que recurrir al historial de versiones betas de Microsoft Remote Deesktop disponibles en el AppCenter del gigante de Bill Gates. Más que nada porque a los espabilados de Apple les ha dado por poner solo disponible la última versión de cada aplicación, así solo se pueda ejecutar con versiones de iOS superiores a la 12 o a la 14. Los que tenemos dispositivos con 5 ó 6 años nos jodemos.

¿Por qué necesitaba este escritorio remoto? Porque voy a jugar con estos CSV utilizando Power BI Desktop a través de un VPS montado con un Windows Server 2022 ya que este aplicativo de la casa no está disponible para sistemas Mac o Linux.

Los datos, como dije, están en datos.canarias.es y consta de dos CSV, uno con todas las filas de datos (nuestros hechos) y otro con los descriptivos de cada columna (para saber qué entienden los funcionarios con cada cosa).

Tanto nuestro fichero contratos.csv como nuestro fichero diccionario.csv se pueden conectar directamente a Power BI a través de un enlace anónimo, ya que al ser datos abiertos y estar difundidos de manera pública pues, obviamente, no necesitas nada.

Tampoco descargarlos. Quiero que, si esos ficheros se actualizan, se actualice el cuadro de mandos final. Aunque también podría ser que alguien se cabrease por esto que estoy haciendo y los datos desaparezcan, dejen de estar públicos o simplemente el fichero cambie de URL.

Bien, los ficheros no presentan ningún problema más allá de los que ya sé que me voy a encontrar en sus columnas y en los que tendré que aplicar algunos procesos sencillos de ETL. Y el VPS con Server 2022 y un Intel Xeon Gold 6240R con 2 procesadores y 8 GB de RAM no sé si será lo suficientemente potente como para procesar las casi 175.000 filas de datos con sus 23 columnas cada una.

Y así están las cosas, a las 19:48 horas en la isla de Tenerife, a día 21 de noviembre, cuando inicio esta aventura.

2. Procesos ETL iniciales

21 noviembre 2024

Lo primero es adecentar la tabla de diccionario, aunque solo la use para entender qué es cada columna. Eso de columna 1, columna 2… me pone de los nervios, además de eliminar una columna vacía que no aporta nada.

Ahora que puedo entender cada campo voy a empezar a eliminar aquellos que, a priori, no me van a servir para mostrar los importes_ofertados por las entidades adjudicatarias, que es el único campo de valor numérico y económico en la tabla, de manera categorizada. O bien porque la categoría no aporta nada.

Lo primero es meterle mano a objeto_contrato, que es un campo de texto en el que a simple vista pareciera que se repiten ciertos ítems así que voy a darle una pasadita al Text Analytics y de paso trastear un poco más con las IAfunctions a ver qué me encuentro utilizando el extractor de palabras y frases clave. ¿Veremos algo sobre las mascarillas? Parece que, efectivamente, algo encontraremos…

Una vez extraídas las claves digamos adiós a esa primera columna.

Vamos a dejar entidad_adjudicadora (quién paga y pone las perritas). Serán más de 300 y serán un quebradero de cabeza, pero es un dato clave para saber quién convoca, pone los euros y paga. Lo sé porque ya usé este mismo archivo en mi último curso de Excel Avanzado y Power BI para personas en búsqueda de empleo, durante el cual nació la idea de este proyecto.

También se queda procedimiento_contratación, obviamente, porque aquí además aparecen algunos como negociados sin publicidad vinculados con el siguiente campo que son dignos de estudio.

Ídem con tramitación_tipo, que es donde encontraremos a los de convocados por la vía de emergencia.

Lote desaparece porque aunque estoy seguro que puede tener utilidad ahora mismo, por desconocimiento, no llego a entender como relacionar unos con otros. Cuando encuentre a alguien que me lo aclare lo recuperaré.

Obviamente nos quedamos con:

adjudicación_fecha,
adjudicataria_nif,
importe_ofertado,
licitación_estado,
publicación_medio,
financiación_europea,
fuente_financiación_id y
fuente_financiación (relacionada con la anterior y que me llevaré a una tabla de dimensión)

Y hacemos desaparecer:

expediente_número (por ahora),
licitación_enlace,
pliego_enlace (estas dos columnas podrían tener utilidad para escanear las páginas y extraer algún otro dato, pero no sé si llegaré tan lejos con este proyecto),
perfil_contratante_enlace (que es interesante para hacer un webscrapping con un cuaderno Jupyter y extraer los nombres de las adjudicatarias),
localizador_programa_ue (no consigo verle utilidad en un primer acercamiento a los datos),
publicación_fecha (voy a usar como referencia la de adjudicación),
fórmula_revisión_precios (más que nada porque muchas filas están vacías en un abultado 99 missings a 1 con datos) y
clasificación_cpv (porque aunque es interesante, casi un 90% son missings).

Bueno, pues ahora me voy a echar una partida con la PS5 porque se están actualizando las vistas y parece que el VPS va a la pata coja.

(15 minutos después…)

Aún no me he marchado con mi PS5 a jugar al Zenless Zone Zero porque al final me he dado cuenta que tener las IAfunctions activas vuelve el proceso muy lento así que lo dejaré para una segunda o tercera o cuarta o quinta… fase del proyecto.

En breve… más.

3. Procesos ETL: creando las primeras dimensiones

(22 noviembre 2024)

Vamos a darle otro empujón a este proyecto que me da a mí que va a ser más largo que una ruta de bicicleta en los montes de Tenerife sin agua y sin barritas energéticas.

(Está bien tirado el enlace ese, ¡eh!)

Primero vamos a convertir fuente_financiación_id y fuente_financiación en una dimensión. En el CSV original ese id es la abreviatura de la entidad financiadora (no de quien da el dinero sino lo pone sobre la mesa financia para que alguien lo adjudique, como por ejemplo FEDER). He cogido el camino largo duplicando la tabla original, quitando otras columnas, eliminando duplicados y agregando índice antes de combinar el resultado con la tabla original para dejar solo un id numérico.

Con financiación_europea, que solo contiene «SÍ» y «NO» como valores, además de un porrón de «_U» (que a la sazón, en esta tabla de datos, significa desconocido o ausente) lo dejamos tal cual pero cambiando ese feo «_U» por un bonito «¿?».

Para licitación_resultado ha bastado con crear una tabla nueva donde introducir dos filas con dos columnas para cada etiqueta.

¿Por qué sustituir «Adjudicado» y «Formalizado» por «1» y «2»?

Pues si contamos caracteres y multiplicamos por casi 175.000 registros en un caso o el otro se entenderá perfectamente. En un caso tienes 10 y a veces 11 caracteres mientras que en el otro tienes siempre 1 byte por cada fila.

De todas formas hacerlo así, manualmente, podría introducir errores en el futuro si el CSV se actualiza y esta columna adquiere nuevos valores. Dejo este comentario en rojo y negrita para mi yo del futuro.

Por hoy no voy a avanzar mucho más, añado los dos primeros objetos visuales para ir haciéndome una idea de la dimensión de lo que tengo entre manos y ordeno las tablas en Power Query por ecología de procesos, aunque esta tontería me generó un error (renombrar tablas y agruparlas en carpetas). Y es que BI como Query son más sensibles que un gato consentido.

(20 minutos después…)

Pues al final lo que ha pasado es que la tabla de medidas, al cambiarle el nombre en Power Query, en el paso de cerrar y aplicar cambios, a la vuelta, ha dado un error en Power BI porque no ha reconocido la tabla y ésta la observa como vacía.

Ha tocado eliminar la susodicha en Power Query y volverla a crear en Power BI. Otro detallito aprendido.

Creo que por hoy, ha sido suficiente. En breve… sí, efectivamente, más.

4. Procesos ETL: más dimensiones

(23 noviembre 2024)

Pero antes de comenzar con las dimensiones, algo de diversión, voy a empezar a construir o preparar lo que será el aspecto visual. Mi intención no es armar jaleo, solo que el análisis de los datos permita a cualquier persona, pertenezca a la institución autonómica o no, sea ciudadano de a pie o periodista en busca de pruebas de lo que sea, el poder obtener conclusiones de una tabla de datos proporcionada por la propia Comunidad Autónoma que contiene casi 175.000 registros y que resulta intratable para la mayoría de las personas.

La primera página, por lo tanto, será una visión general de los datos. Cuántos hay, cuánto suman, desde cuándo y hasta cuándo, cómo se reparten por las principales categorías, las más simples, etc…

Las sucesivas páginas del informe profundizarán en quién adjudica, quién reciben y, por qué no, habrá algunas páginas que busquen el sensacionalismo como las que estudiarán aquellas adjudicaciones vinculadas con la palabra mascarilla. Después de todo necesitaré de algún gancho que motive a las personas a hacer clic y acabar en este diario y jugando con el dashboard interactivo (cuando lo incorpore).

A fecha de hoy aún no está incrustado aquí, así que no lo busques.

Acabado esto, vamos a añadir un par de dimensiones. En publicación_medio vamos a resolver un tema de minúsculas y mayúsculas antes de pasar la información a una dimensión propia.

Y, como voy a dejarlo ya, voy a añadir un primer gráfico de tartas con las primeras dimensiones que ya he creado, siendo las primeras conclusiones que casi el 95% no tiene definida una fuente de financiación, lo que podría indicar que es financiación propia (autonómica, nuestros presupuestos propios), que alguito más del 95% de los contratos presentes en el CSV están adjudicados y que el 95% de los contratos se han publicado en el perfil del contratante, no teniendo muy claro ahora mismo qué significa eso, pero que tendrá que ver, seguramente, con la exposición pública de los contratos recibidos y que se debería de poder ver en la web contrataciondelestado.es, en la ficha de los datos del adjudicatario (quién recibe las perritas).

Aún quedan algunas dimensiones aún más interesantes que éstas que lo reducen todo a un escenario bastante predecible. Estas conclusiones me podrían llevar a excluir de futuros análisis estos conceptos ya que se agrupan masivamente (95%) alrededor una única categoría.

(Un par de horas más tarde…)

Como me estoy entusiasmando con este proyecto, me ha dado por darle otro empujón, así que toca dimensionar contrato_tipo, pocas sorpresas, ídem con licitación_estado.

La verdad es que el VPS empieza a quedarse muy corto para este trabajo. Cada acción lleva segundos así que tengo que pensar muy bien lo que quiero hacer, por no hablar que cada dos por tres pierdo la conexión.

Me paro aquí porque las próximas generación de dimensiones no serán sencillas ya que se centrarán en las entidades adjudicadoras (quien da las perritas) y las entidades adjudicatarias (quien recibe las perritas).

La cuestión es que ya conocía este CSV porque lo usé como elemento de práctica en un curso para desempleados de Excel Avanzado y Power BI.

Sé lo que me voy a encontrar en los CIFs de las adjudicatarias (donde además no tengo el nombre de la empresa, por cierto).

Tengo que darle vueltas en cómo extraer de contrataciondelestado.es ese nombre o si usar un servicio de los que te dan información de empresas a través de alguno de sus datos, como el CIF.

Por otro lado en cuanto a las entidades adjudicadoras hay más de 360, si mal no recuerdo. Y aquí el problema son los constantes cambios de nombre cada vez que cambia el gobierno autonómico por lo que estoy pensando en agruparlas por temática (sanidad, turismo, obras públicas, agricultura, pesca, etc.).

En cuanto a dimensiones sencillas solo quedan dos que igual las liquido ya después de darle guardar a este diario.

En tramitación_tipo he tenido que crear una dimensión con una columna extra para redefinir los valores indefinidos que originalmente figuran como «_U» y que figuren en los gráficos como «Indefinida». Con procedimiento_contratación más de lo mismo.

Me estoy dando cuenta que al agregar un índice estaría bien que tuviera las etiquetas tuvieran un orden lógico correlativo. Algo así como malo → regular → bueno pero es algo de lo que me preocuparé más adelante. Igual añado una columna «orden» en las dimensiones que lo necesiten y ordeno las etiquetas según vea más lógico o entendible. Esto es especialmente necesario en categorías que definen una escalera de valor (como pendiente → en proceso → hecho, por ejemplo).

Cerrar y aplicar y me vuelvo a Power BI a añadir algunas tartitas para completar esa visión global del CSV y terminar por hoy, no sin antes revisar las relaciones que automáticamente debería detectar BI ya que todo tiene unos nombrecitos cuquis y adecuados.

Ya se va viendo el resultado del proceso hasta el momento. Creo que en breve publicaré la versión 1.0, jejeje. Ojalá encontrase a alguna entidad o empresa que me pagase por esto.

Cobrar por jugar. ¡No digas que no es la leche!

Estoy revisando las columnas eliminadas por si alguna puede ser recuperable y, aunque hay datos interesantes, vuelvo a darme cuenta y recordar que algunas como clasificación_cpv, que es un código de gastos determinado por Europa y que define en lo que una administración pública puede invertir, tiene un abultado porcentaje de valores inexistentes.

Sin embargo creo que recuperaré objeto_contrato y las IAfunctions para analizar el texto y buscar algunas keywords relevantes, como mascarilla, ya sabes, pero no ahora. Y puede que publicación_fecha, aunque con esta columna primero tengo que comprender el porqué de que en algunos casos sea anterior a la fecha de adjudicación y en otros sea posterior. Necesitaré a un especialista en procedimiento administrativo para comprender ciertos matices de los datos.

Porque al final, analizar no es solo mostrar porcentajes o crear un modelo que analice tendencias sino que antes de todo eso debes entender lo que tienes entre las manos. Dicho lo cual, ahora sí, guardo y cierro.

Hasta aquí el proceso de hoy.

Mañana… o quizás pasado, más. Sigue pendiente.

5. Procesos ETL: los que cobran

(24 noviembre 2024)

Es domingo. Ya amasé cemento para arreglar un patio, ya avancé el curso de Certificación en Ciberseguridad de Google, ya eché unas horas al vicio con la PS5 jugando a Subnáutica… toca avanzar este proyecto. Seré un raro, pero a mí esto me motiva y me divierte.

Entre las columnas, o campos, más relevantes de los datos que podemos encontrar en el CSV con el que estoy jugando, está adjudicataria_nif, que es quién cobra al final el dinero adjudicado para ejecutar la labor por la que se le contrata.

Como ya conozco el CSV por las razones que ya he contado previamente en este diario, sé lo que me voy a encontrar.

Lo primero, tengo el CIF, y en algunos casos el CIF, de la entidad adjudicataria. Pero algunos de estos CIF están escritos con «-» y otros con un «.», unos con un » » (espacio en blanco) y otros con solo 6 dígitos. Además de que hay un buen puñado anonimizado en el que sustituyen los 2, 3 ó 4 caracteres iniciales o finales por «*».

Hablando con los alumnos del curso de Excel Avanzado y Power BI me confirman dos personas que son NIFs, es decir, DNIs que identifican personas. En el curso decidimos sustituirlos todos por la cadena «Persona Física». Aún no sé si aplicar el mismo criterio en este proyecto.

Por otro lado tengo el CIF/NIF pero no el nombre. No sé qué empresa es la que recibe el dinero a no ser que la busque, una a una, en Google o en algún portal como eInforma, por ejemplo. Bien es cierto que tengo una columna que contiene el enlace al perfil del contratante en la web estatal contrataciondelestado.es que ya he nombrado un par de veces. Pero cada vez que intento acceder a esos enlaces…

En fin, vaya gracia con lo de la transparencia y la información pública. Esto es relevante porque hay una columna, publicación_medio, que (intuyo) se refiere a dónde se ha publicado la adjudicación. Y la inmensa mayoría pone, efectivamente, «perfil del contratante».

Y por último está el puñadito de adjudicatarias indefinidas o de valor no presente o no conocido. Otro misterio que tal vez solo apunte a la falta de calidad en los datos recopilados y cuya deficiencia provenga seguramente de las fuentes originales. De cómo cada entidad pública registra esa información y la envía a quién corresponda.

(Aunque si pensamos mal también podríamos pensar en un ocultamiento intencionado, jejeje.)

En fin, por lo pronto vamos a llevarnos la columna, junto al enlace, a una tabla de dimensión propia. Y luego ya decidiré.

Por lo pronto toca ir detectando problemas con esta columna de datos que contiene varios miles de entidades adjudicatarias.

Toca crear una columna adjudicataria_nif_ETL que va a contener los CIFs ya limpios.
Transformar los «-«, «.» y » » (espacio en blanco) en una «» (cadena vacía).
Transformar los NIF anonimizados en «Persona Física» y los «_U» en «Indefinida».

ChatGPT y Gemini dicen que debe tratarse de un error y no encuentran información al respecto. La segunda IA añade que podría tratarse de una codificación antigua anterior a ciertas reformas alrededor del CIF. ¿Podría ser? ¿Podría ser el código asignado a la entidad en eFactura? ¿Podría ser el código de entidades extranjeras?

Hay 74 entidades con un CIF de 6 números.
Hay 1 entidad con un CIF que pone «CANADA» (no sé qué decir).
Hay 54 CIFs con 8 caracteres, algunos parecen CIFs con una letra y 7 números y otros son solo 8 números.
La inmensa mayoría tiene 9 ó 10 caracteres.
Pero hay 111 con 11 caracteres, algunos CIFs son recuperables porque tienen una «,» o un «-» o un » » o un «.» (que ya he limpiado en mi columna ETL). Pero hay otros que tienen las siglas de un país europeo o extranjero delante de los números (o eso parece).
Ídem con los de 12, que son 50 filas.

Y de ahí para arriba hay una suerte de cola larga que tienen entre 13 y hasta 29 caracteres. Algunos claramente errores pero la mayoría por diferencias en cuanto a la tipado de datos. Vamos, que aquí hay más de un trabajador público que introduce los datos como le sale de los… dedos. Dedos. Quería decir dedos.

Ahora lo que quiero es crear un índice pero en función del CIF transformado así que voy a crear una segunda tabla donde eliminar duplicados, agregar índice y luego combinar con la dimensión. Con esto conseguiré que los CIFs que se diferencian por esos caracteres de más pero que son equivalentes acaben teniendo el mismo id.

(Además me permitirá asumir un único ID para los CIFs que no tienen ningún sentido como errores. Pero lo voy a dejar para una segunda fase de depuración de la columna. ¿Motivo? Sé que una de las entidades adjudicatarias de mayor importe tiene 6 dígitos en su CIF. Y no sé que podrá implicar el resto. Aún no puedo valorar la pérdida de información con ese posible agrupamiento.)

En esto que descubro por el camino que tengo algunas columnas con CIF cadena vacía. Vuelta atrás. Para darme cuenta que no es una celda vacía, sino un valor que originariamente contenía solo un «-«. Esto me lleva a ordenar ascendente y descendentemente los CIFs. La cantidad de errores es bastante significativa, ciertamente.

Esto podría utilizarse, de ser mal pensado, para ocultar quién ha recibido tal o cual concreto susceptible de poner en tela de juicio. No digo que ocurra, solo digo que alguien con intenciones podría utilizarlo para borrar la huella en un CSV de acceso público. Solo digo que podría ser, podría pasar.

Al final, 12.530 entidades adjudicatarias que se han repartido más de 9 mil millones de euros entre 2020 y 2024 (hasta octubre). Pero, ¿quién se lleva el gato al agua?

Pues…

Pues hay un problema. Me ha duplicado, tras la combinación con el id de la dimensión del CIF, el número de contratos, así que toca volver atrás para ver dónde ha fallado mi lógica.

Pero…

Antes de eso, un objeto visual simple en forma de tabla de datos resumida directamente realizado sobre la columna adjudicataria_nif porque quiero romper ya esa curiosidad. Y porque sé que tú también lo quieres.

Y el ganador o ganadora es…

Recibiendo 344.294.967 € entre 2020 y 2024 la entidad… 249240… en solo 4 contratos.
Seguido por A78366382 (SACYR Construcción SAU) con 258.036.505 € con 10 contratos.
En cuanto a número de contratos adjudicados en todo el periodo, con 1.495 contratos recibidos, se lleva el premio gordo A08011074 (Novartis Farmacéutica SAU), sumando 192.212.297 € entre todos.
Seguido muy de cerca por B35099506 (Ferretería Germán Medina SL) con 1.348 contratos de bajo importe, acumulando solo 190.138 €.

No es algo que diga yo.

Es algo que dicen los datos.

[Atención, la deducción que los CIFs corresponden a números del Registro Mercantil, es errada. Para más referencia ver el diario, día 10.]

La cosa es, ¿quién será 249240? Pues fíjate tú por donde creo que he resuelto el misterio. Ese código de 6 cifras es, casi con toda seguridad, el número de registro de la empresa en el Registro Mercantil. Aquí es donde aparece ese número relacionado con ~~CEPSA Aviación SA~~.

Ojo, que ~~CEPSA Aviación~~ tenga 4 contratos por +344 millones de euros (posiblemente 1 por año) no quiere decir nada que no digan los datos. Cualquier conclusión al respecto es una opinión y como tal tiene la validez que tiene.

Indagando en el asunto parece ser que los aeropuertos canarios están transferidos a la Comunidad Autónoma y que éstos compran el combustible para luego servirlo a las compañías. ¿Es esta la explicación? No lo sé, pero sería cosa de extraer justo esos 4 contratos y analizar los registros completos de datos. Lo haremos más adelante.

La cuestión es que, de ser así, esas 74 entidades no estarían codificadas con su CIF sino con el número del Registro Mercantil. ¡Voilà! ¡Los tenemos! Ahora la cuestión es como extraer esos datos y traérmelos al modelo.

¿Ya te pusiste a buscar los CIF? ¡A qué sí, curioso, curiosa! ¿A qué ya los estás buscando? Si es que no hay nada como saber, que es muy distinto de opinar.

Por último, para rematar el domingo, he vuelto a repetir el proceso arriba descrito y parece que ahora sí. No entiendo que he hecho distinto, porque para mí he seguido los mismos pasos. Pero sí me he dado cuenta que en Power BI las relaciones para depurar los CIFs quedan de esta manera tan peculiar, lo que me servirá para, más adelante, tomar algunas decisiones sobre todos los errores presentes en la larga cola de datos.

Mañana, más.

6. Procesos ETL y visualización: el calendario

(25 noviembre 2024)

Hoy me apetece más jugar así que en vez de seguir con las entidades adjudicadoras (quien paga) y sabiendo que estoy aparcando muchas filas erróneas o extrañas en las entidades adjudicatarias (quien cobra) voy a construir una tabla «especial» de calendario como dimensión y crear los primeros gráficos interanuales junto a un ranking de las 10 entidades que más reciben dinero y las 10 entidades que más contratos firman, que no es lo mismo, como ya pude ver ayer.

Sé como crear calendarios con Power Query pero me resulta más sencillo y rápido hacerlo desde Power BI con DAX y la función CALENDAR() más luego añadir algunas columnas para el año y el mes. Resuelto en un pispás.

Vamos a crear una segunda página con lo que ya empieza a ser una plantilla de diseño (muy simple, lo sé) donde además voy a añadir ya un filtro por año y uan botonería para navegar entre páginas.

(Una horita después, masumenos…)

Pues la parte visual ya empieza a coger forma. Ya sabemos quién ha recibido más dinero en todo el periodo o filtrado por año, sabemos quién ha firmado más contratos en todo el periodo o filtrado por año, todo eso mostrado en un ranking de las primeras 14 entidades adjudicatarias.

Y justo encima un gráfico de evolución anual con doble eje Y donde se muestra la evolución de los euros adjudicados y los contratos firmados o formalizados por trimestre y año.

La cosa avanza tal que así. Estaba pensando que quizás ya podría publicar una versión beta e incrustarla en este diario pero me voy a esperar a que por lo menos las 4 páginas tengan contenido.

Creo que por hoy ha sido suficiente. Ha sido entretenido. Esto es como un juego. Un juego donde dato mata relato. Un juego en el que aprendes, te desafías, pones en práctica y demuestras lo que sabes hacer.

Y, de paso, te vas enterando de cómo se mueven los millones en las entidades autonómicas de Canarias.

(Bueno, 30 minutos extras añadiendo pijerías, que ya verás, y a cenar. A ver quién lo pilla.)

Mañana, quizás, un poco más.

(¿Quieres ver de manera adelantada el resultado en vivo? ¡Escríbeme!)

7. Procesos ETL y visualización: los tópicos de la prensa

(26 noviembre 2024)

Pues hoy ha tocado otro ratito pero esta vez no desde mi escritorio sino desde la caseta del jardín donde tenemos los trastos de hobbies. Mientras ella pinta sus óleos yo me he puesto a jugar con el Power BI.

Así somos los ratitos.

Toca crear dimensión de entidad_adjudicadora, que es quien paga la pasta en los contratos, la ponga o no. Recordemos que una parte de las perritas vienen de programas europeos o nacionales.

Aquí lo único que he hecho fue sustituir dobles espacios por uno simple en las cadenas de texto de los nombres de las entidades, asignado un id y combinado las consultas.

Pero luego me pareció gracioso, fíjate tú, usar el símil de los discursos políticos cuando quieren justificar cobrar más impuestos: hospitales, colegios y carreteras. Así que he asignado una categorización en función de las palabras presentes en el string del nombre de la entidad.

Por ejemplo, si contienen «sanidad», «salud», «sanitaria» u «hospital» los categorizo como «Hospitales», no porque esos contratos vayan para hospitales sino porque es el discurso que nos venden a través de los medios. Vamos, una coña. Espero que se entienda.

De todas formas tengo pensado cambiar estas categorías por otra más seria. Pero por ahora, así se queda. Llama la atención que el grupo de entidades que más presupuestos otorgan, con diferencia, son las vinculadas a la sanidad pública.

Y que el área de educación, lo que llamé de coña «Colegios», aunque tiene un presupuesto abultado, apenas concede contratos si lo comparamos con la categoría «Hospital» (algo así como el 53%). Incluso muchos menos que la categoría «Resto» que son los que aún no he categorizado por pereza y porque esto llevará su tiempo.

Hasta «Turistas», la categoría de coña para lo vinculado con «turismo», es ligeramente mayor que la vinculada al área de educación.

De todas formas ésta es una idea en proceso a la que todavía le falta madurar un poco más. Esto parte porque hay entidades repetidas. Como el periodo de datos comprende dos mandatos en el gobierno autonómico y es habitual estar cambiando de nombres cada vez que alguien nuevo entra a mandar, aunque sean las mismas funciones (más gasto en papelería y publicidad, por supuesto), quiero agruparlas por áreas de gasto.

Por cierto, la coña de «Carreteras» hace referencia a «obras», «infraestructuras» y «vivienda».

Desde luego algo podemos aprender ya. Si como empresa quieres aspirar a algún contratito millonario, el grupo de la sanidad pública es el que más dinero reparte (aún no sé como será la cosa por número de contratos otorgados).

Y hasta aquí el divertimento de hoy. La verdad es que tengo ya ganas de lanzar la versión 1.0 y me estoy resistiendo a publicar una versión beta previa no sea que se escape algún error en las medidas o la interpretación.

Mañana, tal vez, más.

(Salvo que alguien me llame para que pare y me pague por dejarlo aquí. Podría pasar.) XD

8. Análisis por objetivo simple: ¡A por los contratos para mascarillas!

(27 noviembre 2024)

Hoy lo voy a hacer al revés, mientras el modelo se carga en el Server 2022 que ya empieza a ir a la pata coja.

Lo primero quiero añadir las columnas de números de contratos a las entidades adjudicadoras y aparco lo de la categorización global por ámbito de inversión pública (sanidad, turismo, obras, etc., a las que ya sabes que le puse unas etiquetas de coña) para tomármelo con calma.

En base a esto vemos varias cosas interesantes. En todo el periodo de análisis la entidad que más dinero adjudicó fue la Consejería de Sanidad por un total de +1.200 millones de euros. Hay que tener en cuenta que esto solo la Consejería. Los Hospitales o el Servicio Canario de Salud o las Direcciones de Áreas de Salud suman a parte.

De ahí que si contamos todas las entidades que tienen en su nombre «Salud», «Sanidad», «Hospital» o «Sanitaria» el total acumulado supere el 53% tanto en importe adjudicado como en número de contratos otorgados.

Aún así sorprende que la institución que mayor cantidad de contratos adjudicó fue el Instituto Tecnológico de Canarias S.A., nuestro ITC, ese querido ITC. En fin, quién sepa, ya sabe. En total en todo el periodo otorgó 23.710 contratos por un importe total de +77 millones de euros. ¿Un error del CSV? Desde luego parece un poco disparate. Eso suponen +4.500 contratos anuales de media (2020 al 2024). ¡Para ser exactos casi 13 contratos por día, durante todos los días del año, durante todos los años del periodo!

Además de la categorización de coña que hice y que tendré que montar en serio hay otro problemilla con los nombres de las entidades adjudicadoras en sí mismos, ya que es muy habitual que de un gobierno a otro se cambien los apellidos de las Consejerías, Direcciones Generales, Subdirecciones, etc.

Homogeneizar eso puede ser un verdadero quebradero de cabeza porque en no pocas ocasiones se debe a que las responsabilidades pasan de un lado para otro.

Ya me lo replantearé en la siguiente versión del Análisis.

Por ahora ya sabes que eso está así.

Lo segundo que quiero hacer es recuperar objeto_contrato, una columna que empecé a analizar con las funciones IA de DAX pero que deseché para no andar todo el proyecto a la pata coja. Quiero crear una columna de categorización en función de la presencia de ciertas palabras. La primera será, ¡cómo no!, en cuántos objetos de contrato aparece la palabra «mascarilla». Y a partir de ahí totalizar, saber quién, cuánto y a quién.

Esta columna me servirá para en el futuro sacar más categorías.

Así que, vamos allá.

Bueno, ¿quieres chismosear o no? Ok, pero antes, una aclaración.

El cuadro de mandos que ves, o para ser más exacto, la página de ese cuadro de mandos que ves justo encima de este párrafo, representa dos tablas con las entidades adjudicadoras y adjudicatarias pero filtradas por aquellas en las que la palabra «mascarilla» aparece en el objeto del contrato. Sería pertinente añadir esa aclaración en un comentario en la parte inferior o un filtro para más adelante poner otras categorizaciones como, por ejemplo, «turista» o «turismo», «seguridad», «investigación», etc…

Dicho esto, vayamos al morbo.

Pues resulta que la Dirección General de Recursos Económicos del Servicio Canario de Salud otorgó casi la totalidad del importe de contratos que contienen la palabra «mascarilla» en el objeto del mismo, según el propio CSV que publica el Gobierno de Canarias en su web datos.canarias.es, aclaro.

En total se adjudicaron casi 239 millones de euros en todo el periodo (2020 a 2024) de los que 209 millones los repartió el citado órgano.

La entidad privada que más dinero recibió, en 16 contratos, fue A60407715, con un monto de casi 58 millones de euros. Y le he preguntado a ChatGPT qué empresa española tiene ese CIF y me dice que, según InfoEmpresa y Axesor:

El CIF A60407715 pertenece a Damco Trading & Services, S.A., una empresa registrada en Madrid. Se clasifica como microempresa y tiene entre 1 y 10 empleados. Su actividad incluye operaciones de comercio y servicios, y cuenta con un capital social en el rango de 50.001 a 100.000 €. Ha registrado varias marcas y ha estado involucrada en actos mercantiles recientes, como modificaciones estatutarias en noviembre de 2023.

Y haciendo una búsqueda de prensa en Google, pues:

Si repito el proceso con la segunda empresa que más dinero obtuvo en contratos donde la palabra «mascarilla» aparece en el objeto del mismo, pues:

El CIF B50760123 pertenece a Soluciones de Gestión y Apoyo a Empresas S.L., una empresa registrada en Zaragoza. Su actividad principal se enmarca dentro del código CNAE 4619, que corresponde a intermediarios del comercio de productos diversos. Además, está clasificada bajo el código SIC 5199, relacionado con productos diversos perecederos. La empresa tiene entre 10 y 49 empleados y factura anualmente entre 10 y 50 millones de euros.

Y repitiendo la búsqueda en Google:

Entre ambas empresas se llevaron casi 100 millones de euros que fueron repartidos entre 2020 y 2021, recibiendo cada una 16 contratos de la Dirección de Recursos Económicos del Servicio Canario de Salud.

¿Conclusión del día de hoy?

Paga impuestos, que son para carreteras, hospitales, colegios… y comisiones.

(Malditos hijos de…)

Mañana seguramente publicaré la versión 1.0 del cuadro de mandos, para que juegues con él.

Permanece atento. O atenta.

9. Presentando la versión 1.0 del Análisis

(28 noviembre 2024)

Noveno día traquinando con los datos de contratos adjudicados por el Gobierno de Canarias (y sus distintas entidades que lo conforman, ya sean Consejería, Direcciones o Subdirecciones, recursos como Hospitales Públicos o empresas públicas como ITC).

Por cierto, para mayor coña, mientras escribo esto me salta una publicidad de la Agencia Tributaria que dice que lo que doy vuelve a mí, que hospitales, carreteras y colegios. Más comisiones, añado yo.

Eso después del mosqueo que me agarré ayer analizando el asunto de las mascarillas. En fin, página pasada.

Y ojo con tus valoraciones. Yo no estoy en contra de aportar a mi comunidad una parte de lo que produzco (parte que considero que entre el 10% y el 25% podría ser adecuado, no el +45% como aporto ahora, eso es un robo).

A mí lo que me cabrea es la corrupción y la corruptela, el silencio de quienes lo saben y como las estructuras políticas se aprovechan de las estructuras públicas para medrar, robar y amasar. Me cabrea la falta de eficiencia, eficacia y, sobre todo, efectividad. Se hace mucho pero muy poco da un resultado efectivo, es decir, que se consigue lo que se espera y además es duradero en el tiempo, provoca un cambio real y positivo.

Volviendo a este proyectito, que para eso estamos aquí, hoy voy a incrustar, justo aquí debajo, el cuadro de mandos, para que juegues e interactúes con él.

Recuerda al respecto varias cosas:

Lo llamo versión 1.0 porque es la primera versión que publico y si bien el proceso ETL y representación gráfica está exenta de error, no se puede decir eso mismo de la fuente de información. Por ejemplo, siguen presentes muchos CIFs raros en entidad_adjudicataria que es quien recibe la pasta. No son muchos con respecto al volumen total, pero sí que hay unos pocos cientos de CIFs que son «0» o «00000000» o «-» y otras rarezas similares.
La intención analizar era (y sigue siendo) tener una visión global y saber quién da más dinero y quién recibe más dinero en forma de contratos (al menos en el periodo de datos analizado). Lo de las «mascarillas» fue un juego para crear algo de morbo y atraer un poco de tráfico. Eso no quiere decir que los datos no digan lo que dicen. Lo que ves es el resultado de contar, sumar y agrupar. No tiene mayor ciencia.
En próximas versiones quiero añadir más conceptos, como «publicidad», «coche oficial», «investigación» y alguna otra palabra sensible como «virus». Recuerda que lo que hago es contabilizar contratos que incluyen la palabra en su objeto_contrato.
En algo sí debemos ser estrictos. En leer lo que vemos de manera correcta. Cualquier interpretación que se dé como resultado es, eso, una interpretación, una opinión, que como los culos… todos y todas tenemos uno.
El panel está pensado para ser usado en entornos de escritorio. No está preparado, aún, para versiones móviles. Algún día, en alguna futura versión, cuando alguien me pague por usar este juguete y mejorarlo. ¡Je!

En fin, dicho todo esto, vamos con los últimos retoques y la publicación del cuadro de mandos.

Antes he añadido unas cuantas tartas a la página de «mascarillas» pero para verlos vas a tener que usar la botonería que encontrarás a la izquierda. La página por defecto muestra la lista de entidades adjudicadoras y adjudicatorias, pero si pulsas en Mascarillas Cómo verás el reparto de esos contratos en función del tipo de contrato, el tipo de procedimiento, el medio de publicación y el tipo de tramitación.

Estas tartas te van a dar una fotografía muy definida sobre cómo se realizaron estas contrataciones, a priori legítimas (aunque por alguna razón están bajo investigación, si no ya en los tribunales). La verdad, me ha dado pereza… pereza no, tristeza… seguir indagando.

Ahora te toca a ti.

Invitado, invitada estás a jugar con mi juguete.

El de aquí debajo.

¡Con el cuadro de mandos!

10. Evaluación: las dudas razonables

(30 noviembre 2024)

Hace dos días que publiqué el cuadro de mandos y nadie me ha contactado aún para ofrecerme uno cuantos billetes de 500 para que lo haga desaparecer y olvide este proyecto.

[set Ironía = off]

Y ya me han asaltado algunas dudas. Bueno, una duda. Sobre la interpretación de los códigos de 6 dígitos en la columna entidad_adjudicataria.

¿Realmente la 249240 es CEPSA Aviación S.A.?
¿Por qué al buscar «249240 cif empresa» en Google me devuelve la página de einforma.com?

Es cierto que el código 249240 aparece vinculado a esa empresa en el BORME (Boletín del Registro Mercantil) pero es el número de inscripción de actos. No el de la empresa. En este caso el nombramiento de un apoderado. De hecho, el número anterior y posterior también corresponde como acto inscrito de CEPSA Aviación y también por nombramientos de apoderados.

Plantear estas cuestiones no solo afectan a este código en la columna CIF sino también a los otros 74 presentes.

Vamos a crear una segunda tabla de hechos que solo contenga el objeto del contrato, el CIF de la adjudicataria y el número de caracteres presentes en el campo CIF.

Y a ver qué encuentro.

Pues por lo pronto los 4 contratos por casi 345 millones de euros se otorgaron para:

Ejecución de la obra "Puerto del Rosario-La Caldereta. Variante de las FV-1, FV-3 y FV-2".

Por lo tanto, está claro que mi deducción estaba errada.

(Añado comentario en el día que llegué a esa conclusión errada.)

He añadido una página «Investigados» en el dashboard donde además de las columnas descritas también tienes el importe_ofertado pero con una diferencia. Si bien en el análisis previo las filas con un valor missing en esta columna fueron eliminadas, en el facts de investigados se han dejado sustituyendo el valor «_U» por un «0,00». Interesa saber qué contratos no tienen importe declarado.

Actualizo el dashboard y te lo dejo para que juegues con él. Recuerda que es interactivo y puedes filtrar, ordenar y otras pijerías similares. La última página, la de investigados, ni siquiera le he puesto el formato de presentación y será la primera que veas por defecto.

¡Y, ojo, que la cosa no es asunto baladí! Los contratos de las 74 entidades con un CIF de 6 dígitos suman 799.156.013,43 €, es decir, casi el 9% de los +9.000 millones de euros adjudicados en todo el periodo.

Necesito a alguien del sector público que me aclare: primero, qué significan estos códigos; y, segundo, cómo los interpreto, cómo los investigo, dónde sé a qué entidad pertenece cada código.

Y esto no ha hecho nada más que empezar.

Investigando una cosa sigo descubriendo otras, como que hay +5.500 contratos cuyo CIF contiene solo 2 caracteres.

O por qué el ITC registra +23.000 contratos (es posible porque todos sus gastos figuren en esa base de datos, es decir, que no solo estén los contratos otorgados sino por ejemplo cuando se le paga a alguien ir a un congreso o cuando se compran unos tornillos o bombillas que hicieron falta en una ferretería).

Creo que este proceso de puesta en duda y evaluación da comienzo a la versión 2.0 de este análisis.

Versión 1.0. Fecha: 28 noviembre 2024.

(Apúntate esta URL porque continuará. Avisaré en mi perfil de LinkedIn de las actualizaciones.)

Análisis de la adjudicación de contratos públicos en Canarias (2020-2024)