CREATE TEMP TABLE my_data AS SELECT * FROM ggsql:penguins LIMIT 5
TABULATE * FROM my_data
FORMAT STUB island| Torgersen |
|---|
| Torgersen |
| Torgersen |
| Torgersen |
| Torgersen |
ggsql is still in beta and many parts are being worked out. Read the beta-release announcement
FORMATThe 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.
[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.
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:
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.
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.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.
Declare a stub column.
| species | bill_len | bill_dep | flipper_len | body_mass | sex | year | |
|---|---|---|---|---|---|---|---|
| Torgersen | Adelie | 39.1 | 18.7 | 181 | 3750 | male | 2007 |
| Torgersen | Adelie | 39.5 | 17.4 | 186 | 3800 | female | 2007 |
| Torgersen | Adelie | 40.3 | 18 | 195 | 3250 | female | 2007 |
| Torgersen | Adelie | null | null | null | null | null | 2007 |
| Torgersen | Adelie | 36.7 | 19.3 | 193 | 3450 | female | 2007 |
Stubheads, the column labels for stub columns, are hidden by default. To display these, you can use the LABEL clause.
| Island | species | bill_len | bill_dep | flipper_len | body_mass | sex | year |
|---|---|---|---|---|---|---|---|
| Torgersen | Adelie | 39.1 | 18.7 | 181 | 3750 | male | 2007 |
| Torgersen | Adelie | 39.5 | 17.4 | 186 | 3800 | female | 2007 |
| Torgersen | Adelie | 40.3 | 18 | 195 | 3250 | female | 2007 |
| Torgersen | Adelie | null | null | null | null | null | 2007 |
| Torgersen | Adelie | 36.7 | 19.3 | 193 | 3450 | female | 2007 |
Stub columns cannot be targetted by spans. The boundary between stub and body is un-spannable.
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.
| species | island | bill_len | bill_dep | flipper_len | body_mass | sex | year |
|---|---|---|---|---|---|---|---|
| Adelie | Torgersen | 39.1 | 18.7 | 181 | 3750 | male | 2007 |
| Adelie | Torgersen | 39.5 | 17.4 | 186 | 3800 | female | 2007 |
| Adelie | Torgersen | 40.3 | 18 | 195 | 3250 | female | 2007 |
| Adelie | Torgersen | null | null | null | null | null | 2007 |
| Adelie | Torgersen | 36.7 | 19.3 | 193 | 3450 | female | 2007 |
Control content alignment.
| species | island | bill_len | bill_dep | flipper_len | body_mass | sex | year |
|---|---|---|---|---|---|---|---|
| Adelie | Torgersen | 39.1 | 18.7 | 181 | 3750 | male | 2007 |
| Adelie | Torgersen | 39.5 | 17.4 | 186 | 3800 | female | 2007 |
| Adelie | Torgersen | 40.3 | 18 | 195 | 3250 | female | 2007 |
| Adelie | Torgersen | null | null | null | null | null | 2007 |
| Adelie | Torgersen | 36.7 | 19.3 | 193 | 3450 | female | 2007 |
Set the width of columns.
| species | island | bill_len | bill_dep | flipper_len | body_mass | sex | year |
|---|---|---|---|---|---|---|---|
| Adelie | Torgersen | 39.1 | 18.7 | 181 | 3750 | male | 2007 |
| Adelie | Torgersen | 39.5 | 17.4 | 186 | 3800 | female | 2007 |
| Adelie | Torgersen | 40.3 | 18 | 195 | 3250 | female | 2007 |
| Adelie | Torgersen | null | null | null | null | null | 2007 |
| Adelie | Torgersen | 36.7 | 19.3 | 193 | 3450 | female | 2007 |
Replace null values everywhere by selecting all columns.
| species | island | bill_len | bill_dep | flipper_len | body_mass | sex | year |
|---|---|---|---|---|---|---|---|
| Adelie | Torgersen | 39.1 | 18.7 | 181 | 3750 | male | 2007 |
| Adelie | Torgersen | 39.5 | 17.4 | 186 | 3800 | female | 2007 |
| Adelie | Torgersen | 40.3 | 18 | 195 | 3250 | female | 2007 |
| Adelie | Torgersen | --- | --- | --- | --- | --- | 2007 |
| Adelie | Torgersen | 36.7 | 19.3 | 193 | 3450 | female | 2007 |
Replacing specific pieces of content.
| species | island | bill_len | bill_dep | flipper_len | body_mass | sex | year |
|---|---|---|---|---|---|---|---|
| Adelie | Torgersen | 39.1 | 18.7 | 181 | 3750 | ♂ | 2007 |
| Adelie | Torgersen | 39.5 | 17.4 | 186 | 3800 | ♀ | 2007 |
| Adelie | Torgersen | 40.3 | 18 | 195 | 3250 | ♀ | 2007 |
| Adelie | Torgersen | null | null | null | ? | ? | 2007 |
| Adelie | Torgersen | 36.7 | 19.3 | 193 | 3450 | ♀ | 2007 |
Using a formatter to target all cells in a column.
| species | island | bill_len | bill_dep | flipper_len | body_mass | sex | year |
|---|---|---|---|---|---|---|---|
| Adelie | Torgersen | 39.1 | 18.7 | 181 | 3750g | male | 2007 |
| Adelie | Torgersen | 39.5 | 17.4 | 186 | 3800g | female | 2007 |
| Adelie | Torgersen | 40.3 | 18 | 195 | 3250g | female | 2007 |
| Adelie | Torgersen | null | null | null | null | null | 2007 |
| Adelie | Torgersen | 36.7 | 19.3 | 193 | 3450g | female | 2007 |