Formatting Data

Intro

Columns of data can be formatted with the fmt_*() functions. We can specify the rows of these columns quite precisely with the rows argument. We get to apply these functions exactly once to each data cell (last call wins). Need to do custom formatting? Use the fmt() function and define your own formatter within (or, create a wrapper with fmt() if you prefer). The text_transform() function allows for post-processing of any data, and we provide a function for that transformation.

Functions in this module

fmt_number()
fmt_integer()
fmt_scientific()
fmt_percent()
fmt_currency()
fmt_date()
fmt_time()
fmt_datetime()
fmt_markdown()
fmt_missing()
data_color()

Information functions:

info_locales()
info_currencies()
info_date_style()
info_time_style()
info_paletteer()

`fmt_number()`: Format numeric values

fmt_number(
  data,
  columns,
  rows = everything(),
  decimals = 2,
  n_sigfig = NULL,
  drop_trailing_zeros = FALSE,
  drop_trailing_dec_mark = TRUE,
  use_seps = TRUE,
  accounting = FALSE,
  scale_by = 1,
  suffixing = FALSE,
  pattern = "{x}",
  sep_mark = ",",
  dec_mark = ".",
  force_sign = FALSE,
  locale = NULL
)

With numeric values in a gt table, we can perform number-based formatting so that the targeted values are rendered with a higher consideration for tabular presentation.

We get a lot of control over numeric formatting with the following options:

decimals: choice of the number of decimal places, option to drop trailing zeros, and a choice of the decimal symbol
digit grouping separators: options to enable/disable digit separators and provide a choice of separator symbol
scaling: we can choose to scale targeted values by a multiplier value
large-number suffixing: larger figures (thousands, millions, etc.) can be autoscaled and decorated with the appropriate suffixes
pattern: option to use a text pattern for decoration of the formatted values
locale-based formatting: providing a locale ID (e.g., "de") will result in number formatting specific to the chosen locale

EXAMPLES

Use exibble to create a gt table. Format the num column using fmt_number() and its default options.

exibble %>%
  dplyr::select(num) %>%
  gt() %>%
  fmt_number(columns = num)

num
0.11
2.22
33.33
444.40
5,550.00
NA
777,000.00
8,880,000.00

Again with exibble, format the num column as numeric with three decimal places. Don’t use digit separators (use_seps = FALSE).

exibble %>%
  dplyr::select(num) %>%
  gt() %>%
  fmt_number(
    columns = num,
    decimals = 3,
    use_seps = FALSE
  )

num
0.111
2.222
33.330
444.400
5550.000
NA
777000.000
8880000.000

Format only the first three rows of num, this time with 4 decimal places.

exibble %>%
  dplyr::select(num) %>%
  gt() %>%
  fmt_number(
    columns = num,
    rows = 1:3,
    decimals = 4
  )

num
0.1111
2.2220
33.3300
444.4
5550.0
NA
777000.0
8880000.0

Format only the rows of num (to 4 decimal places) where values in currency are greater than 30.

exibble %>%
  dplyr::select(num, currency) %>%
  gt() %>%
  fmt_number(
    columns = num,
    rows = currency > 30,
    decimals = 4
  )

num	currency
0.1111	49.950
2.222e+00	17.950
3.333e+01	1.390
444.4000	65100.000
5,550.0000	1325.810
NA	13.255
7.770e+05	NA
8.880e+06	0.440

Combine the conditional selection of rows with scaling values by 1/1000 and then putting a "K" after the scaled values (with a pattern).

exibble %>%
  dplyr::select(num) %>%
  gt() %>%
  fmt_number(
    columns = num,
    rows = num > 500,
    decimals = 1,
    scale_by = 1/1000,
    pattern = "{x}K"
  )

num
0.1111
2.2220
33.3300
444.4000
5.5K
NA
777.0K
8,880.0K

Use countrypops to create a gt table. Format all numeric columns to use large-number suffixing (with suffixing = TRUE).

countrypops %>%
  dplyr::select(country_code_3, year, population) %>%
  dplyr::filter(country_code_3 %in% c("CHN", "IND", "USA", "PAK", "IDN")) %>%
  dplyr::filter(year > 1975 & year %% 5 == 0) %>%
  tidyr::spread(year, population) %>%
  dplyr::arrange(desc(`2015`)) %>%
  gt(rowname_col = "country_code_3") %>%
  fmt_number(
    columns = 2:9, # <- selecting columns 2 to 9 from *passed in dataset*
    decimals = 2,
    suffixing = TRUE
  )

	1980	1985	1990	1995	2000	2005	2010	2015
CHN	981.23M	1.05B	1.14B	1.20B	1.26B	1.30B	1.34B	1.37B
IND	696.78M	781.67M	870.13M	960.48M	1.05B	1.14B	1.23B	1.31B
USA	227.22M	237.92M	249.62M	266.28M	282.16M	295.52M	309.34M	321.04M
IDN	147.49M	165.01M	181.44M	196.96M	211.54M	226.71M	242.52M	258.16M
PAK	78.07M	92.22M	107.68M	122.83M	138.52M	153.91M	170.56M	189.38M

With exibble, make a simple gt table. Format the num column as numeric with three decimal places and do this formatting according to the "fr" locale.

exibble %>%
  dplyr::select(num) %>%
  gt() %>%
  fmt_number(
    columns = num,
    decimals = 3,
    locale = "fr" # <- "fr_FR", "fr_CA", "de", "de_AT"
  )

num
0,111
2,222
33,330
444,400
5 550,000
NA
777 000,000
8 880 000,000

`fmt_integer()`: Format values as integers

fmt_number(
  data,
  columns,
  rows = everything(),
  use_seps = TRUE,
  accounting = FALSE,
  scale_by = 1,
  suffixing = FALSE,
  pattern = "{x}",
  sep_mark = ",",
  force_sign = FALSE,
  locale = NULL
)

With numeric values in a gt table, we can perform number-based formatting so that the targeted values are always rendered as integer values.

EXAMPLE

Use exibble to create a gt table. Format the num column using fmt_integer() and its default options.

exibble %>%
  dplyr::select(num) %>%
  gt() %>%
  fmt_integer(columns = num)

num
0
2
33
444
5,550
NA
777,000
8,880,000

`fmt_scientific()`: Format values to scientific notation

fmt_scientific(
  data,
  columns,
  rows = everything(),
  decimals = 2,
  drop_trailing_zeros = FALSE,
  scale_by = 1,
  pattern = "{x}",
  sep_mark = ",",
  dec_mark = ".",
  force_sign = FALSE,
  locale = NULL
)

With numeric values in a gt table, we can perform formatting so that the targeted values are rendered in scientific notation.

EXAMPLE

Use exibble to create a gt table. Format the num column as partially in scientific notation (values <=500).

exibble %>%
  dplyr::select(num, currency) %>%
  gt() %>%
  fmt_scientific(
    columns = num,
    rows = num <= 500,
    decimals = 1
  )

num	currency
1.1 × 10⁻¹	49.950
2.2	17.950
3.3 × 10¹	1.390
4.4 × 10²	65100.000
5550	1325.810
NA	13.255
777000	NA
8880000	0.440

`fmt_engineering()`: Format values to engineering notation

fmt_engineering(
  data,
  columns,
  rows = everything(),
  decimals = 2,
  drop_trailing_zeros = FALSE,
  scale_by = 1,
  pattern = "{x}",
  sep_mark = ",",
  dec_mark = ".",
  force_sign = FALSE,
  locale = NULL
)

With numeric values in a gt table, we can perform formatting so that the targeted values are rendered in engineering notation.

EXAMPLE

Use exibble to create a gt table. Format the num column to be in engineering notation.

exibble %>%
  dplyr::select(num, currency) %>%
  gt() %>%
  fmt_engineering(
    columns = num,
    decimals = 2
  )

num	currency
111.10 × 10⁻³	49.950
2.22	17.950
33.33	1.390
444.40	65100.000
5.55 × 10³	1325.810
NA	13.255
777.00 × 10³	NA
8.88 × 10⁶	0.440

`fmt_percent()`: Format values as a percentage

fmt_percent(
  data,
  columns,
  rows = everything(),
  decimals = 2,
  drop_trailing_zeros = FALSE,
  drop_trailing_dec_mark = TRUE,
  scale_values = TRUE,
  use_seps = TRUE,
  accounting = FALSE,
  pattern = "{x}",
  sep_mark = ",",
  dec_mark = ".",
  force_sign = FALSE,
  incl_space = FALSE,
  placement = "right",
  locale = NULL
)

With numeric values in a gt table, we can perform percentage-based formatting. It is assumed the input numeric values are in a fractional format since the numbers will be automatically multiplied by 100 before decorating with a percent sign. For the other scenario, where values just need a percent sign, use scale_values = FALSE.

EXAMPLE

Use pizzaplace to create a gt table. Format the frac_of_quota column to display values as percentages.

pizzaplace %>%
  dplyr::mutate(month = as.numeric(substr(date, 6, 7))) %>%
  dplyr::group_by(month) %>%
  dplyr::summarize(pizzas_sold = dplyr::n()) %>%
  dplyr::ungroup() %>%
  dplyr::mutate(frac_of_quota = pizzas_sold / 4000) %>%
  gt(rowname_col = "month") %>%
  fmt_percent(
    columns = frac_of_quota,
    decimals = 1
  )

	pizzas_sold	frac_of_quota
1	4232	105.8%
2	3961	99.0%
3	4261	106.5%
4	4151	103.8%
5	4328	108.2%
6	4107	102.7%
7	4392	109.8%
8	4168	104.2%
9	3890	97.2%
10	3883	97.1%
11	4266	106.7%
12	3935	98.4%

`fmt_currency()`: Format values as currencies

fmt_currency(
  data,
  columns,
  rows = everything(),
  currency = "USD",
  use_subunits = TRUE,
  decimals = NULL,
  drop_trailing_dec_mark = TRUE,
  use_seps = TRUE,
  accounting = FALSE,
  scale_by = 1,
  suffixing = FALSE,
  pattern = "{x}",
  sep_mark = ",",
  dec_mark = ".",
  force_sign = FALSE,
  placement = "left",
  incl_space = FALSE,
  locale = NULL
)

With numeric values in a gt table, we can perform currency-based formatting. This function supports both automatic formatting with a three-letter or numeric currency code. We can also specify a custom currency that is formatted according to the output context with the currency() helper function. Numeric formatting facilitated through the use of a locale ID.

EXAMPLES

Use exibble to create a gt table. Format the currency column to have currency values in euros (EUR).

exibble %>%
  gt() %>%
  fmt_currency(
    columns = currency,
    currency = "EUR"
  )

num	char	fctr	date	time	datetime	currency	row	group
1.111e-01	apricot	one	2015-01-15	13:35	2018-01-01 02:22	€49.95	row_1	grp_a
2.222e+00	banana	two	2015-02-15	14:40	2018-02-02 14:33	€17.95	row_2	grp_a
3.333e+01	coconut	three	2015-03-15	15:45	2018-03-03 03:44	€1.39	row_3	grp_a
4.444e+02	durian	four	2015-04-15	16:50	2018-04-04 15:55	€65,100.00	row_4	grp_a
5.550e+03	NA	five	2015-05-15	17:55	2018-05-05 04:00	€1,325.81	row_5	grp_b
NA	fig	six	2015-06-15	NA	2018-06-06 16:11	€13.26	row_6	grp_b
7.770e+05	grapefruit	seven	NA	19:10	2018-07-07 05:22	NA	row_7	grp_b
8.880e+06	honeydew	eight	2015-08-15	20:20	NA	€0.44	row_8	grp_b

Use exibble to create a gt table. Keep only the num and currency, columns, then, format those columns using the CNY and GBP currencies.

exibble %>%
  dplyr::select(num, currency) %>%
  gt() %>%
  fmt_currency(
    columns = num,
    currency = "CNY"
  ) %>%
  fmt_currency(
    columns = currency,
    currency = "GBP"
  )

num	currency
¥0.11	£49.95
¥2.22	£17.95
¥33.33	£1.39
¥444.40	£65,100.00
¥5,550.00	£1,325.81
NA	£13.26
¥777,000.00	NA
¥8,880,000.00	£0.44

`fmt_date()`: Format values as dates

fmt_date(
  data,
  columns,
  rows = everything(),
  date_style = 2
)

Format input date values that are either of the Date type, or, are character-based and expressed according to the ISO 8601 date format (YYYY-MM-DD). Once the appropriate data cells are targeted with columns (and, optionally, rows), we can simply apply a preset date style (see table in info_date_style() for info) to format the dates.

EXAMPLES

Use exibble to create a gt table. Keep only the date and time columns. Format the date column to have dates formatted as month_day_year (date style 5).

exibble %>%
  dplyr::select(date, time) %>%
  gt() %>%
  fmt_date(
    columns = date,
    date_style = 5
  )

date	time
January 15, 2015	13:35
February 15, 2015	14:40
March 15, 2015	15:45
April 15, 2015	16:50
May 15, 2015	17:55
June 15, 2015	NA
NA	19:10
August 15, 2015	20:20

Use exibble to create a gt table. keep only the date and time columns. Format the date column to have mixed date formats (dates after April will be different than the others).

exibble %>%
  dplyr::select(date, time) %>%
  gt() %>%
  fmt_date(
    columns = date,
    rows = as.Date(date) > as.Date("2015-04-01"),
    date_style = 6
  ) %>%
  fmt_date(
    columns = date,
    rows = as.Date(date) <= as.Date("2015-04-01"),
    date_style = 7
  )

date	time
15 Jan 2015	13:35
15 Feb 2015	14:40
15 Mar 2015	15:45
Apr 15, 2015	16:50
May 15, 2015	17:55
Jun 15, 2015	NA
NA	19:10
Aug 15, 2015	20:20

`fmt_time()`: Format values as times

fmt_time(
  data,
  columns,
  rows = everything(),
  time_style = 2
)

Format input time values that are character-based and expressed according to the ISO 8601 time format (HH:MM:SS). Once the appropriate data cells are targeted with columns (and, optionally, rows), we can simply apply a preset time style (see table in info_time_style() for info) to format the times.

EXAMPLES

Use exibble to create a gt table. Keep only the date and time columns. Format the time column to have times formatted as hms_p (time style 3).

exibble %>%
  dplyr::select(date, time) %>%
  gt() %>%
  fmt_time(
    columns = time,
    time_style = 3
  )

date	time
2015-01-15	1:35:00 PM
2015-02-15	2:40:00 PM
2015-03-15	3:45:00 PM
2015-04-15	4:50:00 PM
2015-05-15	5:55:00 PM
2015-06-15	NA
NA	7:10:00 PM
2015-08-15	8:20:00 PM

Use exibble to create a gt table. Keep only the date and time columns. Format the time column to have mixed time formats (times after 16:00 will be different than the others).

exibble %>%
  dplyr::select(date, time) %>%
  gt() %>%
  fmt_time(
    columns = time,
    rows = time > "16:00",
    time_style = 3
  ) %>%
  fmt_time(
    columns = time,
    rows = time <= "16:00",
    time_style = 4
  )

date	time
2015-01-15	1:35 PM
2015-02-15	2:40 PM
2015-03-15	3:45 PM
2015-04-15	4:50:00 PM
2015-05-15	5:55:00 PM
2015-06-15	NA
NA	7:10:00 PM
2015-08-15	8:20:00 PM

`fmt_datetime()`: Format values as date-times

fmt_datetime(
  data,
  columns,
  rows = everything(),
  date_style = 2,
  time_style = 2
)

Format input date-time values that are character-based and expressed according to the ISO 8601 date-time format (YYYY-MM-DD HH:MM:SS). Once the appropriate data cells are targeted with columns (and, optionally, rows), we can simply apply preset date and time styles (see tables in info_date_style() and info_time_style() for more info) to format the data-time values.

EXAMPLE

Use exibble to create a gt table. keep only the datetime column. Format the column to have dates formatted as month_day_year and times to be hms_p.

exibble %>%
  dplyr::select(datetime) %>%
  gt() %>%
  fmt_datetime(
    columns = datetime,
    date_style = 5,
    time_style = 3
  )

datetime
January 1, 2018 2:22:00 AM
February 2, 2018 2:33:00 PM
March 3, 2018 3:44:00 AM
April 4, 2018 3:55:00 PM
May 5, 2018 4:00:00 AM
June 6, 2018 4:11:00 PM
July 7, 2018 5:22:00 AM
NA

`fmt_markdown()`: Format Markdown text

fmt_markdown(
  data,
  columns,
  rows = everything()
)

Any Markdown-formatted text in the incoming cells will be transformed to the appropriate output type during render when using fmt_markdown().

EXAMPLE

Create a few Markdown-based text snippets.

text_1a <- "
### This is Markdown.

Markdown’s syntax is comprised entirely of
punctuation characters, which punctuation
characters have been carefully chosen so as
to look like what they mean... assuming
you’ve ever used email.
"

text_1b <- "
Info on Markdown syntax can be found
[here](https://daringfireball.net/projects/markdown/).
"

text_2a <- "
The **gt** package has these datasets:

 - `countrypops`
 - `sza`
 - `gtcars`
 - `sp500`
 - `pizzaplace`
 - `exibble`
"

text_2b <- "
There's a quick reference [here](https://commonmark.org/help/).
"

Arrange the text snippets as a tibble using the dplyr::tribble() function. Then, create a gt table and format all columns with fmt_markdown().

dplyr::tribble(
  ~Markdown, ~md,
  text_1a,   text_2a,
  text_1b,   text_2b,
) %>%
  gt() %>%
  fmt_markdown(columns = TRUE) %>%
  tab_options(table.width = px(400))

## Warning: `columns = TRUE` has been deprecated in gt 0.3.0:
## * please use `columns = everything()` instead

Markdown	md
This is Markdown. Markdown’s syntax is comprised entirely of punctuation characters, which punctuation characters have been carefully chosen so as to look like what they mean... assuming you’ve ever used email.	The gt package has these datasets: `countrypops` `sza` `gtcars` `sp500` `pizzaplace` `exibble`
Info on Markdown syntax can be found here.	There's a quick reference here.

`fmt_missing()`: Format missing values

fmt_missing(
  data,
  columns,
  rows = everything(),
  missing_text = "---"
)

Wherever there is missing data (i.e., NA values) a customized mark may present better than the standard NA text that would otherwise appear. The fmt_missing() function allows for this replacement through its missing_text argument (where an em dash serves as the default).

EXAMPLE

Use exibble to create a gt table. The NA values in different columns will be given replacement text.

exibble %>%
  dplyr::select(-row, -group) %>%
  gt() %>%
  fmt_missing(
    columns = 1:2,
    missing_text = "missing"
  ) %>%
  fmt_missing(
    columns = 4:7,
    missing_text = "nothing"
  )

num	char	fctr	date	time	datetime	currency
1.111e-01	apricot	one	2015-01-15	13:35	2018-01-01 02:22	49.950
2.222e+00	banana	two	2015-02-15	14:40	2018-02-02 14:33	17.950
3.333e+01	coconut	three	2015-03-15	15:45	2018-03-03 03:44	1.390
4.444e+02	durian	four	2015-04-15	16:50	2018-04-04 15:55	65100.000
5.550e+03	missing	five	2015-05-15	17:55	2018-05-05 04:00	1325.810
missing	fig	six	2015-06-15	nothing	2018-06-06 16:11	13.255
7.770e+05	grapefruit	seven	nothing	19:10	2018-07-07 05:22	nothing
8.880e+06	honeydew	eight	2015-08-15	20:20	nothing	0.440

`data_color()`: Set data cell colors using a palette or a color function

data_color(
  data,
  columns,
  colors,
  alpha = NULL,
  apply_to = c("fill", "text"),
  autocolor_text = TRUE
)

It’s possible to add color to data cells according to their values. The data_color() function colors all rows of any columns supplied. There are two ways to define how cells are colored: (1) through the use of a supplied color palette, and (2) through use of a color mapping function available from the scales package.

The first method colorizes cell data according to whether values are character or numeric. The second method provides more control over how cells are colored since we provide an explicit color function and thus other requirements such as bin counts, cut points, or a numeric domain.

EXAMPLES

Use countrypops to create a gt table. Apply a color scale to the population column with scales::col_numeric, four supplied colors, and a domain.

countrypops %>%
  dplyr::filter(country_name == "Mongolia") %>%
  dplyr::select(-contains("code")) %>%
  tail(10) %>%
  gt() %>%
  data_color(
    columns = population,
    colors = scales::col_numeric(
      palette = c("red", "orange", "green", "blue"),
      domain = c(0.2E7, 0.4E7)
    )
  )

country_name	year	population
Mongolia	2008	2628131
Mongolia	2009	2668289
Mongolia	2010	2712650
Mongolia	2011	2761516
Mongolia	2012	2814226
Mongolia	2013	2869107
Mongolia	2014	2923896
Mongolia	2015	2976877
Mongolia	2016	3027398
Mongolia	2017	3075647

Use pizzaplace to create a gt table. Apply colors from the red_material palette (in the ggsci pkg but more easily gotten from the paletteer package, info at info_paletteer()) to the sold and income columns. Set the domain of scales::col_numeric() to NULL will use the bounds of the available data as the domain.

pizzaplace %>%
  dplyr::filter(type %in% c("chicken", "supreme")) %>%
  dplyr::group_by(type, size) %>%
  dplyr::summarize(
    sold = dplyr::n(),
    income = sum(price),
    .groups = "drop"
  ) %>%
  gt(rowname_col = "size") %>%
  data_color(
    columns = c(sold, income),
    colors = scales::col_numeric(
      palette = paletteer::paletteer_d(
        palette = "ggsci::red_material"
      ) %>% as.character(),
      domain = NULL
    )
  )

	type	sold	income
L	chicken	4932	102339.0
M	chicken	3894	65224.5
S	chicken	2224	28356.0
L	supreme	4564	94258.5
M	supreme	4046	66475.0
S	supreme	3377	47463.5

SUMMARY

Numbers can be formatted with fmt_number(), values in scientific notation with fmt_scientific(), percentages with fmt_percent(), and currency values with fmt_currency().
Options for these number-based formats include: specification of separators, formatting according to a given locale, scaling values, applying a pattern, etc.
Dates, times, and date-times can be formatted to numbered styles with fmt_date(), fmt_time(), and fmt_datetime().
Markdown text can be transformed using fmt_markdown().
Missing values (i.e., NA values) can be substituted with text by using fmt_missing().
Cells can be colored according to their values with data_color(); use functions from the scales package and color palettes from the paletteer package to help with this.
A number of info_*() functions provide useful information on supported locales, currencies, date and time styles, and color palettes in paletteer.

Formatting Data

Intro

Functions in this module

fmt_number(): Format numeric values

EXAMPLES

fmt_integer(): Format values as integers

EXAMPLE

fmt_scientific(): Format values to scientific notation

EXAMPLE

fmt_engineering(): Format values to engineering notation

EXAMPLE

fmt_percent(): Format values as a percentage

EXAMPLE

fmt_currency(): Format values as currencies

EXAMPLES

fmt_date(): Format values as dates

EXAMPLES

fmt_time(): Format values as times

EXAMPLES

fmt_datetime(): Format values as date-times

EXAMPLE

fmt_markdown(): Format Markdown text

EXAMPLE

This is Markdown.

fmt_missing(): Format missing values

EXAMPLE

data_color(): Set data cell colors using a palette or a color function

EXAMPLES

SUMMARY

`fmt_number()`: Format numeric values

`fmt_integer()`: Format values as integers

`fmt_scientific()`: Format values to scientific notation

`fmt_engineering()`: Format values to engineering notation

`fmt_percent()`: Format values as a percentage

`fmt_currency()`: Format values as currencies

`fmt_date()`: Format values as dates

`fmt_time()`: Format values as times

`fmt_datetime()`: Format values as date-times

`fmt_markdown()`: Format Markdown text

`fmt_missing()`: Format missing values

`data_color()`: Set data cell colors using a palette or a color function