ingest_ferc.Rmd

---
title: "FERC doc tagging ingest"
output: html_document
editor_options: 
  chunk_output_type: console
---

```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = TRUE)
```
## Goals

1. Read [FERC doc tagging spreadsheet 11721.xlsx - Google Sheets](https://docs.google.com/spreadsheets/d/1WuTqAOyvKK7n2BMYDsQpFc8xQg4qcsOv/edit#gid=951079264)
1. Extract tag lists
1. Compare with existing Tethys tags
1. Fold into Shiny app

## Setup
```{r}
if (!require(librarian)){
  install.packages("librarian")
}
shelf(
  dplyr, glue, here, readr, readxl, stringr, tidyr)
```

* [How to extract a URL from a hyperlink on Excel](https://howtouseexcel.net/how-to-extract-a-url-from-a-hyperlink-on-excel)

## Prep `data/tags_extra_indented.csv`

```{r}
keys_csv <- here("data/tags_extra_indented.csv")

d_keys <- read_csv(here("data/tags_extra.csv")) %>% 
  select(key_facet = facet, key = item_label, key_parent = tag_parent) %>% 
  filter(key_facet %in% c("stressor","receptor","technology")) %>% 
  mutate(
    key_order = ifelse(
      is.na(key_parent),
      key,
      glue("{key_parent}:{key}")),
    key_spaced = ifelse(
      is.na(key_parent),
      key,
      glue("    {key}", .trim = F))) %>% 
  arrange(key_facet, key_order) # View(keys)
write_csv(d_keys, keys_csv)

<- read_csv(keys_csv)
```

## Prep `data/tethys_mgt_*.csv`

```{r}
mgt_csv <- here("data/tethys_mgt.csv")

d_mgt <- read_csv(mgt_csv) %>% 
  rename(
    Category = `Management Measure Category`,
    Phase    = `Phase of Project`) # View(d_mgt)

paste(names(d_mgt), collapse = '", "') %>% cat()

flds_tags <- list(
  "Technology", "Category", "Phase", "Stressor", 
  c("Receptor", "Specific Receptor"), 
  "Interaction")

keys2flds  <- list(
  technology  = "Technology", 
  category    = "Category",
  phase       = "Phase",
  stressor    = "Stressor",
  receptor    = c("Receptor", "Specific Receptor"),
  interaction = "Interaction")

for (key in names(keys2flds)){ # key = "technology" # key = "receptor"
  key_csv <- here(glue("data/tethys_mgt_tags_{key}.csv"))
  flds    <- keys2flds[[key]]
  
  d_fld <- d_mgt %>% 
    count(across(all_of(flds))) %>%
    mutate(
      across(
        all_of(flds), ~{str_to_title(.x)}, 
        .names = "{str_to_lower(str_replace_all(.col, ' ', '_'))}_titlecase")) %>% 
    separate_rows(all_of(flds), sep = ";") %>% 
    mutate(across(all_of(flds), ~{str_trim(.x)}))
  
  if (key %in% d_keys$key_facet){
    d_fld <- d_fld %>% 
      full_join(
        keys %>% 
          filter(key_facet == !!key), 
        by = setNames("key", fld_s))
  }
  
  write_csv(d_fld, key_csv)
}

d_mgt %>%
  filter(`Specific Receptor` == "All receptors") %>%
  write_csv(here(glue("data/tethys_mgt_tags_specific_receptor.is.All.csv")))
  select(Receptor, `Specific Receptor`, Interaction) %>%
  View()

# TODO: fold new Physical Environment: ALL, Benthic, Physical Environment:Habitat from tags_extra_indented into Shiny report tags
```

## Read xlsx, create short lists for lookup tables (`*_lut.csv`)

```{r}
xlsx <- here("data/FERC doc tagging spreadsheet 11721.xlsx")
# excel_sheets(xlsx)
d <- read_excel(xlsx, "CONSOLIDATED PROJECT MASTER")
paste(names(d), collapse = '", "') %>% cat()
# View(d)

flds_doc    <- c("DOCUMENT", "URL")
flds_tags  <- c("DEVICE TYPE", "PHASE", "ACTIVITY", "STRESSOR", "RECEPTOR", "SUB RECEPTOR", "KEY EFFECTS", "KEY INTERACTION DETAIL")
flds_bln   <- c("PRESENTED AS POTENTIAL INTERACTION?", "DECRIBED FROM OBSERVATIONS AT THE PROJECT SITE?", "MONITORING PLAN (MP)", "ADAPTIVE MANAGEMENT PLAN (AMP)", "PROTECTION, MITIGATION AND ENHANCEMENT", "BMPs APPLIED")
flds_notes <- c("MP NOTES", "AMP NOTES", "GENERAL NOTES")

keys2tags  <- c(
  # TODO: PHASE, ACTIVITY with Management Measures
  "DEVICE TYPE"  = "technology", 
  "STRESSOR"     = "stressor", 
  "RECEPTOR"     = "receptor", 
  "SUB RECEPTOR" = "receptor")

# check that all fields accounted for
stopifnot(all(names(d) %in% c(flds_doc, flds_tags, flds_bln, flds_notes)))

for (fld in flds_tags){ # fld = flds_tags[1]
  fld_s   <- str_to_lower(fld) %>% str_replace_all(" ", "_")
  fld_csv <- here(glue("data/ferc_tags_{fld_s}.csv"))
  
  d_fld <- d %>% 
    count(across(all_of(fld))) %>%
    mutate(across(all_of(fld), ~{str_to_title(.x)}, .names = fld_s)) 
  
  key <- tags2keys[fld]
  if (!is.na(key)){
    d_fld <- d_fld %>% 
      full_join(
        keys %>% 
          filter(key_facet == !!key), 
        by = setNames("key", fld_s))
  }
  
  write_csv(d_fld, fld_csv)
}

# TODO: 
# - stressor/receptor="ALL"
# - Maria to check matches / missing:
#   RECEPTOR:
#   - DISTURBANCE (n=1) = Behavioral Interaction
#   - SCOUR	      (n=3) = Habitat Change
#   - missing: Physical Interaction:Chemicals
#   DEVICE TYPE:
#   - missing: Current, OTEC, Riverine, Salinity Gradient, Wind Energy, Land-Based Wind, Offshore Wind
#   RECEPTOR, SUB RECEPTOR:
#   - "Same as for EA except ONLY ESA-listed species" (n=1) = [skipped]
#   - missing: Birds:Ground-Nesting Birds, Birds:Passerines, Birds:Waterfowl, Human Dimensions:Climate Change, Human Dimensions:Environmental Impact Assessment, Human Dimensions:Legal & Policy, Human Dimensions:Life Cycle Assessment, Human Dimensions:Marine Spatial Planning, Human Dimensions:Stakeholder Engagement, Terrestrial Mammals
#   
```

## Update data from lookup tables (`*_lut.csv`)

```{r}
xlsx <- here("data/FERC doc tagging spreadsheet 11721.xlsx")
csv  <- here("data/ferc_docs.csv")
# excel_sheets(xlsx)
d <- read_excel(xlsx, "CONSOLIDATED PROJECT MASTER")
paste(names(d), collapse = '", "') %>% cat()


# list.files("data", ".*_lut\\.csv$") %>% paste(collapse = ", ")

receptor_lut   <- read_csv("data/ferc_tags_receptors_lut.csv")
stressor_lut   <- read_csv("data/ferc_tags_stressors_lut.csv")
technology_lut <- read_csv("data/ferc_tags_technology_lut.csv")
phase_lut      <- read_csv("data/ferc_tags_phase_lut.csv")

d_lut <- d %>% 
  rename(doc = DOCUMENT, url = URL) %>% 
  # receptor
  left_join(
    receptor_lut %>% 
      select(RECEPTOR, receptor, receptor_parent = key_parent) %>% 
      filter(!is.na(RECEPTOR)), 
    by = c("RECEPTOR" = "RECEPTOR")) %>% 
  left_join(
    receptor_lut %>% 
      select(`SUB RECEPTOR`, receptor_sub = receptor, receptor_sub_parent = key_parent) %>% 
      filter(!is.na(`SUB RECEPTOR`)), 
    by = c("SUB RECEPTOR" = "SUB RECEPTOR")) %>% 
  mutate(
    receptor = ifelse(
      !is.na(receptor_sub_parent),
      receptor_sub,
      receptor)) %>% 
  select(-RECEPTOR, -`SUB RECEPTOR`, -receptor_parent, -receptor_sub, -receptor_sub_parent) %>% 
  # stressor
  left_join(
    stressor_lut %>% 
      select(STRESSOR, stressor) %>% 
      filter(!is.na(STRESSOR)), 
    by = c("STRESSOR" = "STRESSOR")) %>% 
  select(-STRESSOR) %>% 
  # technology
  left_join(
    technology_lut %>% 
      select(`DEVICE TYPE`, technology) %>% 
      filter(!is.na(`DEVICE TYPE`)), 
    by = c("DEVICE TYPE" = "DEVICE TYPE")) %>% 
  select(-`DEVICE TYPE`) %>% 
  # phase
  left_join(
    phase_lut %>% 
      select(PHASE, phase) %>% 
      filter(!is.na(PHASE)), 
    by = c("PHASE" = "PHASE")) %>% 
  select(-PHASE) %>% 
  # booleans
  mutate(
   `PROTECTION, MITIGATION AND ENHANCEMENT` = na_if(`PROTECTION, MITIGATION AND ENHANCEMENT`, "N")) %>% 
  mutate(across(
    all_of(flds_bln), 
    ~{!is.na(.x)}, 
    .names = "{str_to_lower(flds_bln) %>% 
    str_replace_all(' ', '_') %>% 
    str_replace_all('[?,]', '')}")) %>% 
  select(-all_of(flds_bln)) %>% 
  select(-ACTIVITY) %>% 
  rename(
    key_effects            = `KEY EFFECTS`,
    key_interaction_detail = `KEY INTERACTION DETAIL`,
    notes_mp               = `MP NOTES`,
    notes_amp              = `AMP NOTES`,
    notes_general          = `GENERAL NOTES`)
  
write_csv(d_lut, csv)
```


```{r}
ferc_docs      <- read_csv("data/ferc_docs.csv")
ferc_docs2_csv <- "data/ferc_docs2.csv"
technology_lut <- read_csv("data/ferc_tags_technology_lut.csv")
receptor_lut   <- read_csv("data/ferc_tags_receptors_lut.csv")
stressor_lut   <- read_csv("data/ferc_tags_stressors_lut.csv")
phase_lut      <- read_csv("data/ferc_tags_phase_lut.csv")

for (tag in c("receptor","stressor","technology")){ # tag <- "receptor"
  
  message(glue("tag: {tag}"))
  
  lut <- get(glue("{tag}_lut"))
  lookup_csv <- glue("data/ferc_lookup_{tag}.csv")
  
  x <- lut %>% 
    filter(!is.na(key_order)) %>% 
    mutate(
      key_dot = str_replace_all(key_order, ":", "."))
  tag_recode <- setNames(x$key_dot, x[[tag]])
  
  table(ferc_docs[[tag]])
  ferc_docs <- ferc_docs %>% 
    mutate(
      !!tag := recode(!!as.name(tag), !!!tag_recode))
  table(ferc_docs[[tag]])
  
  lut %>% 
    group_by(key_order) %>% 
    summarize(n=1) %>% 
    filter(!is.na(key_order)) %>% 
    rename_with(function(x) ifelse(x=="key_order", tag, x)) %>%
    select(-n) %>% 
    write_csv(lookup_csv)
}

ferc_docs <- ferc_docs %>% 
  mutate(
    phase = recode(
      phase, 
       `1. Site Characteriation and Assessment` = "1. Site Characterization and Assessment"))
table(ferc_docs$phase)

read_csv("data/tags_tethys_receptors.csv") %>% 
  distinct(key_order) %>% 
  transmute(
    receptor = str_replace_all(key_order, ":", ".")) %>% 
  write_csv("data/ferc_lookup_receptor.csv")

read_csv("data/tags_tethys_stressors.csv") %>% 
  distinct(key_order) %>% 
  transmute(
    stressor = str_replace_all(key_order, ":", ".")) %>% 
  write_csv("data/ferc_lookup_stressor.csv")

write_csv(ferc_docs, ferc_docs2_csv)
```