Active and Reporting Public Charities by Expense Level

8.27.2018
Brice McKeever

More from this project:

Number of Active and Reporting 501(c)(3) Public Charities by Expense Level

  1. library(tidyverse)
  2. library(knitr)
  3. library(stringr)
  4. source('https://raw.githubusercontent.com/UrbanInstitute/urban_R_theme/master/urban_theme_windows.R')
  5.  
  6. #Import reduced NCCS Core File Function
  7. prepcorepcfile <- function(corefilepath) {
  8.   output <- read_csv(corefilepath,
  9.                      col_types = cols_only(EIN = col_character(),
  10.                                            NTEEFINAL = col_character(),
  11.                                            NAME = col_character(),
  12.                                            STATE = col_character(),
  13.                                            CITY = col_character(),
  14.                                            OUTNCCS = col_character(),
  15.                                            SUBSECCD = col_character(),
  16.                                            FNDNCD = col_character(),
  17.                                            TOTREV = col_double(),
  18.                                            EXPS = col_double(),
  19.                                            ASS_EOY = col_double(),
  20.                                            GRREC = col_double(),
  21.                                            CONT = col_double(),
  22.                                            RULEDATE =col_integer(),
  23.                                            PROGREV = col_double()
  24.  
  25.                      ))
  26.   names(output) <- toupper(names(output))
  27.   return(output)
  28. }
  29.  
  30. EXPclassify <-function(dataset) {
  31.   dataset$EXPCAT <- " "
  32.   dataset$EXPCAT[dataset$EXPS<100000] <- "a. Under $100,000"
  33.   dataset$EXPCAT[dataset$EXPS >= 100000 & dataset$EXPS< 250000] <- "b. $100,000 to $249,999"
  34.   dataset$EXPCAT[dataset$EXPS >= 250000 & dataset$EXPS< 500000] <- "b. $250,000 to $499,999"
  35.   dataset$EXPCAT[dataset$EXPS >= 500000 & dataset$EXPS< 1000000] <- "c. $500,000 to $999,999"
  36.   dataset$EXPCAT[dataset$EXPS >= 1000000 & dataset$EXPS< 5000000] <- "d. $1 million to $4.99 million"
  37.   dataset$EXPCAT[dataset$EXPS >= 5000000 & dataset$EXPS< 10000000] <- "e. $5 million to $9.99 million"
  38.   dataset$EXPCAT[dataset$EXPS >= 10000000] <- "f. $10 million or more"
  39.   return(dataset)
  40.  
  41. }
  42.  
  43. #Import NCCS Core File for given year
  44. corefile <- prepcorepcfile(as.character(paste("Data/core", "2015", "pc.csv", sep="")))
  45.  
  46. corefile <- EXPclassify(corefile)
  47. #Filter out of scope organizations
  48. corefile <- corefile %>%
  49.   filter((OUTNCCS != "OUT")) %>%
  50.   filter((FNDNCD != "02" & FNDNCD!= "03" & FNDNCD != "04"))
  51.  
  52. #Summarize the variables of interest and group by State
  53. final <- corefile %>%
  54.     group_by(EXPCAT)%>%
  55.     summarize(TotalOrgs = n(),
  56.               TotalRev = sum(TOTREV, na.rm = TRUE),
  57.               Contributions = sum(CONT, na.rm = TRUE),
  58.               ProgRev = sum(PROGREV, na.rm= TRUE),
  59.               TotalExps = sum(EXPS, na.rm = TRUE),
  60.               TotalAssets = sum(ASS_EOY, na.rm = TRUE))
  61.  
  62.  
  63. #rename columns appropriately 
  64. colnames(final)<- c("Expense Level", "Number of Organizations", "Total Revenue","Private Contributions and Government Grants",
  65.                     "Program Service Revenue", "Total Expenses", "Total Assets")
  1. #display table
  2. kable(final, format.args = list(decimal.mark = '.', big.mark = ","))
Expense Level Number of Organizations Total Revenue Private Contributions and Government Grants Program Service Revenue Total Expenses Total Assets
a. Under $100,000 203,574 12,451,581,874 7,316,772,790 2,388,730,929 7,395,691,362 50,778,428,156
b. $100,000 to $249,999 75,167 13,922,241,632 8,216,984,456 4,007,230,847 11,999,349,592 36,684,119,815
b. $250,000 to $499,999 42,827 17,345,261,651 10,167,256,159 5,455,856,910 15,229,801,410 46,512,500,397
c. $500,000 to $999,999 32,654 26,154,092,268 14,753,359,063 8,932,292,719 23,171,672,118 66,489,078,820
d. $1 million to $4.99 million 44,963 109,933,823,867 57,346,202,868 42,886,667,554 100,124,001,301 258,260,274,083
e. $5 million to $9.99 million 10,192 77,202,804,435 34,483,475,019 36,983,241,741 71,757,418,506 163,945,725,683
f. $10 million or more 16,568 1,723,235,337,740 289,665,181,443 1,324,235,097,038 1,612,298,015,959 3,068,867,420,607

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