Skip to main content

Row-grouping using FACET

The FACET clause allows you to split your data and display each group alongside a group label. This technique is great for making structure in the data explicit.

Note

This page is about the TABULATE usage of the FACET clause. For the VISUALISE usage, see this page.

Clause syntax

The FACET syntax contains a number of subclauses.

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

Column selection

<column>, ...

In FACET, one or more comma separated columns can be used to split the data. The first column is mandatory, and subsequent columns add nesting levels. If the columns contain missing null values, these are handled by the missing setting.

Settings

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

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

  • missing: Determines what happens with null values in the grouping columns. Can be one of:
    • 'first' Keep the rows with missing group values and display these on top of regular groups.
    • 'last' (default) Keep the rows with missing group values and display these underneath regular groups.
    • 'drop' Removes the rows with missing group values from the output table.
  • place: Placement options of the group labels. One of the following:
    • 'left' (default) Places group labels in the stub on the left. Group labels are vertically merged across member rows.
    • 'top' Interleaves table body rows with group label rows. Group labels are placed above its member rows spanning all columns.
  • separator: Optional join string to collapse group labels across nesting levels. The default (null) keeps levels in separate label rows or stub columns.

Examples

Declare a facet column. By default, these are placed on the left as a stub.

CREATE TEMP TABLE my_data AS 
SELECT DISTINCT ON (species, island, sex) * FROM ggsql:penguins

TABULATE * FROM my_data
FACET sex
speciesislandbill_lenbill_depflipper_lenbody_massyear
femaleAdelieDream39.516.717832502007
ChinstrapDream46.517.919235002007
GentooBiscoe46.113.221145002007
AdelieTorgersen39.517.418638002007
AdelieBiscoe37.818.317434002007
maleGentooBiscoe5016.323057002007
AdelieDream37.218.117839002007
ChinstrapDream5019.519639002007
AdelieTorgersen39.118.718137502007
AdelieBiscoe37.718.718036002007
nullAdelieTorgersennullnullnullnull2007
AdelieDream37.518.917929752007
GentooBiscoe44.514.321641002007

Omit missing group variables from the table.

TABULATE * FROM my_data
FACET sex SETTING missing => 'drop'
speciesislandbill_lenbill_depflipper_lenbody_massyear
femaleAdelieDream39.516.717832502007
ChinstrapDream46.517.919235002007
GentooBiscoe46.113.221145002007
AdelieTorgersen39.517.418638002007
AdelieBiscoe37.818.317434002007
maleGentooBiscoe5016.323057002007
AdelieDream37.218.117839002007
ChinstrapDream5019.519639002007
AdelieTorgersen39.118.718137502007
AdelieBiscoe37.718.718036002007

Alternatively, missing group variables can be given a proper label using FORMAT. Note that missing => 'drop' has priority over any relabeling in FORMAT.

TABULATE * FROM my_data
FACET sex
FORMAT sex RENAMING null => 'unknown'
speciesislandbill_lenbill_depflipper_lenbody_massyear
femaleAdelieDream39.516.717832502007
ChinstrapDream46.517.919235002007
GentooBiscoe46.113.221145002007
AdelieTorgersen39.517.418638002007
AdelieBiscoe37.818.317434002007
maleGentooBiscoe5016.323057002007
AdelieDream37.218.117839002007
ChinstrapDream5019.519639002007
AdelieTorgersen39.118.718137502007
AdelieBiscoe37.718.718036002007
unknownAdelieTorgersennullnullnullnull2007
AdelieDream37.518.917929752007
GentooBiscoe44.514.321641002007

Place grouping labels in rows above their members.

TABULATE * FROM my_data
FACET sex SETTING place => 'top'
speciesislandbill_lenbill_depflipper_lenbody_massyear
female
AdelieDream39.516.717832502007
ChinstrapDream46.517.919235002007
GentooBiscoe46.113.221145002007
AdelieTorgersen39.517.418638002007
AdelieBiscoe37.818.317434002007
male
GentooBiscoe5016.323057002007
AdelieDream37.218.117839002007
ChinstrapDream5019.519639002007
AdelieTorgersen39.118.718137502007
AdelieBiscoe37.718.718036002007
null
AdelieTorgersennullnullnullnull2007
AdelieDream37.518.917929752007
GentooBiscoe44.514.321641002007

Using multiple group columns to group gives nested labels.

TABULATE * FROM my_data
FACET species, island
bill_lenbill_depflipper_lenbody_masssexyear
AdelieBiscoe37.818.31743400female2007
37.718.71803600male2007
Dream39.516.71783250female2007
37.218.11783900male2007
37.518.91792975null2007
Torgersennullnullnullnullnull2007
39.517.41863800female2007
39.118.71813750male2007
ChinstrapDream46.517.91923500female2007
5019.51963900male2007
GentooBiscoe5016.32305700male2007
46.113.22114500female2007
44.514.32164100null2007

The labels from multiple groups can be collapsed to a single stub column (or single rows) by using the separator setting to join the labels. Labels are joined after FORMAT RENAMING is applied.

TABULATE * FROM my_data
FACET species, island
  SETTING separator => ' - '
bill_lenbill_depflipper_lenbody_masssexyear
Adelie - Biscoe37.818.31743400female2007
37.718.71803600male2007
Adelie - Dream39.516.71783250female2007
37.218.11783900male2007
37.518.91792975null2007
Adelie - Torgersennullnullnullnullnull2007
39.517.41863800female2007
39.118.71813750male2007
Chinstrap - Dream46.517.91923500female2007
5019.51963900male2007
Gentoo - Biscoe5016.32305700male2007
46.113.22114500female2007
44.514.32164100null2007

When group labels are placed in the stub, the labels can be controlled by LABEL. In case of separator-joined labels, the first variable (species in the example) displays the label.

TABULATE * FROM my_data
FACET species, island
LABEL island => 'Place', species => 'Type'
TypePlacebill_lenbill_depflipper_lenbody_masssexyear
AdelieBiscoe37.818.31743400female2007
37.718.71803600male2007
Dream39.516.71783250female2007
37.218.11783900male2007
37.518.91792975null2007
Torgersennullnullnullnullnull2007
39.517.41863800female2007
39.118.71813750male2007
ChinstrapDream46.517.91923500female2007
5019.51963900male2007
GentooBiscoe5016.32305700male2007
46.113.22114500female2007
44.514.32164100null2007