Active and Reporting Other Exempt Organizations by Expense Level
9.13.2018
More from this project:
Active and Reporting Other Exempt Organizations by Expense Level
library(tidyverse)
library(knitr)
library(stringr)
source('https://raw.githubusercontent.com/UrbanInstitute/urban_R_theme/master/urban_theme_windows.R')
#Import reduced NCCS Core File Function
prepcorepcfile <- function(corefilepath) {
output <- read_csv(corefilepath,
col_types = cols_only(EIN = col_character(),
NTEEFINAL = col_character(),
NAME = col_character(),
STATE = col_character(),
CITY = col_character(),
OUTNCCS = col_character(),
SUBSECCD = col_character(),
FNDNCD = col_character(),
TOTREV = col_double(),
EXPS = col_double(),
ASS_EOY = col_double(),
GRREC = col_double(),
CONT = col_double(),
RULEDATE =col_integer(),
PROGREV = col_double()
))
names(output) <- toupper(names(output))
return(output)
}
EXPclassify <-function(dataset) {
dataset$EXPCAT <- " "
dataset$EXPCAT[dataset$EXPS<100000] <- "a. Under $100,000"
dataset$EXPCAT[dataset$EXPS >= 100000 & dataset$EXPS< 250000] <- "b. $100,000 to $249,999"
dataset$EXPCAT[dataset$EXPS >= 250000 & dataset$EXPS< 500000] <- "b. $250,000 to $499,999"
dataset$EXPCAT[dataset$EXPS >= 500000 & dataset$EXPS< 1000000] <- "c. $500,000 to $999,999"
dataset$EXPCAT[dataset$EXPS >= 1000000 & dataset$EXPS< 5000000] <- "d. $1 million to $4.99 million"
dataset$EXPCAT[dataset$EXPS >= 5000000 & dataset$EXPS< 10000000] <- "e. $5 million to $9.99 million"
dataset$EXPCAT[dataset$EXPS >= 10000000] <- "f. $10 million or more"
return(dataset)
}
#Import NCCS Core File for given year
corefile <- prepcorepcfile(as.character(paste("Data/core", "2015", "co.csv", sep="")))
corefile <- EXPclassify(corefile)
#Filter out of scope organizations
corefile <- corefile %>%
filter((OUTNCCS != "OUT")) %>%
filter((FNDNCD != "02" & FNDNCD!= "03" & FNDNCD != "04"))
#Summarize the variables of interest and group by Expense Category
final <- corefile %>%
group_by(EXPCAT)%>%
summarize(TotalOrgs = n(),
TotalRev = sum(TOTREV, na.rm = TRUE),
Contributions = sum(CONT, na.rm = TRUE),
ProgRev = sum(PROGREV, na.rm= TRUE),
TotalExps = sum(EXPS, na.rm = TRUE),
TotalAssets = sum(ASS_EOY, na.rm = TRUE))
#rename columns appropriately
colnames(final)<- c("Expense Level", "Number of Organizations", "Total Revenue","Private Contributions and Government Grants",
"Program Service Revenue", "Total Expenses", "Total Assets")
#display table
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