Active and Reporting Other Exempt Organizations by Expense Level

9.13.2018
Deondre' Jones

More from this project:

Active and Reporting Other Exempt Organizations 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", "co.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 Expense Category
  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 71,268 4,024,717,549 730,990,459 1,838,625,100 2,876,979,076 24,609,006,177
b. $100,000 to $249,999 30,732 5,488,200,490 1,322,015,051 2,768,455,311 4,912,963,022 16,545,942,482
b. $250,000 to $499,999 15,529 5,955,927,559 1,586,738,411 3,346,681,232 5,460,652,460 16,981,323,217
c. $500,000 to $999,999 10,186 8,849,515,524 1,807,339,071 5,976,852,376 7,160,081,608 24,863,771,388
d. $1 million to $4.99 million 12,299 30,019,823,202 4,598,226,983 21,476,588,194 27,255,755,889 102,996,188,390
e. $5 million to $9.99 million 2,802 20,948,149,486 2,161,633,125 15,794,678,840 19,593,436,352 80,153,241,226
f. $10 million or more 4,357 379,923,951,092 10,411,267,189 343,132,165,478 371,590,135,470 1,064,311,191,096

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