Active and Reporting Public Charities by Type

8.27.2018
Brice McKeever

More from this project:

Number of 501(c)(3) Public Charities by NTEE Activity -- Annual Filers Only

  1. library(tidyverse)
  2. library(httr)
  3. library(stringr)
  4. library(knitr)
  5. library(reshape2)
  6. library(extrafont)
  7. source('https://raw.githubusercontent.com/UrbanInstitute/urban_R_theme/master/urban_theme_windows.R')
  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.  
  34. #Create a function to filter out unneccesary columns from the Business Master File 
  35. prepcorepcfile <- function(corefilepath) {
  36.   output <- read_csv(corefilepath,
  37.                      col_types = cols_only(EIN = col_character(),
  38.                                            FISYR = col_integer(),
  39.                                            NAME = col_character(),
  40.                                            STATE = col_character(),
  41.                                            ADDRESS = col_character(),
  42.                                            CITY = col_character(),
  43.                                            ZIP = col_character(),
  44.                                            MSA_NECH = col_character(),
  45.                                            FIPS = col_character(),
  46.                                            PMSA = col_character(),
  47.                                            STYEAR = col_double(),
  48.                                            TAXPER = col_integer(),
  49.                                            OUTNCCS = col_character(),
  50.                                            OutNCCS = col_character(),
  51.                                            SUBSECCD = col_character(),
  52.                                            RULEDATE = col_character(),
  53.                                            FNDNCD = col_character(),
  54.                                            FRCD = col_character(),
  55.                                            TOTREV = col_double(),
  56.                                            EXPS = col_double(),
  57.                                            ASS_EOY = col_double(),
  58.                                            GRREC = col_double(),
  59.                                            CONT = col_double(),
  60.                                            PROGREV = col_double()
  61.                      ))
  62.   names(output) <- toupper(names(output))
  63.   return(output)
  64. }
  65.  
  66.  
  67.  
  68. #This function will apply the most common NTEE Grouping categories to your data.
  69. NTEEclassify <- function(dataset) {
  70.   #merge in Master NTEE look up file
  71.   dataset <- dataset %>%
  72.     left_join(nteedocalleins, by = "EIN")
  73.   #create NTEEGRP classifications
  74.   dataset$NTEEGRP <- "  "
  75.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% arts ] <- "Arts"
  76.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% othered ] <- "Education: Other"
  77.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,2) %in% highered ] <- "Education: Higher"
  78.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% envanimals] <- "Environment and Animals"
  79.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% otherhlth] <- "Health Care: Other"
  80.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,3) %in% hospitals] <- "Health Care: Hospitals and primary care facilities"
  81.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% humanserv] <- "Human Services"
  82.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% intl] <- "International"
  83.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% pubben] <- "Other Public and social benefit"
  84.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% relig] <- "Religion related"
  85.   dataset$NTEEGRP[is.na(dataset$NTEEFINAL)] <- "Other Public and social benefit"
  86.   return(dataset)
  87. }
  88.  
  89. #Import NCCS Core File for given year
  90. corefile <- prepcorepcfile(as.character(paste("Data/core", "2015", "pc.csv", sep="")))
  91.  
  92. #Filter out of scope organizations
  93. corefile <- corefile %>%
  94.   filter((OUTNCCS != "OUT")) %>%
  95.   filter((FNDNCD != "02" & FNDNCD!= "03" & FNDNCD != "04"))
  96.  
  97. #Use the function to apply the appropriate NTEE classifications
  98. corefile <- NTEEclassify(corefile)
  99.  
  100. #Summarize the variables of interest and group by State
  101. final <- corefile %>%
  102.   group_by(NTEEGRP)%>%
  103.   summarize(TotalOrgs = n(),
  104.             TotalRev = sum(TOTREV, na.rm = TRUE),
  105.             Contributions = sum(CONT, na.rm = TRUE),
  106.             ProgRev = sum(PROGREV, na.rm= TRUE),
  107.             TotalExps = sum(EXPS, na.rm = TRUE),
  108.             TotalAssets = sum(ASS_EOY, na.rm = TRUE))
  109.  
  110. #rename columns appropriately 
  111. colnames(final)<- c("Organization Type", "Number of Organizations", "Total Revenue","Private Contributions and Government Grants", "Program Service Revenue", "Total Expenses", "Total Assets")
  1. #display table
  2. kable(final, format.args = list(decimal.mark = '.', big.mark = ","))
Organization Type Number of Organizations Total Revenue Private Contributions and Government Grants Program Service Revenue Total Expenses Total Assets
Arts 45,594 40,821,358,554 21,868,955,464 13,979,014,996 36,038,463,373 129,842,371,676
Education: Higher 2,400 230,895,105,483 41,604,368,074 158,289,559,842 207,416,187,379 736,289,466,127
Education: Other 70,994 123,751,464,248 55,464,250,129 52,309,395,018 108,566,005,252 395,310,475,160
Environment and Animals 20,872 19,776,147,786 12,388,265,217 5,212,206,310 16,599,094,962 48,671,972,059
Health Care: Hospitals and primary care facilities 8,052 977,036,798,735 28,616,376,969 910,449,318,083 927,047,910,196 1,285,564,693,277
Health Care: Other 41,318 183,535,248,650 46,380,339,289 124,607,194,244 175,932,900,536 295,322,356,047
Human Services 143,291 234,641,085,813 99,666,660,229 122,685,999,460 224,747,278,130 361,459,451,327
International 9,705 38,531,408,706 34,255,861,994 3,423,400,343 34,585,392,467 43,404,304,031
Other Public and social benefit 53,826 111,491,310,285 69,209,914,192 28,930,415,943 93,719,076,027 352,041,467,452
Religion related 29,893 19,765,215,207 12,494,240,241 5,002,613,499 17,323,641,926 43,630,990,405

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