Make NA values in table just blank cells when usin

Question

This question already has an answer here:

• Hiding NA's when printing a dataframe in knitr 2 answers

Following on from a previous question, I am using the cell_spec function from kableExtra to change the background color of cells within a table. How can you remove the NA text and color the background white for the NA cells so that an NA appears just as a blank cell?

Below is my example table with the NA cells. In real life there is a dynamic number of columns with various number of NAs in each column.

---
output: pdf_document
---

```{r setup, include=FALSE}
library(kableExtra)
library(dplyr)
knitr::opts_chunk$set(echo = TRUE)

set.seed(2)
d <- data_frame(group = sample(LETTERS[1:5], 10, replace=TRUE), cat1=runif(10, 0, 100), cat2=runif(10, 0, 100))
d[3:5,2:3] <- NA

# Functions used to create color palette
max.val <- max(d[ , sapply(d, is.numeric)], na.rm=TRUE)
pal.fnc <- colorRamp(c("red", "yellow", "green"))

d <- d %>%
    mutate_if(is.numeric, function(x) {
  cell_spec(round(x,1), "latex", bold = F, color=grey(.3),
            background = rgb(pal.fnc(x/max.val) %>% replace(., is.na(.), 200), maxColorValue=255))
})   
```

```{r table, echo = FALSE}
kable(d,
      linesep = "",
      booktabs = T, 
      escape = F )
```

Answer 1

You can control knitr options(knitr.kable.NA = '') to suppress NAs, however this is not working here as you are overwriting the NAs with the value as \\cellcolor[HTML]{C8C8C8}{\\textcolor[HTML]{4D4D4D}{NA}}".

To make the options(knitr.kable.NA = '') line work, we will have to control the cell_spec function only run on non NA values, and prevent it from adding the extra HTML code to the NA values. We can use case_when within dplyr to conditionally mutate the data, so that the NAs are left untouched. To make it easier to read I created a function colourCell which is then used within the mutate_if statement before creating the table:

---
output: pdf_document
---

```{r setup, include=FALSE}
library(kableExtra)
library(dplyr)

options(knitr.kable.NA = '')

# Create Data
set.seed(2)
d <- data_frame(group = sample(LETTERS[1:5], 10, replace=TRUE), cat1=runif(10, 0, 100), cat2=runif(10, 0, 100))
d[3:5,2:3] <- NA

# Functions to format cell
max.val = max(d[ , sapply(d, is.numeric)], na.rm=TRUE)
pal.fnc <- colorRamp(c("red", "yellow", "green"))

colourCell <- function(x) {
  cell_spec(round(x,1), "latex",
            bold = F,
            color=grey(.3),
            background = rgb(pal.fnc(x/max.val) %>% 
                               replace(., is.na(.), 200), maxColorValue=255))
}
```

```{r table, echo = FALSE}
d %>%
  mutate_if(is.numeric, function(x) {
    case_when(!is.na(x) ~ colourCell(x))
})  %>%
  kable(
      linesep = "",
      booktabs = T, 
      escape = F)
```