---
title: "Creating Summary Tables"
output: rmarkdown::html_vignette
description: |
  Basics of create Mission Director Briefer Tables
vignette: >
  %\VignetteIndexEntry{Creating Summary Tables}
  %\VignetteEngine{knitr::rmarkdown}
  %\VignetteEncoding{UTF-8}
---
  
```{r, include = FALSE}
knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>"
)
```


# GHSD MSD Summary Tables

The MSD Summary Tables are an SI product created on a quarterly basis using the latest OU_IM or PSNU_IM MER Structured dataset (MSD). Two tables are produced, one which summarizes the current achievement for a set of core (main) indicators, and another which summarizes TX_MMD, VLS, and VLC (treatment). 

Starting in FY21 Q2, the SI team migrated the production of the tables from Tableau to R to reduce the manual burden of creating and copy and pasting the tables into over 50 briefers. The SI team created a package to automate the data munging required for the tables. The `selfdestructin5` package is the result of these efforts. 

Below we review the process for creating the main and treatment tables.

```{r setup, eval=F, echo=T, include = T}
# Load libraries needed for table creation (glitr, glamr and gophr are OHA-SI packages not on CRAN)
  library(gagglr)
  library(tidyverse)
  library(gt)
  library(selfdestructin5)
```

Load helper functions/paths from other SI packages.

```{r read in data, eval=F, echo=T}

# Set up paths 
    mdb_out <- "../../Sandbox/" #Alter this path to where you'd like saved tables to go
    merdata <- si_path("path_msd")
    msd_path <- return_latest(folderpath = merdata, pattern = "OU_IM")
    load_secrets()
    
# Load OU_IM table - using FY23 Q3 data from Panorama
    ou_im <- read_psd(msd_path)

# With the new updates, you only need to set the meta data one time using the set_metadata() function
# This new approach stores the metadata in a package environment
    set_metadata(gophr::get_metadata(msd_path))
```

With our data loaded and time objects created, we are ready to munge the data. Let's start with the main table.
```{r munge ou_im msd, eval=F, echo=T}
  # Main Table
   # Create the long mdb_df of the main summary indicators 
   # This will remove mechs with known issues by default. If you want to keep all mechs set `resolve_issues == FALSE`
      summary_df   <- make_mdb_df(ou_im)
    
   # Create the reshaped df that is gt() ready
      summary_tbl  <- reshape_mdb_df(summary_df)
    
   # the `agg_type` column flags the operatingunit as either OU, Region-Country or Agency
      summary_tbl %>% distinct(agg_type, operatingunit) %>% slice(1:15)
```

The `mdb_tbl` data frame is a wide-shaped data frame that contains embedded svg icons from the `fontawesome` package. We can pass this to the `create_mdb` function to make a table for a desired operating unit.

```{r create main table, eval=F, echo=T}

# Generate base table for global results
    create_mdb(summary_tbl, ou = "Global", type = "main")

# Try a specific country now
    create_mdb(summary_tbl, ou = "Zambia", type = "main")
    
# Or a regional program
    create_mdb(summary_tbl, ou = "Asia Region-Indonesia", type = "main")

```

The steps are similar for creating the treatment table.
```{r create treatment table, eval=F, echo=T}

# Create the treatment data frame needed for derived indicators
    summary_df_tx    <- make_mdb_tx_df(ou_im, resolve_issues = F)
    summary_tbl_tx   <- reshape_mdb_tx_df(summary_df_tx)
    
    create_mdb(summary_tbl_tx, ou = "Global", type = "treatment")
```

# Batching Tables
The reshape functions for the main and treatment indicators return a single data frame of all operating units in PEPFAR, including a Global and region-country level. Using the `agg_type` column, we can define a list of operating units over which we can batch create tables. 

```{r function to create operating unit lists, eval=F, echo=T}
# First, define a function to return the distinct levels in each aggregation type

# create batch tables
 distinct_agg_type <- function(df, type = "OU"){
   df %>% 
     filter(agg_type == {{type}}) %>% 
     distinct(operatingunit) %>% 
     pull()
  }
  
  # Write the different types to character objects
  ous <- distinct_agg_type(summary_tbl, "OU")
  glb <- distinct_agg_type(summary_tbl, "Agency")
  rgl <- distinct_agg_type(summary_tbl, "Region-Country")
  
  # Use purr to map across the list and create tables for all entries in each object
  purrr::map(ous[2:3], ~create_mdb(summary_tbl, ou = .x, type = "main") %>% 
        gtsave(., path = mdb_out, filename = glue::glue("{.x}_{pkg_env$meta$curr_pd}_mdb_main.png")))
  
  
  # TREATMENT
  ous_tx <- distinct_agg_type(summary_tbl_tx, "OU")
  
  map(ous[10:12], ~create_mdb(summary_tbl_tx, ou = .x, type = "treatment") %>% 
        gtsave(., path = mdb_out, filename = glue::glue("{.x}_{pkg_env$meta$curr_pd}_mdb_treatment.png")))
```

# Creating bespoke tables
If there is a core indicator on which you'd like to focus across a list of operating units, it is possible to use the main or treatment data frames to create a custom table of OUs by indicator. For example, say we would like to compare Zambia, Malawi, Mozambique, Tanzania, and Zimbabwes's HTS_TST_POS performance. We can filter the `mdb_tbl` data frame by a given indicator.

```{r custom table, eval=F, echo=T}

# Filter existing wide data frame to desired indicator and OUs
  mdb_tbl_hts_tst <- summary_tbl %>% 
    filter(indicator == "TX_CURR", agency == "USAID") %>% 
    filter(operatingunit %in% c("Malawi", "Zambia", "Tanzania", "Mozambique", "Zimbabwe"))


# Pass resulting data frame to the mdb_main_theme() with a bit of rearranging to get a desired sort order.
  mdb_tbl_hts_tst %>% 
    mutate(operatingunit = fct_reorder(operatingunit, present_targets_achievement, .desc = T)) %>%  
    arrange(agency, operatingunit) %>% 
    gt(groupname_col = "agency") %>% 
    mdb_main_theme() %>% 
    cols_unhide("operatingunit") %>% 
    cols_align(
      align = "left",
      columns = operatingunit
    ) %>% 
    cols_hide(indicator2) %>% 
    tab_header(
      title = glue::glue("HTS_TST Comparison Across OUs")
    )

```

You can also create a comparison table of OU achievement by core indicators. With a couple reshapes and filters, you can generate summary table organized in descending order by TX_CURR. 

```{r custom tables II, eval=F, echo=T}

  summary_tbl %>% 
    filter(agency == "USAID", agg_type == "OU") %>% 
    select(operatingunit, present_targets_achievement, indicator) %>% 
    pivot_wider(names_from = indicator, 
                values_from = present_targets_achievement,
                names_sort = TRUE) %>% 
    arrange(desc(TX_CURR)) %>% 
    gt() %>% 
    sub_missing(columns = -c("operatingunit"), missing_text = "-") %>% 
    fmt_percent(columns = -c("operatingunit"), decimals = 0) %>% 
    cols_label(operatingunit = "") %>% 
    tab_options(
      source_notes.font.size = 8,
      table.font.size = 13, 
      data_row.padding = gt::px(5)
    ) %>% 
    tab_header(title = glue::glue("USAID OU PERFORMANCE SUMMARY FOR {pkg_env$meta$curr_pd}"))
```

If you wish the create tables by implementing partner, you could do something as below.
```{r Partner tables, eval=F, echo=T}
# Create a custom function to pull IP tables
# Filter the MSD to the partner of focus before passing the data to the reshape_msd_df()
  mk_ptr_tbl <- function(df, mech_id)  {
    
    ip_mdb <- 
      df %>% 
      filter(mech_code == mech_id) %>% 
      make_mdb_df() %>% 
      reshape_mdb_df(.) 
    
    mech_name <-  
      df %>% 
      filter(mech_code == mech_id) %>%
      distinct(mech_name) %>% 
      pull(mech_name)
    
    ip_mdb %>%   
      create_mdb(ou = "Minoria", type = "main") %>% 
      tab_header(
        title = glue::glue("{mech_name} PERFORMANCE SUMMARY")
      ) %>% 
      gtsave(path = "Images", filename = glue::glue("{mech_name}_mdb_main.png"))
  }


# Loop over function and create tables for each of the main C&T mechs
  mech_list <- c(123456, 789101, 654321)
  map(mech_list, ~mk_ptr_tbl(ou_im, .x))

```

