--- 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)) ```