Query across groups

A single AGS file is a graph: a LOCA borehole owns its SAMP samples, which own their test results. laterite gives you four ways to walk that graph — from a one-line fan-out to raw SQL.

Fan out to a borehole's group set

# what this shows: ags.at(code, ids) fans out to one borehole's related group set, exposed as q.groups.
import laterite

ags = laterite.read("examples/sample_site.ags")
q = ags.at("LOCA", ["BH01", "BH02"])

print(q.groups)
assert "LOCA" in q.groups and "SAMP" in q.groups

['LOCA', 'SAMP', 'LLPL']

ags.at("LOCA", ["BH01", "BH02"]) follows the dictionary's parent/child links and returns a query scoped to just those boreholes. .groups tells you which group codes came along for the ride — here the locations plus the samples and plasticity tests that hang off them.

Materialise the record set as frames

# what this shows: materialise a borehole's full record set as a dict of polars frames via .at(..).frames()
import laterite

ags = laterite.read("examples/sample_site.ags")
frames = ags.at("LOCA", ["BH01"]).frames()

# frames is a dict {group_code: polars frame}; pull one out by code (NOT q["SAMP"])
print(sorted(frames))
print(frames["SAMP"].height)

assert isinstance(frames, dict) and "SAMP" in frames and frames["SAMP"].height >= 1

['LLPL', 'LOCA', 'SAMP']
4

.frames() turns that scoped query into a plain dict of born-typed polars frames, keyed by group code. Pull one out with frames["SAMP"] (the dict, not the query, is what you subscript) and you have BH01's four samples ready to work with — already typed, no casting.

Build a query lazily

# what this shows: the lazy single-result AgsQuery builder + its four terminals.
import laterite
import pandas as pd
import polars as pl
from duckdb import DuckDBPyRelation

# Build a query lazily: nothing runs until a terminal is called.
q = (
    laterite.read("examples/sample_site.ags")
    .query("SELECT * FROM LOCA")
    .filter("LOCA_GL > 28")
    .select("LOCA_ID", "LOCA_TYPE", "LOCA_GL")
)

# Four terminals materialise the same lazy plan:
frame = q.frame()  # handle's default backend (polars)
pl_df = q.to_polars()  # always polars
pd_df = q.to_pandas()  # always pandas
rel = q.relation()  # the lazy DuckDBPyRelation (not yet materialised)

print(pl_df)

# Projection is honoured exactly as declared.
assert frame.columns == ["LOCA_ID", "LOCA_TYPE", "LOCA_GL"]
assert pl_df.columns == ["LOCA_ID", "LOCA_TYPE", "LOCA_GL"]

# Each terminal returns its expected type.
assert isinstance(frame, pl.DataFrame)
assert isinstance(pl_df, pl.DataFrame)
assert isinstance(pd_df, pd.DataFrame)
assert isinstance(rel, DuckDBPyRelation)

shape: (7, 3)
┌─────────┬───────────┬─────────┐
│ LOCA_ID ┆ LOCA_TYPE ┆ LOCA_GL │
│ ---     ┆ ---       ┆ ---     │
│ str     ┆ str       ┆ f64     │
╞═════════╪═══════════╪═════════╡
│ BH02    ┆ RC        ┆ 32.49   │
│ BH03    ┆ RC        ┆ 28.54   │
│ BH04    ┆ RC        ┆ 29.04   │
│ BH05    ┆ RC        ┆ 31.62   │
│ BH07    ┆ RC        ┆ 31.33   │
│ BH08    ┆ CP        ┆ 28.67   │
│ BH09    ┆ CP        ┆ 30.98   │
└─────────┴───────────┴─────────┘

.query(sql) returns a lazy AgsQuery. Chain .filter(...) and .select(...) to refine it — each call hands back a new AgsQuery, and nothing runs until a terminal is called. There are four: .frame() (the handle's default backend), .to_polars(), .to_pandas(), and .relation() (the raw DuckDBPyRelation, still lazy). Because the type IS the AGS type, LOCA_GL > 28 compares numbers, not strings.

Drop to SQL for a cross-group join

# what this shows: drop to raw SQL to join across groups, count samples per location.
import laterite

rel = laterite.read("examples/sample_site.ags").sql(
    "SELECT l.LOCA_ID, count(*) n FROM SAMP s JOIN LOCA l USING (LOCA_ID) "
    "GROUP BY 1 ORDER BY 1"
)

# rel is a DuckDBPyRelation (terminal); materialise to polars with .pl().
df = rel.pl()
print(df)

assert hasattr(rel, "pl")
assert df.height >= 1 and "n" in df.columns

shape: (14, 2)
┌─────────┬─────┐
│ LOCA_ID ┆ n   │
│ ---     ┆ --- │
│ str     ┆ i64 │
╞═════════╪═════╡
│ BH01    ┆ 4   │
│ BH02    ┆ 2   │
│ BH03    ┆ 3   │
│ BH04    ┆ 4   │
│ BH05    ┆ 2   │
│ …       ┆ …   │
│ BH10    ┆ 3   │
│ BH11    ┆ 3   │
│ BH12    ┆ 3   │
│ BH13    ┆ 4   │
│ BH14    ┆ 4   │
└─────────┴─────┘

When you need a real join, .sql(...) exposes every group as a DuckDB table by its code — so SAMP s JOIN LOCA l USING (LOCA_ID) just works. It returns a DuckDBPyRelation (a terminal); call .pl(), .df(), or .arrow() to land it as polars, pandas, or Arrow.

Tip

.query() is the guard-railed builder for single-group work; .sql() is the escape hatch for anything DuckDB can express. Both share the same engine.

Next → Produce AGS4