Data Transformation

Introduction

If you've used R's tidyverse, you know the joy of piping data through a chain of verbs: filter(), mutate(), select(), arrange(), count(), summarize(). tidyduck brings that same fluency to the browser. Instead of tibbles and dplyr, you get parquet files and a reactive query builder powered by DuckDB-WASM and Svelte 5.

The goal of this chapter is to give you an overview of the key tools for transforming data in the browser. We'll start with functions that operate on rows, then on columns, and finally on groups. Along the way, we'll compare the tidyverse equivalent so you can map your existing knowledge to this new API.

Prerequisites

Install the add-on into any SvelteKit project:

npx sv add @the-vcsi/tidyduck

This drops three files into src/lib/db/:

Place your data files in static/data/. For this guide, we use flights.parquet. Then import database() and register your tables — similar to Observable's DuckDBClient.of():

import { database } from '$lib/db/duck.svelte';

const db = database({
  flights: 'flights.parquet'
});

const flights = db.from('flights');

That's the tidyduck equivalent of library(tidyverse) and flights. The database() function creates a named registry that maps table names to parquet file paths in static/data/. Calling db.from() returns a query builder — nothing runs until you materialize it with a verb like .rows().

nycflights13

We use the nycflights13 dataset throughout — all 0 flights that departed from New York City in 2013. The same dataset used in R for Data Science. Except here, the data lives in a .parquet file and is queried entirely in your browser.

In R, typing flights prints the first few rows of the tibble. The tidyduck equivalent is .head():

const preview = flights.head();  // first 6 rows (default)

For a transposed view showing every column with its type and sample values — like R's glimpse() — use .glimpse():

const info = flights.glimpse();
// info.columns → [{ name, type, sample }, ...]
// info.nRows, info.nCols

Finally, for a statistical overview of every column — like R's summary() or Observable's table summary — use .describe(). It runs DuckDB's SUMMARIZE under the hood, giving you min, max, quantiles, and null percentages in one call:

const summary = flights.describe();
// summary.rows → [{ column_name, column_type, min, max, q25, q50, q75, avg, ... }, ...]

dplyr basics

In dplyr, every verb takes a data frame as its first argument and returns a new data frame. The pipe |> threads them together. In tidyduck, db.from() creates an immutable builder. Each method returns a new builder — the original is never modified:

// dplyr:  flights |> filter(...) |> arrange(...)
// tidyduck:
const q = flights
  .between('year', () => selectedYear)
  .in('carrier', () => selectedCarriers)
  .eq('origin', () => selectedOrigin);

Filter methods — .where(), .between(), .in(), .ilike(), .eq() — add reactive clauses. Materialization methods — .rows(), .count(), .distinct(), .summarize() — actually run the query and return reactive results.

Because each filter method takes a function (not a raw value), the query automatically re-runs when your Svelte state changes. No manual subscriptions, no useEffect — just Svelte 5 runes doing their thing.

Rows

The most important verbs that operate on rows are filter() (keep rows matching a condition), arrange() (reorder rows), and distinct() (find unique rows). Here's how each maps to tidyduck.

filter() → .where(), .eq(), .in(), .between(), .ilike()

dplyr's filter() keeps rows based on conditions. In tidyduck, the builder offers several specialized filter methods that handle common patterns and automatically skip inactive filters:

# dplyr: flights that departed more than 120 minutes late
flights |> 
  filter(dep_delay > 120)

// tidyduck
const delayed = flights.where(() => "dep_delay > 120").rows();

The .where() method accepts any SQL expression. For common patterns, use the specialized helpers:

# dplyr: flights in January or February
flights |> filter(month %in% c(1, 2))

// tidyduck: .in() works for string columns
// For numeric columns like month, use .where() with raw SQL:
let selectedMonths = $state([1, 2]);

const janFeb = flights
  .where(() => selectedMonths.length === 0
    ? null
    : `month IN (${selectedMonths.join(', ')})`
  )
  .rows();

Here's the full set of filter helpers and when to reach for each:

Automatic skip behavior. Every filter method gracefully handles "no selection" states. An empty array in .in(), a blank string in .ilike(), or null in .eq() means the filter is simply ignored — no if statements needed. This is one of tidyduck's most powerful features: your query adapts to the UI state automatically.

Combining conditions with or()

Chained filters combine with AND, just like comma-separated conditions in dplyr's filter(). For OR logic, use the or() helper inside .where():

// Search across multiple columns at once
let search = $state('');

const results = flights
  .where(() => or(
    ilike('carrier', search),
    ilike('dest', search),
    ilike('origin', search)
  ))
  .count();

arrange()

dplyr's arrange() reorders rows. In tidyduck, .arrange() sets the ORDER BY clause. Use plain column names for ascending order, or wrap with desc() for descending — just like dplyr. NULLs always sort last, matching R's behavior:

# dplyr: most delayed flights first
flights |> arrange(desc(dep_delay))

import { database, desc } from '$lib/db/duck.svelte';

// tidyduck: same pattern
const mostDelayed = flights.arrange(desc('dep_delay')).rows();

// multiple columns
const sorted = flights.arrange('carrier', desc('dep_delay')).rows();

.arrange() returns a new builder — the original is unmodified. It applies to .rows() and .head(). For grouped queries, use .sql() with ORDER BY directly.

distinct()

dplyr's distinct() finds unique rows. In tidyduck, .distinct() is overloaded just like in dplyr:

# dplyr: unique carriers
flights |> distinct(carrier)
# unique origin-dest combos
flights |> distinct(origin, dest)

// Single column → { items } (flat array, perfect for dropdowns)
const carriers = flights.distinct('carrier');
// carriers.items → ['AA', 'B6', 'DL', 'EV', 'UA', ...]

// Multiple columns → { rows } (unique combinations)
const routes = flights.distinct('origin', 'dest');
// routes.rows → [{ origin: 'EWR', dest: 'ALB' }, ...]

// No args → all unique rows
const unique = flights.distinct();

Note. Single-column .distinct('col') returns sorted, non-null values as a flat array — ideal for populating filter chips and dropdowns. Multi-column and no-arg forms return rows, like .rows().

count()

In dplyr, count() is overloaded: with no arguments it gives the total number of rows, and with column names it gives grouped counts sorted in descending order. tidyduck works the same way:

# dplyr: count by origin and destination, sorted
flights |> count(origin, dest, sort = TRUE)

const byRoute = flights.count('origin', 'dest');
// byRoute.rows → [{ origin: 'JFK', dest: 'LAX', n: 11262 }, ...]

Columns

The most important verbs that affect the columns without changing the rows are mutate() (creates new columns from existing ones), select() (picks which columns to keep), and rename() (changes column names). Here's how each maps to tidyduck.

mutate()

dplyr's mutate() adds new columns that are calculated from existing columns. In tidyduck, .mutate() takes an object of {alias: "SQL expression"} pairs and appends them to SELECT *:

# dplyr
flights |> mutate(
  gain = dep_delay - arr_delay,
  speed = distance / air_time * 60
)

// tidyduck
const withComputed = flights.mutate({
  gain: 'dep_delay - arr_delay',
  speed: 'ROUND(distance / air_time * 60, 1)'
});
// .rows() → SELECT *, dep_delay - arr_delay AS gain, ... FROM ...

Like all builder methods, .mutate() returns a new builder — the original is never modified. Multiple .mutate() calls accumulate:

// Chain multiple mutate calls
const computed = flights
  .mutate({ gain: 'dep_delay - arr_delay' })
  .mutate({ hours: 'air_time / 60' });

Keep or select. In dplyr, .keep = "used" retains only the columns used in the expression. In tidyduck, combine .mutate() with .select() to pick which columns to keep alongside your new ones.

select()

dplyr's select() lets you zoom in on specific columns. In tidyduck, .select() replaces SELECT * with your chosen columns:

# dplyr: select columns by name
flights |> select(year, month, day)

// tidyduck
const subset = flights.select('year', 'month', 'day');
// .rows() → SELECT year, month, day FROM ...

You can also rename columns as you select them, using SQL's AS syntax — just like dplyr's select(new_name = old_name):

// Rename while selecting
const renamed = flights.select('tailnum AS tail_num', 'carrier', 'dest');

For dplyr-style helpers like starts_with() or contains(), use DuckDB's COLUMNS expression in .sql():

// DuckDB COLUMNS regex — like select(contains("delay"))
const delays = flights.sql((where) =>
  `SELECT COLUMNS('.*delay.*') FROM 'flights.parquet' ${where}`
);

rename()

dplyr's rename() changes column names while keeping all columns. In tidyduck, .rename() uses DuckDB's EXCLUDE syntax under the hood:

# dplyr
flights |> rename(tail_num = tailnum)

// tidyduck
const renamed = flights.rename({ tail_num: 'tailnum' });
// .rows() → SELECT * EXCLUDE (tailnum), tailnum AS tail_num FROM ...

Note. dplyr also has relocate() to move columns around. In browser-side data analysis, column order rarely matters since you control how data is displayed in your Svelte components. If you need a specific order, use .select() to list columns in the desired sequence.

The Pipe

In R, the pipe |> lets you chain verbs into readable pipelines. In tidyduck, method chaining is the pipe. Because each method returns a new builder, you can thread operations together naturally:

# dplyr: find fastest flights to Houston
flights |>
  filter(dest == "IAH") |>
  mutate(speed = distance / air_time * 60) |>
  select(year:day, dep_time, carrier, flight, speed) |>
  arrange(desc(speed))

// tidyduck: same pipeline
import { database, desc } from '$lib/db/duck.svelte';

const fastest = flights
  .where(() => "dest = 'IAH'")
  .mutate({ speed: 'ROUND(distance / air_time * 60, 1)' })
  .select('year', 'month', 'day', 'dep_time', 'carrier', 'flight', 'speed')
  .arrange(desc('speed'))
  .head(10);

Even though this pipeline has five steps, it's easy to read because each method starts a new line. Without chaining, you'd need to write raw SQL:

// Without the builder — same query, harder to compose
const fastest = duck(() => `
  SELECT year, month, day, dep_time, carrier, flight,
    ROUND(distance / air_time * 60, 1) AS speed
  FROM 'flights.parquet'
  WHERE dest = 'IAH'
  ORDER BY speed DESC
  LIMIT 10
`);

The builder approach has two advantages: readability (each verb is a clear step) and composability (you can fork a builder to create multiple views of the same data):

// Fork from a shared base
const houston = flights.where(() => "dest = 'IAH'");

const topCarriers = houston.count('carrier');  // who flies there most?
const delays      = houston.summarize({ avg: 'ROUND(AVG(arr_delay), 1)' });
const fastest     = houston
  .mutate({ speed: 'ROUND(distance / air_time * 60, 1)' })
  .arrange(desc('speed'))
  .head(5);

Groups

So far you've learned about functions that work with rows and columns. The builder gets even more powerful when you add in the ability to work with groups. In this section, we'll focus on group_by(), summarize(), and the slice_ family.

group_by()

In dplyr, group_by() is a separate step that marks a data frame for subsequent grouped operations. In tidyduck, there's no separate group_by() — instead, grouping is always per-operation, just like dplyr's newer .by argument. You pass grouping columns directly to .summarize(), .count(), or .sliceMax():

# dplyr: two equivalent ways
flights |> group_by(month) |> summarize(avg = mean(dep_delay, na.rm = TRUE))
flights |> summarize(avg = mean(dep_delay, na.rm = TRUE), .by = month)

// tidyduck: grouping is always inline (like .by)
const monthly = flights.summarize({
  avg_delay: 'ROUND(AVG(dep_delay), 1)'
}, 'month');

This means you never need to ungroup() — each operation is self-contained. This is simpler and avoids the common dplyr pitfall of forgetting to ungroup.

summarize()

The .summarize() method takes a dictionary of {alias: "SQL_EXPRESSION"} pairs. Without a by argument, it aggregates the whole table:

# dplyr: overall summary
flights |> summarize(
  avg_delay = mean(arr_delay, na.rm = TRUE),
  max_delay = max(arr_delay, na.rm = TRUE),
  n = n()
)

const stats = flights.summarize({
  avg_delay: 'ROUND(AVG(arr_delay), 2)',
  max_delay: 'MAX(arr_delay)',
  total: 'COUNT(*)'
});

Add a by argument to group — the result has one row per group, sorted by the grouping columns:

# dplyr: grouped summary
flights |>
  group_by(month) |>
  summarize(
    avg_delay = mean(dep_delay, na.rm = TRUE),
    n = n()
  )

// tidyduck: pass grouping column(s) as second argument
const monthly = flights.summarize({
  avg_delay: 'ROUND(AVG(dep_delay), 1)',
  n: 'COUNT(*)'
}, 'month');

A note on missing values. In R, mean(x) returns NA if any value is missing — you must explicitly opt in with na.rm = TRUE. SQL takes the opposite default: aggregate functions like AVG, SUM, MIN, and MAX silently skip NULLs. This is convenient, but means missing data won't loudly announce itself the way R encourages. If your analysis depends on understanding missingness, use .describe() to check null_percentage per column before aggregating.

For multiple grouping variables, pass an array:

// Group by origin and destination
const byRoute = flights.summarize({
  avg_delay: 'ROUND(AVG(arr_delay), 1)',
  n: 'COUNT(*)'
}, ['origin', 'dest']);

In dplyr, you can keep piping after summarize() — e.g., filter(n > 100) to drop small groups. In tidyduck, .summarize() is a terminal verb that materializes results, so you can't chain further. For post-aggregation filtering (HAVING) or custom ordering, use .sql() — it gives you full SQL and still respects your builder's WHERE clause:

# dplyr: filter after grouping
flights |>
  group_by(dest) |>
  summarize(avg_delay = mean(arr_delay, na.rm = TRUE), n = n()) |>
  filter(n > 100)

// tidyduck: use .sql() for HAVING and custom ordering
const byDest = flights.sql((where) => `
  SELECT dest,
    ROUND(AVG(arr_delay), 1) AS avg_delay,
    COUNT(*) AS n
  FROM 'flights.parquet'
  ${where}
  GROUP BY dest
  HAVING COUNT(*) > 100
  ORDER BY avg_delay DESC
`);

sliceMax() and sliceMin()

dplyr has a family of slice_ functions for extracting specific rows within groups. The most useful are slice_max() and slice_min(), which find the rows with the largest or smallest values. tidyduck provides .sliceMax() and .sliceMin():

# dplyr: most delayed flight per destination
flights |>
  group_by(dest) |>
  slice_max(arr_delay, n = 1)

// tidyduck: same, with per-operation grouping
const mostDelayed = flights.sliceMax('arr_delay', 1, 'dest');

// Without grouping — just the overall top N
const top5 = flights.sliceMax('arr_delay', 5);

// Bottom N works the same way
const shortest = flights.sliceMin('air_time', 5);

By default, tidyduck breaks ties arbitrarily (one row per rank). Like dplyr's with_ties argument, pass { withTies: true } to keep all tied rows:

// Keep all ties (uses RANK instead of ROW_NUMBER)
const mostDelayed = flights.sliceMax('arr_delay', 1, 'dest', { withTies: true });

Grouped count()

.count() with column names is the quickest way to see group sizes, sorted in descending order — just like dplyr:

# dplyr
flights |> count(carrier, sort = TRUE)

const byCarrier = flights.count('carrier');
// byCarrier.rows → [{ carrier: 'UA', n: 58665 }, { carrier: 'B6', n: 54635 }, ...]

Multiple grouping variables work too — you can count by as many columns as you need:

// Daily flight counts
const daily = flights.count('year', 'month', 'day');

Joins

It's rare that an analysis involves only a single table. The nycflights13 dataset actually has five related tables — flights, airlines, airports, planes, and weather — connected through shared keys like carrier, tailnum, and faa.

This is where database() shines. Register all your tables upfront, then use db.sql() for joins:

import { database } from '$lib/db/duck.svelte';

const db = database({
  flights: 'flights.parquet',
  airlines: 'airlines.parquet',
  airports: 'airports.parquet',
  planes: 'planes.parquet',
  weather: 'weather.parquet'
});

Mutating joins

A mutating join adds columns from one table based on matching keys — like dplyr's left_join(). In tidyduck, you write SQL joins via db.sql().

For example, adding the full airline name to flights:

# dplyr
flights2 |> left_join(airlines)

// tidyduck
const withAirline = db.sql(t =>
  `SELECT f.*, a.name as airline_name
  FROM ${t.flights} f
  LEFT JOIN ${t.airlines} a ON f.carrier = a.carrier`
);

Or finding out what size of plane was flying:

# dplyr
flights2 |>
  left_join(planes |> select(tailnum, type, engines, seats))

// tidyduck
const withPlane = db.sql(t =>
  `SELECT f.year, f.month, f.day, f.carrier, f.flight,
          p.type, p.engines, p.seats
  FROM ${t.flights} f
  LEFT JOIN ${t.planes} p ON f.tailnum = p.tailnum`
);

When keys have different names across tables, you specify them explicitly in the ON clause. For example, joining flights to airports by destination:

# dplyr — keys have different names
flights2 |> left_join(airports, join_by(dest == faa))

// tidyduck — ON spells out the mapping
const withAirport = db.sql(t =>
  `SELECT f.*, a.name, a.lat, a.lon
  FROM ${t.flights} f
  LEFT JOIN ${t.airports} a ON f.dest = a.faa`
);

When LEFT JOIN fails to find a match, it fills the new columns with NULL — just like dplyr fills with NA. For example, filtering to a specific tail number that's missing from the planes table:

# dplyr
flights2 |>
  filter(tailnum == "N3ALAA") |>
  left_join(planes |> select(tailnum, type, engines, seats))

// tidyduck — combine builder filter with db.sql() join
const mystery = db.sql(t =>
  `SELECT f.year, f.time_hour, f.origin, f.dest, f.tailnum, f.carrier,
          p.type, p.engines, p.seats
  FROM ${t.flights} f
  LEFT JOIN ${t.planes} p ON f.tailnum = p.tailnum
  WHERE f.tailnum = 'N3ALAA'
  LIMIT 6`
);

The type, engines, and seats columns are all NULL — plane N3ALAA isn't in the planes table.

Filtering joins

Filtering joins keep or drop rows based on whether they match another table — without adding columns.

A semi-join keeps rows in x that have a match in y. For example, finding airports that are actual destinations:

# dplyr
airports |> semi_join(flights2, join_by(faa == dest))

// tidyduck — semi-join via WHERE EXISTS
      const destAirports = db.sql(t =>
        `SELECT a.*
        FROM ${t.airports} a
        WHERE EXISTS (
          SELECT 1 FROM ${t.flights} f WHERE f.dest = a.faa
        )`
      );

An anti-join is the opposite: rows in x that don't match y. Useful for finding missing data:

# dplyr — destinations not in airports table
flights2 |>
  anti_join(airports, join_by(dest == faa)) |>
  distinct(dest)

// tidyduck — anti-join via WHERE NOT EXISTS
const missingAirports = db.sql<{ dest: string }>(t =>
  `SELECT DISTINCT f.dest
  FROM ${t.flights} f
  WHERE NOT EXISTS (
    SELECT 1 FROM ${t.airports} a WHERE a.faa = f.dest
  )`
);

Joins are where the builder hands off to raw SQL — and that's by design. SQL's JOIN syntax is already concise and universally understood. The database() registry handles the tedious part (file paths), and db.sql() gives you the full power of DuckDB's SQL engine, including inequality joins, ASOF joins, and CTEs.