C. Organization of Data for Analysis · Organization of Data for Analysis

Organizing Data: Tables, Spreadsheets and Databases

C2.5About 70 minutes

Learning goals

I can organize data into a well-structured table with one record per row and one variable per column.
I can sort and filter data by one or more attributes.
I can build frequency tables, relative-frequency tables and two-way tables from raw data.
I can use spreadsheet functions such as COUNTIF, AVERAGEIF and COUNTIFS to summarize data.
I can clean messy data and explain how a relational database links tables.

Words to know

database
An organized collection of data, stored so that it can be searched, sorted and summarized easily.

record
One row of a data table: all the data about one individual (one person, one song, one day).

field
One column of a data table: one variable, with a name in the header row.

frequency table
A table listing each category (or interval) and how many records fall in it. A relative-frequency table gives the fraction or percentage.

two-way (contingency) table
A table that counts records by two categorical variables at once, one along the rows and one along the columns.

pivot table
A spreadsheet tool that groups and summarizes records by one or more fields automatically.

data cleaning
Finding and fixing errors, inconsistent labels, duplicates and missing values before analysis.

key
A field whose value identifies each record uniquely (such as a student number); keys are used to link tables in a relational database.

From a pile of facts to a database

Suppose you list your favourite songs in a notebook, any old way: some with the artist, some with the year, a few with notes like "really good for running". Now try to answer: how many of your songs are rock? Which decade do most come from? It is painful.

Now put the same information in a table with the same columns for every song. The answers take seconds. This lesson is about organizing data so it is ready for analysis. The curriculum's example is exactly this: organizing data about a music collection classified by artist, date of recording and type of music, using a spreadsheet or statistical software.

Records, fields and tidy data

A data table (or database table) has:

a header row of field names (the variables);
one record (row) for each individual;
one value in each cell.

Statisticians call a well-organized table tidy:

Each variable has its own column.
Each individual has its own row.
Each cell holds one value (not "Rock/Pop" or "3 min 34 s").

Also give each table a data dictionary (a short note explaining each field, its units and its allowed values), and use consistent labels and units (always seconds, always "Rock" with a capital R).

The parts of a data table: a field is a column (shaded), a record is a row (shaded), and a value is one cell (outlined).

ID	Artist	Year recorded	Genre	Length (s)
1	Northern Lights Trio	2019	Folk	214
2	Kai & the Currents	2021	Pop	187
3	Northern Lights Trio	2015	Folk	241
4	Signal Hill	2008	Rock	262
5	Kai & the Currents	2023	Pop	176
6	Prairie Static	1998	Rock	295
7	Lumière	2017	Jazz	318
8	Signal Hill	2012	Rock	233
9	Lumière	2020	Jazz	352
10	Prairie Static	2001	Rock	248
11	Kai & the Currents	2018	Pop	199
12	Northern Lights Trio	2022	Folk	226

A small music collection (artists are fictional). It is tidy: one recording per row, one variable per column, one value per cell.

Sorting and filtering

Sorting puts records in order by a field, for example by year. You can sort by two fields: first by genre (alphabetically), then by year within each genre. In a spreadsheet, select the whole table first, or the rows will get scrambled!
Filtering shows only records that meet a condition, such as Genre = Rock, or Year ≥ 2020.

Summarizing: frequency and two-way tables

A frequency table counts the records in each category of one variable. A relative-frequency table divides each count by the total. A two-way table counts records by two categorical variables at once. For numerical variables, such as year, you first group the values into intervals (decades here).

In a spreadsheet, a pivot table builds these summaries automatically: choose a field for the rows, a field for the columns, and what to count or average.

A bar graph made from the genre frequency table. Organizing first makes graphing quick.

Worked example

Example 1: Frequency and relative-frequency table

Make a frequency and relative-frequency table for the genre of the 12 recordings.

Count each genre: Folk 3, Jazz 2, Pop 3, Rock 4. Check: 3 + 2 + 3 + 4 = 12. ✓
Relative frequency = count ÷ 12. For example, Rock: 4 ÷ 12 ≈ 0.333, or 33.3%.
Relative frequencies should add to 1 (or 100%), allowing for rounding.

Folk 3 (25.0%), Jazz 2 (16.7%), Pop 3 (25.0%), Rock 4 (33.3%).

Genre	Frequency	Relative frequency	Percent
Folk	3	0.25	25.0%
Jazz	2	0.167	16.7%
Pop	3	0.25	25.0%
Rock	4	0.333	33.3%
Total	12	1	100.0%

Frequency table for genre. (Percents shown to one decimal place; they add to 100%.)

Worked example

Example 2: A two-way table by genre and decade

Organize the collection by two attributes: genre and decade of recording.

Group the years into decades: 1990s (1990 to 1999), 2000s, 2010s, 2020s.
For each record, add 1 to the cell for its genre and decade. For example, record 6 (Rock, 1998) goes in Rock / 1990s.
Add row totals (which must match the genre frequency table) and column totals (records per decade). The grand total must be 12.

See the table below. For example, all 3 Pop recordings are from the 2010s or 2020s, while the 1990s and 2000s are all Rock.

Genre	1990s	2000s	2010s	2020s	Total
Folk	0	0	2	1	3
Jazz	0	0	1	1	2
Pop	0	0	1	2	3
Rock	1	2	1	0	4
Total	1	2	5	4	12

Two-way table of the music collection by genre and decade. You will analyse tables like this in Two-Variable Data Analysis and Validity.

Worked example

Example 3: Spreadsheet formulas

The table is in a spreadsheet with headers in row 1 and records in rows 2 to 13: column B = Artist, C = Year, D = Genre, E = Length (s). Write formulas to find (a) the number of Rock recordings, (b) the mean length of the Jazz recordings, (c) the number of Pop recordings from 2020 or later. Then find each answer.

(a) =COUNTIF(D2:D13, "Rock") counts cells in D2:D13 equal to Rock: 4.
(b) =AVERAGEIF(D2:D13, "Jazz", E2:E13) averages lengths where the genre is Jazz: (318 + 352) ÷ 2 = 335 s.
(c) =COUNTIFS(D2:D13, "Pop", C2:C13, ">=2020") counts rows meeting both conditions: records 2 (2021) and 5 (2023): 2.
These formulas work in Google Sheets, Microsoft Excel and LibreOffice Calc.

(a) 4, (b) 335 s, (c) 2.

Worked example

Example 4: Cleaning messy data

A class survey spreadsheet has these entries in the "Transport" field: Bus, bus, BUS , walk, Walking, car, Car, (blank), bike, Bike. In the "Height" field it has 172, 168, 1.75, 165, 610, 159 (cm). Clean the data.

Inconsistent labels: to a computer, "Bus", "bus" and "BUS " (with a trailing space) are three different categories. Standardize: Bus, Walk, Car, Bike. Merge "walk" and "Walking" into Walk.
Missing value: leave the blank cell empty (or mark it as missing). Do not fill in a guess, and do not type 0.
Unit error: 1.75 is in metres; convert to 175 cm.
Impossible value: 610 cm is not a human height. It is probably a typo (perhaps 161 or 160). Check the original survey; if you cannot, mark it as missing and record what you did.
Keep a log of every change so others can see how the data were cleaned.

Four transport categories with consistent spelling, one missing value, heights all in centimetres, and the impossible value checked or removed, with every change documented.

Relational databases

Large organizations store data in relational databases: several tables linked by key fields. A school might keep a Students table (student number, name, grade) and a Courses table (student number, course code, mark). The student number links them, so each student's name is stored only once, even though they take eight courses. A query combines and filters tables, for example "list the names of all Grade 12 students with a mark above 90 in MDM4U". Database query languages such as SQL are taught in university and college computer science and data science programs, and are used in careers from health records to sports analytics.

Organizing checklist

One record per row, one variable per column, one value per cell.
Clear field names with units.
Consistent category labels and units.
Missing values left blank or marked, never guessed.
Keep the raw data unchanged in one place and clean a copy.
Write down the source of secondary data and every cleaning step.

Just for fun

Riddle: why did the spreadsheet break up with the notebook?

The notebook kept writing "Rock", "rock" and "ROCK " and insisting they were the same thing. The spreadsheet needed consistency.

Hands-on activity

Catalogue a collection at home

You need: a free spreadsheet program (Google Sheets, LibreOffice Calc or Excel) and a collection at home with at least 20 items: books on a shelf, board games, songs in a playlist, spices in a cupboard, or movies.

Choose at least 5 fields, including at least two categorical (for example genre, author or brand) and two numerical (for example year published, pages, price, number of players). Write a short data dictionary.
Enter one record per item. Keep the labels consistent.
Sort by one field, then by two fields.
Filter to show only items meeting a condition.
Use COUNTIF for one category, AVERAGEIF for a numerical field within a category, and COUNTIFS with two conditions.
Build a pivot table or a two-way table by hand for two categorical fields, and make one bar graph.
Write two sentences describing something you learned about your collection.

What to look for: a tidy table, formulas that give results you can verify by counting, row and column totals that agree, and a short note on any cleaning you had to do (for example, the same author spelled two ways).

Your turn

In a data table, what is a record and what is a field?

Why is "Rock/Pop" in a single Genre cell a problem?

Using the music table, sort the Folk recordings by year. Which is first?

Write a formula to count the Folk recordings in the music spreadsheet (genres in D2:D13).

From the two-way table, how many recordings are from the 2010s? Which genre has recordings in the most different decades?

A survey's "Age" field contains 17, 16, seventeen, 18, 170. Clean it.

What does a pivot table do?

More practice: warm-up

Each variable should have its own ____.

Each individual should have its own ____.

Which spreadsheet function counts cells that meet one condition?

Should a missing value be entered as 0?

How many recordings in the music table are by Signal Hill?

More practice: on your own

Find the mean length of the Rock recordings in the music table.

Write a formula for the mean length of Rock recordings, and one for the number of recordings made before 2010.

Make a frequency table for the artist field.

A school stores each student's name in every course record. Explain the problem and how a relational database fixes it.

Explain why you should sort the whole table rather than one column.

Challenge

Using the music table, build a two-way table of genre by "length under 240 s" versus "240 s or longer". What percentage of Jazz and of Pop recordings are 240 s or longer?

Design a relational database for a public library with at least three tables. Name the key fields and say how the tables link.

A data set of 500 survey responses has 40 blank answers to "hours of sleep". A student fills every blank with the mean of the other 460 answers. Explain one problem with this.

Check your understanding

Quiz

In a tidy data table, each row represents:

Which formula counts the number of cells in A2:A50 equal to "Yes"?

A table counts students by grade (rows) and favourite season (columns). This is a:

A height of 1.68 appears in a column of heights in centimetres. The best action is to:

In a relational database, tables are linked by:

Using the music collection, what is the mean length of the Jazz recordings?

For parents and teachers

Big idea (C2.5): data must be organized before they can be analysed: one record per row, one variable per column, consistent labels and units. Spreadsheets sort, filter and summarize data quickly with tools such as COUNTIF, AVERAGEIF, COUNTIFS and pivot tables.

How to help: The cataloguing activity works with any collection in your home (books, games, spices) and is a good way to practise spreadsheet skills that are useful in almost every job. Ask your teen to show you how a pivot table or COUNTIF works.

Common difficulties:

Putting more than one value in a cell, or mixing units in one column.
Inconsistent spelling or capitalization of categories, which a spreadsheet treats as different categories.
Sorting one column instead of the whole table, which scrambles records.
Entering 0 for a missing value, or guessing a value.
Forgetting to check that the totals in a two-way table match the frequency tables.