Year 9 · Spring 1

Data Science and AI · Lesson 3 of 5

Analysing in a spreadsheet or Python

PracticalTopic 18 Data, spreadsheets and AI

Year 9 · Data Science and AI · Lesson 3 of 5

You will learn to

  • Calculate the mean, median, mode and range of a small data set by hand.
  • Use AVERAGE, MEDIAN, MODE, and MAX minus MIN in a spreadsheet to check the answers.
  • Work out the same answers in Python from a list, using sum, len, sorted, max and min.
  • Write an SQL query with WHERE that picks out only the records a question needs.

Year 9 · Data Science and AI · Lesson 3 of 5

Success criteria

You have got it when you can say:

  • I can work out all four values by hand for seven numbers.
  • I can say which one fits a question, like the mode for the most common way to travel.
  • I can get the same mean in the spreadsheet and in Python.

Year 9 · Data Science and AI · Lesson 3 of 5

Retrieval starter

Questions from before. Write your answers, then show them.

  1. Last lesson

    A face-recognition model works badly on faces unlike its training photos. This is…

    A Bias from unrepresentative training dataB A syntax errorC Overflow

    A. Bias from unrepresentative training data

  2. Last unit

    The best defence against SQL injection is…

    A A longer passwordB Validation and parameterised queriesC A firewall

    B. Validation and parameterised queries

  3. Earlier this year

    Which loop always runs at least once?

    A REPEAT … UNTILB WHILE … ENDWHILEC FOR i ← 1 TO 0

    A. REPEAT … UNTIL

Year 9 · Data Science and AI · Lesson 3 of 5

Key words (1 of 2)

Formula
A spreadsheet calculation. Always starts with =.
Function
A subroutine that returns a value.
Record / field
One row in a database table: everything stored about one thing.
Primary key
A field whose value is unique for every record.

Year 9 · Data Science and AI · Lesson 3 of 5

Key words (2 of 2)

SQL
Structured Query Language: asks databases questions.
Machine learning
The computer finds rules itself from labelled examples.

The explanation · step 1 of 2

Spreadsheets: the grid

A spreadsheet is a grid of cells. Columns are letters, rows are numbers. B3 = column B, row 3.

The explanation · step 2 of 2

The data science cycle

  1. Ask a question.
  2. Collect data.
  3. Clean it: duplicates, typos, impossible values.
  4. Analyse: averages, sorting, charts.
  5. Conclude, honestly.
!Watch out

A link between two things (correlation) doesn't prove one causes the other.

The explanation · step 2 of 2

The maths: The mean (average)

Year 9 · Data Science and AI · Lesson 3 of 5

Try it: Ask the database

Year 9 · Data Science and AI · Lesson 3 of 5

Quick quiz · 1 of 3

In Spanish Excel, =IF(B2>=25,"Pass","Retry") becomes…

  1. A =SI(B2>=25;"Aprobado";"Repetir")
  2. B =IF(B2>=25;"Pass";"Retry")
  3. C =SI(B2>=25,"Aprobado","Repetir")

A. =SI(B2>=25;"Aprobado";"Repetir")

Spanish name SI, and semicolons, because the comma is the decimal point.

Year 9 · Data Science and AI · Lesson 3 of 5

Quick quiz · 2 of 3

The numbers 3, 9, 4, 4, 10. What is the median?

  1. A 6
  2. B 9
  3. C 4

C. 4

Sort: 3, 4, 4, 9, 10. The middle (3rd) value is 4.

Year 9 · Data Science and AI · Lesson 3 of 5

Quick quiz · 3 of 3

Which field makes a good primary key?

  1. A FirstName
  2. B House
  3. C StudentID

C. StudentID

It is unique for every student and doesn't change.

Year 9 · Data Science and AI · Lesson 3 of 5

Exit ticket

Answer on paper before you go. Then check.

  1. To show change over time, use a…

    A Pie chartB Line chartC Scatter graph

    B. Line chart A line joins the points in time order, so you see the trend.

  2. SELECT FirstName FROM Students WHERE Year = 8; returns…

    A Only the first names of Year 8 studentsB Every field of every studentC The number of Year 8 students

    A. Only the first names of Year 8 students WHERE keeps the Year 8 records. SELECT keeps only the FirstName field.