Special zero value in gt tables

Question

I'm using the {gt} package to create tables in a report. I would like it so that the "$0.00" currency values are replaced with "-", which is something that's easy to do in Excel but seems surprisingly hard to do with {gt}. Here is an example of a table I would like to have the zero values replaced with something easier on the eyes. The best I can seem to do is use fmt and make a custom function that recreates the entire functionality of fmt_currency, which doesn't seem great.

library(gt)
library(magrittr)
data <- data.frame(x=c(1.23,4.56,0,0,0,0,0))
table <- gt(data) %>%
  fmt_currency(x)

Cutup

Answer 1

I think that gt::text_transform() can solve your immediate problem.

library(gt)
library(magrittr)
data <- data.frame(x=c(1.23,4.56,0,0,0,50,1.5))
table <- data %>% 
  gt() %>%
  fmt_currency(x)

table

table %>% 
  text_transform(
    locations = cells_body(
      x,
      rows = x==0
    ),
    fn = function(x){
      "-"
    }
  )

Multiple Columns

If you want to do it across multiple columns, you may want to also wrap it into a function and call against specific columns.

data <- data.frame(
  x = c( 0, -0.230, 0, -0.445, 0),
  y = c( -0.230, 0.0705, 0.460, -0.686, 0),
  z = c( 0, 0, 0.07, 0.129, -0.68)
)

currency_dash <- function(gt_data, col_name) {
  text_transform(
    gt_data,
    locations = cells_body(
      columns = {{ col_name }},
      rows = {{ col_name }} == 0
    ),
    fn = function(x) {
      "-"
    }
  )
}

data %>% 
  gt() %>% 
  fmt_currency(columns = everything()) %>% 
  currency_dash(x) %>% 
  currency_dash(y) %>% 
  currency_dash(z)

General Transform

But you'd likely be better suited with just putting the logic into the text_transform().

data <- data.frame(
  x = c( 0, -0.230, 0, -0.445, 0),
  y = c( -0.230, 0.0705, 0.460, -0.686, 0),
  z = c( 0, 0, 0.07, 0.129, -0.68)
)

table_currency <- data %>% 
  gt() %>% 
  fmt_currency(everything()) 

table_currency %>% 
  text_transform(
    locations = cells_body(),
    fn = function(x) ifelse(x == "$0.00", "-", x))
  ) 

Spermary

Oh interesting-- I wasn't expecting it to have a post processing step but I like this because I can easily make it work with numbers too. Thanks! – Cutup

Happy to help! Note that a potential downside is that if you have a lot of columns to apply this to, you may want to wrap it up into a function rather that takes the col_name/row_name as arguments. Added an example above. – Spermary

Or what I did in this immediate situation is I put the logic into the function: ``` text_transform(locations = cells_body(), fn = function(x) if_else(x == "$0.00"),"-",x) ``` – Cutup

Answer 2

Of course, the other responses are great. I like to solve this problem by using gt::fmt(), a general formatting function, instead of gt::fmt_currency().

library(gt)

data <- 
  data.frame(
    x = c( 0, -0.230, 0, -0.445, 0),
    y = c( -0.230, 0.0705, 0.460, -0.686, 0),
    z = c( 0, 0, 0.07, 0.129, -0.68)
  )

table <- 
  data %>% 
  gt() %>%
  fmt(
    columns = everything(),
    fns = function(x) ifelse(x == 0, "—", scales::dollar(x, accuracy = 0.01))
  )

^{Created on 2021-08-24 by the reprex package (v2.0.1)}

Chloride

This is the most succinct of the various options. – Spermary

Gets my vote for optimal compactness and readability among the options. – Nonillion

Answer 3

Upvoted the answer from @thomas-mock because it's internal to gt. If you're not confined to doing all the operations within gt::fmt_currency, leveraging dplyr and scales can be useful, particularly when you might want to combine with dplyr::across to replace anywhere this happens in an entire table.

library(gt)
library(dplyr)

df <- data.frame(
  amt_x = c(1.23,4.56,0,0,0,0,0),
  amt_y = c(12.32,2.26,4.23,0,0,0,0)
)

df %>%
  mutate(
    across(starts_with("amt"), scales::dollar),
    across(starts_with("amt"), ~recode(.x, "$0.00" = "-")),
  ) %>%
  gt() %>%
  cols_align(
    align = "right",
    columns = starts_with("amt")
  )

Nonillion