Calidad de los datos¶

Ejercicio data-quality-audit-annex.pdf · PDF

Le haces a la tabla games una pregunta sencilla: ¿cuál es el juego más antiguo? Ordenas por fecha de lanzamiento y la respuesta llega al instante. Un juego de octubre de 2014. Justo detrás, uno de abril de 2004.

2014 antes que 2004. La consulta no falló. No hubo ningún mensaje en rojo. La tabla simplemente te dio una respuesta equivocada con la cara muy seria. Eso es lo que hacen los datos sucios: no se cuelgan, mienten en silencio. Antes de limpiar nada, necesitas saber exactamente dónde está la suciedad. Esta página te enseña a mirar.

Abre el cofre antes de fundir¶

La calidad de los datos es hasta qué punto puedes fiarte de una tabla para responder a tu pregunta: sus valores están presentes, son correctos, están escritos de una forma coherente y no están repetidos.

En el videojuego de crafteo que usamos todo el año, la Unidad 1 era farmear y saquear: traías los materiales a casa. La Unidad 2 es el horno: prepararlos. Pero nadie funde a ciegas. Primero abres el cofre del botín y miras: huecos vacíos, el mismo objeto dos veces, objetos rotos, una espada con estadísticas absurdas.

Una auditoría de datos es esa mirada. Inspeccionas una tabla, cuentas lo que tiene mal y lo apuntas, sin cambiar nada.

Nuestro cofre es games: 26,688 juegos de Steam, uno por fila, recogidos del sitio de estadísticas Steam Spy y dejados sucios a propósito. Puedes descargar el archivo desde la página de Recursos.

flowchart LR
    A["<b>Auditar</b><br>abrir el cofre, contar la suciedad"] --> B["<b>Decidir</b><br>corregir, marcar o dejar"]
    B --> C["<b>Limpiar</b><br>el horno: las próximas lecciones"]
    C --> D["<b>Informar</b><br>decir qué hiciste y qué queda"]

Cuatro tipos de suciedad¶

Casi todo lo que puede estar mal en una tabla cae en cuatro grupos. Cada uno tiene su propia forma de encontrarse.

Las próximas cuatro secciones los tratan uno a uno, sobre games. Cada número de abajo sale de ejecutar la consulta sobre la tabla de clase.

Valores ausentes: los huecos vacíos¶

Un valor ausente es una celda sin nada dentro. En SQL se escribe NULL, que significa "ningún valor", no cero ni una palabra vacía.

Ya conoces la herramienta de la Unidad 1. COUNT(*) cuenta filas. COUNT(column) cuenta solo las filas donde esa columna no está vacía. La diferencia es el número de huecos vacíos.

SELECT COUNT(*) AS total,
       COUNT(price) AS with_price,
       COUNT(*) - COUNT(price) AS no_price
FROM games;

Haz lo mismo con todas las columnas, en un único SELECT, y tendrás la imagen completa:

Escribe siempre el porcentaje junto a la cuenta. "23,838 puntuaciones vacías" suena a mucho. "El 89.3% de la columna está vacío" te dice que la columna casi no existe.

Valores ausentes disfrazados¶

Algunos huecos vacíos no lo parecen. Prueba estos dos:

SELECT COUNT(*) FROM games WHERE average_playtime = 0;   -- 25757
SELECT COUNT(*) FROM games WHERE price = 0;              -- 0

average_playtime vale 0 en 25,757 filas: el 96.5% de la tabla. Nadie cree que el 96.5% de los juegos de Steam se hayan jugado cero minutos. Ese cero significa "no tenemos datos". Es un valor ausente con disfraz.

Y price = 0 no encuentra nada. Ni un solo juego cuesta exactamente cero. Entonces, ¿dónde están los juegos gratuitos? Son los 3,095 que tienen NULL.

Tipos y formatos incorrectos: texto disfrazado¶

Un tipo de dato es la clase de valor que una columna puede contener: número entero, número decimal, texto, fecha. El tipo decide qué puedes hacer con la columna.

Pregúntale a la base de datos de qué está hecha cada columna:

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'games'
ORDER BY ordinal_position;

Dos columnas son text y no deberían: release_date (una fecha) y owners (una cantidad). Esto es lo que rompió la consulta del principio de la página:

SELECT game, release_date FROM games ORDER BY release_date LIMIT 3;

El texto se ordena como un diccionario, carácter a carácter. 8 va antes que cualquier letra, y Apr va antes que Feb porque la A va antes que la F. Una fecha guardada como texto no se puede ordenar, ni filtrar por año, ni poner en un eje de tiempo.

Y empeora. Un formato es la manera en que se escribe un valor. La mayoría de las fechas de aquí tienen el aspecto Nov 16, 2004. ¿Son todas así?

SELECT release_date, COUNT(*) AS n
FROM games
WHERE release_date NOT LIKE '___ %, ____'   -- 3 letters, space, day, comma, 4 digits
GROUP BY release_date
ORDER BY n DESC;

42 filas tienen otra forma: 41 dan solo el mes y el año (Oct 2010 tres veces, Jun 2009 dos...) y una está escrita al revés, 8 Oct, 2014. Una columna, tres maneras de escribir una fecha.

owners tiene la misma enfermedad. Solo contiene 13 valores distintos, y todos son rangos escritos como texto, como 10,000,000 .. 20,000,000. El más común es 0 .. 20,000, con 17,923 juegos. Ordénalo para encontrar el juego con más propietarios y obtendrás un disparate:

SELECT game, owners FROM games ORDER BY owners DESC LIMIT 3;

Dota 2, con 100,000,000 .. 200,000,000, pierde. Como texto, 5 gana a 1, así que "500 mil" le gana a "100 millones".

Duplicados: el mismo objeto dos veces¶

Un duplicado es la misma cosa real registrada en más de una fila.

¿Hay algún juego dos veces en la tabla? Agrupa por nombre y quédate con los nombres que aparecen más de una vez:

SELECT game, COUNT(*) AS n
FROM games
WHERE game IS NOT NULL
GROUP BY game
HAVING COUNT(*) > 1
ORDER BY n DESC, game;

Salen 74 nombres, que cubren 149 filas. Arriba del todo, Dark Matter, tres veces. Antes de llamarlo duplicado, míralo:

SELECT number, game, release_date, developer
FROM games WHERE game = 'Dark Matter';

Tres desarrolladores, tres fechas. Son tres juegos distintos que casualmente comparten nombre. Si borras dos de ellos, has destruido datos reales.

Valores atípicos: el objeto con estadísticas absurdas¶

Un valor atípico es un valor muy alejado de todos los demás. Puede ser un error de escritura. También puede ser perfectamente real.

Los precios están en dólares estadounidenses. Mira los extremos de la columna:

SELECT MIN(price), MAX(price), ROUND(AVG(price)::numeric, 2) AS mean FROM games;

El juego medio cuesta 8.95 y el más caro cuesta 595.99. Más de sesenta veces la media. ¿Una errata? Mira antes de decidir:

SELECT game, price, developer
FROM games ORDER BY price DESC NULLS LAST LIMIT 4;

Una herramienta para hacer juegos, un editor de vídeo, un programa de diseño de moda en 3D. Steam también vende software profesional, y el software es caro. 27 filas cuestan más de 100 dólares, y son sobre todo productos como estos, no errores de escritura.

El inventario de suciedad¶

Un inventario de suciedad es el resultado escrito de una auditoría: una línea por problema, cada una con su evidencia y una decisión.

Cada línea tiene cuatro partes. La regla para la tercera es estricta: sin número, no hay evidencia. "Hay NULLs" no es una auditoría. "3,095 NULL en price, el 11.6% de las filas" sí lo es.

Para la decisión tienes tres verbos:

Este es el inventario de games. También es el mapa del resto de la unidad:

Pruébalo: audita games tú mismo¶

Abre SQL → SQL Lab, elige la base de datos Dades classe y el esquema public. Ejecuta una sentencia cada vez: selecciónala y pulsa Run. Si ejecutas un bloque entero, SQL Lab solo muestra el último resultado.

Paso 1 — Tamaño y tipos. Ejecuta SELECT COUNT(*) FROM games; y después la consulta a information_schema.columns de esta página. Deberías ver 26,688 filas y dos columnas text que no deberían ser texto.

Paso 2 — Valores ausentes. Escribe un solo SELECT con un COUNT(*) - COUNT(column) para cada una de las diez columnas. Tus números deberían coincidir con la tabla de "Valores ausentes". Después ejecuta las dos comprobaciones de disfraz (average_playtime = 0 y price = 0).

Paso 3 — Formatos incorrectos. Ejecuta la consulta con NOT LIKE y las dos consultas sobre owners. Deberías poder decir cuántas fechas tienen una forma distinta (42) y por qué DOOM 3 no es realmente el juego con más propietarios.

Paso 4 — Duplicados. Ejecuta la consulta con HAVING COUNT(*) > 1 y después mira las filas de Dark Matter. Escribe una frase: ¿son el mismo juego?

Paso 5 — Valores atípicos. Ejecuta la consulta de MIN/MAX y los precios más altos. Decide, con una razón, si 595.99 es un error de escritura.

Ideas clave¶

• Una auditoría de datos significa mirar y contar sin cambiar nada. Su único producto es una lista.
• Hay cuatro tipos de suciedad: valores ausentes, duplicados, tipos o formatos incorrectos y valores atípicos.
• COUNT(*) - COUNT(column) da las celdas vacías de una columna. Añade siempre el porcentaje.
• NULL y 0 son cosas distintas. Un NULL puede significar "desconocido" (metascore) o llevar significado (un juego gratuito), y un 0 puede ser un valor ausente disfrazado (average_playtime).
• Una fecha o un número guardado como texto se ordena como un diccionario y no se puede calcular con él.
• Mismo nombre no es un duplicado, y un valor atípico no es un error: mira la fila antes de decidir.
• Un inventario de suciedad tiene cuatro partes por línea: columna, tipo de suciedad, evidencia con un número y una decisión (corregir, marcar o dejar).

Comprobación rápida¶

Ahora ya sabes qué está sucio, y tienes un plan. Es hora de encender el horno: a continuación, Limpieza: tipos y formatos, donde release_date por fin se convierte en una fecha de verdad.