Largest Active and Reporting International Public Charities by Expenses

9.13.2018
Deondre' Jones

More from this project:

Largest Active and Reporting International Public Charities by Expenses

  1. library(tidyverse)
  2. library(knitr)
  3. library(stringr)
  4. library(scales)
  5. library(httr)
  6. source('https://raw.githubusercontent.com/UrbanInstitute/urban_R_theme/master/urban_theme_windows.R')
  7.  
  8.  
  9. #Create NTEE grouping categories
  10. arts <- c("A")
  11. highered <- c("B4", "B5")
  12. othered <- c("B")
  13. envanimals <- c("C", "D")
  14. hospitals <- c('E20','E21','E22','E23','E24','F31','E30','E31','E32')
  15. otherhlth <- c("E", "F", "G", "H")
  16. humanserv <- c("I", "J", "K", "L", "M", "N", "O", "P")
  17. intl <- c("Q")
  18. pubben <- c("R", "S", "T", "U", "V", "W", "Y", "Z")
  19. relig <- c("X")
  20.  
  21. #Import the Reduced NCCS Data Archive
  22. nteedocalleins <- read.csv("Data/nteedocalleins.csv")
  23.  
  24. #convert variable names to upper case
  25. names(nteedocalleins) <- toupper(names(nteedocalleins))
  26.  
  27. #This function will apply the most common NTEE Grouping categories to your data.
  28. NTEEclassify <- function(dataset) {
  29.   #merge in Master NTEE look up file
  30.   dataset <- dataset %>%
  31.     left_join(nteedocalleins, by = "EIN")
  32.   #create NTEEGRP classifications
  33.   dataset$NTEEGRP <- "  "
  34.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% arts ] <- "Arts"
  35.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% othered ] <- "Education: Other"
  36.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,2) %in% highered ] <- "Education: Higher"
  37.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% envanimals] <- "Environment and Animals"
  38.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% otherhlth] <- "Health Care: Other"
  39.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,3) %in% hospitals] <- "Health Care: Hospitals and primary care facilities"
  40.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% humanserv] <- "Human Services"
  41.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% intl] <- "International"
  42.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% pubben] <- "Other Public and social benefit"
  43.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% relig] <- "Religion related"
  44.   dataset$NTEEGRP[is.na(dataset$NTEEFINAL)] <- "Other Public and social benefit"
  45.   return(dataset)
  46. }
  47.  
  48. #Import reduced NCCS Core File Function
  49. prepcorepcfile <- function(corefilepath) {
  50.   output <- read_csv(corefilepath,
  51.                      col_types = cols_only(EIN = col_character(),
  52.                                            FISYR = col_integer(),
  53.                                            NAME = col_character(),
  54.                                            STATE = col_character(),
  55.                                            ADDRESS = col_character(),
  56.                                            CITY = col_character(),
  57.                                            ZIP = col_character(),
  58.                                            MSA_NECH = col_character(),
  59.                                            FIPS = col_character(),
  60.                                            PMSA = col_character(),
  61.                                            STYEAR = col_double(),
  62.                                            TAXPER = col_integer(),
  63.                                            OUTNCCS = col_character(),
  64.                                            OutNCCS = col_character(),
  65.                                            SUBSECCD = col_character(),
  66.                                            RULEDATE = col_character(),
  67.                                            FNDNCD = col_character(),
  68.                                            FRCD = col_character(),
  69.                                            TOTREV = col_double(),
  70.                                            EXPS = col_double(),
  71.                                            ASS_EOY = col_double(),
  72.                                            GRREC = col_double()
  73.  
  74.                      ))
  75.   names(output) <- toupper(names(output))
  76.   return(output)
  77. }
  78.  
  79. #Import NCCS Core File for given year
  80. corefile <- prepcorepcfile(as.character(paste("Data/core", "2015", "pc.csv", sep="")))
  81.  
  82. #Add NTEE Classifications to the Core File
  83. corefile <- NTEEclassify(corefile)
  84.  
  85. #Filter out of scope organizations 
  86. corefile <- corefile %>%
  87.   filter((OUTNCCS != "OUT")) %>%
  88.   filter((FNDNCD != "02" & FNDNCD!= "03" & FNDNCD != "04")) %>%
  89.   filter((NTEEGRP == "International"))
  90.  
  91. #Sort the corefile in descending order by expenses
  92. LargestExpenses <- corefile[with(corefile,order(-EXPS)),]
  93.  
  94. #Limit the list to 10
  95. LargestExpenses <- LargestExpenses[1:10,]
  96.  
  97. #Select the appropriate columns, drop the rest
  98. LargestExpenses <- LargestExpenses %>% 
  99.   select(EIN, NTEEFINAL, NTEEGRP, NAME, EXPS)
  100.  
  101. #Rename columns appropriately
  102. colnames(LargestExpenses) <- c("EIN", "NTEE Code", "NTEE Group", "Name", "Expenses")
  1. #display table
  2. kable(LargestExpenses, format.args = list(decimal.mark = '.', big.mark = ","))
EIN NTEE Code NTEE Group Name Expenses
980593375 Q33 International GAVI ALLIANCE 1,813,161,781
592174510 Q33 International FOOD FOR THE POOR INC 1,158,339,554
951922279 Q33 International WORLD VISION 993,127,196
061008595 Q33 International AMERICARES FOUNDATION INC 982,086,854
131624046 Q22 International INSTITUTE OF INTERNATIONAL EDUCATION INC 830,196,153
530242652 Q38 International NATURE CONSERVANCY 796,011,941
362423707 Q33 International COMPASSION INTERNATIONAL INCORPORATED 787,035,423
951831116 Q39 International DIRECT RELIEF 716,768,367
510169168 Q33 International PLAN INTERNATIONAL INC 689,494,953
135660870 Q71 International INTERNATIONAL RESCUE COMMITTEE INC 673,939,914

Source: NCCS 501(c)(3) Public Charities Core File 2015