Syntax Reference

Pivotal is a line-oriented pipeline language. Each statement occupies one or more lines, sub-options are indented beneath their parent, and operations flow top-to-bottom.

Structure

A Pivotal script is a sequence of blocks. Each block begins with a top-level statement (load, with, save, delete) and its indented sub-statements:

load "data/sales.csv" as sales      # top-level: load a file

with sales as summary             # top-level: define a table
    filter status == "active"     # indented: sub-operation
    group by region               #           sub-operation
        agg sum revenue as total  #           sub-option of group by
    sort total desc               # back to with level

save "report"                     # top-level: save output

Indentation

Indentation is significant but flexible — any consistent number of spaces or tabs works. Sub-options must be indented relative to their parent; they end when the indentation returns to the parent level.

Comments

# Single-line comment (hash)

-- Single-line comment (SQL style)

/* Multi-line
   comment */

Python variable references

Prefix a Python variable name with : to reference it from the surrounding scope:

threshold = 500
categories = ["A", "B"]

with sales as filtered
    filter amount > :threshold
    filter category in :categories

load :my_file_path as data

This works in Jupyter (referencing notebook variables) and in the Python API (referencing the namespace passed to execute()).

String quoting

Strings use double quotes:

filter name == "Alice"
load "path/to/file.csv" as data

Statements

Statement	Purpose
`load`	Load a file into a table
`with`	Set or create a table
`filter`	Filter rows
`select`	Keep specific columns
`drop`	Remove specific columns
`distinct`	Remove duplicate rows
`assign`	Create or modify columns
`cast`	Cast a column to a different type
`rename`	Rename columns
`sort`	Sort rows
`group by`	Aggregate by groups
`agg`	Aggregate over all rows (no grouping)
`merge`	Join two tables
`pivot`	Pivot to wide format
`unpivot`	Pivot to long format
`rank`	Rank rows
`lag` / `lead`	Shift values
`cumsum` etc.	Cumulative statistics
`rolling`	Rolling window statistics
`fillna`	Fill missing values
`dropna`	Drop rows with missing values
`concat`	Stack tables vertically
`python`	Embed Python code
`apply`	Apply a Python function to a table
`show`	Display inline
`plot`	Create a chart
`pivot plot`	Create a chart with aggregation
`table`	Create a publication-ready table
`save`	Export tables and charts
`delete`	Remove a table from memory