0.6.14 Sorting, Filtering, and Summarizing Data

Structured Data Becomes More Useful When You Can Explore It

A clean table can contain hundreds or thousands of records.

Reading every row manually is rarely the best way to answer a question.

Spreadsheet tools support several basic exploration operations:

  • sorting ;
  • filtering ;
  • summarizing .

Each operation answers a different kind of question.

Sorting Changes the Order of the View

Suppose a song table contains:

Title Artist Genre Year
Northern Lights Example Band Rock 2024
Side Street Dana Lee Jazz 2022
Open Road Example Band Rock 2021

Sorting by Year can reorder the records.

Ascending:

Activity diagram showing all records, then match Genre condition, then match Year condition, then visible subset.
AllRecords.
  • New
  • Changed
  • Removed
Summaries Answer Questions About Groups

A summary reduces many records into useful results.

For example:

Possible result:

Genre Song Count
Jazz 12
Rock 28
Pop 19

The summary is not a replacement for the original records.

It is a new view derived from them.

Choose a Summary That Matches the Question

If the question is:

a count makes sense.

If the question is:

an average may make sense if a numeric rating field exists.

Do not choose a calculation merely because the spreadsheet tool offers it.

The aggregation should fit the meaning of the field and the question.

Clean Data Matters Before Summarizing

Suppose Genre contains:

Rock
rock
ROCK

A summary may show three groups.

The spreadsheet is accurately summarizing the values it received.

The underlying data is inconsistent.

This is why cleaning comes before serious interpretation.

Sorting, Filtering, and Summarizing Are Different

Sort

A spreadsheet workflow may use all three.

Do not confuse them.

Ask a Question Before Choosing the Tool

Instead of:

begin with:

Then choose the operation.

Examples:

Question: Which songs are newest?

Operation: Sort by Year descending.

Question: Which songs are Rock?

Operation: Filter Genre.

Question: How many songs are in each genre?

Operation: Summarize by Genre.

The question gives the spreadsheet operation a purpose.

Exploration Should Preserve the Source Data

Sorting, filtering, and summaries are ways to examine the dataset.

Avoid destroying or rewriting source records simply to produce a particular view.

A strong analysis keeps the original data available while creating useful views from it.