Maria Guideng
Looking for a way to generate a formatted table in R? No? Well now you can!
R is not that great for final product data visualization, but if there was a simple way to give my plain tables a little love, I figured, 'Why not?' So I asked The Google, and it brought me to this article by Hao Zhu, creator of the kableExtra R package. The features to manipulate table styles seemed straightforward enough for a noob like me with no experience programming in CSS, so I decided to give it a run. The lucky table chosen for the makeover was from tabular data to calculate location quotients (LQs) in Las Vegas. The LQs quantified how concentrated a particular industry is in a region as compared to the nation. Specifically, I wanted to highlight the obvious about my table: gaming and hospitality drives the Vegas economy. It took a few iterations, but I figured out how to style everything the way I wanted (after augmentation with the formattable package). Check out the before and after tables below.
This is the basic HTML table in kable:
kable(lqtab, "html")| NAICS | Industry | US Employment | US % Share | Las Vegas Employment | Las Vegas % Share | Employment for Las Vegas Requirements | Las Vegas LQ | Las Vegas LQ Classification | Las Vegas Excess Employment (to export) or Deficit |
|---|---|---|---|---|---|---|---|---|---|
| 11 | Forestry | 1,259,490 | 1.05% | 513 | 0.06% | 8,771 | 0.06 | -8,258 | |
| 21 | Mining | 613,389 | 0.51% | 331 | 0.04% | 4,272 | 0.08 | -3,941 | |
| 22 | Utilities | 553,007 | 0.46% | 2,646 | 0.32% | 3,851 | 0.69 | -1,205 | |
| 23 | Construction | 6,686,142 | 5.55% | 54,652 | 6.51% | 46,561 | 1.17 | 8,091 | |
| 31-33 | Manufacturing | 12,296,697 | 10.2% | 22,011 | 2.62% | 85,632 | 0.26 | -63,621 | |
| 42 | Wholesale | 5,859,605 | 4.86% | 21,544 | 2.57% | 40,805 | 0.53 | -19,261 | |
| 44-45 | Retail | 15,824,396 | 13.13% | 107,103 | 12.76% | 110,198 | 0.97 | -3,095 | |
| 48-49 | Trans/Ware | 4,765,869 | 3.95% | 38,558 | 4.59% | 33,189 | 1.16 | 5,369 | |
| 51 | Information | 2,796,947 | 2.32% | 11,428 | 1.36% | 19,477 | 0.59 | -8,049 | |
| 52 | Finance | 5,826,386 | 4.83% | 25,588 | 3.05% | 40,574 | 0.63 | -14,986 | |
| 53 | RE | 2,127,375 | 1.77% | 20,450 | 2.44% | 14,815 | 1.38 | Export | 5,635 |
| 54 | Professional | 8,840,443 | 7.34% | 39,176 | 4.67% | 61,563 | 0.64 | -22,387 | |
| 55 | Management | 2,230,131 | 1.85% | 19,601 | 2.34% | 15,530 | 1.26 | Export | 4,071 |
| 56 | Admin | 8,954,343 | 7.43% | 74,822 | 8.92% | 62,356 | 1.20 | Export | 12,466 |
| 61 | Educational | 2,766,964 | 2.3% | 8,744 | 1.04% | 19,269 | 0.45 | -10,525 | |
| 62 | Health | 18,887,301 | 15.67% | 81,382 | 9.7% | 131,528 | 0.62 | -50,146 | |
| 71 | Arts | 2,237,922 | 1.86% | 20,540 | 2.45% | 15,584 | 1.32 | Export | 4,956 |
| 72 | Accommodation | 13,318,703 | 11.05% | 265,110 | 31.59% | 92,749 | 2.86 | Export | 172,361 |
| 81 | Other | 4,387,613 | 3.64% | 24,149 | 2.88% | 30,555 | 0.79 | -6,406 | |
| 99 | Industries not classified | 271,898 | 0.23% | 825 | 0.1% | 1,893 | 0.44 | -1,068 | |
| Total | 120,504,621 | 100% | 839,173 | 100% | 839,172 | NA | NA |
It's a nice bundle of functions. It's one that is easy to use and easy to understand; thus, it should be popular for those looking to keep the simplicity of the Bootstrap table style while adding flexible customization. It's better suited for model-output documents and statistical result-type tables, rather than for fully leveraging on the power of visualization to help interpret the data.
Package description
"Build complex HTML or 'LaTeX' tables using 'kable()' from 'knitr' and the piping syntax from 'magrittr'. Function 'kable()' is a light weight table generator coming from 'knitr'. This package simplifies the way to manipulate the HTML or 'LaTeX' codes generated by 'kable()' and allows users to construct complex tables and customize styles using a readable syntax."
Process
The source of the industry data comes from the Bureau of Labor Statistics QCEW program. The two CSV input files are zipped up and located in the /data/allfiles subfolder. One is Las Vegas data and the other is national data. This mini-project is for 2016 annual averages only, but other years are available (/data/allfilesMASTER).
Files in the /script subfolder serve different purposes.
- (1script-merge-allfiles.R): Merges the two CSV input files for the chosen year into a single dataframe.
- (2script-lq-model.R): Calculates and models the LQs into a table layout.
- (3script-lq-table.Rmd): Runs the
knitr::kableandkableExtrafunctions to format the table. This is also the output since it's intended for conversions from R Markdown to Markdown and HTML.
