Skip to main content

Configure column display using FORMAT

The FORMAT clause is used to tweak the formatting and structure of one or more whole columns. It is the workhorse for column-focussed changes: value formatting, display properties and stub design.

Clause syntax

FORMAT [ANY | BODY | STUB] 
  (* | <column>, ...)
  SETTING <parameter> => <value>, ...
  RENAMING <value> => <string>, ...

Target

[ANY | BODY | STUB]

The target directly following the FORMAT clause determines what kind of column is being formatted. Setting the target to BODY affects the cells in the body part of the table. The target can be set to STUB, which will move the column to the left of the table to serve as row labels. Without spelling out a target, the default will be ANY. The ANY target is relevant when the same column is formatted by multiple FORMAT statements. ANY will give priority to BODY and STUB targets in other statements. Unless instructed otherwise, ANY will resolve to BODY.

-- Same as `FORMAT ANY foo, bar`
FORMAT foo, bar

-- Designate column as stub
FORMAT STUB foo

A column named any, body or stub is read as a target when it comes first in the column list, so quote it to format the column itself:

-- Format the column named `body`, not the BODY target
FORMAT `body`, foo

Column selection

(* | <column>, ...)

The FORMAT clause requires a comma-separated list of columns to format. In addition to column names, it can use SPAN identifiers as well to target the spanner’s columns. It can also take *, meaning to format all columns.

-- Targeting regular columns
FORMAT foo, bar

-- Targeting every column
FORMAT *

-- Targeting spanner id: bar, baz and qux become targeted
SPAN foo OVER bar, baz
FORMAT foo, qux

Settings

SETTING <parameter> => <value>, ...

It takes a typical SETTING clause with key-value pairs. It currently takes the following parameters:

  • hjust: numeric value between 0-1 or string, for horizontal justification of cell content. Unset, the default depends on the column: right-aligned for numbers and stub columns, left-aligned otherwise. Strings can be 'left', 'right', 'centre', or 'center'. By default content is left-aligned for strings, and right-aligned for numbers or stub columns.
  • width: a unit as a string giving the number of pixels (e.g. '100px') or a percentage ('20%'). Sets the display width of the column.

Renaming

RENAMING <value> => <string>, ...

The RENAMING clause is the place to format the cell contents. It works the same as the namesake in the SCALE clause. Generally, it works not unlike the LABEL clause in the sense that you specify a cell’s content on the left and a replacement value on the right. Alternatively, you can use a formatter with the * => '...' syntax. Specifically see the break formatting section for a list of formatters.

-- Replace a specific piece of content
FORMAT species RENAMING 'adelie' => 'Pygoscelis adeliae'

-- Suppress/hide a specific piece of content
FORMAT species RENAMING 'adelie' => null

-- Format all cells in the column as lower-case
FORMAT species RENAMING * => '{:lower}'

Examples

Declare a stub column.

CREATE TEMP TABLE my_data AS SELECT * FROM ggsql:penguins LIMIT 5

TABULATE * FROM my_data
  FORMAT STUB island
speciesbill_lenbill_depflipper_lenbody_masssexyear
TorgersenAdelie39.118.71813750male2007
TorgersenAdelie39.517.41863800female2007
TorgersenAdelie40.3181953250female2007
TorgersenAdelienullnullnullnullnull2007
TorgersenAdelie36.719.31933450female2007

Stubheads, the column labels for stub columns, are hidden by default. To display these, you can use the LABEL clause.

TABULATE * FROM my_data
  FORMAT STUB island
  LABEL island => 'Island'
Islandspeciesbill_lenbill_depflipper_lenbody_masssexyear
TorgersenAdelie39.118.71813750male2007
TorgersenAdelie39.517.41863800female2007
TorgersenAdelie40.3181953250female2007
TorgersenAdelienullnullnullnullnull2007
TorgersenAdelie36.719.31933450female2007

Stub columns cannot be targetted by spans. The boundary between stub and body is un-spannable.

TABULATE * FROM my_data
  FORMAT STUB island
  SPAN "My label" OVER island, species
ExecutionError: Validation error: SPAN 1: cannot include stub column 'island'.
Validation error: SPAN 1: cannot include stub column 'island'.

When a column is targeted by multiple FORMAT clauses, the clauses are combined; in case of conflicts the last FORMAT wins. Without target, ANY target is assumed which will not override targets. Note that the island column is not in the stub, because BODY overrides STUB.

TABULATE * FROM my_data
  FORMAT STUB island
  FORMAT BODY island
speciesislandbill_lenbill_depflipper_lenbody_masssexyear
AdelieTorgersen39.118.71813750male2007
AdelieTorgersen39.517.41863800female2007
AdelieTorgersen40.3181953250female2007
AdelieTorgersennullnullnullnullnull2007
AdelieTorgersen36.719.31933450female2007

Control content alignment.

TABULATE * FROM my_data
  FORMAT sex
    SETTING hjust => 'right'
speciesislandbill_lenbill_depflipper_lenbody_masssexyear
AdelieTorgersen39.118.71813750male2007
AdelieTorgersen39.517.41863800female2007
AdelieTorgersen40.3181953250female2007
AdelieTorgersennullnullnullnullnull2007
AdelieTorgersen36.719.31933450female2007

Set the width of columns.

TABULATE * FROM my_data
  FORMAT species, island
    SETTING width => '150px'
speciesislandbill_lenbill_depflipper_lenbody_masssexyear
AdelieTorgersen39.118.71813750male2007
AdelieTorgersen39.517.41863800female2007
AdelieTorgersen40.3181953250female2007
AdelieTorgersennullnullnullnullnull2007
AdelieTorgersen36.719.31933450female2007

Replace null values everywhere by selecting all columns.

TABULATE * FROM my_data
  FORMAT * RENAMING null => '---'
speciesislandbill_lenbill_depflipper_lenbody_masssexyear
AdelieTorgersen39.118.71813750male2007
AdelieTorgersen39.517.41863800female2007
AdelieTorgersen40.3181953250female2007
AdelieTorgersen---------------2007
AdelieTorgersen36.719.31933450female2007

Replacing specific pieces of content.

TABULATE * FROM my_data
  FORMAT body_mass, sex
    RENAMING 
      null => '?',
      'male' => '♂',
      'female' => '♀'
speciesislandbill_lenbill_depflipper_lenbody_masssexyear
AdelieTorgersen39.118.71813750♂2007
AdelieTorgersen39.517.41863800♀2007
AdelieTorgersen40.3181953250♀2007
AdelieTorgersennullnullnull??2007
AdelieTorgersen36.719.31933450♀2007

Using a formatter to target all cells in a column.

TABULATE * FROM my_data
  FORMAT body_mass
    RENAMING * => '{:num %.0f}g'
speciesislandbill_lenbill_depflipper_lenbody_masssexyear
AdelieTorgersen39.118.71813750gmale2007
AdelieTorgersen39.517.41863800gfemale2007
AdelieTorgersen40.3181953250gfemale2007
AdelieTorgersennullnullnullnullnull2007
AdelieTorgersen36.719.31933450gfemale2007