R_Tables.Rmd

---
title: "Setting the Table"
author: "Eden Axelrad"
date: "3/19/2022"
output: 
  html_document:
    code_folding: hide
---

```{r setup, include=FALSE}

# Load required packages
library(tidyverse)

library(gt)

library(rvest)
library(xml2)
library(formattable)
library(sparkline)

```

## Table Examples {.tabset .tabset-pills}

### Starwars gt

<br>

```{r gt}

# Setting up the data
sw_table <- starwars %>%
  # Add column that counts number of films per character 
  mutate(n_films = map_int(films, length)) %>%
  # Arrange from high to low and filter to greater than 3 films
  arrange(desc(n_films)) %>%
  filter(n_films > 3) %>%
  # Use unnest on films columns and add a count column to later be converted to a check mark
  unnest(films) %>%
  mutate(count = 1) %>%
  # Use pivot wider to show characters as the ID column, number of films,and all 7 films 
  pivot_wider(names_from = films, values_from = count, values_fill = 0) %>%
  # Select only relevant columns and order the 7 films correctly (Episode I to VII)
  select(name, n_films, `The Phantom Menace`, `Attack of the Clones`, `Revenge of the Sith`, 
         `A New Hope`, `The Empire Strikes Back`, `Return of the Jedi`, `The Force Awakens`) 


# Create a vector of image paths for character icons 
# Icons from https://symbolicons.com/free
img_paths <- c(
  "r2d2.png", 
  "c3p0.png",
  "obiwan-kenobi.png",
  "luke-skywalker.png",
  "princess-leia.png",
  "chewbacca.png", 
  "yoda.png",
  "emperor-palpatine.png",
  "darth-vader.png",
  "han-solo.png"
  )

img_paths <- paste0("/gt Starwars Icons/", img_paths)


# Add image paths as first column in the Starwars data frame 
sw_table <- cbind.data.frame(img_paths, sw_table)


# Create a color palette to be used with Number of Films column
pct_pal <- scales::col_numeric(c("#e8cfe1", "#a374c4"), domain = c(2, 7), alpha = 0.5)


# html color and code for check mark
check <- "<span style=\"color:#a374c4\">&#10004;</span>"


# Generate the gt table
sw_table %>% 
  gt() %>%
  # All caps on column headers
  opt_all_caps() %>% 
  # Change font to Franklin Demi 
  opt_table_font(font = list(
    google_font("Franklin Demi"), 
    default_fonts())
    ) %>%
  # Set the alignment 
  cols_align(
    align = "center",
    columns = c(1, 3:10)
    ) %>%
  # Set the column widths
  cols_width(
    columns = 1 ~ px(65),
    columns = 2 ~ px(135),
    columns = 3:10 ~ px(88)
    ) %>%
  # Rename n_films and img_paths columns
  cols_label(
    n_films = "Number of Films",
    img_paths = ""
    ) %>% 
  # Color the Number of Films column
  data_color(
    columns = c(n_films),
    colors = pct_pal
    ) %>%
  # Make the check marks bold
  tab_style(
    style = list(cell_text(weight = "bold")),
    locations = cells_body(columns = 4:10)
    ) %>%
  # Add a title and subtitle
  tab_header(
    title = md("&#10024; **Starwars Characters by Film Appearance** &#10024;"),
    subtitle = md("**[For Characters in 4+ Films]**")
    ) %>%
  # Provide additional cosmetics - border width and color
  tab_options(
    column_labels.border.top.width = px(18),
    column_labels.border.top.color = "white",
    table.border.top.color = "white",
    table.border.bottom.color = "white"
    ) %>%
  # Use text transform to replace 1s with check marks and 0s with blanks
  text_transform(
    locations = cells_body(columns = 4:10),
      fn = function(x) {
        dplyr::case_when(
          x > 0   ~ paste(check),
          x == 0  ~ "")
          }) %>%
  # Add images using text_transform and local_image function
  text_transform(
    locations = cells_body(columns = img_paths),
      fn = function(x) {
       local_image(
          filename = img_paths,
          height = 25)
          }) %>%
  # Add a source/credit
  tab_source_note(
    source_note = md("Table by [Eden Axelrad](https://github.com/edenaxe) 
                     with icons from [symbolicons](https://symbolicons.com/free)"))

```

<br>

### Formattable + Sparklines

```{r formattable}

# This website shows screen time breakdown for starwars films
web <- 'https://www.imdb.com/list/ls027631145/'
website <- read_html(web)

# Retrieve the titles of movies listed on the website
title_data_html <- html_nodes(website,'.lister-item-header a')
title_data <- html_text(title_data_html)

# Retrieve the screen time data by character
table_data_html <- html_nodes(website,'.list-description p')
table_data <- html_text(table_data_html)
table_data <- table_data[2:12]

# Combine with the film title 
sw_table <- cbind.data.frame(title_data, table_data)


screen_time <- function(st, title) {
  
# Clean up the table data concatenated string
# Split the string at ">" so each character is an item in a vector list
st <- as.list(strsplit(st, ">")[[1]]) %>%
  # Remove unused html "\n" 
  gsub(pattern = "\n", replacement = "") %>%
  as.data.frame() %>%
  # Separate the string in to 3 portions (name, minutes, seconds) using "<" and ":" as separators 
  mutate(seperated = str_split_fixed(., ' <|:', 3)) %>%
  # Convert/keep data as a data frame and select and rename desired columns (name, minutes, seconds)
  do.call(data.frame, .) %>%
  select("name" = seperated.1, "minutes" = seperated.2, "seconds" = seperated.3) %>%
  # Convert minutes and seconds to numeric and replace NAs with 0s 
  mutate_at(.vars = c("minutes", "seconds"), .funs = as.numeric) %>%
  replace_na(list(minutes = 0, seconds = 0)) %>%
  # Create a column that shows total minutes
  mutate(total_mins = minutes + seconds/60) %>%
  select(name, total_mins) %>%
  mutate(film = title)
  
}


# Use the above function to sum screen time, clean, and append all films
all_films <- map2(.f = screen_time, 
                  .x = sw_table$table_data, 
                  .y = sw_table$title_data) %>%
  # Bind rows to combine all characters and films into one data frame
  bind_rows() %>%
  # Use pivot wider to show films as columns and total screen time per film as the value
  pivot_wider(names_from = film, values_from = total_mins, values_fill = 0) %>%
  # Add a column that shows total screen time across all films
  rowwise() %>%
  mutate(`Total Screen Time` = sum(c_across(`Star Wars: Episode I - The Phantom Menace`:`Solo: A Star Wars Story`))) %>%
  # Filter to characters with more than 30 minutes of screen time and arrange in descending order
  filter(`Total Screen Time` > 30) %>%
  arrange(desc(`Total Screen Time`))




# Create the trendline column using sparkline package
all_films$`Line Chart` <- apply(all_films[,2:12], 1, FUN = function(x) spk_chr(as.numeric(x), type = "line"))


as.htmlwidget(
  formattable(all_films,
              list(`name` = formatter("span", 
                                        style = ~ formattable::style(font.weight = "bold")))))  %>%
  spk_add_deps()

```