Imputation¶

Exercise imputation-annex.pdf · PDF

Your teacher is about to calculate the class average. Four students, three marks in the markbook: 6, 8 and 10. Your cell is empty. What should go in it?

A zero? Then the average drops from 8 to 6, and you have just failed an exam you were ill for. The class average? Then you get a mark for work nobody saw. Nothing at all? Then the average is quietly computed without you. Three options, three different averages, and not one of them is "obviously right". Every empty cell in a table asks this same question. This page is about answering it honestly.

A blank is not a zero¶

Four lessons of cleaning, and these cells of games are still empty:

You cannot clean an empty cell. There is nothing in it to repair. You can only decide what to do with it.

Imputation is deciding what value, if any, stands in for a missing one, and writing that decision down.

The analogy for the whole page is the teacher's markbook from the top: every decision you take here is one of its rows.

A zero is a mark. A blank is a question: why is it empty? The answer to that question, not the SQL, decides the option.

flowchart TB
    Q1{"Do you know what<br>the blank means?"}
    Q1 -- "Yes" --> C["<b>A constant</b><br>write what it means<br>and add a flag"]
    Q1 -- "No" --> Q2{"Are the blanks few,<br>and spread at random?"}
    Q2 -- "Yes" --> M["<b>A central value</b><br>mean or median<br>and add a flag"]
    Q2 -- "No: many, or a special group" --> L["<b>Leave it NULL</b><br>and add a flag"]
    Q1 -- "The row is useless without it" --> D["<b>Delete the row</b><br>only if they are very few"]

What NULL does to a calculation¶

Before filling anything, you need to know what the database does with an empty cell when you leave it alone. It does three different things, and none of them asks your permission.

See it on the real table. The same column, two averages:

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

Which one is "the average price of a game on Steam"? It depends on what the blank means. AVG silently skipped the empty rows: 8.95 is a sum divided by 23,593 games, not by 26,688. Nobody told it to.

COALESCE: the value and the note in the margin¶

COALESCE(a, b, ...) returns the first of its arguments that is not NULL. Read it aloud as "a, and if it is empty, 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');

A flag column is an extra column, true or false, that records something about the row. Here: "this value was empty in the original data".

The two always travel in pairs: the filled value and a flag that remembers it was empty. Once the NULL is overwritten, nobody can tell a real 0 from an invented one. The flag is the note the teacher writes in the margin of the markbook.

The replacement must have the same type as the column. COALESCE(developer, 'Unknown') works: text for text. COALESCE(developer, 0) stops with COALESCE types text and integer cannot be matched. A loud error, the good kind.

price: a constant you can defend¶

The categorisation lesson labelled the games with no price as Free. That was an assumption. Now check whether you were allowed to make it. Look for evidence before filling anything:

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;

Three pieces of evidence:

Domain knowledge is what you know about the real world behind the data: how Steam works, what a free-to-play game is. No query can give it to you.

Domain knowledge says a game with no price on Steam is free to play, and the data agrees. Decision: a constant, 0, plus the flag is_free.

So which average do you publish? Both are correct answers to different questions:

metascore: why you do not fill it¶

Now the big hole: 89.3% of the scores are missing. The tempting move is a central value.

The mean is the sum of the values divided by how many there are. The median is the value in the middle when you sort them. Both describe a "typical" value; you will study them properly in Exploratory statistics.

The mean metascore is 71.9 and the median is 73. So fill the blanks with 72, and count:

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

114 games really scored 72. After filling, 23,952 games "scored" 72. Look at what that does to the column:

The dangerous part: the mean barely moves. It was 71.9 and it becomes 72.0. The average hides the damage. Everything else about the column (how spread out the scores are, which games are good, which are bad) has been flattened into one spike.

Who is missing?¶

There is a second reason, and it is the stronger one. Check which games have no score. Small games first:

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;

Among games with 20,000 owners or more, 27.0% have a score. Among the smallest ones, 2.7%. Ten times less. Critics review the games people have heard of.

Values are missing at random when a blank is equally likely in any row. They are not missing at random when the blanks pile up in one particular group.

Here they pile up in the small, unknown games. Writing 72 in those cells would say "a decent game, according to the critics" about 23,838 games that no critic ever opened. And writing 0 would be worse: "the worst game ever reviewed".

Decision: leave it NULL, and add the flag (metascore IS NOT NULL) AS has_metascore. Any chart of scores must then say "based on the 11% of games that were reviewed".

Same SQL tool, opposite verdict.

Text columns: a label for the blank¶

A text column cannot take a mean. For developer and publisher, give the blank its own category, so that GROUP BY shows it instead of hiding it:

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

NULLIF: when a value should be a blank¶

The opposite problem also exists: a value that should be empty and is not. The audit found it in average_playtime: 0 for 96.5% of the games, because nobody measured it.

NULLIF(x, y) returns NULL when x equals y, and x otherwise. It turns a fake value back into an honest blank.

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;

An average of 9.1 with the fake zeros, 262.1 without them, computed on the 922 games that were actually measured. Almost thirty times bigger. The zeros were dragging the average down to nothing.

The imputation log¶

An imputation log is the written record of every imputation decision: column, how much was missing, what you did, and why.

This is the one for games. Every line is a row of the markbook:

Try it: write your imputation log¶

Open SQL → SQL Lab, database Dades classe, schema public. Every query is a SELECT; nothing gets modified. Run one statement at a time.

Step 1 — Measure the hole. Run this for price, then repeat it for metascore, developer and 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;

You should see 11.6, 89.3, 0.6 and 0.4.

Step 2 — price. Run the three evidence queries, then the two-averages query. Write which average you would publish, and for which question.

Step 3 — metascore. Run the two counts with 72 and the "who is missing" query. Write one sentence: why is 72 a lie for a game nobody reviewed?

Step 4 — Text columns. Run the COALESCE(developer, 'Unknown') query. Who is the "biggest developer", and why is that a trap?

Step 5 — The log. Write one line per column: delete, constant, central value, or leave empty and flag.

Key takeaways¶

• Imputation is deciding what stands in for a missing value. It is an assumption written in SQL, so it must be justified and written down.
• AVG, SUM and COUNT(column) skip NULL; arithmetic and comparisons with NULL give NULL. An average hides how many rows it was computed on.
• COALESCE(x, value) fills a blank; always pair it with a flag column that remembers the value was empty. NULLIF(x, value) does the opposite.
• Use a constant when you know what the blank means (price: free means 0), with domain knowledge and evidence.
• Use a central value (mean or median) only for few blanks that are missing at random.
• Leave it NULL and flag it when you do not know or most of the column is empty: filling metascore with 72 turns 114 real values into 23,952.
• Delete rows only when they are useless and very few.

Quick check¶

The empty cells now have a decision and a written reason. You have stopped repairing columns; it is time to invent new ones. Next: Synthetic variables, where the clean table games_clean is finally built.