Tables in, tables out¶

The dispatch model from Run a model, fed from parquet files and read back as tables. The last two steps keep the solve as an archive (glossary), a directory that holds the spec and parquet files, and query it.

Every block on this page runs when the site is built, and what you see under it is what it printed on this commit. A block that raises fails the build. The files go to a temporary directory, work.

import tempfile
from pathlib import Path

import polars as pl

import specsolve as sps

SPEC = 'examples/dispatch.yaml'
work = Path(tempfile.mkdtemp())

1. Tables in¶

Start from two tables: one row per generator, and the load over four snapshots. Write one parquet file per parameter, with the parameter's dimension columns and a value column:

generators = pl.DataFrame(
    {'generator': ['wind', 'solar', 'gas'], 'p_max': [80.0, 0.0, 200.0], 'cost': [10.0, 25.0, 50.0]}
)
load = pl.DataFrame({'snapshot': range(4), 'value': [60.0, 120.0, 180.0, 90.0]})

generators.select('generator', value='p_max').write_parquet(work / 'p_max.parquet')
generators.select('generator', value='cost').write_parquet(work / 'cost.parquet')
load.write_parquet(work / 'load.parquet')

print(pl.read_parquet(work / 'cost.parquet'))

shape: (3, 2)
┌───────────┬───────┐
│ generator ┆ value │
│ ---       ┆ ---   │
│ str       ┆ f64   │
╞═══════════╪═══════╡
│ wind      ┆ 10.0  │
│ solar     ┆ 25.0  │
│ gas       ┆ 50.0  │
└───────────┴───────┘

sources names a table for each key the file declares (the data contract). A value is a parquet path or a table in memory. The generator labels come from the polars frame, and the snapshot labels from the column of that name in load.parquet:

sources = {
    'generator': generators,
    'snapshot': work / 'load.parquet',
    'p_max': work / 'p_max.parquet',
    'cost': work / 'cost.parquet',
    'load': work / 'load.parquet',
}

result = sps.solve(SPEC, sources)
print(result.objective)

10500.0

2. Tables out¶

primal gives the value of a variable as a polars table: one row per coordinate, keyed by the dimension labels, with a value column. Solar has no capacity, so p has no rows for it:

p = result.primal('p')
print(p)

shape: (8, 3)
┌──────────┬───────────┬───────┐
│ snapshot ┆ generator ┆ value │
│ ---      ┆ ---       ┆ ---   │
│ i64      ┆ str       ┆ f64   │
╞══════════╪═══════════╪═══════╡
│ 0        ┆ wind      ┆ 60.0  │
│ 0        ┆ gas       ┆ 0.0   │
│ 1        ┆ wind      ┆ 80.0  │
│ 1        ┆ gas       ┆ 40.0  │
│ 2        ┆ wind      ┆ 80.0  │
│ 2        ┆ gas       ┆ 100.0 │
│ 3        ┆ wind      ┆ 80.0  │
│ 3        ┆ gas       ┆ 10.0  │
└──────────┴───────────┴───────┘

dual gives the price of each constraint row in the same shape:

print(result.dual('power_balance'))

shape: (4, 2)
┌──────────┬───────┐
│ snapshot ┆ value │
│ ---      ┆ ---   │
│ i64      ┆ f64   │
╞══════════╪═══════╡
│ 0        ┆ 10.0  │
│ 1        ┆ 50.0  │
│ 2        ┆ 50.0  │
│ 3        ┆ 50.0  │
└──────────┴───────┘

The result is an ordinary table, so polars works on it directly. The cost per generator is a join and a sum:

spend = (
    p.join(generators, on='generator')
    .group_by('generator')
    .agg(spend=(pl.col('value') * pl.col('cost')).sum())
    .sort('generator')
)
print(spend)

shape: (2, 2)
┌───────────┬────────┐
│ generator ┆ spend  │
│ ---       ┆ ---    │
│ str       ┆ f64    │
╞═══════════╪════════╡
│ gas       ┆ 7500.0 │
│ wind      ┆ 3000.0 │
└───────────┴────────┘

The two rows add up to the objective.

3. Archive¶

archive= writes the spec, the sources and the answer to one directory (archiving a solve):

case = work / 'case'
sps.solve(SPEC, sources, archive=case)

for file in sorted(case.rglob('*')):
    if file.is_file():
        print(file.relative_to(case))

answer/activity/power_balance.parquet
answer/dual/power_balance.parquet
answer/format.json
answer/metrics.parquet
answer/primal/p.parquet
answer/record.parquet
sources/cost.parquet
sources/generator.parquet
sources/load.parquet
sources/p_max.parquet
sources/snapshot.parquet
sources.parquet
spec.yaml

spec.yaml is the spec. sources/ holds one parquet file per key, and sources.parquet a digest of each. answer/ holds the record of the solve, its metrics, and one parquet file per variable and constraint. The record is a table too:

print(pl.read_parquet(case / 'answer' / 'record.parquet').select('run', 'status', 'objective'))

shape: (1, 3)
┌──────┬────────┬───────────┐
│ run  ┆ status ┆ objective │
│ ---  ┆ ---    ┆ ---       │
│ str  ┆ str    ┆ f64       │
╞══════╪════════╪═══════════╡
│ case ┆ ok     ┆ 10500.0   │
└──────┴────────┴───────────┘

load_archive reads the directory back. The spec and the sources in it ask the same question again:

archived = sps.load_archive(case)
print(sps.solve(archived.spec, archived.sources).objective)

10500.0

4. Query the archive¶

The archive is parquet on disk, so a query needs no specsolve. This scan joins the answer to the costs it was solved with, and gives the cost per generator of step 2:

solved = pl.scan_parquet(case / 'answer' / 'primal' / 'p.parquet')
costs = pl.scan_parquet(case / 'sources' / 'cost.parquet')

query = (
    solved.join(costs, on='generator', suffix='_cost')
    .group_by('generator')
    .agg(spend=(pl.col('value') * pl.col('value_cost')).sum())
    .sort('generator')
)
print(query.collect())

shape: (2, 2)
┌───────────┬────────┐
│ generator ┆ spend  │
│ ---       ┆ ---    │
│ str       ┆ f64    │
╞═══════════╪════════╡
│ gas       ┆ 7500.0 │
│ wind      ┆ 3000.0 │
└───────────┴────────┘

A database that reads parquet runs the same query. The SQL for DuckDB is on reading a directory of runs.

Where next¶