Exploratory statistics¶

Exercise exploratory-statistics-annex.pdf · PDF

Without running anything: how much does a typical game on Steam cost? Say a number. Write it down, even.

You have spent weeks cleaning games_clean. You know its columns by heart. And still you have to guess, because nobody has asked the table a single question yet. Today you ask. The answer is going to be two numbers that disagree with each other, and understanding why they disagree is the most useful piece of statistics you will ever learn.

Today you produce the numbers; the next lesson turns them into the report.

The queue: two kinds of middle¶

Exploratory statistics means describing a column with a handful of numbers before you draw or conclude anything: where its middle is, how spread out it is, and what shape it has.

Five friends compare what they earn in a month. Four have ordinary jobs. The fifth is a streamer who had a very good month.

1 200    1 300    1 400    1 500    30 000

What does a typical friend earn? There are two honest ways to answer.

The mean (the everyday "average") is what each one would get if everything were added up and split equally.

Add the five: 35,400. Split by five: 7,080.

The median is the value of the one standing in the middle when everybody queues up from smallest to largest.

Put them in a queue by income. The third of five is in the middle: 1,400.

Give the streamer 300,000 instead of 30,000. The mean jumps to 61,080. The median is still 1,400.

We keep the queue for the whole page. Put the data in a queue, smallest first. Half are in front of the median, half behind. Everything you compute today is a question about that queue:

Centre: the same on 26,685 games¶

Now the real queue. Before you run it, bet: will the mean price be above or below the median?

SELECT ROUND(AVG(price), 2)                               AS mean,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price) AS median
FROM games_clean;

The mean is AVG, an old friend from SQL Lab: groups. The median needs a longer line. Read it piece by piece:

A percentile is a position in the queue, given as a fraction: 0.5 is the middle, 0.25 is a quarter of the way in.

PostgreSQL has no MEDIAN() function. The median is percentile 0.5.

So which is it: does a game cost 7.91 dollars or 4.99? Both numbers are correct. They answer different questions. To see which question, you need the rest of the queue.

Spread: how far apart¶

A centre alone hides too much. "The middle game costs 4.99" is true whether every game costs 4.99 or whether prices run from zero to hundreds. Start with the two ends of the queue:

SELECT COUNT(*) AS n, MIN(price) AS lowest, MAX(price) AS highest,
       ROUND(AVG(price), 2) AS mean,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price) AS median
FROM games_clean;

A game at 595.99? Never report a maximum without looking at who it is:

SELECT game, price FROM games_clean ORDER BY price DESC LIMIT 3;

The second is a tool for making games and the third is a video editor. They are software sold on Steam, not games. These are the streamers of our queue.

The ends of the queue are set by one row each. A sturdier way to describe spread is to cut the queue in four equal parts.

The quartiles are the three cuts that split the queue into four parts with the same number of rows: Q1 at 25%, the median at 50%, Q3 at 75%.

The standard deviation is, roughly, the typical distance between a value and the mean. (The exact formula squares the distances first; you do not need it today, and STDDEV does it for you.)

SELECT ROUND(STDDEV(price), 2) AS std_dev,
       PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY price) AS q1,
       PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY price) AS q3
FROM games_clean;

Between Q1 and Q3 stands the middle half of the queue. So you can write a sentence that is short, exact and true: half of all games cost between 1.99 and 9.99 dollars.

Shape: where the queue bunches up¶

Centre and spread are still only numbers. To see the queue you need its shape.

A distribution is how the values of a column are shared out: which values are common and which are rare.

The simplest way to see it is a frequency table: cut the range into boxes of equal width and count how many rows fall in each box.

width_bucket(value, from, to, n) tells you which of n equal boxes between from and to a value falls in. Below: six boxes of 10 dollars from 0 to 60. Anything above 60 goes to an extra box, number 7. The last column draws a bar: one # for every 500 games.

SELECT width_bucket(price, 0, 60, 6) AS bucket,
       MIN(price) AS lowest, MAX(price) AS highest, COUNT(*) AS games,
       REPEAT('#', (COUNT(*) / 500)::int) AS bar
FROM games_clean GROUP BY bucket ORDER BY bucket;

 bucket | lowest | highest | games | bar
--------+--------+---------+-------+--------------------------------------------
      1 |   0.00 |    9.99 | 21318 | ##########################################
      2 |  10.00 |   19.99 |  4101 | ########
      3 |  20.00 |   29.99 |   701 | #
      4 |  30.00 |   39.99 |   269 |
      5 |  40.00 |   49.99 |   117 |
      6 |  54.99 |   59.99 |    96 |
      7 |  64.99 |  595.99 |    83 |

There is the queue. Almost everybody is crammed into the first box: 21,318 games out of 26,685 cost less than 10 dollars. Then a long, thin line of stragglers stretches to the right, all the way to 595.99.

Do the same for a second column, metascore, the review score from 0 to 100. Two changes: only the games that have a score (WHERE has_metascore), and a cast to numeric, because ROUND with decimals does not accept the type of that column directly.

SELECT COUNT(*) AS n, MIN(metascore) AS lowest, MAX(metascore) AS highest,
       ROUND(AVG(metascore)::numeric, 1) AS mean,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY metascore) AS median,
       ROUND(STDDEV(metascore)::numeric, 1) AS std_dev,
       PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY metascore) AS q1,
       PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY metascore) AS q3
FROM games_clean WHERE has_metascore;

SELECT width_bucket(metascore, 20, 100, 8) AS bucket,
       MIN(metascore) AS lowest, MAX(metascore) AS highest, COUNT(*) AS games,
       REPEAT('#', (COUNT(*) / 25)::int) AS bar
FROM games_clean WHERE has_metascore GROUP BY bucket ORDER BY bucket;

 bucket | lowest | highest | games | bar
--------+--------+---------+-------+--------------------------------------------
      1 |     20 |      29 |     8 |
      2 |     30 |      39 |    21 |
      3 |     40 |      49 |    85 | ###
      4 |     50 |      59 |   261 | ##########
      5 |     60 |      69 |   666 | ##########################
      6 |     70 |      79 |  1055 | ##########################################
      7 |     80 |      89 |   677 | ###########################
      8 |     90 |      98 |    76 | ###

A completely different queue. This one is a hill: few games at the bottom, few at the top, and a crowd in the middle, between 70 and 79.

Two shapes: the tail and the hill¶

Here are both frequency tables as a picture, with the mean and the median marked.

A distribution is skewed when most values pile up on one side and a long tail of rare values stretches out on the other. It is symmetric when both sides of the centre look roughly alike.

Now you can tell the shape without drawing anything. Compare the two centres. Far apart means there is a tail, and the mean has been dragged towards it. Close together means a hill.

Which number do we print?¶

Go back to the number you wrote down at the start. Were you close? Close to which one?

In the next lesson you write a report, and one line of it will say what a game costs. If you print 7.91, you describe a game that barely exists. Here is the same fact, written three ways:

A number without its shape is half a truth. Print the median, the quartiles, and say in words that there is a tail.

Try it¶

Use games_clean in SQL Lab (database Dades classe, schema public). Build your own stat card: a small table with eight numbers per column.

Step 1 — Run the queries of this page and fill in both rows. Your numbers must match the ones above.

Step 2 — Count how many games cost more than the mean, with WHERE price > 7.91. You should get 10,218.

Step 3 — On paper, sketch the seven bars of price and the eight bars of metascore. Mark where the mean and the median fall on each sketch.

Step 4 — Do Hit games cost more? Add a group to the first query:

SELECT owners_tier, ROUND(AVG(price), 2) AS mean,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price) AS median
FROM games_clean GROUP BY owners_tier;

Step 5 — Finish the two sentences: "A typical game costs __ because..." and "A typical reviewed game scores __ because...". Keep the card: it is the raw material of your report.

Key takeaways¶

• Exploratory statistics describes a column with a few numbers: centre, spread and shape.
• The mean adds everything and splits it equally; the median is the middle of the queue. One extreme value moves the mean and leaves the median where it was.
• PostgreSQL has no MEDIAN(): use PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY column). The quartiles Q1 and Q3 are percentiles 0.25 and 0.75.
• A frequency table with width_bucket and COUNT(*) shows the distribution: where the values bunch up.
• price is skewed (mean 7.91, median 4.99, a tail up to 595.99). metascore is roughly symmetric (mean 71.9, median 73).
• Mean and median far apart means a tail. For a skewed column, report the median and the quartiles, and say that there is a tail.

Quick check¶

You now have a card full of honest numbers. Numbers alone are not a report, though. Next: The descriptive report, where you turn the stat card into a page someone else can trust.