---
title: "Create a PowerPoint from Satellite Course Slides"
output: html_document
authors: Madison Richardson
date: "2025-04-04"
---

## **Introduction**

CoastWatch has collected informational slides from many of the participant who have taken the CoastWatch Satellite Training Course. The slides outline what each person worked on during the course and what they accomplished. The slide collection is stored in a password-protected ERDDAP dataset as .PNG files.

We paired the participant slides with additional information about each participant that was collected during the course registration process. This information was uploaded into a separate password-protected metadata "data base" in the form of an ERDDAP tabledap dataset. The dataset is queryable by categories like the course's name and year it was offered, as well as the participants name, affiliation, satellite products of interests, and software use. The result of a query is a listing of participant slide IDs that match your search criteria along with the associated metadata. This information is valuable for reports and presentations that document the reach and impact of the CoastWatch Satellite Training Courses.

This R tutorial shows how to **generate a PowerPoint presentation of participant slides** by downloading slides from the slide collection dataset based on the output from a query to the course metadata dataset.

## **Resources**

In order to run this tutorial, you will need:

-   Internet Connection
-   RStudio
-   Microsoft PowerPoint

## Datasets

-   The course slide metadata dataset is at the following location:\
    <https://coastwatch.pfeg.noaa.gov/wcn/erddap/tabledap/satellite_course_slides_metadata>

-   The archive of course slides is at the following location:\
    <https://coastwatch.pfeg.noaa.gov/wcn/erddap/tabledap/course_slide_deck.html>

    You can directly download the slide images from the files path in ERDDAP:\
    <https://coastwatch.pfeg.noaa.gov/wcn/erddap/files/course_slide_deck/>

## **Load Required Packages**

```{r}
pkgTest <- function(x)
{
  if (!require(x,character.only = TRUE))
  {
    install.packages(x,dep=TRUE,repos='http://cran.us.r-project.org')
    if(!require(x,character.only = TRUE)) stop(x, " :Package not found")
  }
}

# Create list of required packages
list.of.packages <- c(
  "selenider", "dplyr", "httr2", "readr", "future",
  "furrr", "officer", "magick", "stringr", "hrbrthemes",
  "ggplot2"
)

# Create list of installed packages
pkges = installed.packages()[,"Package"]

# Install and load all required pkgs
for (pk in list.of.packages) {
  pkgTest(pk)
}
```

## **Function to Filter Metadata Rows**

```{r}
#' Filter Metadata Rows by Keyword Match
#'
#' Filters a metadata data frame to return only rows where the specified column
#' contains one or more user-defined keywords. Useful for identifying slides
#' related to specific products or topics (e.g., "SST", "Chlorophyll", "R").
#'
#' @param df A data frame containing the ERDDAP slide metadata.
#' @param keywords A character vector of keywords to search for (e.g., c("SST", "Chlorophyll")).
#' @param column A character string specifying the column name to search within (default is "SlideTopic").
#'
#' @return A filtered data frame containing only rows where the selected column
#'         includes one or more of the specified keywords.
#'
#' @examples
#' filter_slide_columns(metadata_df, c("SST", "Chlorophyll"))
#' filter_slide_columns(metadata_df, "Fish Habitat", column = Topic")
#'
#' @export
filter_slide_columns <- function(df, keywords, column = "Topic") {
  if (!column %in% colnames(df)) {
    stop(paste("Column", column, "not found in metadata."))
  }
  pattern <- paste(keywords, collapse = "|")
  filtered_df <- df %>%
    filter(str_detect(.data[[column]], regex(pattern, ignore_case = TRUE)))
  return(filtered_df)
}
```

## **Select the Metadata for Slides**

There are 2 options for accessing the metadata for the satellite course slides.

1.  **Manually Code Metadata URL**

-   Open the ERDDAP login page in a browser: [https://coastwatch.pfeg.noaa.gov/wcn/erddap/login.html](https://coastwatch.pfeg.noaa.gov/wcn/erddap/loginGoogle.html){.uri}
-   After you log into ERDDAP using your NOAA account, navigate to the dataset: **satellite_course_slides_metadata**.
-   Explore the available options and then update `selected_vars` with the metadata columns you want.
-   **ID** must always be present in order to located the correct slide PNGs.
-   For this example, we selected "ID", "Year", "Affiliation", "Topic", and "Products".
-   To further customize our query, we filter the columns be specifying the key values we are interested in.
-   For this example, we selected "Affiliation_Type"=**NOAA**, "NOAA_LO"=**NMFS**, and "JPSS"=**yes**.
-   Make sure that you also select 'yes' on the **Slide** dropdown to limit query results to rows that have course slides.

2.  **Obtain the URL from ERDDAP**

-   Open the ERDDAP login page in a browser: [https://coastwatch.pfeg.noaa.gov/wcn/erddap/login.html](https://coastwatch.pfeg.noaa.gov/wcn/erddap/loginGoogle.html){.uri}
-   After you log into ERDDAP using your NOAA account, navigate to the dataset: **satellite_course_slides_metadata**. Then click on the subset link.
-   Use the dropdown menus to customize your query (See screenshot below). For this example, we selected "Affiliation_Type"=**NOAA**, "NOAA_LO"=**NMFS**, and "JPSS"=**yes**.
-   Make sure that you also select 'yes' on the **Slide** dropdown to limit query results to rows that have course slides.
-   A preview of the results of your query selection can be viewed at the bottom of the page.
-   Once your query selections are made, click on the **Data Access Form** link.

On the **Data Access Form** page, you will see that the choices you made on the subset page now populate the Data Access Form.

-   On the left side of the page, a column labeled **Variable** is the list of all metadata items you can download. Unchecking the checkbox will remove the item from the download request and checking an item will add the item from the download request.
-   For this example, we will uncheck everything except **ID**, **Year**, **Affiliation**, and **Topic**. You may choose a different set of metadata items to download.
-   Make sure that **Topic**. is checked because it is needed to locate the correct slides to download.
-   Next select **.csv** from the **File type** dropdown.
-   Now click the **Just generate the URL** button. A URL will be generated in the text box to the right of the button.
-   Copy the URL and paste as the value for `metadata_url` below. It is the ERDDAP data request you will use to download the metadata you selected.

![](erddap_screenshot1.png)

![](erddap_screenshot2.png)

For this example, we are going to use option 1. If you are interested in option 2, skip the next 2 chunks of code and run the chunk labeled **Option 2 Metdata URL**.

## **Option 1: Manually Code Metadata URL**

```{r}
# Option 1

# Base ERDDAP tabledap endpoint
erddap_base <- "https://coastwatch.pfeg.noaa.gov/wcn/erddap/tabledap/satellite_course_slides_metadata.csv"

# Columns to retrieve
selected_vars <- c("ID", "Year", "Affiliation", "Topic", "Products")

# Filters (key-value pairs — unquoted for now)
filters <- list(
  Affiliation_Type = "NOAA",
  NOAA_LO = "NMFS",
  JPSS = "yes",
  Slide  = "yes"
)

```

## **Option 1: Construct the Metadata URL**

```{r}
# Join selected variables (columns)
var_string <- paste(selected_vars, collapse = ",")

# Encode each filter value with surrounding quotes and URL encode them
filter_string <- paste0(
  names(filters), "=", 
  sapply(filters, function(x) URLencode(paste0('"', x, '"'), reserved = TRUE)),
  collapse = "&"
)

# Constructed metadata URL
metadata_url <- paste0(
  erddap_base, "?", 
  URLencode(var_string, reserved = TRUE), "&",
  filter_string
)

print(metadata_url)

```

## **Option 2: Obtain the URL from ERDDAP**

```{r}
# Option 2

# ERDDAP metadata URL
metadata_url = "https://coastwatch.pfeg.noaa.gov/wcn/erddap/tabledap/satellite_course_slides_metadata.csv?ID%2CYear%2CAffiliation%2CProducts%2CTopic&Affiliation_Type=%22NOAA%22&NOAA_LO=%22NMFS%22&JPSS=%22yes%22"

print(metadata_url)

```

## Get the metadata selected in your query

#### Obtaining the data is a two step process

### 1. ERDDAP authentication

To login into the ERDDAP server, begin by click the **Sign in with Google** button in the red box labeled #1. This will prompt you to sign in with email and password, followed by 2-step verification. Once authenticated, click the **login** button shown in the red box labeled #2. After clicking, you should see a prompt such as "Continue as [Your Name]". The process starts up a browser within R, and sets a cookie. Then, the cookie is retrieved to allow downloads from a password protected dataset.

![](login_page1.png) ![](login_page2.png)

```{r}
# Start a Selenium session with Chrome
session <- selenider_session("selenium", browser = "chrome")

# Navigate to Google
open_url("https://coastwatch.pfeg.noaa.gov/wcn/erddap/login.html")

```

### **Extract Cookies from ERDDAP**

```{r}
# Get cookies via Selenium
cookies <- session$driver$get_cookies()

# Convert cookies to a data frame
cookie_df <- bind_rows(cookies)
print(cookie_df)

# Convert cookies to a single string
cookie_header <- paste0(
  apply(cookie_df, 1, function(row) paste0(row["name"], "=", row["value"])),
  collapse = "; "
)

```

### 2. Download the metadata

The cookie is used to obtain a response from the from the password-protected metadata dataset, using the ERDDAP request URL (`metadata_url`) you generated earlier.

```{r}
# Send authenticated request
resp <- request(metadata_url) |>
  req_headers(
    Cookie = cookie_header,
    `User-Agent` = "Mozilla/5.0"
  ) |>
  req_perform()

# Load CSV as a data frame
metadata_df <- read_csv(resp_body_raw(resp))

# Clean the metadata
metadata_df <- metadata_df[!is.na(metadata_df$ID) & metadata_df$ID != "", ]
print(metadata_df)

```

## View Unique Values in Columns of the Metadata

Explore the unique values of every a specified column in **metadata_df** to filter it in the next step. We are exploring the "Products" and "Topic" column in this tutorial.

```{r}
unique_products <- metadata_df$Products |>
  strsplit(",") |>
  unlist() |>
  trimws() |>
  unique() |>
  sort()

print(unique_products)

```

```{r}
unique_topics <- metadata_df$Topic |>
  strsplit(",") |>
  unlist() |>
  trimws() |>
  unique() |>
  sort()

print(unique_topics)

```

## **Filter Slides by Keyword in Metadata Columns**

The `filter_slide_columns()` function helps you find specific slides by filtering the ERDDAP metadata using **partial keyword matching**. In this tutorial, we want to include every slide that uses a Ocean Color product so we specify **Ocean Color** as the keyword and **Products** as the column inside the **metadata_df**.

Here are some additional examples of how to use the function:

-   You want to find all slides that mention a specific topic (e.g., `"Fish Habitat"`)

-   You’re looking for slides that involve a certain software tool (e.g., `"R"` or `"Python"`)

-   You need more flexibility than ERDDAP's web filters

```{r}
# Use the function to filter for slides that use a SST product
product_filtered_slides <- filter_slide_columns(
  df = metadata_df,
  keywords = c("Ocean Color"),
  column = "Products"
)

# View the filtered results
print(product_filtered_slides)
```

## **Generate Dynamic Slide Titles**

Update `selected_columns` with the metadata columns you want displayed as the title. Make sure that the columns you chose are inside `metadata_df`. Create the titles of each slide based on the selected columns. Create the footer titles based on `below_image_columns`.

```{r}
# Choose columns for slide titles
selected_columns <- c("Affiliation", "Topic")

# Choose columns for footer titles
below_image_columns <- c("Products", "Year")

# Check for missing columns
missing_cols <- setdiff(selected_columns, colnames(product_filtered_slides))
if (length(missing_cols) > 0) {
  stop(paste("Missing columns:", paste(missing_cols, collapse = ", ")))
}

# Dynamically create slide titles by combining selected metadata
titles <- apply(product_filtered_slides[, selected_columns, drop = FALSE], 1, function(row) {
  paste(
    sapply(names(row), function(col) {
      if (!is.na(row[col])) paste(col, ":", row[col]) else NULL
    }),
    collapse = " | "
  )
})

# Dynamically create slide footers by combining selected metadata
footer_titles <- apply(product_filtered_slides[, below_image_columns, drop = FALSE], 1, function(row) {
  paste(
    sapply(names(row), function(col) {
      if (!is.na(row[col])) paste(col, ":", row[col]) else NULL
    }),
    collapse = " | "
  )
})

```

## **Create a PowerPoint Presentation**

Create a blank PowerPoint that is populated with the selected PNGs, which are customized with a black border, title, and footer.

```{r}
# Initialize PowerPoint
ppt <- read_pptx()

# Use slide IDs to build image URLs
slide_base_url <- "https://coastwatch.pfeg.noaa.gov/wcn/erddap/files/course_slide_deck/"
slide_ids <- product_filtered_slides$ID

# Loop through each slide for URL and title building
for (i in seq_along(slide_ids)) {
  slide_id <- slide_ids[i]
  image_url <- paste0(slide_base_url, slide_id, ".png")
  title_text <- titles[i]
  
  if (is.na(title_text) || title_text == "") {
    title_text <- "No Title Available"
  }
  
  # Format the title as font size 18
  title_paragraph <- fpar(ftext(title_text, fp_text(font.size = 18)))
  
  # Add footers
  footer_title <- footer_titles[i]
  
  if (is.na(footer_title) || footer_title == "") {
    footer_title <- "No Title Available"
  }
  
  # Format the footer as font size 14
  footer_paragraph <- fpar(ftext(footer_title, fp_text(font.size = 14)))
  
  # Temp file path to save PNGs
  temp_file <- tempfile(fileext = ".png")
  
  tryCatch({
    # Send a HTTP request to download the PNGs
    img_resp <- httr::GET(
      image_url,
      httr::add_headers(
        Cookie = cookie_header,
        `User-Agent` = "Mozilla/5.0"
      )
    )
    
    if (httr::status_code(img_resp) != 200) stop("Image download failed.")
    
    # Check if PNG was downloaded, then add a 10px black border
    img_magick <- image_read(httr::content(img_resp, as = "raw")) |>
      image_border("black", "5x5")
    
    # Save PNG in temp file
    image_write(img_magick, path = temp_file, format = "png")
    
    # Add a blank slide
    ppt <- add_slide(ppt, layout = "Blank", master = "Office Theme")
    
    # Insert PNG at specific size and location
    ppt <- ph_with(ppt, external_img(temp_file, width = 8, height = 5),
                   location = ph_location(left = 1, top = 1.5, width = 8, height = 5))
    
    # Insert title above the PNG on the slide
    ppt <- ph_with(ppt, title_paragraph,
                   location = ph_location(left = 1, top = 0.5, width = 8, height = 1))
    
    # Insert title below the PNG on the slide
    ppt <- ph_with(ppt, footer_paragraph,
                   location = ph_location(left = 1, top = 6.6, width = 8, height = 0.5))
    
  }, error = function(e) {
    message(paste("Error downloading slide ID", slide_id, ":", e$message))
  })
}

```

## **Save the PowerPoint File**

Save the PowerPoint to your current working directory. You can also change the name **course_slides_R.pptx**.

```{r}
output_file <- file.path(getwd(), "course_slides_R.pptx")
print(ppt, target = output_file)
message(paste("Presentation saved to:", output_file))
```

## Visualize the frequency of products used with a Lollipop Chart

```{r}
product_counts <- metadata_df$Products |>
  strsplit(",") |>
  unlist() |>
  str_trim() |>
  na.omit() |>
  as.data.frame() |>
  setNames("Products") |>
  group_by(Products) |>
  summarise(Frequency = n(), .groups = "drop") |>
  arrange(desc(Frequency))

print(product_counts)

```

```{r}
product_counts %>%
  mutate(Products = factor(Products, levels = rev(Products))) %>%
  ggplot(aes(x = Products, y = Frequency)) +
  geom_segment(aes(xend = Products, y = 0, yend = Frequency), color = "grey") +
  geom_point(size = 4, color = "#0072ce") +
  coord_flip() +
  theme_ipsum() +
  theme(
    panel.grid.minor.y = element_blank(),
    panel.grid.major.y = element_blank(),
    legend.position = "none"
  ) +
  xlab("") +
  ylab("Frequency of Products Used in Satellite Course Slides")

```

## Visualize the frequency of topics with a Lollipop Chart

```{r}
topic_counts <- metadata_df$Topic |>
  strsplit(",") |>
  unlist() |>
  trimws() |>
  as.data.frame() |>
  setNames("Topic") |>
  mutate(Topic = case_when(
    Topic %in% c("time series analysis") ~ "Time Series Analysis",
    TRUE ~ str_to_title(Topic)
  )) |>
  group_by(Topic) |>
  summarise(Frequency = n(), .groups = "drop") |>
  arrange(desc(Frequency))

print(topic_counts)

```

```{r}
topic_counts %>%
  mutate(Topic = factor(Topic, levels = rev(Topic))) %>%
  ggplot(aes(x = Topic, y = Frequency)) +
  geom_segment(aes(xend = Topic, y = 0, yend = Frequency), color = "grey") +
  geom_point(size = 4, color = "#0072ce") +
  coord_flip() +
  theme_ipsum() +
  theme(
    panel.grid.minor.y = element_blank(),
    panel.grid.major.y = element_blank(),
    legend.position = "none"
  ) +
  xlab("") +
  ylab("Frequency of Topics Used in Satellite Course Slides")


```
