Having trouble with mutate and NA values/kableExtra

Publishing Your Results
1

I'm trying to improve the readability of a rmarkdown document I'm working on. To that end, I'm trying to conditionally colour code my table output using kableExtra so people can more easily skim read my report. I've managed to do some conditional formatting previously (e.g. if value > 0, colour it green), but I'm having trouble doing so when I have NA in the variable by which I want to format my cells (if that makes sense).

I've included a reprex below which hopefully illustrates what I'm attempting to do. Sorry if it's a bit OTT!

library(tidyverse)
library(kableExtra)

# Example data 
data <- tribble(
  ~direction, ~value,
  "Ipsi", 25,
  "Conv", 30,
  "Contra", 9,
  NA, 10
  )

# Colours for directions
cols  <- c(
           "Ipsi"   = "#CC6677",
           "Conv"   = "#2DA17E",
           "Contra" = "#4477AA"
          )
na.col = "#A5A5A5"

# Testing ifelse
data %>%
  mutate(
    simple_test = ifelse(
      is.na(direction),
       'direction is NA',
      'direction is not NA'
    )
  )
#> # A tibble: 4 x 3
#>   direction value simple_test        
#>   <chr>     <dbl> <chr>              
#> 1 Ipsi         25 direction is not NA
#> 2 Conv         30 direction is not NA
#> 3 Contra        9 direction is not NA
#> 4 <NA>         10 direction is NA

# Do colours show up as expected?
data %>%
  mutate(
    call_colours = ifelse(
      is.na(direction),
      na.col,
      cols[direction]
    )
  )
#> # A tibble: 4 x 3
#>   direction value call_colours
#>   <chr>     <dbl> <chr>       
#> 1 Ipsi         25 #CC6677     
#> 2 Conv         30 #2DA17E     
#> 3 Contra        9 #4477AA     
#> 4 <NA>         10 #A5A5A5

# Try combining with cell_spec from KableExtra; Results in error
data %>%
  mutate(
    call_cell_spec = ifelse(
      is.na(direction),
      cell_spec('direction is NA', background = na.col),
      cell_spec(direction, background = cols[direction])
    )
  )
#> Error in mutate_impl(.data, dots): Evaluation error: missing value where TRUE/FALSE needed.

# Filtering out NA values in direction works
data %>%
  filter(!is.na(direction)) %>%
  mutate(
    call_cell_spec = ifelse(
      is.na(direction),
      cell_spec('direction is NA', background = na.col),
      cell_spec(direction, background = cols[direction])
    )
  )
#> # A tibble: 3 x 3
#>   direction value call_cell_spec                                          
#>   <chr>     <dbl> <chr>                                                   
#> 1 Ipsi         25 "<span style=\"     border-radius: 4px; padding-right: ~
#> 2 Conv         30 "<span style=\"     border-radius: 4px; padding-right: ~
#> 3 Contra        9 "<span style=\"     border-radius: 4px; padding-right: ~

Created on 2018-10-24 by the reprex package (v0.2.1)

2

I'm not sure why two calls to cell_spec for a single operation doesn't work, but the solution is to use a single cell_spec call and move the ifelse into the background argument. Below is a sample rmarkdown document to do that. I've actually created the highlighted table two ways. The first uses a function to generate the colors. That was my first approach, then I realized that the issue is having two cell_spec calls, and I should just move the ifelse inside a single cell_spec call. I've left my original approach in as well, in case you find it useful.

---
output:
  html_document: default
  pdf_document: default
---

```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = FALSE, message=FALSE, warning=FALSE)
```

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

# Example data 
data <- tribble(
  ~direction, ~value,
  "Ipsi", 25,
  "Conv", 30,
  "Contra", 9,
  NA, 10
)

na.col="#A5A5A5"

cols  <- c(
           "Ipsi"   = "#CC6677",
           "Conv"   = "#2DA17E",
           "Contra" = "#4477AA"
          )

# Function to provide the colors
pal.fnc = function(x, palette=cols, na.col="#A5A5A5") {
  ifelse(is.na(x), na.col, palette[match(x, names(palette))])
}
```

```{r}
# Use a color-generating function
data %>%
  mutate(direction = cell_spec(direction, background=pal.fnc(direction))) %>%
  kable(format="markdown")
```

```{r}
# Your example, but with ifelse inside a single cell_spec call
data %>%
  mutate(direction = cell_spec(direction, background=ifelse(is.na(direction), na.col, cols[direction]))) %>% 
  kable(format="markdown")
```

Here is the output, for both html and PDF:

25%20PM

04%20PM

2 Likes
3

Thanks yet again Joel for the explanation and code :slight_smile:

I'm not sure I understand either why cell_spec doesn't work in this instance. I've used case_when (probably improperly!) to format other cells which has 2 or more cell_spec, for e.g. I have this function which I can use to change a cell to bold if it's associated with a significant value:

signif_bold <- function(signif_val, var_name) {
  temp = case_when(
    signif_val < 0.05 ~ cell_spec(var_name, color = "black", bold = T),
    TRUE ~ cell_spec(var_name)
  )
  return(temp)
}

# Pass results from a dunn.test here..
dunn_df %>%
  mutate(
    Comparisons = signif_bold(P.adjusted,comparisons)
  ) %>%
kable()

and that works without throwing up any errors...