<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/make-beautiful-tables-with-the-formattable-package/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://www.displayr.com/formattable/?utm_medium=Feed&utm_source=Syndication"> R-Bloggers – Displayr</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>I love the <span>formattable</span> package, but I always struggle to remember its syntax. A quick Google search reveals that I’m not alone in this struggle. This post is intended as a reminder for myself of how the package works – and hopefully you’ll find it useful too!</p>
<h2>Default formattable example</h2>
<p>The table below is an R <em>data frame</em> (you can turn most things into a data frame using <span>as.data.frame(x)</span>, where <span>x</span> is whatever you are converting). It’s by no means as bad as most R tables, but clearly it is not good enough to be shared with others.</p>
<p> </p>
<p><img sizes="(max-width: 674px) 100vw, 674px" src="https://i2.wp.com/www.displayr.com/wp-content/uploads/2018/10/RTextTable-1.png?w=450&ssl=1" alt="" class="wp-image-13054" data-recalc-dims="1"></p>
<p>If we give this table (called <span>prevalence)</span> to <span>formattable,</span> it does a great job just using defaults.</p>
<pre> 
library(formattable)
formattable(prevalence)
</pre>
<p><img sizes="(max-width: 848px) 100vw, 848px" src="https://i0.wp.com/www.displayr.com/wp-content/uploads/2018/10/FormattableDefaults.png?w=450&ssl=1" alt="" class="wp-image-13055" data-recalc-dims="1"></p>
<h2>Column alignment</h2>
<p>We can control column alignment using the <span>align</span> parameter. In the example below, I set the first column to left-aligned, and the remaining columns are right-aligned.</p>
<pre> 
formattable(prevalence, align = c(&quot;l&quot;, rep(&quot;r&quot;, NCOL(prevalence) - 1)))
</pre>
<p><img sizes="(max-width: 855px) 100vw, 855px" src="https://i1.wp.com/www.displayr.com/wp-content/uploads/2018/10/ColumnAlignmnent.png?w=450&ssl=1" alt="" class="wp-image-13057" data-recalc-dims="1"></p>
<h2>Simple column formatting</h2>
<p>The main reason people love <span>formattable</span> is the formatting of columns. Below, the first column has been changed to grey, color bars have been added to Average, and the last column has been formatted as percentages. Note that the variable names are surrounded by <em>backticks</em> (the key above your Tab on English-language keyboards), not single quotation marks.</p>
<pre> 
prevalence[, &quot;Improvement&quot;] = prevalence[, &quot;Improvement&quot;] / 100
formattable(prevalence, 
            align = c(&quot;l&quot;,rep(&quot;r&quot;, NCOL(prevalence) - 1)),
            list(`Indicator Name` = formatter(&quot;span&quot;, style = ~ style(color = &quot;grey&quot;, font.weight = &quot;bold&quot;)), 
                 `Average` = color_bar(&quot;#FA614B&quot;), 
                 `Improvement` = percent))

</pre>
<p><img sizes="(max-width: 857px) 100vw, 857px" src="https://i0.wp.com/www.displayr.com/wp-content/uploads/2018/10/FormattingColumns-1.png?w=450&ssl=1" alt="" class="wp-image-13061" data-recalc-dims="1"></p>
<h2>More customized column formatting</h2>
<p>In the example above, prior to using <span>formattable</span> I divided the last column by 100, as <span>formattable</span>‘s percent function assumes the inputs are decimals. However, we can perform transformations within <span>formattable</span>. In the code below, I divide by 100 and I also color the values as red or green depending on their value. Note that in the bottom two lines, we define <span>x</span> as being the value by placing it to the left of the ~ and then use it in the function to the right (it is a <em>lambda </em>function, to use some jargon).</p>
<pre> 
formattable(prevalence, 
            align = c(&quot;l&quot;,rep(&quot;r&quot;, NCOL(prevalence) - 1)),
            list(`Indicator Name` = formatter(&quot;span&quot;, style = ~ style(color = &quot;grey&quot;, font.weight = &quot;bold&quot;)), 
                 `Average` = color_bar(&quot;#FA614B&quot;), 
                 `Improvement` = formatter(&quot;span&quot;, 
                                           x ~ percent(x / 100),
                                           style = x ~ style(color = ifelse(x&amp;amp;amp;amp;lt; 0, &quot;red&quot;, &quot;green&quot;)))))
</pre>
<p><img sizes="(max-width: 884px) 100vw, 884px" src="https://i0.wp.com/www.displayr.com/wp-content/uploads/2018/10/FormattingColumn.png?w=450&ssl=1" alt="" class="wp-image-13064" data-recalc-dims="1"></p>
<p>Below I extend this even further, replacing the percentages with ticks, crosses, and words.</p>
<pre> 
formattable(prevalence, 
            align = c(&quot;l&quot;,rep(&quot;r&quot;, NCOL(prevalence) - 1)),
            list(`Indicator Name` = formatter(&quot;span&quot;, style = ~ style(color = &quot;grey&quot;, font.weight = &quot;bold&quot;)), 
                 `Average` = color_bar(&quot;#FA614B&quot;), 
                 `Improvement` = formatter(&quot;span&quot;, 
                                           x ~ icontext(ifelse(x &lt; 0, &quot;ok&quot;, &quot;remove&quot;), ifelse(&lt; 0, &quot;Yes&quot;, &quot;No&quot;)), 
                                           style = x ~ style(color = ifelse(x &amp;amp;amp;amp;lt; 0, &quot;red&quot;, &quot;green&quot;)))))
</pre>
<p><img sizes="(max-width: 868px) 100vw, 868px" src="https://i1.wp.com/www.displayr.com/wp-content/uploads/2018/10/FormattingColumnTicks-1.png?w=450&ssl=1" alt="" class="wp-image-13068" data-recalc-dims="1"></p>
<h2>Controlling the width of the bars</h2>
<p>In the table below I have used the standard color bar, which scales the bars so that the bar lengths are proportional to the values being displayed. However, IQ cannot really be 0, so arguably the bars are misleading.</p>
<p><img sizes="(max-width: 136px) 100vw, 136px" src="https://i0.wp.com/www.displayr.com/wp-content/uploads/2018/11/WidthColorBar1.png?resize=136%2C136&ssl=1" alt="" width="136" class="wp-image-13260" data-recalc-dims="1" height="136"></p>
<p>The fix to this problem is to provide a function that has a more appropriate mapping between the values and the length of the bars. In the code below, I create a function that returns a 0 for the lowest value (70), and a 1 for the highest value (150).</p>
<pre> 
unit.scale = function(x) (x - min(x)) / (max(x) - min(x))
formattable(iq.data, 
            align = c(&quot;l&quot;,&quot;r&quot;),
            list(`IQ` = color_bar(&quot;#FA614B66&quot;, fun = unit.scale)))
</pre>
<p><img sizes="(max-width: 159px) 100vw, 159px" src="https://i2.wp.com/www.displayr.com/wp-content/uploads/2018/11/WidthColorBar2.png?resize=159%2C152&ssl=1" alt="" width="159" class="wp-image-13261" data-recalc-dims="1" height="152"></p>
<h2>Formatting areas (ranges of cells)</h2>
<p>It is possible to also set the shading of ranges of cells,  rather than just individual columns. In the example below, I’ve created a heatmap using two shades of green. In this case I have specified the area using just the columns, but <span>row</span> can also be supplied as well as or in place of <span>col</span>.</p>
<pre> 
formattable(prevalence, align = c(&quot;l&quot;,rep(&quot;r&quot;, NCOL(prevalence) - 1)), list(
    `Indicator Name` = formatter(&quot;span&quot;, style = ~ style(color = &quot;grey&quot;,font.weight = &quot;bold&quot;)), 
     area(col = 2:7) ~ color_tile(&quot;#DeF7E9&quot;, &quot;#71CA97&quot;)))
</pre>
<p><img sizes="(max-width: 919px) 100vw, 919px" src="https://i1.wp.com/www.displayr.com/wp-content/uploads/2018/10/FormattingRanges.png?w=450&ssl=1" alt="" class="wp-image-13074" data-recalc-dims="1"></p>
<p>In this next example, I first format all the cells to be percentages, and then apply the color shading to the year columns. I have to wrap <span>percent</span> in another function, as <span>percent</span> only works on a single column of numbers.</p>
<pre> 
library(formattable)
formattable(prevalence, align = c(&quot;l&quot;,rep(&quot;r&quot;, NCOL(prevalence) - 1)), 
            list(`Indicator Name` = formatter(&quot;span&quot;, style = ~ style(color = &quot;grey&quot;,font.weight = &quot;bold&quot;)), 
                 area(col = 2:7) ~ function(x) percent(x / 100, digits = 0),
                 area(col = 2:7) ~ color_tile(&quot;#DeF7E9&quot;, &quot;#71CA97&quot;)))
</pre>
<p><img sizes="(max-width: 922px) 100vw, 922px" src="https://i0.wp.com/www.displayr.com/wp-content/uploads/2018/10/FormattingRanges1.png?w=450&ssl=1" alt="" class="wp-image-13076" data-recalc-dims="1"></p>
<h2>Custom formatters</h2>
<p>You can also write your own functions for controlling formatting. In the example below, rather than use <span>formattable</span>‘s in-built <span>color_tile</span> (as done in the previous example), I’ve instead customized it, controlling the padding, border radios, and font color.</p>
<pre> 
custom_color_tile &amp;amp;amp;amp;amp;amp;amp;amp;lt;- function (...) 
{
    formatter(&quot;span&quot;,
              style = function(x) style(display = &quot;block&quot;, 
                                        padding = &quot;0 4px&quot;, 
                                        `color` = &quot;white&quot;, 
                                        `border-radius` = &quot;4px&quot;, 
                                        `background-color` = csscolor(gradient(as.numeric(x), 
                                        ...))))
}
formattable(prevalence, align = &quot;r&quot;, list(
  `Indicator Name` = formatter(&quot;span&quot;, style = ~ style(color = &quot;grey&quot;,font.weight = &quot;bold&quot;)), 
                     area(col = 2:9) ~ function(x) percent(x / 100, digits = 0),
                     area(col = 2:7) ~ custom_color_tile(&quot;#B1CBEB&quot;, &quot;#3E7DCC&quot;)))
</pre>
<h2><img sizes="(max-width: 809px) 100vw, 809px" src="https://i1.wp.com/www.displayr.com/wp-content/uploads/2018/11/customtiles.png?w=450&ssl=1" alt="" class="wp-image-13258" data-recalc-dims="1"></h2>
<h2>Arrows (and hiding columns)</h2>
<p>Sometimes it is useful to use arrows to show statistical significance. While <span>formattable</span> is not super flexible in this regard, it can do a good job nonetheless. The first step is to create a table where in addition to the data to be displayed, we also have a column containing <em>z-</em>scores.</p>
<p><img sizes="(max-width: 470px) 100vw, 470px" src="https://i0.wp.com/www.displayr.com/wp-content/uploads/2018/11/SigData-1.png?w=450&ssl=1" alt="" class="wp-image-13264" data-recalc-dims="1"></p>
<p>In the code below I first hide the column called z (<span>z = FALSE</span>), add arrows for z-scores of less than -1.96 and greater than 1.96, and make z scores of greater than 0 green and less than 0 red.</p>
<pre> 
formattable(prev.sig,
            list(z = FALSE,
                 `2016` = formatter(&quot;span&quot;, 
                 style = ~ style(color = ifelse(`2016` > `2015`, &quot;green&quot;, &quot;red&quot;)),                                    
                 ~ icontext(sapply(`z`, function(x) if (x &lt; -1.96) &quot;arrow-down&quot; else if (x > 1.96) &quot;arrow-up&quot; else &quot;&quot;), `2016`))))
</pre>
<p><img sizes="(max-width: 509px) 100vw, 509px" src="https://i0.wp.com/www.displayr.com/wp-content/uploads/2018/11/arrows.png?w=450&ssl=1" alt="" class="wp-image-13263" data-recalc-dims="1"></p>
<p>A problem with this table is that the arrows are to the left of the numbers and are not lined up neatly. The only way I have figured out to avoid this is to put the arrows in a separate column, as shown here:</p>
<p><img sizes="(max-width: 461px) 100vw, 461px" src="https://i2.wp.com/www.displayr.com/wp-content/uploads/2018/11/arrows1.png?w=450&ssl=1" alt="" class="wp-image-13265" data-recalc-dims="1"></p>
<p>This is done by:</p>
<ul>
<li>Replacing <span>z</span> with an invisible character (<span>&amp;nbsp</span>).</li>
<li>Replacing the values with the arrows.</li>
</ul>
<pre> 
names(prev.sig)[4] = &quot;&amp;nbsp&quot;
formattable(prev.sig,
            list( &quot;&amp;nbsp&quot; = formatter(&quot;span&quot;, 
                 style = ~ style(color = ifelse(`2016` > `2015`, &quot;green&quot;, &quot;red&quot;)),                                    
                  ~ icontext(sapply(`&amp;nbsp`, function(x) if (x &lt; -1.96) &quot;arrow-down&quot; else if (x > 1.96) &quot;arrow-up&quot; else &quot;&quot;)))))
</pre>
<p> </p>
<h2>Adding sparklines to tables</h2>
<p>The <span>sparklines</span> package can be used to create <em>sparklines</em>: <img src="https://i0.wp.com/www.displayr.com/wp-content/uploads/2018/11/sparkline.png?resize=43%2C34&ssl=1" alt="" width="43" class="wp-image-13266" data-recalc-dims="1" height="34"></p>
<pre> 
library(sparkline)
sparkline(c(1,2,7,6,5), type = &quot;bar&quot;, barColor = &quot;green&quot;)
</pre>
<p>We can also include them in <span>formattable</span> tables.<br>
<img sizes="(max-width: 208px) 100vw, 208px" src="https://i0.wp.com/www.displayr.com/wp-content/uploads/2018/11/sparklinetable.png?resize=208%2C251&ssl=1" alt="" width="208" class="wp-image-13267" data-recalc-dims="1" height="251"></p>
<p>The way that we do this is by converting the sparkline into text (<span>character(htmltools::as.tags</span>),  and then (in the last two lines), telling the <span>formattable</span> HTML widget that it also contains sparklines.</p>
<pre> 
library(sparkline)
library(formattable)
df = data.frame(&quot;Type&quot; = c(&quot;bar&quot;, &quot;line&quot;, &quot;bullet&quot;, &quot;pie&quot;, &quot;tristate&quot;, &quot;discrete&quot;),
               Sparkline = c(as.character(htmltools::as.tags(sparkline(c(1,2,7,6,5), type = &quot;bar&quot;))), 
                             as.character(htmltools::as.tags(sparkline(c(1,2,7,6,5), type = &quot;line&quot;))), 
                             as.character(htmltools::as.tags(sparkline(c(1,2,7,6,5), type = &quot;bullet&quot;))), 
                             as.character(htmltools::as.tags(sparkline(c(1,2,7,6,5), type = &quot;pie&quot;))), 
                             as.character(htmltools::as.tags(sparkline(c(-1,0,1,1,1,-1,0,2), type = &quot;tristate&quot;))), 
                             as.character(htmltools::as.tags(sparkline(c(1,2,7,6,5), type = &quot;discrete&quot;)))))
out = as.htmlwidget(formattable(df))
out$dependencies = c(out$dependencies, htmlwidgets:::widget_dependencies(&quot;sparkline&quot;, &quot;sparkline&quot;))
out
</pre>
<h2>Putting it all together</h2>
<p>In this final example, I combine many of the different ideas I’ve discussed into one table.</p>
<p><img sizes="(max-width: 542px) 100vw, 542px" src="https://i2.wp.com/www.displayr.com/wp-content/uploads/2018/11/finalhtmlwidgettable.png?w=450&ssl=1" alt="" class="wp-image-13268" data-recalc-dims="1"></p>
<pre> 
library(formattable)
library(sparkline)
prevalence$`&amp;nbsp` = c(4.1, -.3, .5, 1.4)
prevalence$`2012` = apply(prevalence[, 2:7], 1, FUN = function(x) as.character(htmltools::as.tags(sparkline(as.numeric(x), type = &quot;line&quot;))))
names(prevalence)[3] = &quot;&amp;nbsp&amp;nbsp&quot;
new.prevalance = prevalence[, c(1, 2, 3, 7, 10)]                          
out = as.htmlwidget(formattable(new.prevalance,
            align = c(&quot;l&quot;,rep(&quot;r&quot;, NCOL(prevalence) - 1)), 
            list(`Indicator Name` = formatter(&quot;span&quot;, style = ~ style(color = &quot;grey&quot;, font.weight = &quot;bold&quot;)),
                 &quot;&amp;nbsp&quot; = formatter(&quot;span&quot;, 
                      style = ~ style(color = ifelse(`2016` > `2011`, &quot;green&quot;, &quot;red&quot;)),                                    
                       ~ icontext(sapply(`&amp;nbsp`, function(x) if (x &lt; -1.96) &quot;arrow-down&quot; else if (x> 1.96) &quot;arrow-up&quot; else &quot;&quot;))))))                          
out$dependencies = c(out$dependencies, htmlwidgets:::widget_dependencies(&quot;sparkline&quot;, &quot;sparkline&quot;))
out
</pre>
<h2>View all the source code and play around with these examples yourself</h2>
<p>I’ve created all the examples in this post in a live Displayr document, so you can look at the code and play around with it yourself. <a rel="nofollow" href="https://app.displayr.com/Try/Formattable" target="_blank">Click here</a> to view the code and tables discussed in this post.</p>
<h2>Acknowledgements</h2>
<p>The main example and many of the ideas in this post are from <a rel="nofollow" href="https://www.littlemissdata.com/blog/prettytables" target="_blank">LITTLE MISS DATA</a>, although I’ve reworked the code quite significantly. The <a rel="nofollow" href="https://github.com/renkun-ken/formattable/issues/83" target="_blank">hack for getting sparklines into the tables</a> comes from HTML widget guru <a rel="nofollow" href="https://github.com/timelyportfolio" target="_blank">Kent Russell</a>. <a rel="nofollow" href="https://github.com/BertWassink" target="_blank">Bert Wassink</a> provided the <a rel="nofollow" href="https://github.com/renkun-ken/formattable/issues/90" target="_blank">trick for having a blank column name</a>. My colleague Justin helped me a lot with this post.</p>
<p><strong>View and modify the code in all these examples <a rel="nofollow" href="https://app.displayr.com/Try/Formattable" target="_blank">here</a>!</strong></p>
	<p>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="https://www.displayr.com/formattable/?utm_medium=Feed&utm_source=Syndication"> R-Bloggers – Displayr</a></strong>.</div>
<hr>
<a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="https://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a rel="nofollow" href="https://www.r-project.org/" title="The R Project for Statistical Computing">R</a> news and <a rel="nofollow" href="https://www.r-bloggers.com/search/tutorial" title="R tutorials">tutorials</a> on topics such as: <a rel="nofollow" href="https://www.r-bloggers.com/search/data%20science" title="Data science">Data science</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data" title="Big Data">Big Data, <a rel="nofollow" href="https://www.r-users.com/" title="R jobs">R jobs</a>, visualization (<a rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2" title="ggplot and ggplot2 tutorials">ggplot2</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/boxplot" title="Boxplots using lattice and ggplot2 tutorials">Boxplots</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/map" title="Maps and gis">maps</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/animation" title="Animation in R">animation</a>), programming (<a rel="nofollow" href="https://www.r-bloggers.com/search/RStudio" title="RStudio IDE for R">RStudio</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/sweave" title="Sweave and literate programming">Sweave</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX" title="LaTeX in R">LaTeX</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/SQL" title="SQL and databases">SQL</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/eclipse" title="Eclipse IDE for R">Eclipse</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/git" title="git and github, Version Control System">git</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/hadoop" title="Large data in R using Hadoop">hadoop</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping" title="Web Scraping of google, facebook, yahoo, twitter and more using R">Web Scraping</a>) statistics (<a rel="nofollow" href="https://www.r-bloggers.com/search/regression" title="Regressions and ANOVA analysis tutorials">regression</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/PCA" title="principal component analysis tutorial">PCA</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/time+series" title="Time series">time series</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/trading" title="finance trading">trading</a>) and more...
<img width="1" alt="" src="http://feeds.feedburner.com/~r/RBloggers/~4/SZDqWpMXwrY" height="1"></a></div>