Qualitat de les dades¶

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

Fas una pregunta senzilla a la taula games: quin és el joc més antic? Ordenes per data de llançament i la resposta arriba a l'instant. Un joc d'octubre de 2014. Just darrere, un d'abril de 2004.

2014 abans que 2004. La consulta no ha fallat. No hi ha cap missatge en vermell. La taula t'ha donat una resposta errònia amb tota la tranquil·litat del món. Això és el que fan les dades brutes: no peten, menteixen en silenci. Abans de netejar res, has de saber exactament on és la brutícia. Aquesta pàgina t'ensenya a mirar.

Obre el cofre abans de fondre¶

La qualitat de les dades és fins on et pots refiar d'una taula per respondre la teva pregunta: els seus valors hi són, són correctes, s'escriuen d'una sola manera i no es repeteixen.

Al joc de crafting que fem servir tot l'any, la Unitat 1 era collir i saquejar: portaves els materials a casa. La Unitat 2 és el forn: preparar-los. Però ningú fon a cegues. Primer obres el cofre del botí i mires: ranures buides, el mateix objecte dues vegades, objectes trencats, una espasa amb unes estadístiques absurdes.

Una auditoria de dades és aquesta mirada. Inspecciones una taula, comptes què hi ha malament i ho apuntes, sense canviar res.

El nostre cofre és games: 26,688 jocs de Steam, un per fila, recollits del lloc d'estadístiques Steam Spy i deixats bruts a propòsit. Pots baixar el fitxer des de la pàgina de Recursos.

flowchart LR
    A["<b>Auditar</b><br>obre el cofre, compta la brutícia"] --> B["<b>Decidir</b><br>arreglar, marcar o deixar"]
    B --> C["<b>Netejar</b><br>el forn: les properes lliçons"]
    C --> D["<b>Informar</b><br>digues què has fet i què queda"]

Quatre tipus de brutícia¶

Gairebé tot el que pot anar malament en una taula cau en quatre grups. Cadascun té la seva manera de ser trobat.

Les quatre seccions següents els tracten un per un, sobre games. Cada nombre d'aquí avall surt d'executar la consulta a la taula de classe.

Valors absents: les ranures buides¶

Un valor absent és una cel·la sense res a dins. En SQL s'escriu NULL, que vol dir «cap valor», no zero i no una paraula buida.

Ja coneixes l'eina de la Unitat 1. COUNT(*) compta files. COUNT(column) només compta les files on aquella columna no és buida. La diferència és el nombre de ranures buides.

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

Fes el mateix per a totes les columnes, en un únic SELECT, i tindràs el quadre complet:

Escriu sempre el percentatge al costat del recompte. «23,838 puntuacions buides» sembla molt. «El 89.3% de la columna és buit» et diu que la columna gairebé no hi és.

Valors absents disfressats¶

Algunes ranures buides no ho semblen. Prova aquestes dues:

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

average_playtime és 0 en 25,757 files: el 96.5% de la taula. Ningú no es creu que el 96.5% dels jocs de Steam s'hagin jugat zero minuts. Aquest zero vol dir «no tenim dades». És un valor absent disfressat.

I price = 0 no troba res. Ni un sol joc costa exactament zero. Doncs on són els jocs gratuïts? Són els 3,095 amb NULL.

Tipus i formats incorrectes: text disfressat¶

Un tipus de dades és la classe de valor que una columna pot contenir: nombre enter, nombre decimal, text, data. El tipus decideix què pots fer amb la columna.

Pregunta a la base de dades de què està feta cada columna:

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

Dues columnes són text i no ho haurien de ser: release_date (una data) i owners (una quantitat). Això és el que va trencar la consulta de dalt de tot de la pàgina:

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

El text s'ordena com un diccionari, caràcter a caràcter. 8 va abans que qualsevol lletra, i Apr va abans de Feb perquè la A va abans que la F. Una data guardada com a text no es pot ordenar, ni filtrar per any, ni posar en un eix de temps.

Encara és pitjor. Un format és la manera com s'escriu un valor. La majoria de les dates d'aquí semblen Nov 16, 2004. Són totes així?

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 files tenen una altra forma: 41 donen només el mes i l'any (Oct 2010 tres vegades, Jun 2009 dues...) i una està escrita al revés, 8 Oct, 2014. Una columna, tres maneres d'escriure una data.

owners té la mateixa malaltia. Només conté 13 valors diferents, i tots són intervals escrits com a text, com ara 10,000,000 .. 20,000,000. El més habitual és 0 .. 20,000, amb 17,923 jocs. Ordena'l per trobar el joc amb més propietaris i obtindràs un sense-sentit:

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

Dota 2, amb 100,000,000 .. 200,000,000, perd. Com a text, 5 guanya a 1, de manera que «500 mil» guanya a «100 milions».

Duplicats: el mateix objecte dues vegades¶

Un duplicat és la mateixa cosa real registrada en més d'una fila.

Hi ha algun joc dues vegades a la taula? Agrupa per nom i queda't amb els noms que apareixen més d'una vegada:

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

Tornen 74 noms, que cobreixen 149 files. A dalt de tot, Dark Matter, tres vegades. Abans de dir-ne duplicat, mira-ho:

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

Tres desenvolupadors, tres dates. Són tres jocs diferents que casualment comparteixen nom. Esborra'n dos i hauràs destruït dades reals.

Valors atípics: l'objecte amb estadístiques absurdes¶

Un valor atípic és un valor molt allunyat de tots els altres. Pot ser un error de tecleig. Però també pot ser perfectament real.

Els preus són en dòlars dels EUA. Mira els extrems de la columna:

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

El joc mitjà costa 8.95 i el més car en costa 595.99. Més de seixanta vegades la mitjana. Un error de tecleig? Mira-ho abans de decidir:

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

Una eina per fer jocs, un editor de vídeo, un programa de disseny de moda en 3D. Steam també ven programari professional, i el programari és car. 27 files costen més de 100 dòlars, i són sobretot productes com aquests, no errors de tecleig.

L'inventari de brutícia¶

Un inventari de brutícia és el resultat escrit d'una auditoria: una línia per problema, cadascuna amb la seva evidència i una decisió.

Cada línia té quatre parts. La regla de la tercera és estricta: sense nombre, no hi ha evidència. «Hi ha NULLs» no és una auditoria. «3,095 NULL a price, l'11.6% de les files» sí que ho és.

Per a la decisió tens tres verbs:

Aquest és l'inventari de games. També és el mapa de la resta de la unitat:

Prova-ho: audita games tu mateix¶

Obre SQL → SQL Lab, tria la base de dades Dades classe i l'esquema public. Executa una sentència cada vegada: selecciona-la i prem Run. Si executes un bloc sencer, SQL Lab només mostra l'últim resultat.

Pas 1 — Mida i tipus. Executa SELECT COUNT(*) FROM games; i després la consulta d'information_schema.columns d'aquesta pàgina. Hauries de veure 26,688 files i dues columnes text que no haurien de ser text.

Pas 2 — Valors absents. Escriu un únic SELECT amb un COUNT(*) - COUNT(column) per a cadascuna de les deu columnes. Els teus nombres haurien de coincidir amb la taula de «Valors absents». Després executa les dues comprovacions de disfressa (average_playtime = 0 i price = 0).

Pas 3 — Formats incorrectes. Executa la consulta amb NOT LIKE i les dues consultes de owners. Hauries de poder dir quantes dates tenen una forma diferent (42) i per què DOOM 3 no és realment el joc amb més propietaris.

Pas 4 — Duplicats. Executa la consulta amb HAVING COUNT(*) > 1 i després mira les files de Dark Matter. Escriu una frase: són el mateix joc?

Pas 5 — Valors atípics. Executa la consulta de MIN/MAX i els preus més alts. Decideix, amb un motiu, si 595.99 és un error de tecleig.

Idees clau¶

• Una auditoria de dades vol dir mirar i comptar sense canviar res. El seu únic producte és una llista.
• Hi ha quatre tipus de brutícia: valors absents, duplicats, tipus o formats incorrectes i valors atípics.
• COUNT(*) - COUNT(column) dona les cel·les buides d'una columna. Afegeix-hi sempre el percentatge.
• NULL i 0 són coses diferents. Un NULL pot voler dir «desconegut» (metascore) o portar significat (un joc gratuït), i un 0 pot ser un valor absent disfressat (average_playtime).
• Una data o un nombre emmagatzemats com a text s'ordenen com un diccionari i no es poden fer servir en càlculs.
• El mateix nom no és un duplicat, i un valor atípic no és un error: mira la fila abans de decidir.
• Un inventari de brutícia té quatre parts per línia: columna, tipus de brutícia, evidència amb un nombre i una decisió (arreglar, marcar o deixar).

Comprovació ràpida¶

Ara ja saps què és brut, i tens un pla. Hora d'encendre el forn: tot seguit, Neteja: tipus i formats, on release_date es converteix per fi en una data de debò.