Active and Reporting Public Charities by State

library(tidyverse)

library(knitr)

library(stringr)

library(scales)

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(),

                                           PROGREV = col_double()

                     ))

  names(output) <- toupper(names(output))

  return(output)

}

#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"))

#Summarize the variables of interest and group by State

final <- corefile %>%

    group_by(STATE)%>%

    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))

#Add in full state names

final <- final %>%

  mutate(State = state.name[match(STATE, state.abb)])

#DC isn't recognized as a state and displays as N/A. This function renames it.

final$State[final$STATE =="DC"] <- "Washington, DC"

#Rearrange the table and drop the state abbreviation column

final <- final %>%

  select(State, TotalOrgs, TotalRev, Contributions, ProgRev, TotalExps,TotalAssets)

#Arrange states in alphabetical order

final <- final[order(final$State),]

#rename columns appropriately 

colnames(final)<- c("State", "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 = ","))