More from this project:
Number of Active and Reporting 501(c)(3) Public Charities 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", "pc.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 State
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 | 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