how do you change colors of certain cells in xtable markdown?

Question

I have a data frame called j:

dput(j)

structure(list(Trans = c(89.8, 3337, NA, 97.55, NA, 3558.7, NA, 
4290.6, NA, 65.95, 94.55, 3495.9), `%CPU` = c(6.938, 79.853, 
1.875, 4.87, 1.46, 37.885, 1.63, 64.576, 1.165, 3.425, 5.67, 
33.856), `%Heap Used` = c(9.9, 76.95, 3.77, 9.8, 6.73, 59.23, 
3.94, 67.38, 3.73, 9.13, 9.57, 62.11), `Heap Usage/MB` = c(263.884, 
1942.246, 99.104, 257.717, 178.951, 1657.447, 99.933, 2137.134, 
96.687, 242.024, 256.302, 1646.117)), .Names = c("Trans", "%CPU", 
"%Heap Used", "Heap Usage/MB"), class = "data.frame", row.names = c(NA, 
-12L))

If I want to color code a cell based on threshold in r markdown, how would I do that?

For example if %CPU>70, I need to color code that cell to be red.

I can print it in r markdown like this:

print(xtable(j_cor,digits=2,row.names=FALSE,caption="JVM Usage"),caption.placement="top", tabular.environment="longtable",comment=FALSE,floating=FALSE)

But I like to place colors in the cells based on threshold r markdown Any ideas?

I have tried something like this, but the color or the cell did not change in pdf file:

j[,2] = ifelse(j[,2] < 60, paste0("\\colorbox{red}{", j[,2], "}"), j[,2])


sessionInfo()

R version 3.2.4 (2016-03-10)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 7 x64 (build 7601) Service Pack 1

locale:
[1] LC_COLLATE=English_United States.1252  LC_CTYPE=English_United States.1252    LC_MONETARY=English_United States.1252
[4] LC_NUMERIC=C                           LC_TIME=English_United States.1252    

attached base packages:
[1] stats4    grid      stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
 [1] ggthemes_3.4.0    flexclust_1.3-4   modeltools_0.2-21 lattice_0.20-35   tidyr_0.6.1       jsonlite_1.4      Rcpp_0.12.10      lazyeval_0.2.0   
 [9] knitr_1.16        gridExtra_2.0.0   xtable_1.8-0      data.table_1.9.6  cowplot_0.6.2     reshape2_1.4.1    corrplot_0.77     scales_0.4.1     
[17] stringr_1.0.0     chron_2.3-47      ggplot2_2.2.1     dplyr_0.5.0       purrr_0.2.2       xml2_1.0.0        plyr_1.8.4        RCurl_1.95-4.7   
[25] bitops_1.0-6      XML_3.98-1.3      httr_1.0.0        rmarkdown_1.5    

loaded via a namespace (and not attached):
 [1] tools_3.2.4      digest_0.6.12    evaluate_0.10    tibble_1.3.0     gtable_0.2.0     DBI_0.6-1        parallel_3.2.4   yaml_2.1.14     
 [9] rprojroot_1.2    R6_2.2.1         magrittr_1.5     backports_1.1.0  htmltools_0.3.5  assertthat_0.2.0 colorspace_1.3-2 labeling_0.3    
[17] stringi_1.1.5    munsell_0.4.3 

I have copied the exact text to my Rstudio and tried to run it, I am getting this error:

"C:/Program Files/RStudio/bin/pandoc/pandoc" +RTS -K512m -RTS test_color.utf8.md --to latex --from markdown+autolink_bare_uris+ascii_identifiers+tex_math_single_backslash --output test_color.pdf --template "C:\R\win-library\3.2\rmarkdown\rmd\latex\default.tex" --highlight-style tango --latex-engine xelatex --variable graphics=yes --variable "geometry:margin=1in" 
! LaTeX Error: Missing \begin{document}.

See the LaTeX manual or LaTeX Companion for explanation.
Type  H <return>  for immediate help.
 ...                                              

l.1 <

pandoc.exe: Error producing PDF from TeX source
Error: pandoc document conversion failed with error 43
In addition: Warning message:
running command '"C:/Program Files/RStudio/bin/pandoc/pandoc" +RTS -K512m -RTS test_color.utf8.md --to latex --from markdown+autolink_bare_uris+ascii_identifiers+tex_math_single_backslash --output test_color.pdf --template "C:\Documents\R\win-library\3.2\rmarkdown\rmd\latex\default.tex" --highlight-style tango --latex-engine xelatex --variable graphics=yes --variable "geometry:margin=1in"' had status 43 

Answer 1

Be careful with the % signs in the column names. You'll need to specify sanitizing functions for text, to deal with the LaTeX commands, and for column names to handle the percent signs.

Use the \cellcolor command to color a cell in LaTeX table.

Here is an example .Rmd file

---
title: "Colors of xtable cells"
header-includes:
   - \usepackage{longtable}
   - \usepackage[table]{xcolor}
   - \usepackage{colortbl}
output:
    pdf_document
---

Build the example data set

```{r, include = FALSE}
library(xtable)
j <-
  structure(list(Trans = c(89.8, 3337, NA, 97.55, NA, 3558.7, NA, 
  4290.6, NA, 65.95, 94.55, 3495.9), `%CPU` = c(6.938, 79.853, 
  1.875, 4.87, 1.46, 37.885, 1.63, 64.576, 1.165, 3.425, 5.67, 
  33.856), `%Heap Used` = c(9.9, 76.95, 3.77, 9.8, 6.73, 59.23, 
  3.94, 67.38, 3.73, 9.13, 9.57, 62.11), `Heap Usage/MB` = c(263.884, 
  1942.246, 99.104, 257.717, 178.951, 1657.447, 99.933, 2137.134, 
  96.687, 242.024, 256.302, 1646.117)), .Names = c("Trans", "%CPU", 
  "%Heap Used", "Heap Usage/MB"), class = "data.frame", row.names = c(NA, 
  -12L))
```

Create a version of the `data.frame` with red cells for \%CPU greater than 70.
Set the color and round to two digits.

```{r, include = FALSE}
j[, 2] <- ifelse(j[, 2] > 70, paste("\\cellcolor{red}{", round(j[, 2], 2), "}"), round(j[, 2], 2))
```

The resulting table is:

```{r results = "asis"}
print(xtable(j,
             digits = 2,
             row.names = FALSE,
             caption = "JVM Usage"),
      sanitize.text.function = identity,
      sanitize.colnames.function = NULL,
      caption.placement   = "top",
      tabular.environment = "longtable",
      comment = FALSE,
      floating=FALSE) 
```

Which produces the following output: