PEPFAR Academy Pivot Table Content

[achafetz]

In preparation for the PEPFAR Academy, the DIV/GCB team will put together material for a number of short (1-5 minute) sessions on creating pivot tables in Excel using the MSD.

@ICPI/guidance-capacity-building members (and other volunteers) should sign up for one of the session below and outline the content. Update the markdown file with your session material. We review all sessions prior to the Dec 11 training session where we will record them and make a course on PEPFAR Academy.

**Sign up for sessions below and complete outline on the markdown document **

Pivot Table Training

1. What is a pivot table? (Aaron)
2. Data structure (tidy data) (Aaron)
3. How to create a pivot table
4. Different parts of a pivot table
5. Adding in data
6. Structural options
7. Filtering
8. Including Slicers
9. What are calculated indicators and how to create them
10. Building pivot charts (Shazad)
11. Components to consider when building an Excel tool (Shazad)

UPDATED Feb 8 Data set to use - Training Dataset (FY18Q4)

[shapaklyak]

@achafetz @abrahamagedewICPI do we need to update the training dataset for this course? fyi @imujawar @jb3436

[shapaklyak]

hi all, please login to the PEPFAR Virtual Academy to take a look at our course outline in open edX.

[shapaklyak]

Here's a snapshot

Course Outline _ ICPI Workspace - Sandbox _ Studio.pdf

[achafetz]

I updated the masked dataset again from the initial to the cleaned data. Masked datasets are accessible on the ICPI/TrainingDataset repo

[achafetz]

I'm attaching the subset training dataset to use for our videos. It includes just HTS (HTS_TST, HTS_TST_POS, HTS_TST_NEG).

FY18Q4c_TRAINING_MSD_HTS.zip

#CODE FOR PVA TRAINING DATASET

#dependencies
library(tidyverse)
library(fs)
library(ICPIutilities)

#training dataset url on Github
dataset_url <- "https://media.githubusercontent.com/media/ICPI/TrainingDataset/master/Output/MER_Structured_TRAINING_Dataset_PSNU_IM_FY17-18_20181221_v2_1.txt"

#import
df_trn <- read_msd(dataset_url, to_lower = FALSE, save_rds = FALSE)

#create a temporary local save location
(tmp <- dir_create(file_temp()))

#save name
filename <- file.path(tmp, "FY18Q4c_TRAINING_MSD_HTS.txt")

#filter and export
df_trn %>% 
  filter(str_detect(indicator, "HTS_TST")) %>% 
  write_tsv(filename, na = "")

#zip folder name
zipname <- str_replace(filename, "txt", "zip")

#zip
zip(zipname, filename, extras = "-j")

[shapaklyak]

@achafetz i'm having a hard time opening the training dataset...i keep getting sent through a bunch of folders matroyshka (nesting doll) style

[jb3436]

@achafetz
Is it possible for this file to not be buried in 8 sub-folders? I was able to access it, but it makes some people's computers freeze.

[achafetz]

😬 oh boy that is a mess! Apparently I was missing an option in the zip() function to not include the full file tree (extras = "-j").

zipped filed updated here and code is updated above.

FY18Q4c_TRAINING_MSD_HTS.zip

[shapaklyak]

@achafetz thank you! is this stored in a file/folder on the DIV repo or another repo currently? If not, can we put it here or somewhere else that makes sense? I'd like to try to link to it from the PVA course https://github.com/ICPI/DIV/tree/master/Training/PVA_PEPFARBetweentheSheets

[achafetz]

That makes sense. It's just linked in this issue, but I think it makes sense to add it to the training site. I'll do that now.