More from this project:
Number of 501(c)(3) Public Charities by NTEE Activity -- Annual Filers Only
library(tidyverse)
library(httr)
library(stringr)
library(knitr)
library(reshape2)
library(extrafont)
source('https://raw.githubusercontent.com/UrbanInstitute/urban_R_theme/master/urban_theme_windows.R')
#Create NTEE grouping categories
arts <- c("A")
highered <- c("B4", "B5")
othered <- c("B")
envanimals <- c("C", "D")
hospitals <- c('E20','E21','E22','E23','E24','F31','E30','E31','E32')
otherhlth <- c("E", "F", "G", "H")
humanserv <- c("I", "J", "K", "L", "M", "N", "O", "P")
intl <- c("Q")
pubben <- c("R", "S", "T", "U", "V", "W", "Y", "Z")
relig <- c("X")
#link to NCCS Data Archive
nteedoc<- GET("http://nccs-data.urban.org/data/misc/nccs.nteedocAllEins.csv")
#pull only the most important columns (EIN, , NTEECC, Nteefinal)
nteedocalleins <-content(nteedoc, type = "text/csv",
col_types=cols_only(EIN = col_character(),
NteeCC = col_character(),
NteeFinal = col_character()))
#convert variable names to upper case
names(nteedocalleins) <- toupper(names(nteedocalleins))
#Create a function to filter out unneccesary columns from the Business Master File
prepcorepcfile <- function(corefilepath) {
output <- read_csv(corefilepath,
col_types = cols_only(EIN = col_character(),
FISYR = col_integer(),
NAME = col_character(),
STATE = col_character(),
ADDRESS = col_character(),
CITY = col_character(),
ZIP = col_character(),
MSA_NECH = col_character(),
FIPS = col_character(),
PMSA = col_character(),
STYEAR = col_double(),
TAXPER = col_integer(),
OUTNCCS = col_character(),
OutNCCS = col_character(),
SUBSECCD = col_character(),
RULEDATE = col_character(),
FNDNCD = col_character(),
FRCD = col_character(),
TOTREV = col_double(),
EXPS = col_double(),
ASS_EOY = col_double(),
GRREC = col_double(),
CONT = col_double(),
PROGREV = col_double()
))
names(output) <- toupper(names(output))
return(output)
}
#This function will apply the most common NTEE Grouping categories to your data.
NTEEclassify <- function(dataset) {
#merge in Master NTEE look up file
dataset <- dataset %>%
left_join(nteedocalleins, by = "EIN")
#create NTEEGRP classifications
dataset$NTEEGRP <- " "
dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% arts ] <- "Arts"
dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% othered ] <- "Education: Other"
dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,2) %in% highered ] <- "Education: Higher"
dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% envanimals] <- "Environment and Animals"
dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% otherhlth] <- "Health Care: Other"
dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,3) %in% hospitals] <- "Health Care: Hospitals and primary care facilities"
dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% humanserv] <- "Human Services"
dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% intl] <- "International"
dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% pubben] <- "Other Public and social benefit"
dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% relig] <- "Religion related"
dataset$NTEEGRP[is.na(dataset$NTEEFINAL)] <- "Other Public and social benefit"
return(dataset)
}
#Import NCCS Core File for given year
corefile <- prepcorepcfile(as.character(paste("Data/core", "2015", "pc.csv", sep="")))
#Filter out of scope organizations
corefile <- corefile %>%
filter((OUTNCCS != "OUT")) %>%
filter((FNDNCD != "02" & FNDNCD!= "03" & FNDNCD != "04"))
#Use the function to apply the appropriate NTEE classifications
corefile <- NTEEclassify(corefile)
#Summarize the variables of interest and group by State
final <- corefile %>%
group_by(NTEEGRP)%>%
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("Organization Type", "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 = ","))
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