More from this project:
Number of Registered 501(c)(3) Public Charities by NTEE Group
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
prepbmffile <- function(bmffilepath) {
output <- read_csv(bmffilepath,
col_types = cols_only(EIN = col_character(),
NTEECC = col_character(),
STATE = col_character(),
OUTNCCS = col_character(),
SUBSECCD = col_character(),
FNDNCD = col_character(),
CFILER = col_character(),
CZFILER = col_character(),
CTAXPER = col_character(),
CTOTREV = col_double(),
LEVEL4 = col_character(),
CASSETS = 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)
}
#Run the functions on the BMF
bmf2016 <- prepbmffile("Data/bm1608.csv")
bmf2016 <- NTEEclassify(bmf2016)
#Filter the bmf to isolate nonprofits that are actively filing
output2<- bmf2016 %>%
#Filter out out of scope orgs
filter((OUTNCCS != "OUT")) %>%
filter((FNDNCD != "02" & FNDNCD!= "03" & FNDNCD != "04")) %>%
#Filter out non 501(c)(3)s
filter(SUBSECCD =="03") %>%
#Filter out orgs that haven't filed taxes in the last 2 years
filter(CFILER == "Y") %>%
#filter(CZFILER == "N") %>%
#Total the filtered number of orgs and display by NTEE Group
group_by(NTEEGRP)%>%
summarise(TotalActive = n())
#Filter the bmf to isolate number of nonprofits by NTEE Group
output <- bmf2016 %>%
#filter out of scope orgs
filter((OUTNCCS != "OUT")) %>%
filter((FNDNCD != "02" & FNDNCD!= "03" & FNDNCD != "04")) %>%
#Filter out non 501(c)(3)s
filter(SUBSECCD =="03") %>%
#Total the filtered number of orgs and display by NTEE Group
group_by(NTEEGRP) %>%
summarize(TotalOrgs = n())
#join the two output files to a single table
final <- left_join(output, output2, by = "NTEEGRP")
#rename columns appropriately
colnames(final)<- c("Organization Type", "Number of Organizations", "Number Filing Annually")
#display table
kable(final, format.args = list(decimal.mark = '.', big.mark = ","))
Organization Type | Number of Organizations | Number Filing Annually |
---|---|---|
Arts | 103,926 | 91,939 |
Education: Higher | 5,212 | 3,962 |
Education: Other | 157,775 | 132,287 |
Environment and Animals | 55,170 | 41,185 |
Health Care: Hospitals and primary care facilities | 10,615 | 9,297 |
Health Care: Other | 72,137 | 62,323 |
Human Services | 284,329 | 241,584 |
International | 18,802 | 16,216 |
Other Public and social benefit | 125,366 | 101,825 |
Religion related | 275,320 | 56,648 |
Source: Internal Revenue Service Business Master Files, Exempt Organizations August, 2016