Imputación¶

Ejercicio imputation-annex.pdf · PDF

Tu profesor está a punto de calcular la media de la clase. Cuatro alumnos, tres notas en el cuaderno de calificaciones: 6, 8 y 10. Tu celda está vacía. ¿Qué debería ir en ella?

¿Un cero? Entonces la media baja de 8 a 6, y acabas de suspender un examen al que faltaste por enfermedad. ¿La media de la clase? Entonces recibes una nota por un trabajo que nadie vio. ¿Nada? Entonces la media se calcula en silencio sin ti. Tres opciones, tres medias distintas, y ninguna es "obviamente la correcta". Cada celda vacía de una tabla plantea esta misma pregunta. Esta página trata de responderla con honestidad.

Un vacío no es un cero¶

Cuatro lecciones de limpieza, y estas celdas de games siguen vacías:

No puedes limpiar una celda vacía. No hay nada dentro que reparar. Solo puedes decidir qué hacer con ella.

La imputación es decidir qué valor, si hay alguno, sustituye a uno ausente, y dejar escrita esa decisión.

La analogía de toda la página es el cuaderno de calificaciones del profesor del principio: cada decisión que tomes aquí es una de sus filas.

Un cero es una nota. Un vacío es una pregunta: ¿por qué está vacío? La respuesta a esa pregunta, no el SQL, decide la opción.

flowchart TB
    Q1{"¿Sabes qué significa<br>el vacío?"}
    Q1 -- "Sí" --> C["<b>Una constante</b><br>escribe lo que significa<br>y añade un indicador"]
    Q1 -- "No" --> Q2{"¿Hay pocos vacíos,<br>repartidos al azar?"}
    Q2 -- "Sí" --> M["<b>Un valor central</b><br>media o mediana<br>y añade un indicador"]
    Q2 -- "No: muchos, o un grupo especial" --> L["<b>Déjalo en NULL</b><br>y añade un indicador"]
    Q1 -- "La fila no sirve sin él" --> D["<b>Borra la fila</b><br>solo si son muy pocas"]

Qué le hace NULL a un cálculo¶

Antes de rellenar nada, necesitas saber qué hace la base de datos con una celda vacía cuando la dejas en paz. Hace tres cosas distintas, y ninguna te pide permiso.

Velo en la tabla real. La misma columna, dos medias:

SELECT ROUND(AVG(price)::numeric, 2)              AS avg_ignoring_null,
       ROUND(AVG(COALESCE(price, 0))::numeric, 2) AS avg_free_as_zero
FROM games;

¿Cuál es "el precio medio de un juego en Steam"? Depende de lo que signifique el vacío. AVG se saltó en silencio las filas vacías: 8.95 es una suma dividida entre 23,593 juegos, no entre 26,688. Nadie se lo pidió.

COALESCE: el valor y la nota al margen¶

COALESCE(a, b, ...) devuelve el primero de sus argumentos que no es NULL. Léelo en voz alta como "a, y si está vacío, b".

SELECT game,
       COALESCE(price, 0)::numeric(8,2) AS price,     -- the value we filled in
       (price IS NULL)                  AS is_free    -- the note in the margin
FROM games
WHERE game IN ('Dota 2', 'Half-Life 2');

Una columna indicadora (flag column) es una columna extra, verdadera o falsa, que registra algo sobre la fila. Aquí: "este valor estaba vacío en los datos originales".

Los dos siempre viajan en pareja: el valor rellenado y un indicador que recuerda que estaba vacío. Una vez sobrescrito el NULL, nadie puede distinguir un 0 real de uno inventado. El indicador es la nota que el profesor escribe al margen del cuaderno.

El sustituto debe tener el mismo tipo que la columna. COALESCE(developer, 'Unknown') funciona: texto por texto. COALESCE(developer, 0) se detiene con COALESCE types text and integer cannot be matched. Un error ruidoso, del bueno.

price: una constante que puedes defender¶

La lección de categorización etiquetó como Free los juegos sin precio. Eso era una suposición. Ahora comprueba si tenías derecho a hacerla. Busca pruebas antes de rellenar nada:

SELECT COUNT(*) FROM games WHERE price = 0;        -- 0
SELECT MIN(price) FROM games;                      -- 0.49
SELECT game, owners FROM games
WHERE price IS NULL ORDER BY number LIMIT 4;

Tres pruebas:

El conocimiento del dominio es lo que sabes del mundo real que hay detrás de los datos: cómo funciona Steam, qué es un juego free-to-play. Ninguna consulta te lo puede dar.

El conocimiento del dominio dice que un juego sin precio en Steam es de acceso gratuito, y los datos están de acuerdo. Decisión: una constante, 0, más el indicador is_free.

Entonces, ¿qué media publicas? Las dos son respuestas correctas a preguntas distintas:

metascore: por qué no se rellena¶

Ahora el gran agujero: falta el 89.3% de las puntuaciones. La tentación es un valor central.

La media es la suma de los valores dividida entre cuántos hay. La mediana es el valor del medio cuando los ordenas. Las dos describen un valor "típico"; las estudiarás como es debido en Estadística exploratoria.

La metascore media es 71.9 y la mediana es 73. Así que rellena los vacíos con 72, y cuenta:

SELECT COUNT(*) FROM games WHERE metascore = 72;                -- 114
SELECT COUNT(*) FROM games WHERE COALESCE(metascore, 72) = 72;  -- 23952

114 juegos sacaron realmente un 72. Tras el relleno, 23,952 juegos "sacaron" un 72. Mira lo que eso le hace a la columna:

Lo peligroso: la media apenas se mueve. Era 71.9 y pasa a ser 72.0. La media esconde el daño. Todo lo demás de la columna (cuán dispersas están las puntuaciones, qué juegos son buenos, cuáles malos) se ha aplastado en un solo pico.

¿Quién falta?¶

Hay un segundo motivo, y es el más fuerte. Comprueba qué juegos no tienen puntuación. Primero los juegos pequeños:

SELECT (owners LIKE '0%')  AS under_20k_owners,
       COUNT(*)            AS games,
       COUNT(metascore)    AS with_score,
       ROUND(100.0 * COUNT(metascore) / COUNT(*), 1) AS pct_with_score
FROM games GROUP BY 1;

Entre los juegos con 20,000 propietarios o más, el 27.0% tiene puntuación. Entre los más pequeños, el 2.7%. Diez veces menos. La crítica reseña los juegos de los que la gente ha oído hablar.

Los valores faltan al azar cuando un vacío es igual de probable en cualquier fila. No faltan al azar cuando los vacíos se amontonan en un grupo concreto.

Aquí se amontonan en los juegos pequeños y desconocidos. Escribir 72 en esas celdas diría "un juego decente, según la crítica" de 23,838 juegos que ningún crítico abrió jamás. Y escribir 0 sería peor: "el peor juego jamás reseñado".

Decisión: déjalo en NULL, y añade el indicador (metascore IS NOT NULL) AS has_metascore. Cualquier gráfico de puntuaciones deberá entonces decir "basado en el 11% de los juegos que fueron reseñados".

La misma herramienta SQL, veredicto opuesto.

Columnas de texto: una etiqueta para el vacío¶

Una columna de texto no puede tener media. Para developer y publisher, dale al vacío su propia categoría, para que GROUP BY lo muestre en lugar de esconderlo:

SELECT COALESCE(developer, 'Unknown') AS developer, COUNT(*) AS games
FROM games GROUP BY 1 ORDER BY games DESC LIMIT 3;

NULLIF: cuando un valor debería ser un vacío¶

También existe el problema opuesto: un valor que debería estar vacío y no lo está. La auditoría lo encontró en average_playtime: 0 en el 96.5% de los juegos, porque nadie lo midió.

NULLIF(x, y) devuelve NULL cuando x es igual a y, y x en caso contrario. Convierte un valor falso otra vez en un vacío honesto.

SELECT ROUND(AVG(average_playtime)::numeric, 1)            AS with_zeros,
       ROUND(AVG(NULLIF(average_playtime, 0))::numeric, 1) AS zeros_as_null,
       COUNT(NULLIF(average_playtime, 0))                  AS measured
FROM games;

Una media de 9.1 con los ceros falsos, 262.1 sin ellos, calculada sobre los 922 juegos que sí se midieron. Casi treinta veces mayor. Los ceros arrastraban la media hasta casi nada.

El registro de imputaciones¶

Un registro de imputaciones es el documento escrito de cada decisión de imputación: columna, cuánto faltaba, qué hiciste y por qué.

Este es el de games. Cada línea es una fila del cuaderno:

Pruébalo: escribe tu registro de imputaciones¶

Abre SQL → SQL Lab, base de datos Dades classe, esquema public. Cada consulta es un SELECT; no se modifica nada. Ejecuta una sentencia cada vez.

Paso 1 — Mide el agujero. Ejecuta esto para price, y luego repítelo para metascore, developer y publisher:

SELECT COUNT(*)                 AS total_rows,
       COUNT(price)             AS with_value,
       COUNT(*) - COUNT(price)  AS missing,
       ROUND(100.0 * (COUNT(*) - COUNT(price)) / COUNT(*), 1) AS pct_missing
FROM games;

Deberías ver 11.6, 89.3, 0.6 y 0.4.

Paso 2 — price. Ejecuta las tres consultas de pruebas y después la consulta de las dos medias. Escribe qué media publicarías, y para qué pregunta.

Paso 3 — metascore. Ejecuta los dos recuentos con 72 y la consulta de "quién falta". Escribe una frase: ¿por qué 72 es una mentira para un juego que nadie reseñó?

Paso 4 — Columnas de texto. Ejecuta la consulta con COALESCE(developer, 'Unknown'). ¿Quién es el "mayor desarrollador", y por qué es una trampa?

Paso 5 — El registro. Escribe una línea por columna: borrar, constante, valor central, o dejar vacío y marcar.

Ideas clave¶

• La imputación es decidir qué sustituye a un valor ausente. Es una suposición escrita en SQL, así que hay que justificarla y dejarla por escrito.
• AVG, SUM y COUNT(column) se saltan NULL; la aritmética y las comparaciones con NULL dan NULL. Una media esconde sobre cuántas filas se calculó.
• COALESCE(x, value) rellena un vacío; empareja siempre con una columna indicadora que recuerde que el valor estaba vacío. NULLIF(x, value) hace lo contrario.
• Usa una constante cuando sabes qué significa el vacío (price: gratuito significa 0), con conocimiento del dominio y pruebas.
• Usa un valor central (media o mediana) solo para pocos vacíos que faltan al azar.
• Déjalo en NULL y márcalo cuando no lo sepas o la mayor parte de la columna esté vacía: rellenar metascore con 72 convierte 114 valores reales en 23,952.
• Borra filas solo cuando no sirven y son muy pocas.

Comprobación rápida¶

Las celdas vacías ya tienen una decisión y una razón escrita. Has dejado de reparar columnas; es hora de inventar otras nuevas. A continuación: Variables sintéticas, donde por fin se construye la tabla limpia games_clean.