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.

to_csv() is the same frame for a machine rather than a person, and it round-trips: csv(f.to_csv()) gives you back an equal frame. Quoting follows RFC 4180 — a field containing a comma, a double quote, CR or LF is quoted and an inner quote is doubled; a nil cell is an empty field. That is worth having in the language rather than hand-rolled, because joining values with commas works perfectly until one field contains a comma, and then writes a file that isn't obviously broken — just silently wrong by one column from that row on.

Reading types is lossless or it doesn't happen: csv() infers a number only when that number renders back to exactly the text in the cell. 42 becomes an int and 1.50 an exact decimal, but 007, +7 and 1_000 stay strings — turning them into 7, 7 and 1000 would destroy what the cell said, and nothing downstream could get it back. That is what keeps a zip code, a product code or a zero-padded order id intact.

#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

A frame owns its data. Building one copies the arrays you pass in, and column / column_names hand back copies — so changing an array after you have built a frame from it does not change the frame, and writing into a column you read out does not either:

let c = [1, 2, 3]
let f = frame({ x = c })
c.push(4)
print(f.rows(), f.total("x"))     -- 3  6   (not 4 rows, not 10)

That is what makes the operations above safe to reason about: every one of them returns a new frame, and no frame's answers can change under it. The cost is that building a frame from a large array you already hold means two copies of that data until you drop the original.

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; a variable of your own that isn't a column (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

The rules for a bare name, in order:

• A column is the column, even when a built-in has the same name. A table with count, year or total columns filters on them as you'd expect: f.filter(year == 2024). Where there's no such column, the built-in keeps its meaning, and a name you call, like abs(delta), is always the function.
• One of your own variables that is also a column is an error. With let region = "EU" and a region column, f.filter(region == "EU") could mean either, so it stops and says so rather than quietly picking one. Rename the variable, or write the predicate as a function (below). This includes a function of your own, and a name you have taken from a built-in: with your own fn year(d), a year column is an error too, not silently the column, wherever in the file the fn year is declared.
• row is the current row. Use it to spell a column out, including a name that can't be written bare: f.filter(row.amount > 100.00d), f.filter(row["unit price"] > 5). A column named like a reserved word is written after the dot too: f.filter(row.end > row.start).

For logic the bare form can't express, or to use a variable and a column that share a name, pass a function of the row instead:

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.

#Summarising: the three calls you make first

Before you know what a table holds, you ask three things: what does each column look like, which values does a text column actually contain, and what happens when I break it down by group. Those are describe, value_counts and group_by.

let orders = frame({
  region = ["north", "south", "north", "east", "south", "north"],
  amount = [120.50d, 80.00d, 310.00d, 45.25d, 200.00d, 60.00d],
  rep    = ["ana", "bo", "ana", "cy", "bo", "dee"],
})

print(orders.describe())
print(orders.value_counts("region"))
print(orders.group_by("region", {
  revenue = "sum:amount",
  orders  = "count",
  typical = "median:amount",
  reps    = "count_distinct:rep",
}))

$ scua summarise.scua
column  count  mean     std      min    p25   median  p75      max   
amount  6      135.958  101.745  45.25  65.0  100.25  180.125  310.00
(1 rows × 9 cols)
region  count
north   3    
south   2    
east    1    
(3 rows × 2 cols)
region  revenue  orders  typical  reps
north   490.50   3       120.50   2   
south   280.00   2       140.00   1   
east    45.25    1       45.25    1   
(3 rows × 5 cols)

Three things worth noticing.

describe skips the text columns. region and rep have no mean, so they get no row — a table of nils would be noise. Those columns answer value_counts and mode instead.

group_by computes everything in one pass. Each entry of the spec is "<reducer>:<column>", and you get one output column per entry. The reducers are sum, mean, median, min, max, std, variance, count, count_distinct, first, last, product and mode. A bare "count" counts the rows in the group; "count:amount" counts the rows whose amount is not nil, which is a different question.

The exact columns stay exact. revenue and typical are decimals, not floats — 490.50, not 490.5000000000001. That holds through grouping, and it is the reason to keep money in a decimal column rather than a float one.

A printed table shortens long floats; the values themselves are untouched. The mean above shows as 135.958, but the number is still 135.95833333333334 — ask for it on its own and you get all of it:

let orders = frame({ amount = [120.50d, 80.00d, 310.00d, 45.25d, 200.00d, 60.00d] })
print(orders.describe())
print(orders.mean("amount"))

$ scua mean.scua
column  count  mean     std      min    p25   median  p75      max   
amount  6      135.958  101.745  45.25  65.0  100.25  180.125  310.00
(1 rows × 9 cols)
135.95833333333334

A printed table keeps six meaningful digits, which is enough to see a small rate without a wall of digits swamping the row. Two things it never does: it never shortens an exact number, so a money column prints in full however long it is, and it never adds digits — 0.5 stays 0.5. If you want every digit of everything, to_csv() is the exact counterpart to the printed table.

#The column reducers on their own

Every reducer also works on a whole column: orders.median("amount"), orders.std("amount"), orders.count_distinct("rep"). They are the same functions group_by uses, so a grouped median and median agree by construction.

Two groups behave differently, deliberately:

• min, max, first, last and mode hand back an actual cell, so they keep the column's kind — the mode of a text column is text, and the max of a money column is money. min and max are the same globals you use on numbers: min(3, 1, 4) for scalars, min(f, "amount") for a column.
• std and variance are always floats, because a standard deviation of money is not a money amount. Both use the sample convention (dividing by n-1), the same as a spreadsheet's STDEV and pandas' .std(), and both are nil for fewer than two values — no spread has been observed, which is not the same claim as a spread of zero.

median and quantile sit between the two: they return an actual cell when the answer lands on one, and an exact midpoint otherwise. The midpoint of 20.00 and 35.00 is 27.50 — exact, and no wider than it needs to be.

Missing values, running totals, and how two columns relate

Real data has gaps, and a gap is not a zero — it is "we do not know". Four verbs deal with what follows from that.

dropna removes incomplete rows. With no column named, a row goes if anything in it is missing. Name a column and a row goes only when that column is missing, which is usually the question you actually have — you care that the amount is there, not that every note field was filled in.

fillna puts a value into the gaps of one column, and it will not let you make an exact column approximate. fillna("amount", 0) fills a money column, because zero is exactly zero. fillna("amount", 0.1) is refused, because a rough 0.1 is not one tenth and writing it into an exact column would quietly stop the column being exact.

cumsum adds a running total as a new column. You name the new column, because a running total is one value per row — it has to land somewhere, and replacing the column you are totalling would destroy it.

corr says how strongly two numeric columns move together, from -1 (one rises as the other falls) through 0 (no straight-line relationship) to 1. Only rows where both values are present count.

let sales = frame({
  region  = ["north", "south", "north", "north", "south"],
  quarter = ["Q1", "Q1", "Q2", "Q1", "Q2"],
  amount  = [100.00d, 50.00d, 200.00d, nil, 75.00d],
})

print(sales.dropna("amount").group_by(["region", "quarter"], { revenue = "sum:amount", orders = "count" }))
print(sales.fillna("amount", 0))
print(sales.dropna("amount").cumsum("amount", "running"))
print(frame({ x = [1, 2, 3, 4], y = [2, 4, 7, 8] }).corr("x", "y"))

$ scua gaps.scua
region  quarter  revenue  orders
north   Q1       100.00   1     
south   Q1       50.00    1     
north   Q2       200.00   1     
south   Q2       75.00    1     
(4 rows × 4 cols)
region  quarter  amount
north   Q1       100.00
south   Q1       50.00 
north   Q2       200.00
north   Q1       0.00  
south   Q2       75.00 
(5 rows × 3 cols)
region  quarter  amount  running
north   Q1       100.00  100.00 
south   Q1       50.00   150.00 
north   Q2       200.00  350.00 
south   Q2       75.00   425.00 
(4 rows × 4 cols)
0.9844951849708403

group_by takes several keys. group_by(["region", "quarter"], …) breaks down by both, and each key comes back as its own column — so the answer is a frame you can filter, sort and group again, rather than a joined "north|Q1" string you would have to take apart first.

Two details worth knowing. The filled 0 printed as 0.00, matching the column rather than sitting in it at a different precision. And cumsum keeps the column exact, so the last running total equals total("amount") — they cannot drift, because both go through the same addition.

#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.