03-format-data.qmd

---
title: "Formatting Data"
format: html
html-table-processing: none
---

```{r}
library(gt)
library(tidyverse)
```

## 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 formatting function (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()`
- `sub_missing()`
- `data_color()`
- `text_transform()`

Information functions:

- `info_locales()`
- `info_currencies()`
- `info_date_style()`
- `info_time_style()`
- `info_paletteer()`

------


### `fmt_number()`: Format numeric values

```r
fmt_number(
  data,
  columns = everything(),
  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,
  system = c("intl", "ind"),
  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.

```{r}
exibble |>
  dplyr::select(num, char) |>
  gt() |>
  fmt_number()
```


We can limit which numeric columns are formatted by targeting columns to format in the `columns` argument.

```{r}
exibble |>
  dplyr::select(num, char, currency) |>
  gt() |>
  fmt_number(columns = num)
```


Let's format the `num` column as numeric with three decimal places. Don't use digit separators (`use_seps = FALSE`).

```{r}
exibble |>
  dplyr::select(num) |>
  gt() |>
  fmt_number(
    decimals = 3,
    use_seps = FALSE
  )
```


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

```{r}
exibble |>
  dplyr::select(num) |>
  gt() |>
  fmt_number(
    rows = 1:3,
    decimals = 4
  )
```


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

```{r}
exibble |>
  dplyr::select(num, currency) |>
  gt() |>
  fmt_number(
    columns = num,
    rows = currency > 30,
    decimals = 4
  )
```


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

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


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

```{r}
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(
    decimals = 2,
    suffixing = TRUE
  )
```


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.

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

------

### `fmt_integer()`: Format values as integers

```r
fmt_integer(
  data,
  columns = everything(),
  rows = everything(),
  use_seps = TRUE,
  accounting = FALSE,
  scale_by = 1,
  suffixing = FALSE,
  pattern = "{x}",
  sep_mark = ",",
  force_sign = FALSE,
  system = c("intl", "ind"),
  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.

```{r}
exibble |>
  dplyr::select(num) |>
  gt() |>
  fmt_integer()
```


------

### `fmt_scientific()`: Format values to scientific notation

```r
fmt_scientific(
  data,
  columns = everything(),
  rows = everything(),
  decimals = 2,
  drop_trailing_zeros = FALSE,
  scale_by = 1,
  exp_style = "x10n",
  pattern = "{x}",
  sep_mark = ",",
  dec_mark = ".",
  force_sign_m = FALSE,
  force_sign_n = 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).

```{r}
exibble |>
  dplyr::select(num, currency) |>
  gt() |>
  fmt_scientific(
    columns = num,
    rows = num <= 500,
    decimals = 1
  )
```


------

### `fmt_percent()`: Format values as a percentage

```r
fmt_percent(
  data,
  columns = everything(),
  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",
  system = c("intl", "ind"),
  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.

```{r}
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
  )
```

------

### `fmt_currency()`: Format values as currencies

```r
fmt_currency(
  data,
  columns = everything(),
  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,
  system = c("intl", "ind"),
  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`).

```{r}
exibble |>
  gt() |>
  fmt_currency(
    columns = currency,
    currency = "EUR"
  )
```


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

```{r}
exibble |>
  dplyr::select(num, currency) |>
  gt() |>
  fmt_currency(
    columns = num,
    currency = "JPY"
  ) |>
  fmt_currency(
    columns = currency,
    currency = "GBP"
  )
```

------

### `fmt_date()`: Format values as dates

```r
fmt_date(
  data,
  columns = everything(),
  rows = everything(),
  date_style = "iso",
  pattern = "{x}",
  locale = NULL
)
```

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`).

```{r}
exibble |>
  dplyr::select(date, time) |>
  gt() |>
  fmt_date(
    columns = date,
    date_style = "month_day_year"
  )
```


Information on these formats are available in `info_date_style()`

```{r}
info_date_style()
```


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).

```{r}
exibble |>
  dplyr::select(date, time) |>
  gt() |>
  fmt_date(
    columns = date,
    rows = as.Date(date) > as.Date("2015-04-01"),
    date_style = "m_day_year"
  ) |>
  fmt_date(
    columns = date,
    rows = as.Date(date) <= as.Date("2015-04-01"),
    date_style = "day_m_year"
  )
```


------

### `fmt_time()`: Format values as times

```r
fmt_time(
  data,
  columns = everything(),
  rows = everything(),
  time_style = "iso",
  pattern = "{x}",
  locale = NULL
)
```

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 `"h_m_s_p"` (time style `3`).

```{r}
exibble |>
  dplyr::select(date, time) |>
  gt() |>
  fmt_time(
    columns = time,
    time_style = "h_m_s_p"
  )
```


Information on these formats are available in `info_time_style()`

```{r}
info_time_style()
```


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).

```{r}
exibble |>
  dplyr::select(date, time) |>
  gt() |>
  fmt_time(
    columns = time,
    rows = time > "16:00",
    time_style = "h_m_s_p"
  ) |>
  fmt_time(
    columns = time,
    rows = time <= "16:00",
    time_style = "h_m_p"
  )
```

------

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

```r
fmt_datetime(
  data,
  columns = everything(),
  rows = everything(),
  date_style = "iso",
  time_style = "iso",
  sep = " ",
  format = NULL,
  tz = NULL,
  pattern = "{x}",
  locale = NULL
)
```

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 `"h_m_s_p"`.

```{r}
exibble |>
  dplyr::select(datetime) |>
  gt() |>
  fmt_datetime(
    columns = datetime,
    date_style = "month_day_year",
    time_style = "h_m_s_p"
  )
```


------

### `fmt_markdown()`: Format Markdown text

```r
fmt_markdown(
  data,
  columns = everything(),
  rows = everything(),
  md_engine = c("markdown", "commonmark")
)
```

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.

```{r}
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()`.

```{r}
dplyr::tribble(
  ~Markdown, ~md,
  text_1a,   text_2a,
  text_1b,   text_2b,
) |>
  gt() |>
  fmt_markdown() |>
  tab_options(table.width = px(400))
```

------

### `sub_missing()`: Format missing values

```r
sub_missing(
  data,
  columns = everything(),
  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 `sub_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.

```{r}
exibble |>
  dplyr::select(-row, -group) |>
  gt() |>
  sub_missing(
    columns = c(num, char),
    missing_text = "missing"
  ) |>
  sub_missing(
    columns = -c(num, char),
    missing_text = "nothing"
  )
```

------

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

```r
data_color(
  data,
  columns = everything(),
  rows = everything(),
  direction = c("column", "row"),
  target_columns = NULL,
  method = c("auto", "numeric", "bin", "quantile", "factor"),
  palette = NULL,
  domain = NULL,
  bins = 8,
  quantiles = 4,
  levels = NULL,
  ordered = FALSE,
  na_color = NULL,
  alpha = NULL,
  reverse = FALSE,
  fn = NULL,
  apply_to = c("fill", "text"),
  autocolor_text = TRUE,
  contrast_algo = c("apca", "wcag"),
  colors = NULL
)
```

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 `method = "numeric"`, four supplied colors in `palette`, and a `domain` range (two-element vector).

```{r}
countrypops |>
  dplyr::filter(country_name == "Mongolia") |>
  dplyr::select(-contains("code")) |>
  tail(10) |>
  gt() |>
  data_color(
    columns = population,
    method = "numeric",
    palette = c("red", "orange", "green", "blue"),
    domain = c(2E6, 4E6)
  )
```


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.

```{r}
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),
    method = "numeric",
    palette = "ggsci::red_material"
  )
```

------

### `text_transform()`: Perform text transformations with a custom function

```r
text_transform(
  data,
  fn,
  locations = cells_body()
)
```

Text transforming in **gt** is the act of modifying formatted strings in targeted cells. The `text_transform()` function provides the most flexibility of all the `⁠text_*(`)⁠ functions in their family of functions. With it, you target the cells to undergo modification in the locations argument while also supplying a function to the `fn` argument.

The function given to `fn` should ideally at the very least take `x` as an input (it stands for the character vector that is essentially the targeted cells) and return a character vector of the same length as the input. Using the construction `function(x) { .. }` for the function is recommended.

##### EXAMPLES

Use a subset of the `sp500` dataset to create a **gt** table. Transform the text in the `date` column using a function supplied to `text_transform()` (via the `fn` argument).

```{r}
sp500 |>
  dplyr::slice_head(n = 10) |>
  dplyr::select(date, open, close) |>
  dplyr::arrange(-dplyr::row_number()) |>
  gt() |>
  fmt_currency() |>
  text_transform(
    fn = function(x) {
      paste0("Date: ", x)
    },
    locations = cells_body(columns = date)
  )
```

------


### SUMMARY

1. Numbers can be formatted with `fmt_number()`, values in scientific notation with `fmt_scientific()`, percentages with `fmt_percent()`, and currency values with `fmt_currency()`.
2. Options for these number-based formats include: specification of separators, formatting according to a given locale, scaling values, applying a pattern, etc.
3. Dates, times, and date-times can be formatted to numbered styles with `fmt_date()`, `fmt_time()`, and `fmt_datetime()`. 
4. Markdown text can be transformed using `fmt_markdown()`.
5. Missing values (i.e., `NA` values) can be substituted with text by using `sub_missing()`.
6. 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.
7. A number of `info_*()` functions provide useful information on supported locales, currencies, date and time styles, and color palettes in **paletteer**.