Imputació¶

Exercici imputation-annex.pdf · PDF

El teu professor està a punt de calcular la mitjana de la classe. Quatre alumnes, tres notes al quadern: 6, 8 i 10. La teva cel·la és buida. Què hi ha d'anar?

Un zero? Aleshores la mitjana baixa de 8 a 6, i acabes de suspendre un examen que et vas perdre perquè estaves malalt. La mitjana de la classe? Aleshores reps una nota per un treball que ningú no ha vist. Res? Aleshores la mitjana es calcula, en silenci, sense tu. Tres opcions, tres mitjanes diferents, i cap d'elles no és "evidentment la bona". Cada cel·la buida d'una taula fa aquesta mateixa pregunta. Aquesta pàgina va de respondre-la honestament.

Un buit no és un zero¶

Quatre lliçons de neteja, i aquestes cel·les de games encara són buides:

Una cel·la buida no es pot netejar. No hi ha res a dins per reparar. Només pots decidir què en fas.

Imputació és decidir quin valor, si n'hi ha cap, ocupa el lloc d'un valor absent, i deixar-ho escrit.

L'analogia de tota la pàgina és el quadern del professor de l'inici: cada decisió que prens aquí és una fila seva.

Un zero és una nota. Un buit és una pregunta: per què és buit? La resposta a aquesta pregunta, no l'SQL, decideix l'opció.

flowchart TB
    Q1{"Saps què vol dir<br>el buit?"}
    Q1 -- "Sí" --> C["<b>Una constant</b><br>escriu què vol dir<br>i afegeix un indicador"]
    Q1 -- "No" --> Q2{"Els buits són pocs<br>i repartits a l'atzar?"}
    Q2 -- "Sí" --> M["<b>Un valor central</b><br>mitjana o mediana<br>i afegeix un indicador"]
    Q2 -- "No: molts, o un grup especial" --> L["<b>Deixa'l NULL</b><br>i afegeix un indicador"]
    Q1 -- "La fila no serveix sense ell" --> D["<b>Esborra la fila</b><br>només si són molt pocs"]

Què fa NULL en un càlcul¶

Abans d'omplir res, has de saber què fa la base de dades amb una cel·la buida quan la deixes tal com és. Fa tres coses diferents, i cap no et demana permís.

Mira-ho a la taula real. La mateixa columna, dues mitjanes:

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

Quina és "el preu mitjà d'un joc a Steam"? Depèn de què vol dir el buit. AVG ha saltat les files buides en silenci: 8.95 és una suma dividida entre 23,593 jocs, no entre 26,688. Ningú no li ho ha dit.

COALESCE: el valor i la nota al marge¶

COALESCE(a, b, ...) retorna el primer dels seus arguments que no és NULL. Llegeix-ho en veu alta com "a, i si és buit, 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) és una columna addicional, certa o falsa, que registra alguna cosa sobre la fila. Aquí: "aquest valor era buit a les dades originals".

Les dues sempre viatgen en parella: el valor omplert i un indicador que recorda que era buit. Quan el NULL s'ha sobreescrit, ningú no pot distingir un 0 real d'un d'inventat. L'indicador és la nota que el professor escriu al marge del quadern.

El reemplaçament ha de tenir el mateix tipus que la columna. COALESCE(developer, 'Unknown') funciona: text per a text. COALESCE(developer, 0) s'atura amb COALESCE types text and integer cannot be matched. Un error sorollós, del bo.

price: una constant que pots defensar¶

La lliçó de categorització va etiquetar els jocs sense preu com a Free. Era una suposició. Ara comprova si en teníem dret. Busca proves abans d'omplir res:

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 proves:

El coneixement del domini és el que saps del món real que hi ha darrere de les dades: com funciona Steam, què és un joc free-to-play. Cap consulta no te'l pot donar.

El coneixement del domini diu que un joc sense preu a Steam és gratuït (free to play), i les dades hi estan d'acord. Decisió: una constant, 0, més l'indicador is_free.

Així doncs, quina mitjana publiques? Les dues són respostes correctes a preguntes diferents:

metascore: per què no s'omple¶

Ara, el gran forat: falta el 89.3% de les puntuacions. La temptació és un valor central.

La mitjana és la suma dels valors dividida pel nombre de valors. La mediana és el valor del mig quan els ordenes. Les dues descriuen un valor "típic"; les estudiaràs bé a Estadística exploratòria.

La mitjana del metascore és 71.9 i la mediana és 73. Així que omple els buits amb 72, i compta:

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

114 jocs van treure realment un 72. Després d'omplir, 23,952 jocs "han tret" un 72. Mira què li fa això a la columna:

La part perillosa: la mitjana gairebé no es mou. Era 71.9 i passa a ser 72.0. La mitjana amaga el desastre. Tota la resta de la columna (com de dispersades són les puntuacions, quins jocs són bons, quins dolents) s'ha aplanat en un únic pic.

Qui falta?¶

Hi ha una segona raó, i és la més forta. Comprova quins jocs no tenen puntuació. Primer els jocs petits:

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 els jocs amb 20,000 propietaris o més, el 27.0% té puntuació. Entre els més petits, el 2.7%. Deu vegades menys. Els crítics ressenyen els jocs de què la gent ha sentit parlar.

Els valors falten a l'atzar quan un buit és igual de probable a qualsevol fila. No falten a l'atzar quan els buits s'amunteguen en un grup concret.

Aquí s'amunteguen en els jocs petits i desconeguts. Escriure 72 en aquestes cel·les diria "un joc decent, segons els crítics" de 23,838 jocs que cap crític no ha obert mai. I escriure 0 seria pitjor: "el pitjor joc mai ressenyat".

Decisió: deixar-ho NULL, i afegir l'indicador (metascore IS NOT NULL) AS has_metascore. Qualsevol gràfic de puntuacions haurà de dir "basat en l'11% dels jocs que van ser ressenyats".

La mateixa eina SQL, un veredicte oposat.

Columnes de text: una etiqueta per al buit¶

Una columna de text no té mitjana. Per a developer i publisher, dona al buit la seva pròpia categoria, perquè GROUP BY el mostri en lloc d'amagar-lo:

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

NULLIF: quan un valor hauria de ser un buit¶

El problema contrari també existeix: un valor que hauria de ser buit i no ho és. L'auditoria el va trobar a average_playtime: 0 per al 96.5% dels jocs, perquè ningú no ho va mesurar.

NULLIF(x, y) retorna NULL quan x és igual a y, i x en cas contrari. Torna a convertir un valor fals en un buit honest.

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 mitjana de 9.1 amb els zeros falsos, 262.1 sense ells, calculada sobre els 922 jocs que sí que es van mesurar. Gairebé trenta vegades més gran. Els zeros estaven arrossegant la mitjana fins a no-res.

El registre d'imputació¶

Un registre d'imputació és l'anotació escrita de cada decisió d'imputació: columna, quant faltava, què has fet i per què.

Aquest és el de games. Cada línia és una fila del quadern:

Prova-ho: escriu el teu registre d'imputació¶

Obre SQL → SQL Lab, base de dades Dades classe, esquema public. Totes les consultes són un SELECT; no es modifica res. Executa una sentència cada vegada.

Pas 1 — Mesura el forat. Executa això per a price, i repeteix-ho per a metascore, developer i 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;

Hauries de veure 11.6, 89.3, 0.6 i 0.4.

Pas 2 — price. Executa les tres consultes de proves, i després la consulta de les dues mitjanes. Escriu quina mitjana publicaries, i per a quina pregunta.

Pas 3 — metascore. Executa els dos recomptes amb 72 i la consulta "qui falta". Escriu una frase: per què 72 és una mentida per a un joc que ningú no ha ressenyat?

Pas 4 — Columnes de text. Executa la consulta COALESCE(developer, 'Unknown'). Qui és el "desenvolupador més gran", i per què és una trampa?

Pas 5 — El registre. Escriu una línia per columna: esborrar, constant, valor central, o deixar-ho buit i marcar-ho.

Idees clau¶

• La imputació és decidir què ocupa el lloc d'un valor absent. És una suposició escrita en SQL, així que s'ha de justificar i deixar per escrit.
• AVG, SUM i COUNT(column) salten NULL; l'aritmètica i les comparacions amb NULL donen NULL. Una mitjana amaga sobre quantes files s'ha calculat.
• COALESCE(x, value) omple un buit; fes-lo sempre acompanyar d'una columna indicadora que recordi que el valor era buit. NULLIF(x, value) fa el contrari.
• Usa una constant quan saps què vol dir el buit (price: gratuït vol dir 0), amb coneixement del domini i proves.
• Usa un valor central (mitjana o mediana) només per a pocs buits que falten a l'atzar.
• Deixa'l NULL i marca'l quan no ho saps o la major part de la columna és buida: omplir metascore amb 72 converteix 114 valors reals en 23,952.
• Esborra files només quan no serveixen de res i són molt poques.

Comprovació ràpida¶

Les cel·les buides ja tenen una decisió i una raó escrita. Has deixat de reparar columnes; és hora d'inventar-ne de noves. A continuació: Variables sintètiques, on finalment es construeix la taula neta games_clean.