Largest Active and Reporting Public Charities by End of Year Assets

9.5.2018
Deondre' Jones

More from this project:

Largest Active and Reporting Public Charities by End of Year Assets

  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.  
  90.  
  91. #Sort the corefile in descending order by EOY Assets
  92. LargestExpenses <- corefile[with(corefile,order(-ASS_EOY)),]
  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, ASS_EOY)
  100.  
  101. #Rename columns appropriately
  102. colnames(LargestExpenses) <- c("EIN", "NTEE Code", "NTEE Group", "Name", "End of Year Assets")
  1. #display table
  2. kable(LargestExpenses, format.args = list(decimal.mark = '.', big.mark = ","))
EIN NTEE Code NTEE Group Name End of Year Assets
042103580 B43 Education: Higher PRESIDENT AND FELLOWS OF HARVARD COLLEGE 73,518,242,000
941105628 E21 Health Care: Hospitals and primary care facilities KAISER FOUNDATION HOSPITALS 43,991,189,809
941156365 B43 Education: Higher THE BOARD OF TRUSTEES OF THE LELAND STANFORD JUNIOR UNIVERSITY 36,213,882,456
060646973 B43 Education: Higher YALE UNIVERSITY 34,133,509,535
210634501 B43 Education: Higher TRUSTEES OF PRINCETON UNIVERSITY FINANCE AND TREASURY OFFICE 27,631,907,000
042103594 B43 Education: Higher MASSACHUSETTS INSTITUTE OF TECHNOLOGY 21,882,075,000
590735717 H90 Health Care: Other HOWARD HUGHES MEDICAL INSTITUTE 21,693,736,438
941340523 E31 Health Care: Hospitals and primary care facilities KAISER FOUNDATION HEALTH PLAN INC 19,173,143,251
231352685 B43 Education: Higher TRUSTEES OF THE UNIVERSITY OF PENNSYLVANIA 17,230,855,000
135598093 B43 Education: Higher TRUSTEES OF COLUMBIA UNIVERSITY IN THE CITY OF NEW YORK 16,779,372,320

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