Active and Reporting 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 

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