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 using RMarkdown.

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 kable

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 processing: 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 *NA’s.
• Then I turn those three columns into formal html lists. I paste0 the content of topic between starting <ul><li> and ending </li></ul> tags.
• Lastly I use str_replace from the stringr package 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 select from 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 responsive 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)

sessionInfo()

## 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:
## [1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8
## 
## attached base packages:
## [1] stats     graphics  grDevices utils     datasets  methods   base     
## 
## other attached packages:
## [1] stringr_1.4.0    kableExtra_1.1.0 readxl_1.3.1     dplyr_1.0.0     
## 
## loaded via a namespace (and not attached):
##  [1] Rcpp_1.0.4.6      rstudioapi_0.11   xml2_1.3.2        knitr_1.28       
##  [5] magrittr_1.5      hms_0.5.3         munsell_0.5.0     rvest_0.3.5      
##  [9] tidyselect_1.1.0  viridisLite_0.3.0 colorspace_1.4-1  R6_2.4.1         
## [13] rlang_0.4.6       httr_1.4.1        tools_4.0.0       webshot_0.5.2    
## [17] xfun_0.14         htmltools_0.4.0   ellipsis_0.3.1    yaml_2.2.1       
## [21] digest_0.6.25     tibble_3.0.1      lifecycle_0.2.0   crayon_1.3.4     
## [25] bookdown_0.19     readr_1.3.1       purrr_0.3.4       vctrs_0.3.0      
## [29] glue_1.4.1        evaluate_0.14     rmarkdown_2.1     blogdown_0.19    
## [33] stringi_1.4.6     cellranger_1.1.0  compiler_4.0.0    pillar_1.4.4     
## [37] scales_1.1.1      generics_0.0.2    pkgconfig_2.0.3