Data sources, filters and totals¶

Exercise data-sources-filters-totals-annex.pdf · PDF

Open the screen-time summary of your phone. How many hours yesterday? Which app comes first? Now the real question: who counted that? Did somebody type it?

Nobody did. Your phone stored thousands of tiny rows (app, start, end). The summary is a report: rows filtered to yesterday, a value calculated from them (end − start), a group per app, and totals. Today you build the same machine for the wireframe you drew, with real data, in a SvelteKit route. You will also meet the two bugs that almost every report ships with.

From market to plate¶

We will keep one picture all the way through this page: a kitchen.

flowchart LR
    S["<b>Sources</b><br>the shops"] --> C["<b>Convert</b><br>wash"]
    C --> F["<b>Filter</b><br>choose"]
    F --> K["<b>Calculate</b><br>cook"]
    K --> G["<b>Group and total</b><br>plate"]

One rule to remember: a report never stores a total. It cooks it again every time, from fresh rows. A stored total goes stale the moment a row changes.

Four shops¶

A data source is the place a report takes its rows from.

Here is the same note in two shops:

CSV    13,WCAG contrast examples,School,2026-10-15,526,false
JSON   { "id": 13, "title": "WCAG contrast examples", "folder": "School",
         "created": "2026-10-15", "words": 526, "pinned": false }

In which of the two is 526 a number? Only in JSON. In CSV there are no types: everything is text until you say otherwise.

Set up the project¶

You need a SvelteKit project (see Dev environment) and the data of NoteBox: 36 notes in 4 folders.

notes.csv · folders.json

static/data/notes.csv            ← download it here
src/lib/data/folders.json        ← download it here
src/routes/report/+page.svelte   ← new, empty

npm install d3-dsv

Source 1: JSON, the easy shop¶

Vite, the tool that builds your project, imports a JSON file as a ready-made array. In +page.svelte:

<script>
  import folders from '#lib/data/folders.json';
</script>

<h1>Notes report</h1>
<ul>
  {#each folders as f (f.name)}
    <li>{f.name}: {f.description}</li>
  {/each}
</ul>

Open /report and you see four folders. You did not convert anything: JSON arrived with its types.

Source 2: CSV, the shop that sells everything as text¶

The CSV lives in static/, so the browser can ask for it by URL. A load function (from Unit 3) fetches it before the page renders. New file src/routes/report/+page.js:

import { csvParse } from 'd3-dsv';

export async function load({ fetch }) {
  const response = await fetch('/data/notes.csv');
  if (!response.ok) throw new Error(`Could not load notes.csv (${response.status})`);
  const notes = csvParse(await response.text());
  return { notes };
}

Why a library, and not line.split(',')? Because a title can contain a comma: "Milk, eggs and bread". RFC 4180 wraps such a value in double quotes, and split would cut it in two. csvParse, from the package d3-dsv, follows the RFC.

The trap: "34" + "39"¶

Add up the words the naive way:

data.notes.reduce((sum, n) => sum + n.words, 0)
// "0419485566299396226173252344132723952630196644102..."

That is not 9213. Every words is text, and + glues text together: "34" + "39" gives "3439", while 34 + 39 gives 73.

So you wash the ingredients once, at the door. New file src/lib/report.js, with one function that converts one row:

export function toNote(row) {
  return {
    id: Number(row.id),
    title: row.title,
    folder: row.folder,
    created: row.created, // ISO date "2026-11-25": sorts and compares as text
    words: Number(row.words),
    pinned: row.pinned === 'true'
  };
}

csvParse accepts this function as its second argument and applies it to every row. In +page.js, import it with import { toNote } from '#lib/report.js'; and write csvParse(await response.text(), toNote). The sum now says 9213.

A calculated value¶

A calculated value is a new column computed from other columns of the same row. It is not stored anywhere.

How long does a note take to read? An adult reads non-fiction silently at about 238 words per minute (Brysbaert, 2019, a review of 190 studies). So the reading time is words ÷ 238, rounded up: a 30-word note takes 1 minute, not 0.

Store words, cook minutes. Change toNote so that it calculates the value:

export const WORDS_PER_MINUTE = 238; // Brysbaert (2019), silent reading, non-fiction

export function toNote(row) {
  const words = Number(row.words);
  return {
    id: Number(row.id),
    title: row.title,
    folder: row.folder,
    created: row.created, // ISO date "2026-11-25": sorts and compares as text
    words,
    pinned: row.pinned === 'true',
    minutes: Math.ceil(words / WORDS_PER_MINUTE) // calculated value
  };
}

Check one row by hand: WCAG contrast examples has 526 words, and 526 ÷ 238 = 2.2, so it takes 3 minutes.

Sources 3 and 4: database and API¶

You do not need these two for your report today, but you must recognise them.

A database can do the counting for you. If NoteBox's notes lived in a SQLite table, this SQL query would do the sums:

SELECT folder, COUNT(*) AS notes, SUM(words) AS words, ROUND(AVG(words)) AS average
FROM notes GROUP BY folder ORDER BY folder

Who did the counting, JavaScript or the database? The database. With a million rows that matters: the kitchen receives four rows instead of a million.

An API is a shop with a counter: you ask, it serves. In SvelteKit, an API is a +server.js file. This one answers GET /api/notes?folder=Shopping&pinned=true with two notes, as JSON:

// src/routes/api/notes/+server.js
import { json } from '@sveltejs/kit';
import { readFile } from 'node:fs/promises';
import { csvParse } from 'd3-dsv';
import { toNote, filterNotes } from '#lib/report.js';

export async function GET({ url }) {
  const notes = csvParse(await readFile('static/data/notes.csv', 'utf8'), toNote);
  const rows = filterNotes(notes, {
    folder: url.searchParams.get('folder') ?? 'All',
    from: url.searchParams.get('from') ?? '',
    pinnedOnly: url.searchParams.get('pinned') === 'true'
  });
  return json(rows);
}

The part of the URL after the ? holds the parameters. (filterNotes is coming in the next section.)

// src/routes/api/db/+server.js
import { json } from '@sveltejs/kit';
import { DatabaseSync } from 'node:sqlite';

export function GET({ url }) {
  const db = new DatabaseSync('notebox.db', { readOnly: true });
  const folder = url.searchParams.get('folder');
  const rows = folder
    ? db.prepare('SELECT * FROM notes WHERE folder = ? ORDER BY created').all(folder)
    : db.prepare('SELECT * FROM notes ORDER BY created').all();
  db.close();
  return json(rows);
}

Four shops, one ingredient list. From here on, the kitchen does not care where the rows came from.

Filters and parameters¶

A filter decides which rows enter the report. A parameter is the value the user gives to the filter.

"Folder equals..." is the filter. School is the parameter. In report.js, each line is a question the row must pass:

export function filterNotes(notes, { folder = 'All', from = '', pinnedOnly = false } = {}) {
  return notes.filter(
    (n) =>
      (folder === 'All' || n.folder === folder) &&
      (from === '' || n.created >= from) &&
      (!pinnedOnly || n.pinned)
  );
}

n.created >= from compares text, and that works because ISO dates such as 2026-12-01 sort like the calendar. With 01/12/2026 it would sort by day first, which is wrong.

In the page, the parameters are $state and the rows are $derived (see Reactivity):

let folder = $state('All');
let from = $state('');
let pinnedOnly = $state(false);

let rows = $derived(filterNotes(data.notes, { folder, from, pinnedOnly }));

Two sources meet in the controls: the options of the selector come from the JSON, and the rows come from the CSV.

The empty state¶

Now try the test that breaks every report: Work + Pinned only + from 01/01/2027. You get an empty table with a lonely header. A filter with no results is not the user's problem. It is a state of your screen, so say why it is empty and offer a way out:

{#if rows.length === 0}
  <p role="status">
    No notes match these filters.
    <button type="button" onclick={reset}>Reset filters</button>
  </p>
{:else}
  <table> ... </table>
{/if}

Counts, totals and groups¶

In report.js:

export function totals(rows) {
  const words = rows.reduce((sum, n) => sum + n.words, 0);
  return {
    count: rows.length,
    words,
    minutes: rows.reduce((sum, n) => sum + n.minutes, 0),
    average: rows.length ? Math.round(words / rows.length) : 0
  };
}

Why rows.length ? ... : 0? Because with zero rows, 0 / 0 is NaN, and "average NaN words" would reach the screen.

For the group header and group footer of your wireframe, Object.groupBy (JavaScript 2024) puts the rows in one drawer per folder. Then the same totals function gives the subtotals:

export function groupByFolder(rows) {
  const groups = Object.groupBy(rows, (n) => n.folder);
  return Object.entries(groups)
    .map(([folder, notes]) => ({ folder, notes, ...totals(notes) }))
    .sort((a, b) => a.folder.localeCompare(b.folder));
}

The average trap¶

The four folder averages are 182, 435, 41 and 259. What is the average of the whole report? Many people add them and divide by four, which gives 229. The right answer is 256: 9213 words ÷ 36 notes.

School has 12 long notes and Shopping has 7 tiny ones. An average of averages gives both folders the same vote.

Put it together¶

All the logic lives in pure functions, with no Svelte and no DOM. That makes them easy to test and easy to reuse, in a way you will be glad of when the change requests arrive.

// Pure report logic: no Svelte, no DOM. Easy to test, easy to reuse.
export const WORDS_PER_MINUTE = 238; // Brysbaert (2019), silent reading, non-fiction

/** CSV gives text only: convert each row to real types and add the calculated value. */
export function toNote(row) {
  const words = Number(row.words);
  return {
    id: Number(row.id),
    title: row.title,
    folder: row.folder,
    created: row.created, // ISO date "2026-11-25": sorts and compares as text
    words,
    pinned: row.pinned === 'true',
    minutes: Math.ceil(words / WORDS_PER_MINUTE) // calculated value
  };
}

/** Parameters decide which rows enter the report. */
export function filterNotes(notes, { folder = 'All', from = '', pinnedOnly = false } = {}) {
  return notes.filter(
    (n) =>
      (folder === 'All' || n.folder === folder) &&
      (from === '' || n.created >= from) &&
      (!pinnedOnly || n.pinned)
  );
}

/** Count and totals of any list of rows. */
export function totals(rows) {
  const words = rows.reduce((sum, n) => sum + n.words, 0);
  return {
    count: rows.length,
    words,
    minutes: rows.reduce((sum, n) => sum + n.minutes, 0),
    average: rows.length ? Math.round(words / rows.length) : 0
  };
}

/** One group per folder (A to Z), each with its rows and its subtotals. */
export function groupByFolder(rows) {
  const groups = Object.groupBy(rows, (n) => n.folder);
  return Object.entries(groups)
    .map(([folder, notes]) => ({ folder, notes, ...totals(notes) }))
    .sort((a, b) => a.folder.localeCompare(b.folder));
}

import { csvParse } from 'd3-dsv';
import { toNote } from '#lib/report.js';

export async function load({ fetch }) {
  const response = await fetch('/data/notes.csv');
  if (!response.ok) throw new Error(`Could not load notes.csv (${response.status})`);
  const notes = csvParse(await response.text(), toNote);
  return { notes };
}

<script>
  import folders from '#lib/data/folders.json';
  import { filterNotes, groupByFolder, totals } from '#lib/report.js';

  let { data } = $props();

  // Parameters: what the user chooses
  let folder = $state('All');
  let from = $state('');
  let pinnedOnly = $state(false);

  // Everything else is derived from the data and the parameters
  let rows = $derived(filterNotes(data.notes, { folder, from, pinnedOnly }));
  let groups = $derived(groupByFolder(rows));
  let total = $derived(totals(rows));

  function reset() {
    folder = 'All';
    from = '';
    pinnedOnly = false;
  }
</script>

<h1>Notes report</h1>

<form class="filters" onsubmit={(e) => e.preventDefault()}>
  <label>
    Folder
    <select bind:value={folder}>
      <option>All</option>
      {#each folders as f (f.name)}<option>{f.name}</option>{/each}
    </select>
  </label>
  <label>Created from <input type="date" bind:value={from} /></label>
  <label><input type="checkbox" bind:checked={pinnedOnly} /> Pinned only</label>
  <button type="button" onclick={reset}>Reset filters</button>
</form>

{#if rows.length === 0}
  <p role="status">
    No notes match these filters.
    <button type="button" onclick={reset}>Reset filters</button>
  </p>
{:else}
  <table>
    <thead>
      <tr>
        <th>Title</th><th>Created</th>
        <th class="num">Words</th><th class="num">Reading (min)</th>
      </tr>
    </thead>
    {#each groups as group (group.folder)}
      <tbody>
        <tr><th colspan="4">{group.folder}</th></tr>
        {#each group.notes as note (note.id)}
          <tr>
            <td>{note.title}</td><td>{note.created}</td>
            <td class="num">{note.words}</td><td class="num">{note.minutes}</td>
          </tr>
        {/each}
        <tr>
          <td colspan="2">{group.count} notes in {group.folder}</td>
          <td class="num">{group.words}</td><td class="num">{group.minutes}</td>
        </tr>
      </tbody>
    {/each}
    <tfoot>
      <tr>
        <td colspan="2">Total · {total.count} notes · {total.average} words per note</td>
        <td class="num">{total.words}</td><td class="num">{total.minutes}</td>
      </tr>
    </tfoot>
  </table>
{/if}

<style>
  .num { text-align: right; }
</style>

Check your numbers¶

Numbers you can check by hand are the difference between "it runs" and "it is right". Try each filter and compare:

If yours differ, the symptom usually tells you the cause:

Now do it for your own report: create its data file (10 to 30 rows, JSON or CSV), convert its rows, add one calculated value, one filter with its empty state, group subtotals and grand totals. It must match your wireframe. If the code made you change the design, update the wireframe and say why.

Key takeaways¶

• JSON gives you real types. CSV gives you only text, so convert every row once, at the door.
• A calculated value is computed from other columns of the same row and is never stored.
• A filter decides which rows enter, and a parameter is the value the user gives it. Parameters are $state, and everything else is $derived.
• Totals are cooked, not stored: count, sum and average come from the rows every time.
• The total average is total sum ÷ total count, never the average of the group averages.
• Zero rows is a state of your screen: say why it is empty and offer a way out.

Quick check¶

A table makes you read 36 rows to see who writes the most. A chart shows it in one second. Next: Charts with ECharts.