identify_ou.R pulling wrong cell reference?

[jaliasd]

#pull OU name from PLL sheet & store ou <- readxl::read_excel(filepath, sheet = "1 PLL", range = "E4") %>% names()

But I looked at two FASTs and the OU name (the link to it, at least) was in G4

I got the following error when I tried to run it with mapr_dfr

Error: Columnoperatingunitmust be length 1131 (the number of rows) or one, not 0

[achafetz]

Thanks @jaliasd for point this out! I think the change occurred between what was Reviewed by OGAC and what was submitted.

I'm going to download all the files from Google Drive and run the below function on it to see if all of them have the file name now in G4.

 test_ouname_loc <- function(filepath){

   file <- print(basename(filepath))

   #pull OU name from PLL sheet & store
   e4 <- names(readxl::read_excel(filepath,
                            sheet = "1 PLL",
                            range = "E4"))

   tibble::tibble(path = file,
                  col_e4 = e4)

 }

[achafetz]

haha. except now G4, not E4

 test_ouname <- function(filepath){

   file <- print(basename(filepath))

   #pull OU name from PLL sheet & store
   g4 <- names(readxl::read_excel(filepath,
                            sheet = "1 PLL",
                            range = "G4"))

   tibble::tibble(path = file,
                  col_g4 = g4)

 }

[achafetz]

Winner winner chicken dinner :chicken:

Let me update the function now :smile:

# FUNCTION ----------------------------------------------------------------

 test_ouname <- function(filepath){

   #extract file name, not path for df
   file <- print(basename(filepath))

   #pull OU name from PLL sheet & store
   g4 <- names(readxl::read_excel(filepath, sheet = "1 PLL", range = "G4"))

   #create df for combining
   tibble::tibble(path = file, col_g4 = g4)

 }

# IDENTIFY ALL FAST TOOLS -------------------------------------------------

files <- list.files("C:/Users/achafetz/Downloads/FASTs 2-21-19", full.names = TRUE)

# RUN FUNCTION ------------------------------------------------------------

purrr::map_dfr(.x = files,
               .f = test_ouname)
#> [1] "2-14-2019Zambia_COP19 FAST MASTERSubmission_SGACReview.xlsx"
#> [1] "2019-02-14 South Africa_COP19 FAST Master File - FINAL Submit_SGACReview.xlsx"
#> [1] "2019-02-21-Ukraine_COP19 FAST-Final for submission_SGACReview.xlsx"
#> [1] "2019-02-21_ Vietnam_COP19_Consolidated FAST _New template.xlsx"
#> [1] "Angola_COP19 FAST 02.19.19 FINAL_SGACReview.xlsx"
#> [1] "Botswana_COP19 FAST_Combined FEB14 2019_SGACReview.xlsx"
#> [1] "CDC-USAID_Ethiopia_COP19 FAST  2.14.19_1407_SGACReview_v2.xlsx"
#> [1] "COP19 FAST_Namibia (Original)_02142019_SGACReview.xlsx"
#> [1] "COP19_Burundi_FAST_Feb7_SGACReviewed_v3_2.21.19.xlsx"
#> [1] "Coted_Ivoire_COP19 FAST_SGACCheck_Submission.xlsx"
#> [1] "Dominican Republic_COP19 FAST.xlsx"
#> [1] "DRC_COP19 FAST.xlsx"
#> [1] "Eswatini_COP19 FAST_Feb 14 Final_SGACReview.xlsx"
#> [1] "Haiti_COP19 FAST.xlsx"
#> [1] "Kenya_Consolidated COP19 FAST (Feb 7 Submission)_SGACReview_v2.xlsx"
#> [1] "Lesotho_COP19 FAST v5_WITH FBO_SGACReview.xlsx"
#> [1] "Malawi FAST Consolidated Zero Draft 02082019_SGACReview_v2.xlsx"
#> [1] "MASTER FAST Uganda_COP19 FAST 2.12.2019_12pm_SGACReview_v2.xlsx"
#> [1] "Mozambique COP19 FAST SGAC Review- 2.21.19.xlsx"
#> [1] "Nigeria_COP19 FAST.xlsx"
#> [1] "PEPFAR Cameroon COP19 FAST - Feb 21 2019.xlsx"
#> [1] "Rwanda_COP19 FAST_MASTER_2.7.19_For Submission_SGACReview_v2.xlsx"
#> [1] "South Sudan-COP19 FAST_1930v_02072019_Final_SGACReview_v2.xlsx"
#> [1] "Tanzania COP 19 Consolidated FAST 2.7.19_SGACReview_v2.xlsx"
#> [1] "Zimbabwe_COP19 FAST v2_SGACedits_SGACReview.xlsx"
#> # A tibble: 20 x 2
#>    path                                                         col_g4     
#>    <chr>                                                        <chr>      
#>  1 2-14-2019Zambia_COP19 FAST MASTERSubmission_SGACReview.xlsx  Zambia     
#>  2 2019-02-14 South Africa_COP19 FAST Master File - FINAL Subm~ South Afri~
#>  3 2019-02-21-Ukraine_COP19 FAST-Final for submission_SGACRevi~ Ukraine    
#>  4 2019-02-21_ Vietnam_COP19_Consolidated FAST _New template.x~ Vietnam    
#>  5 Angola_COP19 FAST 02.19.19 FINAL_SGACReview.xlsx             Angola     
#>  6 Botswana_COP19 FAST_Combined FEB14 2019_SGACReview.xlsx      Botswana   
#>  7 CDC-USAID_Ethiopia_COP19 FAST  2.14.19_1407_SGACReview_v2.x~ Ethiopia   
#>  8 COP19 FAST_Namibia (Original)_02142019_SGACReview.xlsx       Namibia    
#>  9 COP19_Burundi_FAST_Feb7_SGACReviewed_v3_2.21.19.xlsx         Burundi    
#> 10 Coted_Ivoire_COP19 FAST_SGACCheck_Submission.xlsx            Cote d'Ivo~
#> 11 Eswatini_COP19 FAST_Feb 14 Final_SGACReview.xlsx             Eswatini   
#> 12 Kenya_Consolidated COP19 FAST (Feb 7 Submission)_SGACReview~ Kenya      
#> 13 Lesotho_COP19 FAST v5_WITH FBO_SGACReview.xlsx               Lesotho    
#> 14 Malawi FAST Consolidated Zero Draft 02082019_SGACReview_v2.~ Malawi     
#> 15 MASTER FAST Uganda_COP19 FAST 2.12.2019_12pm_SGACReview_v2.~ Uganda     
#> 16 Mozambique COP19 FAST SGAC Review- 2.21.19.xlsx              Mozambique 
#> 17 Rwanda_COP19 FAST_MASTER_2.7.19_For Submission_SGACReview_v~ Rwanda     
#> 18 South Sudan-COP19 FAST_1930v_02072019_Final_SGACReview_v2.x~ South Sudan
#> 19 Tanzania COP 19 Consolidated FAST 2.7.19_SGACReview_v2.xlsx  Tanzania   
#> 20 Zimbabwe_COP19 FAST v2_SGACedits_SGACReview.xlsx             Zimbabwe

^{Created on 2019-02-23 by the reprex package (v0.2.1)}