Introduction

Creating an analytic data set is very important when doing data analysis and will be used to reproduce the results.

An analytic data set should include the following:

This presentation will use the R programming language and assume the user is using Rstudio to compile their R markdown files into HTML (R Core Team 2019; RStudio Team 2016). If you want to follow along with the R markdown, it can be downloaded here. The associated bibliography file is here.

Libraries

The libraries knitr, bookdown, and kableExtra are used to generate the HTML output (Xie 2019; Xie 2018; Zhu 2019). The ggplot2 library is loaded for the example data set that is used (Wickham 2016). The Hmisc and DT libraries provide functions needed to make and display the analytic data set (Harrell Jr, Charles Dupont, and others. 2019; Xie, Cheng, and Tan 2018).

package_loader <- function(x, ...) {
  if (x %in% rownames(installed.packages()) == FALSE) install.packages(x)
  library(x, ...)
}

packages <- c("knitr", "bookdown", "kableExtra", "ggplot2", "Hmisc", "DT")

invisible(sapply(X = packages, FUN = package_loader, character.only = TRUE))

Example Data

The example data for this presentation will be mpg from the ggplot2 package. From the description in the manual:

This dataset contains a subset of the fuel economy data that the EPA makes available here. It contains only models which had a new release every year between 1999 and 2008 - this was used as a proxy for the popularity of the car.

data(mpg)
mpg <- data.frame(mpg)

kable(head(mpg), caption = "Header of <b>mpg</b>.", booktabs = TRUE, escape = FALSE) %>% kable_styling(bootstrap_options = c("striped", "hover", "condensed", "responsive"))
Table 1: Header of mpg.
manufacturer model displ year cyl trans drv cty hwy fl class
audi a4 1.8 1999 4 auto(l5) f 18 29 p compact
audi a4 1.8 1999 4 manual(m5) f 21 29 p compact
audi a4 2.0 2008 4 manual(m6) f 20 31 p compact
audi a4 2.0 2008 4 auto(av) f 21 30 p compact
audi a4 2.8 1999 6 auto(l5) f 16 26 p compact
audi a4 2.8 1999 6 manual(m5) f 18 26 p compact

Data Setup

There are two essential parts to setting up the data. First, make sure each variable is coded to reflect its class. Second, we want to add labels to each variable in the data set using the label function of the Hmisc library.

set.seed(123)
data(mpg)
mpg <- data.frame(mpg)

colnames(mpg)[which(colnames(mpg) == "manufacturer")] <- "manu"

mpg$manu <- factor(mpg$manu)
mpg$model <- factor(mpg$model)
mpg$displ <- as.numeric(mpg$displ)
mpg$year <- factor(mpg$year, levels = c("1999", "2008"), ordered = TRUE)

mpg$dp <- as.Date(NA, origin = "1970-01-01")
mpg$dp[which(mpg$year == "1999")] <- sample(seq(as.Date('1999-01-01', format = "%Y-%m-%d", origin = "1970-01-01"), as.Date('1999-12-25', format = "%Y-%m-%d", origin = "1970-01-01"), by="day"), dim(mpg)[1]/2)
mpg$dp[which(mpg$year == "2008")] <- sample(seq(as.Date('2008-01-01', format = "%Y-%m-%d", origin = "1970-01-01"), as.Date('2008-12-25', format = "%Y-%m-%d", origin = "1970-01-01"), by="day"), dim(mpg)[1]/2)

mpg$cyl <- factor(mpg$cyl, levels = c(4, 5, 6, 8), ordered = TRUE)
mpg$trans <- factor(mpg$trans)
mpg$drv <- factor(mpg$drv, levels = c("f", "r", "4"), labels = c("front-wheel drive", "rear wheel drive", "4wd"))
mpg$fl <- factor(mpg$fl)
mpg$class <- factor(mpg$class)

mpg$rn <- rnorm(dim(mpg)[1], mean = 10, sd = 5)
mpg$rn[sample(1:length(mpg$rn), size = 50)] <- NA

mpg$party <- factor(sample(c("republican", "democrat", "independent", NA), dim(mpg)[1], replace = TRUE), levels = c("republican", "democrat", "independent"))
mpg$comments <- sample(c("I like this car!", "Meh.", "This is the worst car ever!", "Does it come in green?", "want cheese flavoured cars.", "Does it also fly?", "Blah, Blah, Blah, Blah, Blah, Blah, Blah, Blah", NA), dim(mpg)[1], replace = TRUE)

label(mpg$manu) <- "manufacturer"
label(mpg$model) <- "model name"
label(mpg$displ) <- "engine displacement, in litres"
label(mpg$year) <- "year of manufacture"
label(mpg$dp) <- "date of purchase"
label(mpg$cyl) <- "number of cylinders"
label(mpg$trans) <- "type of transmission"
label(mpg$drv) <- "drive type"
label(mpg$cty) <- "city miles per gallon"
label(mpg$hwy) <- "highway miles per gallon"
label(mpg$fl) <- "fuel type"
label(mpg$class) <- "type of car"
label(mpg$rn) <- "some random numbers that are generated from a normal distrubtion with mean = 10 and sd = 5"
label(mpg$party) <- "some random political parties"
label(mpg$comments) <- "some random comments"

kable(head(mpg), caption = "Header of <b>mpg</b>.", booktabs = TRUE, escape = FALSE) %>% kable_styling(bootstrap_options = c("striped", "hover", "condensed", "responsive"))
Table 2: Header of mpg.
manu model displ year cyl trans drv cty hwy fl class dp rn party comments
audi a4 1.8 1999 4 auto(l5) front-wheel drive 18 29 p compact 1999-06-28 18.98570 democrat I like this car!
audi a4 1.8 1999 4 manual(m5) front-wheel drive 21 29 p compact 1999-01-14 NA NA I like this car!
audi a4 2.0 2008 4 manual(m6) front-wheel drive 20 31 p compact 2008-02-08 19.50450 independent Does it also fly?
audi a4 2.0 2008 4 auto(av) front-wheel drive 21 30 p compact 2008-07-14 NA independent I like this car!
audi a4 2.8 1999 6 auto(l5) front-wheel drive 16 26 p compact 1999-07-14 13.68097 democrat Meh.
audi a4 2.8 1999 6 manual(m5) front-wheel drive 18 26 p compact 1999-11-02 16.82888 NA Meh.

Data Dictionary

After the data is set up, the data dictionary can be printed as follows.

html(contents(mpg), 
     sortlevels = TRUE,
     maxlevels = Inf, 
     levelType = "table")

Data frame:mpg

234 observations and 15 variables, maximum # NAs:55  
NameLabelsLevelsClassStorageNAs
manumanufacturer15integer 0
modelmodel name38integer 0
displengine displacement, in litresnumericdouble 0
yearyear of manufacture 2orderedinteger 0
cylnumber of cylinders 4orderedinteger 0
transtype of transmission10integer 0
drvdrive type 3integer 0
ctycity miles per gallonintegerinteger 0
hwyhighway miles per gallonintegerinteger 0
flfuel type 5integer 0
classtype of car 7integer 0
dpdate of purchaseDatedouble 0
rnsome random numbers that are generated from a normal distrubtion with mean = 10 and sd = 5numericdouble50
partysome random political parties 3integer55
commentssome random commentscharactercharacter31

VariableLevels
manuaudi
chevrolet
dodge
ford
honda
hyundai
jeep
land rover
lincoln
mercury
nissan
pontiac
subaru
toyota
volkswagen
model4runner 4wd
a4
a4 quattro
a6 quattro
altima
c1500 suburban 2wd
camry
camry solara
caravan 2wd
civic
corolla
corvette
dakota pickup 4wd
durango 4wd
expedition 2wd
explorer 4wd
f150 pickup 4wd
forester awd
grand cherokee 4wd
grand prix
gti
impreza awd
jetta
k1500 tahoe 4wd
land cruiser wagon 4wd
malibu
maxima
mountaineer 4wd
mustang
navigator 2wd
new beetle
passat
pathfinder 4wd
ram 1500 pickup 4wd
range rover
sonata
tiburon
toyota tacoma 4wd
year1999
2008
cyl4
5
6
8
transauto(av)
auto(l3)
auto(l4)
auto(l5)
auto(l6)
auto(s4)
auto(s5)
auto(s6)
manual(m5)
manual(m6)
drvfront-wheel drive
rear wheel drive
4wd
flc
d
e
p
r
class2seater
compact
midsize
minivan
pickup
subcompact
suv
partyrepublican
democrat
independent

Analytic Data

This analytic data is created using the datatable function of the DT library. There are many options here, so I will only cover the high points. For more information check out the excellent documentation here.

The main things I want to show are the extensions declared by the extensions argument. The Buttons option creates buttons so that the end user can download the data set in various formats. The KeyTable allows users to select cells within the table and navigate similar to how they would in spreadsheet software. The FixedColumns allows us to create fixed columns that follow the cursor and scroll bars.

Another thing to keep in mind is to remove commas from the analytic data set if you want to allow users to export to a CSV format.

commas <- lapply(mpg, grep, pattern = ",")
commas <- names(which(unlist(lapply(commas, length)) > 0))
mpg[, commas] <- gsub(",", "_comma_", mpg[, commas], fixed = TRUE)

datatable(mpg, escape = FALSE,
          extensions = c('Buttons', 'KeyTable', 'FixedColumns'),
          class = 'cell-border stripe',
          rownames = TRUE,
          options = list(
            dom = 'Bfrtip',
            pageLength = 5,
            deferRender = TRUE,
            fixedColumns = list(leftColumns = 1),
            responsive = TRUE,
            scrollX = TRUE,
            scrollCollaspe = TRUE,
            paging = TRUE,
            autoWidth = TRUE,
            keys = TRUE,
            buttons = c('copy', 'csv', 'excel', 'pdf', 'print')
          ))

Session Info

sessionInfo()
## R version 3.6.1 (2019-07-05)
## Platform: x86_64-pc-linux-gnu (64-bit)
## Running under: Ubuntu 18.04.3 LTS
## 
## Matrix products: default
## BLAS:   /usr/lib/x86_64-linux-gnu/blas/libblas.so.3.7.1
## LAPACK: /usr/lib/x86_64-linux-gnu/lapack/liblapack.so.3.7.1
## 
## locale:
##  [1] LC_CTYPE=C.UTF-8       LC_NUMERIC=C           LC_TIME=C.UTF-8       
##  [4] LC_COLLATE=C.UTF-8     LC_MONETARY=C.UTF-8    LC_MESSAGES=C.UTF-8   
##  [7] LC_PAPER=C.UTF-8       LC_NAME=C              LC_ADDRESS=C          
## [10] LC_TELEPHONE=C         LC_MEASUREMENT=C.UTF-8 LC_IDENTIFICATION=C   
## 
## attached base packages:
## [1] stats     graphics  grDevices utils     datasets  methods   base     
## 
## other attached packages:
## [1] DT_0.5           Hmisc_4.2-0      Formula_1.2-3    survival_2.43-3 
## [5] lattice_0.20-38  ggplot2_3.1.1    kableExtra_1.1.0 bookdown_0.9    
## [9] knitr_1.22      
## 
## loaded via a namespace (and not attached):
##  [1] Rcpp_1.0.1          assertthat_0.2.1    digest_0.6.18      
##  [4] mime_0.6            R6_2.4.0            plyr_1.8.4         
##  [7] backports_1.1.4     acepack_1.4.1       evaluate_0.13      
## [10] httr_1.4.0          pillar_1.3.1        rlang_0.3.4        
## [13] lazyeval_0.2.2      rstudioapi_0.10     data.table_1.12.2  
## [16] rpart_4.1-15        Matrix_1.2-17       checkmate_1.9.1    
## [19] rmarkdown_1.12      splines_3.6.1       webshot_0.5.1      
## [22] readr_1.3.1         stringr_1.4.0       foreign_0.8-72     
## [25] htmlwidgets_1.3     munsell_0.5.0       shiny_1.3.2        
## [28] compiler_3.6.1      httpuv_1.5.1        xfun_0.6           
## [31] pkgconfig_2.0.2     base64enc_0.1-3     htmltools_0.3.6    
## [34] nnet_7.3-12         tidyselect_0.2.5    tibble_2.1.1       
## [37] gridExtra_2.3       htmlTable_1.13.1    viridisLite_0.3.0  
## [40] crayon_1.3.4        dplyr_0.8.0.1       withr_2.1.2        
## [43] later_0.8.0         grid_3.6.1          jsonlite_1.6       
## [46] xtable_1.8-4        gtable_0.3.0        magrittr_1.5       
## [49] scales_1.0.0        stringi_1.4.3       promises_1.0.1     
## [52] latticeExtra_0.6-28 xml2_1.2.0          RColorBrewer_1.1-2 
## [55] tools_3.6.1         glue_1.3.1          purrr_0.3.2        
## [58] hms_0.4.2           crosstalk_1.0.0     yaml_2.2.0         
## [61] colorspace_1.4-1    cluster_2.1.0       rvest_0.3.3

References

Harrell Jr, Frank E, with contributions from Charles Dupont, and many others. 2019. Hmisc: Harrell Miscellaneous. https://CRAN.R-project.org/package=Hmisc.

R Core Team. 2019. R: A Language and Environment for Statistical Computing. Vienna, Austria: R Foundation for Statistical Computing. https://www.R-project.org/.

RStudio Team. 2016. RStudio: Integrated Development Environment for R. Boston, MA: RStudio, Inc. http://www.rstudio.com/.

Wickham, Hadley. 2016. Ggplot2: Elegant Graphics for Data Analysis. Springer-Verlag New York. http://ggplot2.org.

Xie, Yihui. 2018. Bookdown: Authoring Books and Technical Documents with R Markdown. https://github.com/rstudio/bookdown.

———. 2019. Knitr: A General-Purpose Package for Dynamic Report Generation in R. https://yihui.name/knitr/.

Xie, Yihui, Joe Cheng, and Xianying Tan. 2018. DT: A Wrapper of the Javascript Library ’Datatables’. https://CRAN.R-project.org/package=DT.

Zhu, Hao. 2019. KableExtra: Construct Complex Table with ’Kable’ and Pipe Syntax. https://CRAN.R-project.org/package=kableExtra.