ingest_meta-db.Rmd

---
title: "Ingest CCIEA Metadata Database"
author: "Ben Best"
date: "5/12/2021"
output:
  html_document:
    code_folding: hide
    toc: true
    toc_float: true
    toc_depth: 4    
editor_options: 
  chunk_output_type: console
---

```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = TRUE, message = F, warning = F)

if (!require(librarian)){
  install.packages("librarian")
  library(librarian)
}
shelf(
  dplyr, DT, fs, glue, googledrive, googlesheets4, here, htmltools, purrr, readr)
```

## Read Metadata

- Source: [ERDDAP_CCIEA_database_2021.20210317 - Google Sheets](https://docs.google.com/spreadsheets/d/1F8H2UFcajLVqq_MIPS0YAUt3ZnSSJP7cZ1hxCsMLW4g/edit?ts=6067b512#gid=747268021)

```{r}
# GoogleSheet and GoogleDrive locations
#   shared to shares@iea-uploader.iam.gserviceaccount.com
gs_url <- "https://docs.google.com/spreadsheets/d/1F8H2UFcajLVqq_MIPS0YAUt3ZnSSJP7cZ1hxCsMLW4g/edit"
gd_url <- "https://drive.google.com/drive/u/2/folders/1seUbRmpwqhOyTjuWIndBql-m6Z0wvjxx"
  
# password authorization file for shares@iea-uploader.iam.gserviceaccount.com
gs_json <- "/Volumes/GoogleDrive/My Drive/projects/iea-auto/data/iea-uploader-27c589771060.json"
stopifnot(file.exists(gs_json))

gs4_auth(path = gs_json)
drive_auth(path = gs_json)

doc  <- read_sheet(gs_url, "Documentation", skip = 2)
meta <- read_sheet(gs_url, "Indicator Metadata")

sheet_names(gs_url)
# "Views"              "Documentation"      "Indicator Metadata"
```

## User Files

To test uploading experience in Meta-app.

### Data Files

Ben's [ca](https://drive.google.com/drive/u/2/folders/1sZpQddkYmT71_hJxDVeKR8jBPnS_7w5H) / [data](`r gd_url`):

```{r}
data_files <- drive_ls(gd_url, recursive = FALSE) %>% 
  mutate(
    url  = glue("https://drive.google.com/file/d/{id}/edit"),
    gd_file = glue("<a href='{url}' target='_blank'>{name}</id>")) %>% 
  rename(basename = name)

data_files %>% 
  select(gd_file) %>% 
  datatable(
    escape=F,
    extensions = 'Buttons',         
    options = list(
      dom = 'Bfrtip',
      buttons = c('copy', 'csv', 'excel', 'pdf', 'print'),
      pageLength = nrow(data_files),
      lengthMenu = c(5, 20, 50, nrow(data_files))))
```

### **files_PI**

Summary of `indicator name in PI file` in **Indicator Metadata** tab of [ERDDAP_CCIEA_database_2021.20210317 - Google Sheet](https://docs.google.com/spreadsheets/d/1F8H2UFcajLVqq_MIPS0YAUt3ZnSSJP7cZ1hxCsMLW4g/edit?ts=6067b512#gid=747268021) and if match in data/ files above in `gd_data`.

```{r}
files_pi <- read_sheet(gs_url, "files_PI")
write_csv(files_pi, "data/files_pi.csv")

googledrive::drive_auth()
# 1: ben@ecoquants.com


# download files
files_pi %>% 
  pwalk(function(gd_file_url, gd_file,...){
    # gd_file <- 'species_landings_2021.csv'
    # gd_file_url <- 'https://drive.google.com/file/d/11TDW38l3EuHnu47CfTZ2g5Pto56LlIT0/edit'
    f <- here(glue("data/raw/{gd_file}"))
    drive_download(gd_file_url, f, overwrite = T) })
    
View(files_pi)

meta_files <- meta %>% 
  filter(
    !is.na(`PI filename`),
    serve_flag == 1) %>% 
  group_by(`PI filename`, `ERDDAP Dataset ID`) %>% 
  summarize(
    vars_erddap = paste(`Variable Name/ERDDAP`, collapse = ", "),
    .groups = "drop") %>% 
  mutate(
    basename = basename(`PI filename`)) %>% 
  left_join(
    data_files %>% 
      select(basename, url, gd_file), by = "basename")

meta_files  %>% 
  select(basename, `PI filename`, `ERDDAP Dataset ID`, gd_file) %>% 
  arrange(gd_file, basename, `ERDDAP Dataset ID`) %>% 
  datatable(
    escape=F,
    extensions = 'Buttons',
    options = list(
      dom = 'Bfrtip',
      buttons = c('copy', 'csv', 'excel', 'pdf', 'print'),
      pageLength = nrow(meta_files),
      lengthMenu = c(5, 20, 50, nrow(meta_files))))
```

#### organize files_pi into datasets

```{r}
files_pi <- read_csv("data/files_pi.csv", show_col_types=F)
# View(files_pi) # names(files_pi)
files_pi <- files_pi %>% 
  rename(
   ed_id = `ERDDAP Dataset ID`) %>% 
  arrange(gd_file) %>% 
  mutate(
    path_0  = here(glue("data/raw/{gd_file}")),
    path    = here(glue("data/upload/{ed_id}/{gd_file}")),
    path_ok = map2_lgl(
      path_0, path, function(p0, p){
        if (file.exists(p0) & !file.exists(p)){
          dir_create(dirname(p))
          file_move(p0, p)
        }
        if (file.exists(p) & !file.exists(p0))
          return(TRUE)
        FALSE
      }))

files_pi <- read_csv("data/files_pi.csv", show_col_types=F)

files_pi_dupes <- files_pi %>% 
  filter(duplicated(gd_file)) %>% 
  distinct(gd_file)

files_pi %>% 
  inner_join(
    files_pi_dupes,
    by = "gd_file") %>% 
  select(gd_file, ed_id, path_ok) %>% 
  arrange(gd_file, ed_id, desc(path_ok)) %>% 
  write_csv("data/files_pi_same-file-diff-dataset.csv")

read_csv(
  "data/files_pi_same-file-diff-dataset.csv", show_col_types=F) %>%
  datatable()
```

## test: cciea_B_AS_DENS

```{r}
librarian::shelf(
  glue, here, readr, yaml)

up_data_csv <- here(glue("data/upload/cciea_B_AS_DENS/Seabird - Ce CC at-sea densities 12-3-19.csv"))
up_meta_yml <- here(glue("data/upload/cciea_B_AS_DENS/Seabird - Ce CC at-sea densities 12-3-19_meta.yml"))

# read uploaded file
up_data <- read_csv(up_data_csv) # View(up_data)

# Which dataset?
ed_id <- "cciea_B_AS_DENS"

# get metadata
ed_meta_yml <- glue("https://github.com/noaa-iea/ecoidx/raw/main/data-raw/{ed_id}_meta.yml")
ed_meta <- read_yaml(ed_meta_yml) # listviewer::jsonedit(ed_meta)

# get data
ed_data_csv <- glue("https://github.com/noaa-iea/ecoidx/raw/main/data-raw/{ed_id}_raw.csv")

# read csv
ed_data <- read_csv(ed_data_csv) # View(ed_data)

# match columns in upload to erddap dataset
up_meta <- list(
  columns = list())

# Time?
up_meta$columns$time <- list(
  time = "year")
# TODO: transform year to datetime

# Other index(es)?
up_meta$columns$index <- list(
  species_cohort = "timeseries")

# Value(s)?
up_meta$columns$values <- list(
  density_anomaly = "anomaly")
# TODO: rename `values` to `metric`

# Error(s)?

# write 
write_yaml(up_meta, up_meta_yml)
up_meta <- read_yaml(up_meta_yml)
# listviewer::jsonedit(up_meta)
```




## test: cciea_EI_HCI

```{r}
librarian::shelf(
  fs, glue, here, readr, yaml)

up_data_csv <- here(glue("data/upload/cciea_EI_HCI_new/ei_hci_rgn1_M.csv"))
#up_data_csv <- here(glue("data/upload/cciea_EI_HCI_new/ei_hci_rgn2_M.csv"))
up_meta_yml <- glue("{path_ext_remove(up_data_csv)}_meta.yml")

# read uploaded file
up_data <- read_csv(up_data_csv) # View(up_data)

# Which dataset?
ed_id <- "cciea_EI_HCI"

# get metadata
#ed_meta_yml <- glue("https://github.com/noaa-iea/ecoidx/raw/main/data-raw/{ed_id}_meta.yml")
ed_meta_yml <- glue("/Users/bbest/github/noaa-iea/ecoidx/data-raw/{ed_id}_meta.yml")
ed_meta <- read_yaml(ed_meta_yml) # listviewer::jsonedit(ed_meta)

# get data
ed_data_csv <- glue("https://github.com/noaa-iea/ecoidx/raw/main/data-raw/{ed_id}_raw.csv")

# read csv
ed_data <- read_csv(ed_data_csv) # View(ed_data)

# match columns in upload to erddap dataset
up_meta <- list(
  dataset = list(),
  columns = list())

# Dataset?
up_meta$dataset = "cciea_EI_HCI"

# Matching columns in dataset?
up_meta$columns <- list(
  time = list(
    transform      = 'to_time("%Y-%m")', # %>% to_time("%Y-%m")
    column_dataset = "time"),
  data = list(
    column_dataset = "hci_regn1"))

# TODO: transform year to datetime

# Other index(es)?
up_meta$columns$index <- list(
  species_cohort = "timeseries")

# Value(s)?
up_meta$columns$values <- list(
  density_anomaly = "anomaly")
# TODO: rename `values` to `metric`

# TODO: Error(s)?

# TODO: column-specific metadata
# https://oceanview.pfeg.noaa.gov/erddap/info/cciea_EI_HCI/index.html
# variable	hci_regn1	float
# attribute	hci_regn1 actual_range	float	0.0, 1.0
# attribute	hci_regn1	ioos_category	String	Other
# attribute	hci_regn1	long_name	String	Habitat Compression Index, 43.5-48N
# attribute	hci_regn1	units

# write 
write_yaml(up_meta, up_meta_yml)
up_meta <- read_yaml(up_meta_yml)
# listviewer::jsonedit(up_meta)
```

## Components

```{r, eval = F}
d_cmp <- readxl::read_excel("/Users/bbest/Desktop/ERDDAP_CCIEA_database_2021.20210317.xlsx", "Indicator Metadata") %>% 
  select(dataset_id=`ERDDAP Dataset ID`,	component=`Component Section`) %>% 
  group_by(dataset_id) %>% 
  summarize(
    component = first(component)) %>% 
  mutate(
    cmp = str_replace(dataset_id, "cciea_([A-Z]+).*", "\\1")) %>% 
  group_by(cmp) %>% 
  summarize(
    component = first(component)) %>% 
  filter(!is.na(cmp))

dir_ecoidx <- "/Users/bbest/github/noaa-iea/ecoidx"
datasets_csv <- file.path(dir_ecoidx, "data-raw/_cciea_datasets.csv")
d_datasets <- read_csv(datasets_csv, show_col_types = F) %>% 
  mutate(
    cmp = str_replace(dataset_id, "cciea_([A-Z]+).*", "\\1"))
d_other <- d_datasets %>% 
  anti_join(
    d_cmp,
    by = "cmp") %>% 
  select(cmp) %>% 
  mutate(
    component = "Newport Hydrographic Line")

d_cmp <- bind_rows(
  d_cmp,
  d_other)

d_cmp <- d_cmp %>% 
  arrange(cmp, component)

write_csv(d_cmp, "data/lut_components.csv")
```


## TODO: juba/shinyglide

#### TODO: search for unmatched elsewhere

Like here:

- [2021 Data - Google Drive](https://drive.google.com/drive/u/3/folders/1OXHLGN8xyVSyvpAFnDp7Jrv3GvdW9fVW)

#### Write **files_pi** to Google Sheet

- Write to **files_pi** tab in [ERDDAP_CCIEA_database_2021.20210317 - Google Sheet](https://docs.google.com/spreadsheets/d/1F8H2UFcajLVqq_MIPS0YAUt3ZnSSJP7cZ1hxCsMLW4g/edit?ts=6067b512#gid=1746509196)

```{r, eval=F}
files_PI <- read_sheet(gs_url, "files_PI") %>% 
  filter(!is.na(gd_file))

meta_files %>% 
  select(gd_file, basename, url, `PI filename`, `ERDDAP Dataset ID`, `vars_erddap`) %>% 
  anti_join(
    files_PI, by = "basename") %>% 
  bind_rows(
    files_PI) %>% 
  arrange(gd_file, basename) %>% 
  mutate(
    gd_file = ifelse(
      !is.na(url),
      glue('=HYPERLINK("{url}", "{basename}")'),
      NA) %>% gs4_formula) %>% 
  select(-url) %>% 
  write_sheet(gs_url, "files_PI")
```

### **files_unmatched**

Files found in Ben's [ca](https://drive.google.com/drive/u/2/folders/1sZpQddkYmT71_hJxDVeKR8jBPnS_7w5H) / [data](`r gd_url`) but not matched with PI files (`indicator name in PI file`).  

```{r}
files_unmatched <- data_files %>%
  anti_join(meta_files, by="basename")

files_unmatched %>% 
  select(gd_file) %>% 
  datatable(
    escape=F,
    extensions = 'Buttons',
    options = list(
      dom = 'Bfrtip',
      buttons = c('copy', 'csv', 'excel', 'pdf', 'print'),
      pageLength = nrow(files_unmatched),
      lengthMenu = c(5, 20, 50, nrow(files_unmatched))))
```

#### Write **files_unmatched** to Google Sheet

- Write to **files_unmatched** tab in [ERDDAP_CCIEA_database_2021.20210317 - Google Sheet](https://docs.google.com/spreadsheets/d/1F8H2UFcajLVqq_MIPS0YAUt3ZnSSJP7cZ1hxCsMLW4g/edit?ts=6067b512#gid=260570123)

```{r, eval=F}
files_unmatched %>% 
  mutate(
    gd_file = glue('=HYPERLINK("{url}", "{name}")') %>% 
      gs4_formula) %>% 
  select(gd_file) %>% 
  write_sheet(gs_url, "files_unmatched")
```

## Metadata

### original: Documentation

```{r}
datatable(doc)
```

### original: Indicator Metadata

```{r}
datatable(meta)
```

## Normalize Metadata

### normalized: providers

```{r}
providers <- meta %>% 
  select(
    pi  = PI, 
    email = Contact) %>% 
  group_by_all() %>% 
  summarize(.groups = "drop")
write_csv(providers, "data/providers.csv")
datatable(providers)
```

### normalized: institutions

```{r}
institutions <- meta %>% 
  select(
    institution = Institution) %>% 
  group_by_all() %>% 
  summarize(.groups = "drop")
write_csv(institutions, "data/institutions.csv")
datatable(institutions)
```

### normalized: components

```{r}
components <- meta %>% 
  select(
    component    = `Component Section`,
    subcomponent = Subcomponent) %>% 
  group_by_all() %>% 
  summarize(.groups = "drop")
write_csv(components, "data/components.csv")
datatable(components)
```

### normalized: datasets

```{r}
datasets <- meta %>% 
  select(
    dataset_id  = `ERDDAP Dataset ID`) %>% 
  group_by_all() %>% 
  summarize(.groups = "drop")
write_csv(datasets, "data/datasets.csv")
datatable(datasets)
```

### normalized: timeseries

```{r}
timeseries <- meta %>% 
  select(
    timeseries_id = `CCIEA timeseries ID`,
    dataset_id    = `ERDDAP Dataset ID`,
    institution   = Institution) %>% 
  group_by_all() %>% 
  summarize(.groups = "drop")
write_csv(timeseries, "data/timeseries.csv")
datatable(timeseries)
```

### normalized: region

```{r}
regions <- meta %>% 
  select(
    region = region) %>% 
  group_by_all() %>% 
  summarize(.groups = "drop")
write_csv(regions, "data/regions.csv")
datatable(regions)
```

### normalized: vars

```{r}
vars <- meta %>% 
  select(
    var_id      = `Variable Name/ERDDAP`,
    var_label   = `Y-axis label (long variable name)`,
    var_units   = `Units`) %>% 
  group_by_all() %>% 
  summarize(.groups = "drop")
write_csv(vars, "data/vars.csv")
datatable(vars)
```

### normalized: dataset_vars

```{r}
dataset_vars <- meta %>% 
  select(
    dataset_id    = `ERDDAP Dataset ID`,
    var_id        = `Variable Name/ERDDAP`,
    fld_orig      = `indicator name in PI file`,
    pi            = PI,
    region, latitude, longitude,
    institution   = Institution,
    sampling_freq = `Sampling frequency`,
    sci_name      = `Scientific name`,
    component     = `Component Section`,
    subcomponent  = Subcomponent) %>% 
  mutate(
    across(where(is.list), as.character)) %>% 
  group_by_all() %>% 
  summarize(.groups = "drop")
write_csv(dataset_vars, "data/dataset_vars.csv")
datatable(dataset_vars)
```