Formattable, change NA into '-' and keep values as numeric

November 16, 2022

I am trying to make a nice table with formattable for my shiny app. I would like to have the shading of ranges of cells, and NAs as -.

example <- structure(list(`2022` = c(NA_integer_, NA_integer_, NA_integer_, 
NA_integer_, NA_integer_, NA_integer_), `2021` = c(929L, NA, 
165L, 265L, 5616L, 13L), `2020` = c(660L, NA, 98L, 266L, 6735L, 
15L), `2019` = c(598L, NA, 96L, 326L, 6236L, 14L), `2018` = c(479L, 
NA, 107L, 384L, 4486L, 14L), `2017` = c(NA, NA, -547L, -3548L, 
3895L, 20L), `2016` = c(NA, NA, 19L, 535L, 6345L, 24L)), row.names = c(NA, 
-6L), class = c("tbl_df", "tbl", "data.frame"))

formattable(example,
            list(` ` = formatter("span", style = ~ style(color = "black",font.weight = "bold")),
                 area(col = 1:7) ~ color_tile("#DeF7E9", "#71CA97")))

My question: Is it possible to have NAs as character and still use shading based on the numeric values?

>Solution :

Does this produce what you want?

library(formattable)
library(dplyr)
library(tidyr)

example <- structure(list(`2022` = c(
  NA_integer_, NA_integer_, NA_integer_,
  NA_integer_, NA_integer_, NA_integer_
), `2021` = c(
  929L, NA,
  165L, 265L, 5616L, 13L
), `2020` = c(
  660L, NA, 98L, 266L, 6735L,
  15L
), `2019` = c(598L, NA, 96L, 326L, 6236L, 14L), `2018` = c(
  479L,
  NA, 107L, 384L, 4486L, 14L
), `2017` = c(
  NA, NA, -547L, -3548L,
  3895L, 20L
), `2016` = c(NA, NA, 19L, 535L, 6345L, 24L)), row.names = c(
  NA,
  -6L
), class = c("tbl_df", "tbl", "data.frame"))

example |>
  dplyr::mutate_all(.funs = ~ tidyr::replace_na(as.character(.x), "-")) |>
  formattable(
    list(
      ` ` = formatter("span", style = ~ style(color = "black", font.weight = "bold")),
      area(col = 1:7) ~ color_tile("#DeF7E9", "#71CA97")
    )
  )
#> Warning in gradient(as.numeric(x), ...): NAs introduced by coercion

^{Created on 2022-11-16 with reprex v2.0.2}

If you want to replace the NA values, with "-", then you must convert to a character column. The function "replace_na" then replaces the NA with the specified value. This might mess up some other things, but it looks like formattable will still style it by converting to integers.