Create tables with conditional formatting with RMarkdown + knitr

Question

I have a data frame and I want to output this in an HTML file through knitr and RMarkdown as a table with conditional formatting. Example:

n <- data.frame(x = c(1,1,1,1,1), y = c(0,1,0,1,0))
> n
  x y
1 1 0
2 1 1
3 1 0
4 1 1
5 1 0

I want rows with different values of x and y to be highlighted. So in this case, that would be rows 1, 3 and 5. Would be nice if the output in the HTML file would be an HTML table, but failing that an image would be fine as well.

Answer 1

I always wanted to extend pandoc.table in my pander package with this feature, but failed to get the time for that. But this question is really inspiring, probably will do that in the next few days. Until then, what about:

1. Load the package:

   library(pander)
   

2. Load your data:

   n <- data.frame(x = c(1,1,1,1,1), y = c(0,1,0,1,0))
   

3. Update your lines to be marked as strong in Pandoc:

   for (i in c(1, 3, 5))
       n[i, ] <- pandoc.strong.return(n[1, ])
   

4. Show the markdown version of your table:

   pandoc.table(n)
   pander(n)       # S3 method
   

5. Covert the markdown to e.g. HTML with brew syntax:

   Pandoc.brew(text = '<%=n%>', output = tempfile(), convert = 'html')
   

---

Update: I have updated pander to take some new arguments to highlight rows/columns/cells easily. Although I am still working on some further helper functions to make this process easier, here goes a quick demo so that you might see how it could help your workflow:

> pandoc.table(n, emphasize.rows = c(1, 3, 5))

-------
 x   y 
--- ---
*1* *0*

 1   1 

*0* *1*

 1   1 

*1* *0*
-------

> pandoc.table(n, emphasize.strong.cells = which(n == 1, arr.ind = TRUE))

-----------
  x     y  
----- -----
**1**   0  

**1** **1**

**1**   0  

**1** **1**

**1**   0  
-----------

---

Update: pander gained some helper functions to highlight cells in the tables even easier:

> t <- mtcars[1:3, 1:5]
> emphasize.cols(1)
> emphasize.rows(1)
> pandoc.table(t)

----------------------------------------------------
      &nbsp;         mpg    cyl   disp   hp    drat 
------------------- ------ ----- ------ ----- ------
   **Mazda RX4**     *21*   *6*  *160*  *110* *3.9* 

 **Mazda RX4 Wag**   *21*    6    160    110   3.9  

  **Datsun 710**    *22.8*   4    108    93    3.85 
----------------------------------------------------

Or directly with pander method:

> emphasize.strong.cells(which(t > 20, arr.ind = TRUE))
> pander(t)

---------------------------------------------------------
                 mpg     cyl   disp     hp     drat 
------------------- -------- ----- ------- ------- ------
   **Mazda RX4**     **21**    6   **160** **110**  3.9  

 **Mazda RX4 Wag**   **21**    6   **160** **110**  3.9  

  **Datsun 710**    **22.8**   4   **108** **93**   3.85 
---------------------------------------------------------

Please note that these new features are not published on CRAN yet, but you can find in the most recent version hosted on GitHub.

Answer 2

Here a solution based on xtable with a custom css. I think the solution is flexible since once you get it work , you can customize your html tables indefinitely if you know some css tricks.

Here we go. The solution consist in 3 files:

1. a css file where I alternate table row color.

   table {
      max-width: 95%;
      border: 1px solid #ccc;
   }
   
   th {
     background-color: #000000;
    color: #ffffff;
   }
   
   table tr:nth-child(odd) td{
      background-color: #FF0000;
   }
   table tr:nth-child(even) td{
       background-color: #00FFFF;
   }
   

2. an R script file to set RStudio markdown with the following content:

   options(rstudio.markdownToHTML = 
         function(inputFile, outputFile) {      
           require(markdown)
           markdownToHTML(inputFile, outputFile, stylesheet='customstyle.css')   
         }
   )
   

3. create a new markdown with the following:

   ```{r}
    source('initmd.R')
   ```
   
   
   ```{r,results='asis'}
   library(xtable)
   n <- data.frame(x = c(1,1,1,1,1), y = c(0,1,0,1,0))
   print(xtable(n),type='html')
   ```
   

finally convert the markdwon to html using knit HTML button and you should get something like this :