Extract data from PLLs and compare with other date sources

[jaliasd]

Working with ICPI (see ticket) , extract relevent data sources from PLL for ulitmate comparison with MSD (Datim) and budget/outlay rackup from Katherine's team (here)

Ultimate goal is to have a structured dataset that has elements triangulated from MER, budget, and ER.

• [x] 1 Copy and paste data from PLLs (w help of ICPI)

• [x] 2 create structured dataset from MSD and budget data (linked above)

• [ ] 3 create dataset from item 1

• [ ] 4 Combine datasets and analyize

[jaliasd]

ICPI completed 1 here

@achafetz completed 2 data located here

[jaliasd]

@achafetz need a little help iterating through this. I tried wrapping this in a function and using map, but no luck. Each of the four spreadsheets needs to have the same code run on them and then have the output combined. Halp?

`pll_merj <- read_xlsx(file.path(folderpath, "PLL_datacall_template_jd.xlsx"), sheet = "FY20 targets", skip = 1) %>% gather(disag, value, -ou, -indicator, -period ) mutate(class = "mer") mutate(source = "pll")

pll_moneyj <- read_xlsx(file.path(folderpath, "PLL_datacall_template_jd.xlsx"), sheet = "Outlays vs Budget table", skip = 1) %>% gather(meta, value, -ou, -funding_agency) %>% separate(meta, c("indicator", "period"), ",") %>% rename("disag" = "funding_agency") %>% mutate(period = as.double(period)) mutate(class = budget) mutate(source = "pll")

pllj <- full_join(pll_merj, pll_moneyj) rm(pll_merj, pll_moneyj)`

Here's what I attempted

`get_pll <- function(file) { read_xlsx(file, sheet = "FY20 targets", skip = 1) %>% gather(disag, value, -ou, -indicator, -period) }

pll_all <- map(.x = files, .f = ~get_pll(.x)) `

[jaliasd]

Dataset posted here

code here

There are some peccadilloes with this as with MSD. Selection of disaggrates is important as is selection of source. Suggest using disaggrates = total for starters. I will update the raw data .csv with an xlsx with pivots asap. @lucasrachel it is worth thinking about how I can remotely support someone else to continue the analysis.