-
Notifications
You must be signed in to change notification settings - Fork 0
/
create-mdb-tables.Rmd
207 lines (158 loc) · 8.08 KB
/
create-mdb-tables.Rmd
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
---
title: "Create MDB Tables"
output: rmarkdown::html_vignette
description: |
Basics of create Mission Director Briefer Tables
vignette: >
%\VignetteIndexEntry{Create MDB Tables}
%\VignetteEngine{knitr::rmarkdown}
%\VignetteEncoding{UTF-8}
---
```{r, include = FALSE}
knitr::opts_chunk$set(
collapse = TRUE,
comment = "#>"
)
```
# Mission Director Briefer Tables
The Mission Director Briefer (MDB) Tables are an SI product that is created on a quarterly basis using the latest OU_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.
Starting in FY21 Q2, the SI team migrated the production of the tables from Tableau to R. Eventually, the SI team created a package to automate much of the data munging required for the tables. The `selfdestructin5` package is the result of these efforts. Even with the winding down of the MDB Tables, the SI team continues to maintain the package due to its value to the team and overseas staff.
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)
# Get the metadata from the MSD dataset; This is used to set the period and source.
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`
mdb_df <- make_mdb_df(ou_im)
# Create the reshaped df that is gt() ready
mdb_tbl <- reshape_mdb_df(mdb_df, metadata$curr_pd)
# the `agg_type` column flags the operatingunit as either OU, Region-Country or Agency
mdb_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(mdb_tbl, ou = "Global", type = "main", metadata$curr_pd, metadata$source)
# Try a specific country now
create_mdb(mdb_tbl, ou = "Zambia", type = "main", metadata$curr_pd, metadata$source)
# Or a regional program
create_mdb(mdb_tbl, ou = "Asia Region-Indonesia", type = "main", metadata$curr_pd, metadata$source)
```
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
mdb_df_tx <- make_mdb_tx_df(ou_im, resolve_issues = F)
mdb_tbl_tx <- reshape_mdb_tx_df(mdb_df_tx, metadata$curr_pd)
create_mdb(mdb_tbl_tx, ou = "Global", type = "treatment", metadata$curr_pd, metadata$source)
```
# 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(mdb_tbl, "OU")
glb <- distinct_agg_type(mdb_tbl, "Agency")
rgl <- distinct_agg_type(mdb_tbl, "Region-Country")
# Use purr to map across the list and create tables for all entries in each object
purrr::map(ous, ~create_mdb(mdb_tbl, ou = .x, type = "main", metadata$curr_pd, metadata$source) %>%
gtsave(., path = mdb_out, filename = glue::glue("{.x}_{metadata$curr_pd}_mdb_main.png")))
# TREATMENT
ous_tx <- distinct_agg_type(mdb_tbl_tx, "OU")
map(ous, ~create_mdb(mdb_tbl_tx, ou = .x, type = "treatment", metadata$curr_pd, metadata$source) %>%
gtsave(., path = mdb_out, filename = glue::glue("{.x}_{metadata$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 <- mdb_tbl %>%
filter(indicator == "HTS_TST_POS", 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(metadata$curr_pd, metadata$curr_pd) %>%
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}
mdb_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 {metadata$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(., metadata$curr_pd)
mech_name <-
df %>%
filter(mech_code == mech_id) %>%
distinct(mech_name) %>%
pull(mech_name)
ip_mdb %>%
create_mdb(ou = "Minoria", type = "main", metadata$curr_pd, metadata$source) %>%
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(df_msd, .x))
```