Fancifying Excel tables in R Markdown with kable_extra
I’m a big fan of automating all the things. Especially when it comes to teaching materials. I build my own course websites for each class I teach, where I post lecture materials, due dates and links to external resources. Posting it online instead of my campus’s LMS makes the materials accessible to not only my current students, but past and future students as well.
I need a schedule, so do my students. I’m not good at winging things. I want to know what’s coming up this week, and next so I can plan my time accordingly. Again, so do my students. The easiest way for me to manage a schedule of topics, assignments, exams, projects for an entire 16 week class (or more) is in Excel, but that’s far from web-ready.
This is a record of how I turned an Excel sheet into a nicely formatted web-ready schedule
Here is the structure of my excel file. Week number, date, day of the week, the topics we’re covering, what the students can do to prep for class, what i’m going to evaluate them on (HW, exams), and notes about the assignment. Things in green are for my eyes only. Note the asterisk between items in the cells. The line breaks are for easy visual separation of items in a list, the asterisk will be used in code to create a formal html list.
So first let’s load the necessary libraries and set options for
library(dplyr);library(readxl); library(kableExtra); library(stringr) options(knitr.table.format = "html", knitr.kable.NA = '')
Read the data into
rawdat, reformat the date, make copies of the columns that need list
topic, prep, eval. The filter is leftover from the Spring semester, after each
exam I hide the weeks prior to that exam so that the most current and upcoming weeks are
presented at the top of the table.
rawdat <- read_excel("schedule_615.xlsx") cdat <- rawdat %>% mutate(Date = format(date, "%m/%d"), Topics = topic, Preparation = prep, Evaluation = eval) %>% filter(as.Date(date) > "2017-04-01") %>% select(Wk, Date, Topics, Preparation, Evaluation, topic, prep, eval)
- First I find the rows that have some content in them, store this as
topiclist. This ensures that I don’t get a list of
- Then I turn those three columns into formal html lists. I
paste0the content of
- Lastly I use
stringrpackage to replace the asterisk between list items with `
- to end the first list item and start a second. Notice that there is no asterisk in the excel file in front of the first list item.
# parse topics - same code for all three vars. Yes I could have applied a function. topiclist <- which(!is.na(cdat$topic)) cdat$Topics[topiclist] <- paste0("<ul><li>", cdat$topic[topiclist], "</li></ul>") cdat$Topics[topiclist] <- str_replace(cdat$Topics[topiclist], "\\*", '</li><li>')
Now I clean up the data frame using
dplyr to only the variables I want to show,
in the order I want to show. Then wrap
kable on the entire table.
escape=FALSE is needed to process the html tags
kable_styling adds some very nice customizations to the table, including a non-custom css
method to adjust column spacing and row shading behavior using
hover, striped and
so that the table maintains it’s width on a cell phone, but lets you scroll to the side to
see the whole table.
out <- select(cdat, Wk, Date, Topics, Preparation, Evaluation) knitr::kable(out, align="l", escape=FALSE) %>% kable_styling(bootstrap_options = c("hover", "striped", "responsive")) %>% column_spec(1, width="1%") %>% column_spec(2, width="1%") %>% column_spec(3, width="20%") %>% column_spec(4, width="30%") %>% column_spec(5, width="30%")
The results can be seen on my MATH 615 course website
kable_extra can apply a lot of neat table customization features that make creating professional tables in
R markdown an actual possibility (without switching to \(\LaTeX\)\Sweave)
I highly recommend you checking it out http://haozhu233.github.io/kableExtra/
Having a nice automated framework like this makes my day-to-day life during the semester much easier.
I just copy the
schedule.Rmd file into another class’s repo and just change the excel file that
is read in. Make adjustments to the excel file as needed, rebuild and git push.
(Typically 5 min before class to fix a typo)
Note that since i’m using blogdown to write this post, many more packages are loaded than what is necessary for the topic of this post.
## R version 4.0.0 (2020-04-24) ## Platform: x86_64-apple-darwin17.0 (64-bit) ## Running under: macOS Mojave 10.14.6 ## ## Matrix products: default ## BLAS: /Library/Frameworks/R.framework/Versions/4.0/Resources/lib/libRblas.dylib ## LAPACK: /Library/Frameworks/R.framework/Versions/4.0/Resources/lib/libRlapack.dylib ## ## locale: ##  en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8 ## ## attached base packages: ##  stats graphics grDevices utils datasets methods base ## ## other attached packages: ##  stringr_1.4.0 kableExtra_1.1.0 readxl_1.3.1 dplyr_1.0.0 ## ## loaded via a namespace (and not attached): ##  Rcpp_220.127.116.11 rstudioapi_0.11 xml2_1.3.2 knitr_1.28 ##  magrittr_1.5 hms_0.5.3 munsell_0.5.0 rvest_0.3.5 ##  tidyselect_1.1.0 viridisLite_0.3.0 colorspace_1.4-1 R6_2.4.1 ##  rlang_0.4.6 httr_1.4.1 tools_4.0.0 webshot_0.5.2 ##  xfun_0.14 htmltools_0.4.0 ellipsis_0.3.1 yaml_2.2.1 ##  digest_0.6.25 tibble_3.0.1 lifecycle_0.2.0 crayon_1.3.4 ##  bookdown_0.19 readr_1.3.1 purrr_0.3.4 vctrs_0.3.0 ##  glue_1.4.1 evaluate_0.14 rmarkdown_2.1 blogdown_0.19 ##  stringi_1.4.6 cellranger_1.1.0 compiler_4.0.0 pillar_1.4.4 ##  scales_1.1.1 generics_0.0.2 pkgconfig_2.0.3