Conditional Formatting Tables using R

[This article was first published on Educate-R - R, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

One thing that I had the opportunity to develop while working last year at Saint Paul Public Schools was figuring out a quick, easy, and painless way to do interactive report generation. When I arrived in the REA department at Saint Paul Public Schools, the report generation process was roughly as follows:
1. Do the analysis in SPSS (compute percent proficient for standard tests by various subgroups).
2. Format output and copy output into Excel.
3. Once in Excel, do lookup tables to generate the report in Excel.

This process provided a few areas that could introduce errors. The copying from SPSS into Excel could produce errors and the lookup formula’s in Excel can be tricky. The correct columns need to be specified in the correct destination on another sheet.

One of the largest pushes in a school district is to receive the district’s test score results and quickly as possible. I felt that I could create an interactive report through the use of R and LaTeX that would greatly enhance the workflow and speed of report generation.

My process involved creating R script files for each report and export the tables for the reports as .tex files. The .tex files were created using the Hmisc R package, more specifically the latex function. The latex function is great as it offers a lot of control over the output of the resulting .tex table file. One thing you can do is conditional formatting of the table, see this document for a more thorough explanation: conditional formatting with the latex function

Here is a small minimal example. In the example, suppose we want to calculate the average Melanoma thickness by the status of the person (i.e. did they die from Melanoma, still alive, or died from other causes).

library<span class="p">(</span>MASS<span class="p">)</span>   <span class="c1"># Load for Melanoma Data</span>
library<span class="p">(</span>Hmisc<span class="p">)</span>  <span class="c1"># Load for latex function</span>
library<span class="p">(</span>data.table<span class="p">)</span>  <span class="c1"># Used for aggregating</span>

mela <span class="o"><-</span> data.table<span class="p">(</span>Melanoma<span class="p">)</span>

<span class="c1"># Aggregating</span>
mela.status <span class="o"><-</span> mela<span class="p">[,</span> list<span class="p">(</span>avgThick <span class="o">=</span> mean<span class="p">(</span>thickness<span class="p">)),</span> by <span class="o">=</span> status<span class="p">]</span>

<span class="c1"># Conditional formatting</span>
cellTex <span class="o"><-</span> matrix<span class="p">(</span>rep<span class="p">(</span><span class="s">""</span><span class="p">,</span> NROW<span class="p">(</span>mela.status<span class="p">)</span> <span class="o">*</span> NCOL<span class="p">(</span>mela.status<span class="p">)),</span>
                  nrow <span class="o">=</span> NROW<span class="p">(</span>mela.status<span class="p">))</span>
cellTex<span class="p">[,</span><span class="m">1</span><span class="p">]</span> <span class="o"><-</span> ifelse<span class="p">(</span>mela.status<span class="o">$</span>avgThick <span class="o">></span> <span class="m">4</span><span class="p">,</span> <span class="s">"cellcolor{red}"</span><span class="p">,</span>
                  ifelse<span class="p">(</span>mela.status<span class="o">$</span>avgThick <span class="o"><</span> <span class="m">3</span><span class="p">,</span> <span class="s">"cellcolor{green}"</span><span class="p">,</span>
                         <span class="s">""</span><span class="p">))</span>

<span class="c1"># Shading alternate rows</span>
my.rownamesTexCmd <span class="o"><-</span> rep<span class="p">(</span><span class="s">""</span><span class="p">,</span> nrow<span class="p">(</span>mela.status<span class="p">))</span>
index <span class="o"><-</span> <span class="p">(</span><span class="m">1</span><span class="o">:</span>nrow<span class="p">(</span>mela.status<span class="p">)</span><span class="o">/</span><span class="m">2</span><span class="p">)</span> <span class="o">==</span> <span class="p">(</span><span class="m">1</span><span class="o">:</span>nrow<span class="p">(</span>mela.status<span class="p">)</span><span class="o">%/%</span><span class="m">2</span><span class="p">)</span>
my.rownamesTexCmd<span class="p">[</span>index<span class="p">]</span> <span class="o"><-</span> <span class="s">"shadeRow"</span>

<span class="c1"># Creating the .tex file</span>
<span class="c1"># Note, this is currently printed in R console</span>
latex<span class="p">(</span>round<span class="p">(</span>mela.status<span class="p">,</span> <span class="m">2</span><span class="p">),</span> title <span class="o">=</span> <span class="s">''</span><span class="p">,</span> file <span class="o">=</span> <span class="s">''</span><span class="p">,</span> booktabs <span class="o">=</span> <span class="kc">TRUE</span><span class="p">,</span> 
      rownamesTexCmd <span class="o">=</span> my.rownamesTexCmd<span class="p">,</span> cellTexCmds <span class="o">=</span> cellTex<span class="p">,</span>
      rowname <span class="o">=</span> <span class="kc">NULL</span><span class="p">)</span>

Below is the resulting LaTeX code that is created from the latex function. The conditional formatting is the \cellcolor{} commands. You need to ensure that the color is defined, either as a default color or one you define in the preamble. Secondly, the \shadeRow command will shade that row and you need to ensure you have the first line below in your preamble.

<span class="c">% Including a similar command in your preamble to define row shading.</span>
<span class="k">\providecommand</span><span class="nb">{</span><span class="k">\shadeRow</span><span class="nb">}{</span><span class="k">\rowcolor</span><span class="na">[rgb]</span><span class="nb">{</span>0, 0.99, 0<span class="nb">}}</span>
<span class="c">% </span>
<span class="c">% </span>
<span class="k">\begin</span><span class="nb">{</span>table<span class="nb">}</span>[!tbp]
<span class="k">\begin</span><span class="nb">{</span>center<span class="nb">}</span>
<span class="k">\begin</span><span class="nb">{</span>tabular<span class="nb">}{</span>rr<span class="nb">}</span>
<span class="k">\toprule</span>
<span class="k">\multicolumn</span><span class="nb">{</span>1<span class="nb">}{</span>c<span class="nb">}{</span>status<span class="nb">}&</span><span class="k">\multicolumn</span><span class="nb">{</span>1<span class="nb">}{</span>c<span class="nb">}{</span>avgThick<span class="nb">}</span><span class="k">\tabularnewline</span>
<span class="k">\midrule</span>
 <span class="s">$</span><span class="m">3</span><span class="s">$</span><span class="nb">&</span>   <span class="s">$</span><span class="m">3</span><span class="nb">.</span><span class="m">72</span><span class="s">$</span><span class="k">\tabularnewline</span>
<span class="k">\shadeRow</span>   <span class="s">$</span><span class="m">2</span><span class="s">$</span><span class="nb">&</span><span class="k">\cellcolor</span><span class="nb">{</span>green<span class="nb">}</span>   <span class="s">$</span><span class="m">2</span><span class="nb">.</span><span class="m">24</span><span class="s">$</span><span class="k">\tabularnewline</span>
  <span class="s">$</span><span class="m">1</span><span class="s">$</span><span class="nb">&</span><span class="k">\cellcolor</span><span class="nb">{</span>red<span class="nb">}</span>   <span class="s">$</span><span class="m">4</span><span class="nb">.</span><span class="m">31</span><span class="s">$</span><span class="k">\tabularnewline</span>
<span class="k">\bottomrule</span>
<span class="k">\end</span><span class="nb">{</span>tabular<span class="nb">}</span>
<span class="k">\end</span><span class="nb">{</span>center<span class="nb">}</span>
<span class="k">\end</span><span class="nb">{</span>table<span class="nb">}</span>

To leave a comment for the author, please follow the link and comment on their blog: Educate-R - R.

R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

Never miss an update!
Subscribe to R-bloggers to receive
e-mails with the latest R posts.
(You will not see this message again.)

Click here to close (This popup will not appear again)