Data tables (frames)

A frame is a small data table — named columns of values you can total, sort, and group, the way you would a spreadsheet or a database query. It's built for the everyday question "given this data, what's the answer?" — and because SCUA has an exact decimal number, a column of money totals exactly, where a float-first tool would drift.

#Building a frame

Pass a table of { column-name = array } — every column the same length:

let sales = frame({
  region  = ["EU", "US", "EU", "US"],
  amount  = [10.50d, 20.00d, 5.25d, 7.75d],   -- exact decimal money
  qty     = [3, 1, 4, 2],
})

A frame prints as an aligned table, so a tool can show someone the answer directly:

$ scua sales.scua
region  amount  qty
EU      10.50   3
US      20.00   1
EU      5.25    4
US      7.75    2
(4 rows × 3 cols)

For a crisper rendering you can print or copy elsewhere — a report, a chat message, a code comment — to_table() returns the frame as a box-drawing table string (the way DuckDB prints), with numeric columns right-aligned:

print(sales.to_table())

┌────────┬────────┬─────┐
│ region │ amount │ qty │
├────────┼────────┼─────┤
│ EU     │  10.50 │   3 │
│ US     │  20.00 │   1 │
│ EU     │   5.25 │   4 │
│ US     │   7.75 │   2 │
└────────┴────────┴─────┘
(4 rows × 3 cols)

Unlike the default print (which caps a wide preview at 20 rows), to_table() renders every row — it's an explicit export helper.

#Querying it

Operations read on the frame and chain, left to right:

print(sales.rows())                 -- 4
print(sales.column_names())         -- [region, amount, qty]
print(sales.total("amount"))        -- 43.50  — EXACT (a decimal column stays exact)
print(sales.total("qty"))           -- 10     — an int column totals to an int
print(sales.mean("qty"))            -- 2.5

-- biggest sales: sort descending, keep the first rows and a couple of columns
print(sales.sort_by("amount", true).head(2).pick(["region", "amount"]))

-- the everyday analytical question: group by one column, sum another (exact for money)
print(sales.group_sum("region", "amount"))   -- EU 15.75, US 27.75

Every operation returns a new frame (or a value) — nothing is mutated in place, and there's no hidden row index to trip over. Empty cells (nil) are skipped by total/mean.

#Column names are checked

When you build a frame from a frame({ … }) literal, its column names are known at compile time, so a mistyped column is caught before the program runs — with a suggestion:

let sales = frame({ region = ["EU", "US"], amount = [10.50d, 20.00d] })
sales.total("amont")          -- error: frame has no column 'amont' — did you mean 'amount'?
sales.filter(regon == "EU")   -- error: frame has no column 'regon' — did you mean 'region'?

The column types are checked too: a reducer that sums or averages — total, mean, group_sum's value column, pivot's values column — must name a number column, so summing a text column is caught up front rather than faulting mid-run:

sales.total("region")   -- error: can't `total` column 'region' — it holds string values, not numbers

The schema follows the chain — sales.filter(…).total("amont") is checked too — and pick, with, and group_sum carry the right columns forward. Frames whose columns aren't known up front (a frame you took as a function argument, or one from csv, join, pivot, or an embedding host) stay flexible: their column names and types are checked at runtime instead, so nothing in your way when the shape is genuinely dynamic.

#Filtering and computed columns

In filter and with, column names are written bare — amount, region — and read as columns of the row; an outer variable (like a threshold you defined) keeps its normal meaning:

let cutoff = 100.00d
sales
  .filter(region == "EU" and amount > cutoff)   -- amount/region are columns; cutoff is the local
  .with("net", amount - fee)                     -- a new column computed per row

For logic the bare form can't express, pass a function of the row instead (the escape hatch):

sales.filter(fn(row) return row.amount > row.fee * 2.00d end)

#Search a text column

Filter free-text columns with the same engine as regex / fs.grep:

import regex

let tickets = frame({
  id = [1, 2, 3],
  note = ["refund request", "shipped OK", "CHARGEBACK dispute"],
})

-- Fixed-string: `col.contains(lit)` (substring) inside filter.
print(tickets.filter(note.contains("ship")))

-- Regex: compile once, then `col.matches(re)`.
match regex.compile("(?i)refund|chargeback")
  Ok(re) -> print(tickets.filter(note.matches(re)))
  Error(why) -> print(why)
end

-- Multi-column shortcut: keep rows where any (or named) string cell matches.
print(tickets.search("chargeback", { ignore_case = true, columns = ["note"] }))
print(tickets.search("ship|other", { regex = true }))   -- string pattern

search options: columns (array of names; default all string cells), ignore_case, regex (string needle is a pattern; a compiled re is always regex). Non-string cells are skipped unless you name the column explicitly, in which case a non-string value faults.

#Loading from CSV

csv(text) parses CSV text straight into a frame — the first line names the columns, each later line is a row. Cells are typed per value: a whole number becomes an int, a decimal-point number an exact decimal (so a money column stays exact), anything else a string, and an empty cell is nil. Quoted fields with embedded commas are handled.

let f = csv("city,revenue
Paris,1200.50
Tokyo,980.00
Paris,300.25")

print(f.group_sum("city", "revenue"))   -- Paris 1500.75, Tokyo 980.00 — exact

(A quoted field may not span multiple lines in this version.)

#Combining and reshaping

join combines two frames on a shared key column — "inner" (only matching rows, the default) or "left" (every left row, with the right columns nil where there's no match). A right column whose name clashes with a left one is suffixed _right:

let tax = frame({ region = ["EU", "US"], rate = [0.20d, 0.08d] })
sales.join(tax, "region")            -- adds a `rate` column to each sales row

pivot makes a pivot table — one row per distinct index value, one column per distinct columns value, each cell the sum of the values column for that pair (exact for a decimal column; an empty cell is nil):

sales.pivot("region", "product", "amount")   -- region down the side, product across the top

unpivot is the inverse — melt a wide table into a long one. Keep the ids columns, and stack the named values columns into two columns: a name column saying which column each value came from, and a value column holding it. The rows come out variable-major (all rows of the first value column, then the second, …); the two new column names default to "name"/"value":

let wide = frame({ region = ["EU", "US"], pen = [10.50d, 20.00d], ink = [5.25d, 7.75d] })
wide.unpivot(["region"], ["pen", "ink"], "product", "amount")
-- region | product | amount
-- EU     | pen     | 10.50
-- US     | pen     | 20.00
-- EU     | ink     | 5.25
-- US     | ink     | 7.75

#Why exact decimal columns matter

Totalling money is the thing data tools get asked most, and it's where float-based tools quietly mislead: 0.1 + 0.2 is not 0.3 in floating point. A decimal column (write the literal with a d suffix — 10.50d) sums and groups exactly, so a column of prices or invoice lines adds up to the cent.

See examples/frames.scua for a runnable tour.