I recently had to recreate a report which was originally produced in Excel. This is currently being converted to pdf and emailed out each morning. Initially I was thinking that a flexdashboard solution would be called for, but on seeing the report it was mainly tables, which the audience liked, and found informative.

I have had zero interest in making tables in R, and usually just default to knitr::kable() when the need arises.
However, for this report, I had an idea that I might want to include graphics within each table cell. I recalled seeing that RStudio were running a competition relating to tables in R.

With a bit of searching, I came across some interesting entries from the previous year, and what really caught my eye was an entry using {reactable}.

And with more digging, I found the {reactablefmtr} package which helps take your tables to the next level.

I have now produced a reactable/reactablefmtr version of the report and am looking forward to seeing how well it is received.

One thing I love is the ability to create in cell charts / heatmaps, a process much simplified by using {reactablefmtr}. I’m looking forward to seeing what the next release of that package brings.

In the meantime, I thought I would try out my new found skills by recreating my Google / YouTube Music 2021 playlist.

I should probably have done some web scraping, but instead I simply selected the table on the page, then copied everything, including the cover images/ thumbnails, and stuck them into Excel.

That meant a bit of manual jiggery-pokery, getting everything back into sensible columns and alignments, but it also meant I could take advantage of my still lingering Excel text function knowledge, to tidy up the links and get them into a suitable format for publishing.

The code below sets a theme, courtesy of {reactablefmtr}, then creates columns for Song, Album , Year, Time (track duration) and the associated thumbnail image. In the final output, I set show = FALSE so that the image column would not display. The images were stored in a sub folder, so the code grabs their location to pass as the image source.

The link function creates a clickable link within each row of the table, pointing to associated video / track on Youtube.

playlist <- reactable(joined,
theme = slate(font_family = "Arial",
              font_size = 18,
              header_font_size = 20,
              header_font_color = "darkorange",
              cell_padding = 8),
columns = list(ID = colDef(name = "ID", show = FALSE),
               Song = colDef(name = "Track"),
               Artist = colDef(name = "Artist"),
               Album = colDef(name = "Album"),
               Year = colDef(name = "Year"),
               Time = colDef(name = "Duration"),#,
              # format = colFormat(time = TRUE, locales = "en-GB")
              Img = colDef(name = "Cover", show = FALSE,
              cell = function(value) {
              img_src <- knitr::image_uri(sprintf("%s", value))
              image <- img(src = img_src, height = "45px", alt = "")
              tagList(div(style = list(display = "inline-block",
                                       width = "45px"), image))}),
              Link = colDef(name = "Link",
                     cell = function(value, index) {
                     url <- value 
                     htmltools::tags$a(href = url, target = "_blank", as.character(value))
                     }) # end of Link definition
                 ), # end of column list
                 pagination = FALSE,
                 filterable = TRUE,
                 striped = TRUE,
                 bordered = TRUE,
                 defaultColDef = colDef(footerStyle = list(fontWeight = "bold")))

# create image
html_file <- "playlist.html"
img_file <- "img.png"
webshot(url = html_file, file = img_file,delay = 0.1, vwidth = 1245)
saveWidget(widget = playlist, file = html_file, selfcontained = TRUE)

I used raw.githack.com to host the rendered html - so you can see the final interactive table here and you too can marvel at how many banging tunes are on there.

The final few lines of the code take an image of the rendered table, which I stuck into the readme on the project repo.

The code and the rendered html are also in there.

Here’s a sneak preview of how it looks:

Finally, I’m not in the habit of making predictions, and I never will, but I think Mastodon are going to usurp Metallica this year.

Tune in this time next year to find out if this non-prediction holds!

In the meantime, I no longer think making tables in R is boring, and I might make some more now, with {reactablefmtr} a new favourite!