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