user3022875 user3022875 - 4 days ago 4
R Question

color cells of pandoc table

I have this table that I would like to render in PDF using PANDOC

table = data.frame(category = c("A","B","C"), groupA = c(.2,.3,.5), groupB= c(.6,.7,.9))
table
pandoc.table(table,split.table = Inf,keep.line.breaks = TRUE)

----------------------------
category groupA groupB
---------- -------- --------
A 0.2 0.6

B 0.3 0.7

C 0.5 0.9
----------------------------


How can I color the cells of the "groupA" and "groupB" columns with conditional formatting like:

>0 and <= .2 = "green"
>.2 and <= .3 = "red"
>.3 and <= .4 = "blue"
>.4 and <= .5 = "orange"
>.5 and <= .6 = "yellow"
>.6 and <= .7 = "black"
>.7 and <= .8 = "brown"
>.8 = "white"

Answer

If you're rendering to PDF, you (or someone else's function) have to format your table with LaTeX. While there are plenty of helpful packages and functions that will do all the work for you (knitr::kable, xtable, stargazer), if you need fine-grained control, you may need to edit LaTeX yourself, at least in part.

A reasonably painless option is Hmisc::latex, which will create the table from the data.frame, and has a parameter (among many) cellTexCmds that allows passing styling for individual cells via a matrix of similar dimensions to the data.frame. The file parameter to '' so it doesn't save a file, and where = '!htbp' so the table appears in the correct place in the document. To set cell background color, you'll need the xcolor or colortbl LaTeX packages, which can be loaded in the YAML frontmatter.

---
title: "Conditional Formatting"
header-includes:
   - \usepackage[table]{xcolor}
output:
  pdf_document: default
---

```{r}
df <- data.frame(category = c("A","B","C"), 
                 groupA = c(.2,.3,.5), 
                 groupB= c(.6,.7,.9))

df.format = matrix('', nrow = nrow(df), ncol = ncol(df))

df.format[, -1] <- paste0('cellcolor{', 
                          sapply(df[-1], function(x){
                              cut(x, breaks = c(0, seq(.2, .8, by = .1), 1), 
                                  labels = c('green', 'red', 'blue', 'orange', 
                                             'yellow', 'black', 'brown', 'white'))}),
                          '}')

df.format
```

```{r table, results='asis'}
Hmisc::latex(df, file = '', cellTexCmds = df.format, where = "!htbp")
```

rmd pdf

One annoyance of this approach is the LaTeX comment containing the latex command that doesn't disappear upon rendering. With some creativity you may be able to eliminate it, but a simple option is to use an Rnw file instead of Rmd, which is not too different and produces a similar result, but without the rendered comment.

\documentclass{article}

\usepackage[table]{xcolor}

\begin{document}
\title{Conditional Formatting}
\date{\vspace{-10ex}}
\maketitle

<<setup>>=
df <- data.frame(category = c("A","B","C"),
                 groupA = c(.2,.3,.5),
                 groupB= c(.6,.7,.9))

df.format = matrix('', nrow = nrow(df), ncol = ncol(df))

df.format[, -1] <- paste0('cellcolor{',
                          sapply(df[-1], function(x){
                              cut(x, breaks = c(0, seq(.2, .8, by = .1), 1),
                                  labels = c('green', 'red', 'blue', 'orange',
                                             'yellow', 'black', 'brown', 'white'))}),
                          '}')

df.format
@

<<table, results='asis'>>=
Hmisc::latex(df, title = '', file = '',
             cellTexCmds = df.format, where = "!htbp")
@

\end{document}
Comments