8 min read

Exploring the gt Package

feat. Pixar’s wonderful world of Monstropolis (all rights doubtless reserved)

Welcome to yet another ride on the wild side. This time we venture into the strange new world that is the gt package. For those unfamiliar, please visit the gt package page. As the README says, gt makes “wonderful-looking tables” in R. What is not to love about that?

Your author had occasion to deploy this beauty recently. With slight variations, and without further ado, please see a toy example below for programatically generating invoices using gt. This is, admittedly, a bit hacky. I’m sure wiser wizaRds could finesse the job.

The goal and the inputs

Let’s imagine you are a hard working monster. You perform various tasks at locations across Monstropolis and keep track of your billing in a messy spreadsheet. If only there was an easy way to generate invoices for all your clients!

Alas, there is. First let’s create the messy invoice data.

# load up libraries

library(tidyverse)
library(gt)

# spreadsheet of work

messy_csv_job_count <- tibble(
  job = c("Cleaning Scare Floor", "Scaring Boo", "Punking Randal"),
  `000 Monster Ln` = sample(3, 3),
  `111 Monster Ln` = sample(3, 3),
  `222 Monster Ln` = sample(3, 3),
  `333 Monster Ln` = sample(3, 3)
)

# fee schedule for various jobs

fee_schedule <- tibble(
  job = messy_csv_job_count$job,
  fee = c(20, 40, 60)
)

# put it all togther

monster_fees <- left_join(messy_csv_job_count, fee_schedule) %>% 
  gather(address, count, `000 Monster Ln`:`333 Monster Ln`) %>% 
  mutate(`fees owed` = fee * count) %>% 
  group_by(address) %>% 
  nest()

The invoices, ie the output

We now have our cleaned up data. Let’s deploy gt to create wonderful-looking invoices. We can use the dreaded for loop to accomplish our task. Each iteration of the loop will pick out the address and create a separate gt table.

output <- list()

for(i in seq_along(monster_fees$address)) {
  
  # create a variable with the target address
  
  add_of_int <- monster_fees$address[i]
  
  # for each job at each address, create a data frame
  
  by_job <- monster_fees %>% 
    filter(address == add_of_int) %>% 
    unnest() %>% 
    select_all(str_to_title) %>% 
    select(-Address) %>% 
    select(Job, Count, Fee, `Fees Owed`)
  
  # add a row with totals and bind
  
  total_df <- tibble(
    Job = "Total",
    Count = sum(by_job$Count),
    Fee = weighted.mean(by_job$Fee, by_job$Count),
    `Fees Owed` = sum(by_job$`Fees Owed`)
  )
  
  address_df <- bind_rows(by_job, total_df)
  last_row <- nrow(address_df)  
  
  # and NOW THE GT, rendered in HTML to facilitate output
  
  address_df %>% 
    gt() %>% 
    tab_header(
      title = html(
        paste0("Invoice for Monster Services RendeRed", 
               "<br>", 
               "<u><b>", add_of_int, "</b></u>")),
      subtitle =  html('<span style="font-weight:bold;color:red">PAYMENT DUE DATE: DEC 31, 2020</span>')
      ) %>%
    fmt_currency(
      columns = vars(Fee, `Fees Owed`),
      decimals = 0,
      currency = "USD"
    ) %>% 
    tab_style(
      style = cells_styles(
        text_weight = "bold",
        bkgd_color = "white"),
      locations = cells_data(rows = last_row)
      ) %>% 
    tab_style(
      style = cells_styles(
        text_color = "white"),
     locations = cells_data(rows = last_row, columns = vars(Fee))
     ) %>% 
    tab_source_note(
      source_note = html(
        '<br>
        <div align="right">
        May there be no questions! <u>Send payments to</u>:
        <br>
        <br>
        Monster University
        <br>
        Attn: Sully
        <br>
        911 Boo Ln
        <br>
        Monstropolis 00000
        </div>')
    ) -> output[[i]]
}

Here is the first of our invoices…

Invoice for Monster Services RendeRed
000 Monster Ln
PAYMENT DUE DATE: DEC 31, 2020
Job Count Fee Fees Owed
Cleaning Scare Floor 2 $20 $40
Scaring Boo 1 $40 $40
Punking Randal 3 $60 $180
Total 6 $43 $260

May there be no questions! Send payments to:

Monster University
Attn: Sully
911 Boo Ln
Monstropolis 00000

…and the last of our invoices

Invoice for Monster Services RendeRed
333 Monster Ln
PAYMENT DUE DATE: DEC 31, 2020
Job Count Fee Fees Owed
Cleaning Scare Floor 3 $20 $60
Scaring Boo 2 $40 $80
Punking Randal 1 $60 $60
Total 6 $33 $200

May there be no questions! Send payments to:

Monster University
Attn: Sully
911 Boo Ln
Monstropolis 00000

And there you have it folks. A collection of beautiful invoices.

For the enterprising folks who need old fashioned pdf output, here is my recommendation. With the loop output saved create a chunk per job (e.g. output[[1]]), and insert <P style="page-break-before: always"> between. Knit to html, set your css, and save to PDF. Of course you could do it in latex, but this weasel opts for the easier html hack output!