Largest Active and Reporting Other Public Benefit Organizations by Assets

9.5.2018
Deondre' Jones

More from this project:

Largest Active and Reporting Other Public Benefit Organizations by 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. #Create NTEE grouping categories
  9. arts <- c("A")
  10. highered <- c("B4", "B5")
  11. othered <- c("B")
  12. envanimals <- c("C", "D")
  13. hospitals <- c('E20','E21','E22','E23','E24','F31','E30','E31','E32')
  14. otherhlth <- c("E", "F", "G", "H")
  15. humanserv <- c("I", "J", "K", "L", "M", "N", "O", "P")
  16. intl <- c("Q")
  17. pubben <- c("R", "S", "T", "U", "V", "W", "Y", "Z")
  18. relig <- c("X")
  19.  
  20. #Import the Reduced NCCS Data Archive
  21. nteedocalleins <- read.csv("Data/nteedocalleins.csv")
  22.  
  23. #convert variable names to upper case
  24. names(nteedocalleins) <- toupper(names(nteedocalleins))
  25.  
  26. #This function will apply the most common NTEE Grouping categories to your data.
  27. NTEEclassify <- function(dataset) {
  28.   #merge in Master NTEE look up file
  29.   dataset <- dataset %>%
  30.     left_join(nteedocalleins, by = "EIN")
  31.   #create NTEEGRP classifications
  32.   dataset$NTEEGRP <- "  "
  33.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% arts ] <- "Arts"
  34.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% othered ] <- "Education: Other"
  35.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,2) %in% highered ] <- "Education: Higher"
  36.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% envanimals] <- "Environment and Animals"
  37.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% otherhlth] <- "Health Care: Other"
  38.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,3) %in% hospitals] <- "Health Care: Hospitals and primary care facilities"
  39.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% humanserv] <- "Human Services"
  40.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% intl] <- "International"
  41.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% pubben] <- "Other Public and social benefit"
  42.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% relig] <- "Religion related"
  43.   dataset$NTEEGRP[is.na(dataset$NTEEFINAL)] <- "Other Public and social benefit"
  44.   return(dataset)
  45. }
  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 == "Other Public and social benefit"))
  90.  
  91. #Sort the corefile in descending order by assets
  92. LargestAssets <- corefile[with(corefile,order(-ASS_EOY)),]
  93.  
  94. #Limit the list to 10
  95. LargestAssets <- LargestAssets[1:10,]
  96.  
  97. #Select the appropriate columns, drop the rest
  98. LargestAssets <- LargestAssets %>% 
  99.   select(EIN, NTEEFINAL, NAME, ASS_EOY)
  100.  
  101. #Rename columns appropriately
  102. colnames(LargestAssets) <- c("EIN", "NTEE Code", "Name", "Total Assets")
  1. #display table
  2. kable(LargestAssets, format.args = list(decimal.mark = '.', big.mark = ","))
EIN NTEE Code Name Total Assets
110303001 T30 FIDELITY INVESTMENTS CHARITABLE GIFT FUND 15,343,987,929
311640316 T30 SCHWAB CHARITABLE FUND 7,529,406,703
205205488 T31 SILICON VALLEY COMMUNITY FOUNDATION 6,304,272,612
135562401 Y30 YOUNG MENS CHRISTIAN ASSOCIATION RETIREMENT FUND YMCA RETIREMENT FUND 6,133,685,280
232719985 W60 ACCESS GROUP INC 5,901,438,766
232888152 T0370 VANGUARD CHARITABLE ENDOWMENT PROGRAM 5,162,019,762
237037968 S50 COMMON FUND FOR NON PROFIT ORGANIZATIONS 4,726,634,912
731574370 T30 GEORGE KAISER FAMILY FOUNDATION 3,849,914,035
336197320 T90 ANNE RAY CHARITABLE TRUST MORSE CHRISTINE M ET AL TTEE 3,661,403,620
411961427 W60 NORTHSTAR EDUCATION FINANCE INC 3,472,295,492

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