Registered Public Charities by Type

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 = ","))