Largest Active and Reporting Public Charities by Expenses

9.13.2018
Deondre' Jones

More from this project:

Largest Active and Reporting 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. #link to NCCS Data Archive
  22. nteedoc<- GET("http://nccs-data.urban.org/data/misc/nccs.nteedocAllEins.csv")
  23.  
  24. #pull only the most important columns (EIN, , NTEECC, Nteefinal)
  25. nteedocalleins <-content(nteedoc, type = "text/csv",
  26.                          col_types=cols_only(EIN = col_character(),
  27.                                              NteeCC = col_character(),
  28.                                              NteeFinal = col_character()))
  29.  
  30. #convert variable names to upper case
  31. names(nteedocalleins) <- toupper(names(nteedocalleins))
  32.  
  33. #This function will apply the most common NTEE Grouping categories to your data.
  34. NTEEclassify <- function(dataset) {
  35.   #merge in Master NTEE look up file
  36.   dataset <- dataset %>%
  37.     left_join(nteedocalleins, by = "EIN")
  38.   #create NTEEGRP classifications
  39.   dataset$NTEEGRP <- "  "
  40.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% arts ] <- "Arts"
  41.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% othered ] <- "Education: Other"
  42.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,2) %in% highered ] <- "Education: Higher"
  43.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% envanimals] <- "Environment and Animals"
  44.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% otherhlth] <- "Health Care: Other"
  45.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,3) %in% hospitals] <- "Health Care: Hospitals and primary care facilities"
  46.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% humanserv] <- "Human Services"
  47.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% intl] <- "International"
  48.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% pubben] <- "Other Public and social benefit"
  49.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% relig] <- "Religion related"
  50.   dataset$NTEEGRP[is.na(dataset$NTEEFINAL)] <- "Other Public and social benefit"
  51.   return(dataset)
  52. }
  53.  
  54. #Import reduced NCCS Core File Function
  55. prepcorepcfile <- function(corefilepath) {
  56.   output <- read_csv(corefilepath,
  57.                      col_types = cols_only(EIN = col_character(),
  58.                                            FISYR = col_integer(),
  59.                                            NAME = col_character(),
  60.                                            STATE = col_character(),
  61.                                            ADDRESS = col_character(),
  62.                                            CITY = col_character(),
  63.                                            ZIP = col_character(),
  64.                                            MSA_NECH = col_character(),
  65.                                            FIPS = col_character(),
  66.                                            PMSA = col_character(),
  67.                                            STYEAR = col_double(),
  68.                                            TAXPER = col_integer(),
  69.                                            OUTNCCS = col_character(),
  70.                                            OutNCCS = col_character(),
  71.                                            SUBSECCD = col_character(),
  72.                                            RULEDATE = col_character(),
  73.                                            FNDNCD = col_character(),
  74.                                            FRCD = col_character(),
  75.                                            TOTREV = col_double(),
  76.                                            EXPS = col_double(),
  77.                                            ASS_EOY = col_double(),
  78.                                            GRREC = col_double()
  79.  
  80.                      ))
  81.   names(output) <- toupper(names(output))
  82.   return(output)
  83. }
  84.  
  85. #Import NCCS Core File for given year
  86. corefile <- prepcorepcfile(as.character(paste("Data/core", "2015", "pc.csv", sep="")))
  87.  
  88. #Add NTEE Classifications to the Core File
  89. corefile <- NTEEclassify(corefile)
  90.  
  91. #Filter out of scope organizations 
  92. corefile <- corefile %>%
  93.   filter((OUTNCCS != "OUT")) %>%
  94.   filter((FNDNCD != "02" & FNDNCD!= "03" & FNDNCD != "04")) 
  95.  
  96. #Sort the corefile in descending order by expenses
  97. LargestExpenses <- corefile[with(corefile,order(-EXPS)),]
  98.  
  99. #Limit the list to 10
  100. LargestExpenses <- LargestExpenses[1:10,]
  101.  
  102. #Select the appropriate columns, drop the rest
  103. LargestExpenses <- LargestExpenses %>% 
  104.   select(EIN, NTEEFINAL, NTEEGRP, NAME, EXPS)
  105.  
  106. #Rename columns appropriately
  107. 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
941340523 E31 Health Care: Hospitals and primary care facilities KAISER FOUNDATION HEALTH PLAN INC 48,252,710,912
941105628 E21 Health Care: Hospitals and primary care facilities KAISER FOUNDATION HOSPITALS 20,177,137,580
900656139 E21 Health Care: Hospitals and primary care facilities PARTNERS HEALTHCARE SYSTEM INC AFFILIATES GROUP RETURN 10,735,291,530
208295721 E21 Health Care: Hospitals and primary care facilities UPMC GROUP RETURN 10,397,939,016
941196203 E21 Health Care: Hospitals and primary care facilities DIGNITY HEALTH 9,598,768,323
912153073 E21 Health Care: Hospitals and primary care facilities CLEVELAND CLINIC FOUNDATION GROUP RETURN 7,031,439,782
720502505 E21 Health Care: Hospitals and primary care facilities OCHSNER CLINIC FOUNDATION 6,546,010,317
113153422 E80 Health Care: Other NEW YORK STATE CATHOLIC HEALTH PLAN INC 6,241,795,918
311143265 E80 Health Care: Other CARESOURCE 6,013,922,970
450233470 E21 Health Care: Hospitals and primary care facilities BANNER HEALTH 5,428,669,563

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