Cleaning: units and duplicates¶

Exercise cleaning-units-duplicates-annex.pdf · PDF

In a crafting game, two items that are the same stack in one slot. Now and then two items look identical and still refuse to stack. Same icon, same name, two slots. Something you cannot see makes them different.

Data does this all the time. A space that is not a space. Three games called exactly the same that are not the same game. A number that looks like "how many people own this" and is not. On this page you learn to find the hidden difference before you merge, split or delete anything.

Looks identical, will not stack¶

The dirt inventory left two lines for today: owners is text and holds a range, and 74 game names appear more than once. Both hide the same kind of trap, so we keep one analogy for the whole page: two items that look the same and will not stack.

This lesson continues normalising: one type, one format and one unit per column, and one row per real thing.

flowchart LR
    subgraph A ["<b>Part 1</b>: the owners column"]
        direction TB
        A1["'10,000,000 .. 20,000,000'<br>text, a range"] --> A2["<b>owners_min</b><br>10000000, a number"]
    end
    subgraph B ["<b>Part 2</b>: repeated names"]
        direction TB
        B1["74 names appear<br>more than once"] --> B2["True duplicate,<br>apparent duplicate<br>or grey zone?"]
    end
    A --> B

The space that is not a space¶

owners reads 10,000,000 .. 20,000,000. We want the first number. The tool is SPLIT_PART(text, separator, n): it cuts a text at a separator and returns piece number n. The separator looks obvious: space, two dots, space.

SELECT owners, SPLIT_PART(owners, ' .. ', 1) AS first_part
FROM games WHERE game = 'Half-Life 2';

Nothing was cut. first_part is the whole text again. Check how many rows contain "space, two dots, space" at all:

SELECT COUNT(*) FROM games WHERE owners LIKE '% .. %';   -- 0

Zero. Not one row. You can see the spaces on your screen, and the database says they are not there.

Take the fingerprint¶

A character code is the number a computer stores for each character. The letter, digit or space you see is only how that number is drawn on screen.

ASCII(character) shows the code. Take the character that sits just before the dots, and compare it with a space typed on your keyboard:

SELECT ASCII(SUBSTR(owners, POSITION('..' IN owners) - 1, 1)) AS mystery,
       ASCII(' ') AS normal_space
FROM games WHERE game = 'Half-Life 2';

Your keyboard types character 32. The file holds character 160.

A non-breaking space is a character that looks exactly like a space but tells a program "do not break the line here". Its code is 160 (in the Unicode standard, U+00A0; in a web page it is written  ).

This table was collected from a website, Steam Spy, and the page's decoration came along with the data. Same look, different item: they do not stack, so the separator never matched.

A hidden character is any character you cannot tell apart by eye: a non-breaking space, a tab, a line break stuck at the end of a value.

From a text range to a number¶

Now build the number, step by step. Each failure teaches one thing.

Cut at what you can trust. The two dots are ordinary characters. Cut there, and measure the piece:

SELECT SPLIT_PART(owners, '..', 1) AS part,
       LENGTH(SPLIT_PART(owners, '..', 1)) AS len,
       LENGTH('10,000,000') AS expected
FROM games WHERE game = 'Half-Life 2';

Eleven characters where you count ten. The extra one is the non-breaking space, still glued to the end.

Fail on purpose. Use the tools of the previous lesson: TRIM the space, REPLACE the commas, cast.

SELECT REPLACE(TRIM(SPLIT_PART(owners, '..', 1)), ',', '')::bigint FROM games;

ERROR:  invalid input syntax for type bigint: "10000000 "

Look inside the quotes, after the digits: there is still a gap. TRIM did not remove it. A loud error, luckily.

Keep only the digits. REGEXP_REPLACE(text, pattern, new, 'g') replaces every piece of the text that matches a pattern. The pattern [^0-9] means "anything that is not a digit": commas, spaces of any kind, and whatever strange character turns up next year.

SELECT REGEXP_REPLACE(SPLIT_PART(owners, '..', 1), '[^0-9]', '', 'g')::bigint AS owners_min,
       COUNT(*) AS n
FROM games GROUP BY owners_min ORDER BY owners_min;

Thirteen values, sorted as numbers, from 0 to 100,000,000. And the ranking that was nonsense in the audit now makes sense: sorted by owners_min from high to low, Dota 2 is on top with 100,000,000, followed by three games at 50,000,000 (Counter-Strike: Global Offensive, Team Fortress 2 and PLAYERUNKNOWN'S BATTLEGROUNDS).

A number needs its unit¶

A unit is what a number counts or measures: dollars, minutes, people, kilometres. A number without its unit can be read in several ways, and at least one of them is wrong.

So what is the unit of owners_min? It is tempting to say "people who own the game". It is not.

17,923 games have owners_min = 0. That does not mean nobody bought them. It means "between 0 and 20,000". We named the column owners_min, and not owners, for exactly that reason: the name carries the unit.

The same applies to the rest of the table. price is in US dollars, not euros. Nothing in the column says so: you only know because the description of the dataset says it. Write it down.

True, apparent and grey: three kinds of "duplicate"¶

Now the 74 names that appear more than once. Do we delete the copies? Before you answer, there are three different cases.

A true duplicate is a row identical to another one in every column: the same record twice. An apparent duplicate only shares some columns, like the name, and is really a different thing.

Of the 74 repeated names, 32 have different developers and 42 have the same developer with different dates. Not one pair shares both the name and the date.

Does games have true duplicates?¶

SELECT DISTINCT * keeps one copy of each fully identical row. Count before and after:

SELECT COUNT(*) FROM games;                                  -- 26688
SELECT COUNT(*) FROM (SELECT DISTINCT * FROM games) AS t;    -- 26688

Same number. games has no true duplicates. The 74 repeated names are all apparent duplicates or grey zone.

The accident: a file uploaded twice¶

Who has never pressed Upload twice? You can simulate it without touching the table. UNION ALL glues two results one under the other, copies included:

WITH twice AS (SELECT * FROM games UNION ALL SELECT * FROM games)
SELECT COUNT(*) FROM twice;                                  -- 53376

53,376 rows: every game twice. Now DISTINCT earns its place:

WITH twice AS (SELECT * FROM games UNION ALL SELECT * FROM games)
SELECT COUNT(*) FROM (SELECT DISTINCT * FROM twice) AS t;    -- 26688

Back to 26,688. The copies were true duplicates, so removing them lost nothing.

Removing duplicates with a key¶

A key is the column, or group of columns, that says "this is the same thing". Two rows with the same key are the same real object.

Sometimes copies are not perfectly identical (one has an extra space, one was updated later), so DISTINCT * does not catch them. Then you choose a key and number the rows inside each group. ROW_NUMBER() does the numbering: 1, 2, 3... restarting in every group.

WITH twice AS (SELECT * FROM games UNION ALL SELECT * FROM games),
numbered AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY game, release_date, developer ORDER BY number) AS rn
  FROM twice
)
SELECT rn, COUNT(*) FROM numbered GROUP BY rn ORDER BY rn;

PARTITION BY names the key: name, date and developer together. Every game got a row number 1 and a copy with row number 2. Keep WHERE rn = 1 and the copies are gone.

flowchart TB
    Q{"Is the whole row<br>identical to another?"}
    Q -- "Yes" --> T["<b>True duplicate</b><br>remove the copy:<br>DISTINCT or rn = 1"]
    Q -- "No" --> K{"Do the key columns<br>all match?"}
    K -- "No: other developer, other date" --> A["<b>Apparent duplicate</b><br>a different thing: keep it"]
    K -- "Some do, some do not" --> G["<b>Grey zone</b><br>decide, and write it down"]

The careless key¶

Now the mistake everybody is tempted to make: "a game is its name". Use PARTITION BY game alone, on the original table:

WITH numbered AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY game ORDER BY number) AS rn
  FROM games
)
SELECT rn, COUNT(*) FROM numbered GROUP BY rn ORDER BY rn;

Keeping only rn = 1 leaves 26,611 rows. That "clean-up" would delete 77 rows, and none of them is a copy. Two of the three Dark Matter games are among them, and so is the second Penumbra Overture. No error. No warning. The table just gets smaller, and it looks tidier than before.

Try it: owners_min and the duplicate hunt¶

Open SQL → SQL Lab, database Dades classe, schema public. Run one statement at a time: select it, then Run.

Step 1 — Find the culprit. Run the SPLIT_PART with ' .. ', the LIKE count and the ASCII query. You should get the whole text back, then 0, then 160 and 32.

Step 2 — Build owners_min. Run the measuring query (11 against 10), the query that fails on purpose, and the REGEXP_REPLACE one. You should see 13 rows sorted as numbers.

Step 3 — Add it to the clean query. Open games_step1 under SQL → Saved Queries and click its name. In the WITH block, replace the plain owners by the REGEXP_REPLACE expression AS owners_min, and rename the block step2. Check it with SELECT game, owners_min FROM step2 ORDER BY owners_min DESC LIMIT 4;. Dota 2 must be on top.

Step 4 — Save it under a new name. Press Save, change the name to games_step2 and choose Save as new. Choosing Update would overwrite games_step1.

Step 5 — Hunt duplicates. Run the two DISTINCT * counts, the twice queries, and finally the careless key. You should be able to say how many real rows PARTITION BY game would delete, and name one of them.

Key takeaways¶

• Every character is stored as a character code. A non-breaking space (code 160) looks like a space (code 32) and is a different character: TRIM and ' ' do not touch it.
• SPLIT_PART cuts a text at a separator; REGEXP_REPLACE(..., '[^0-9]', '', 'g') keeps only the digits; then you cast.
• A number needs its unit. owners_min is the lower bound of an estimate, not a count: 0 means "between 0 and 20,000".
• A true duplicate is the whole row repeated. An apparent duplicate only shares a name. In between there is a grey zone that needs a written decision.
• games has no true duplicates: DISTINCT * returns the same 26,688 rows.
• A key says what makes two rows the same thing. With ROW_NUMBER() OVER (PARTITION BY key), the copies are the rows with rn above 1.
• A careless key deletes real data without any error: one row per game name would lose 77 rows.

Quick check¶

Every column now has one type, one format and a unit you can name. But 199 different prices and 13 owner values are still hard to read at a glance. Next: Categorisation, where numbers become labels like Common, Rare and Epic.