Creating Summary Tables

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.

# 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.

# 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.

  # 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.

# 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.

# 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.

# 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.

# 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.

  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.

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