Largest Active and Reporting Environmental Public Charities by End of Year Assets

9.5.2018
Deondre' Jones

More from this project:

Largest Active and Reporting Environmental 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.   filter((NTEEGRP == "Environment and Animals"))
  90.  
  91.  
  92. #Sort the corefile in descending order by EOY Assets
  93. LargestAssets <- corefile[with(corefile,order(-ASS_EOY)),]
  94.  
  95. #Limit the list to 10
  96. LargestAssets <- LargestAssets[1:10,]
  97.  
  98. #Select the appropriate columns, drop the rest
  99. LargestAssets <- LargestAssets %>% 
  100.   select(EIN, NTEEFINAL, NTEEGRP, NAME, ASS_EOY)
  101.  
  102. #Rename columns appropriately
  103. colnames(LargestAssets) <- c("EIN", "NTEE Code", "NTEE Group", "Name", "End of Year Assets")
  1. #display table
  2. kable(LargestAssets, format.args = list(decimal.mark = '.', big.mark = ","))
EIN NTEE Code NTEE Group Name End of Year Assets
530193519 C60 Environment and Animals NATIONAL GEOGRAPHIC SOCIETY 1,695,152,141
521384139 D30 Environment and Animals NATIONAL FISH AND WILDLIFE FOUNDATION 1,345,544,846
131740011 D50 Environment and Animals WILDLIFE CONSERVATION SOCIETY 1,033,547,676
521388917 C34 Environment and Animals THE CONSERVATION FUND A NONPROFIT CORPORATION 717,760,621
131693134 C41 Environment and Animals NEW YORK BOTANICAL GARDEN 580,487,363
951648219 D50 Environment and Animals ZOOLOGICAL SOCIETY OF SAN DIEGO 545,207,267
582574918 D50 Environment and Animals GEORGIA AQUARIUM INC 474,856,953
521693387 D30 Environment and Animals WORLD WILDLIFE FUND INC 471,481,440
131624102 C30 Environment and Animals NATIONAL AUDUBON SOCIETY INC 471,019,435
942487469 D1150 Environment and Animals MONTEREY BAY AQUARIUM FOUNDATION 387,639,444

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