Anyone wanting to produce professional looking output with R has probably used knitr and xtable to some extent, or at least read about it. knitr enables R to integrate into LaTeX, HTML, Markdown, etc. With relative ease xtable generates LaTeX tables from an R data.table; however, if you find yourself wanting, or needing, a more sophisticated looking table, then some LaTeX markup will have to be manually applied (which we will do to rotate our header columns). LaTeX is a markup language for documents, the word being short for Lamport TeX, and it enables us to advance the look-and-feel of our generated documents. There could be, and may end up being, dozens of other entries regarding practical uses for LaTeX in regards to R output generation, but for now lets just focus on improving the header columns in the Product table below.

Simple xtable

I put together a simple spreadsheet of products that could be sold in most hardware stores on any given day. A simple table containing product name, units sold, unit price and a calculate field for total sales.

There is not much code required to generate the table displayed above. I loaded the data from Excel into a data.table. In order to display some values as dollars in the table I decided to convert them to, well, dollars. A dollar function is available in the scales package. The scales package has a number of methods for graphic label formatting and visualization. It would also have been acceptable to just concatenate ‘\$’ to my original character values; ‘$’ is a special LaTeX character so the escape sequence is required. As I used the dollar function in this example and the values have to be numeric before converting to dollars, I then used the colClasses parameter of read.xlsx to specifically identify my numeric columns. Otherwise they would default to character (stringsAsFactors=FALSE).

<<echo=FALSE, warning=FALSE>>=library(knitr)

require(xtable, quietly = TRUE)
require(xlsx, quietly = TRUE)
require(data.table, quietly = TRUE)
require(scales, quietly = TRUE)

dtProductData <- data.table(read.xlsx("ProductData.xlsx", sheetIndex = 1, stringsAsFactors=FALSE, colClasses=c("character","numeric","numeric","numeric")))

productTable <- xtable(dtProductData[,.("Product Name"=Product,"Units Sold"=dollar(Units.Sold),"Unit Price"=dollar(Unit.Price),"Total Sales" =dollar(Total.Sales))], caption ="Product Sales")

@

The xtable is given the data.table generated from my Excel, and I set my table caption: “Product Sales”. Calling print invokes the xtable print, which generated the LaTeX markup. The print statement does not have to be located in a separate chunk, I did this to separate my code for demonstration purposes.

<<echo=FALSE,results='asis'>>=
print(productTable)
@

Great, with xtable we can easily generate a table to display our data; however, often in business we have graphic designers and marketing specifying how a publication is going to look, especially one distributed outside of the company. These people are only constrained by their imagination, and sometime legal with regards to logos. These are the projects that require additional formatting luster and the assistance of LaTeX markup.

Rotate Column Headers

I am not a graphic designer, more of an experimenter. My Table 2 below will not impress visually, but the how might be of some interest.

First, we need to bring in some additional packages after the document class tag. We are going to be creating new color definitions (xcolor), making cells to rotate the column headers (makecell), and then finally adding horizontal lines after each row (booktabs).

\documentclass{article}
\usepackage{booktabs}
\usepackage{xcolor}
\usepackage{makecell}

The define color tag is a nice way to include reusable custom colors in your document. I used Gimp to find the desired blue and green I wanted and the associated Hex for HTML. There are many paint programs that will display the HTML equivalent of colors, I just happen to like Gimp.

With newcommand I am defining a new command called ‘rhead’, which takes two parameters. Parameter one (#1) is the rotation that is sent into the rotatebox function. Parameter two (#2) is a concatenation of LaTeX markup with my column header names, code displayed in the next knitr chunk. With newcommand the command name cannot exist, if it does, then use renewcommand.

The commented-out line of newcommand illustrates another way to pass parameters to the same command.

\definecolor{DrkBlue}{HTML}{2218e5}
\definecolor{LtGreen}{HTML}{18e535}
\newcommand{\rhead}[2][75]{\makebox[2cm][c]{\rotatebox{#1}{#2}}}%
%\newcommand{\rhead}[1]{\makebox[2cm][c]{\rotatebox{75}{#1}}}%

We again create our xtable, productTable. This first line of code is the same as from above. Because we will not be allowing xtable to print the default column headers, we will need to define our own headers. The variable colHeaderText combines our column names into left aligned cells (makecell[l]) with the text colors we defined earlier (textcolor). The double backslash ‘\\’ inserts a line break between words in our column headers; use escape characters to avoid receiving a failure – ‘\\’ becomes ‘\\\\’ and ‘\’ becomes ‘\\’.

The colmns variable is the result of the command we created earlier, rhead, which if you remember inserts each column header markup text into a 2mm makebox rotated at 75 degrees. Appending ‘\\\\’ completes the column header row. I then align my columns using the align function (align(productTable) <- “llcc|c” ). I have 4 columns plus the row name column that I do not show, thus my visible columns align: left, center, center, (vertical line), center.

Making it print correctly requires a few changes to the print function. First, I don’t want the default column names generated (include.colnames = FALSE). Next I need to incorporate add.to.row in-order to add my column header as the very first row. add.to.row expects a list of positions and commands. To get my header columns to print I need to specify pos ‘0’ (first row) and then pass my column header LaTeX markup to the command.

<<echo=FALSE,results='asis',warning=FALSE>>=productTable <- xtable(dtProductData[,.("Product Name"=Product,"Units Sold"=dollar(Units.Sold),"Unit Price"=dollar(Unit.Price),"Total Sales" =dollar(Total.Sales))], caption ="Product Sales")

colHeaderText <- c(paste0("\\textcolor{DrkBlue}{\\makecell[l]{", c("Product","Units \\\\ Sold","Unit \\\\ Price"),"}}"),"\\textcolor{LtGreen}{\\makecell[l]{Total \\\\ Sales}}")

colmns <- paste0("\\multicolumn{1}{c}{\\rhead{", colHeaderText, "}}", collapse = " & ")
colmns <- paste0( colmns, "\\\\")
align(productTable) <- "llcc|c"
print(productTable,caption.placement="bottom",include.rownames=FALSE, include.colnames = FALSE, hline.after=NULL,
add.to.row = list(pos = as.list(0), command=c(colmns)))
@

Adding Lines Between Rows

I felt additional updates were in order to render the table more readable. Inserting lines between the rows help to make each row stand out from the other. Plus, this allows me to expand on using the add.to.row print parameter.

A small amount of code is required to add the lines to the rows. The package booktabs gives us cmidrule, which I will use to draw a line under each row. Variable rowLines contains a character vector of cmidrule tags set to the column length of my table {1-4}. The vector size is equal to the number of rows in the table ( nrow(productTable)). Now I add every position from 0 to the row count, to the add.to.row print parameter. Each position must have a matching command, hence command = command=c(colmns,rowLines ). If one of these values if off, i.e. column length, number of positions passed in and number of command passed in, then a failure will occur.

<<echo=FALSE,results='asis',warning=FALSE>>=productTable <- xtable(dtProductData[,.("Product Name"=Product,"Units Sold"=dollar(Units.Sold),"Unit Price"=dollar(Unit.Price),"Total Sales" =dollar(Total.Sales))], caption ="Product Sales")
colHeaderText <- c(paste0("\\textcolor{DrkBlue}{\\makecell[l]{", c("Product","Units \\\\ Sold","Unit \\\\ Price"),"}}"),"\\textcolor{LtGreen}{\\makecell[l]{Total \\\\ Sales}}")
rowLines <- rep("\\cmidrule{1-4}", (nrow(productTable)))colmns <- paste0("\\multicolumn{1}{c}{\\rhead{", colHeaderText, "}}", collapse = " & ")
colmns <- paste0( colmns, "\\\\")
align(productTable) <- "llcc|c"
print(productTable,caption.placement="bottom",include.rownames=FALSE, include.colnames = FALSE,
add.to.row = list(pos = as.list(c(0:(nrow(productTable)))), command=c(colmns,rowLines )), hline.after=NULL)@

I have had to produce some interesting documents from R on past projects. Manipulating xtable to meet project requirements was one of my earliest stumbling blocks. Everything else since then has seemed rather smooth in comparison. These are only simple examples to get started. Once a comfort is developed with R reporting, there are some really cool things that can be done.

I hope this helps anyone struggling like I once was.

Categories: R Code

Tags: knitr, LaTeX, R, reporting, table, xtable