Active and Reporting 501(c)(3) Private Foundations 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(),

                                           P1TOTREV = col_double(),

                                           P1TOTEXP = col_double(),

                                           P2TASFMV = col_double(),

                                           P1CONTPD = col_double()

                     ))

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

  return(output)

}

#Import NCCS Core File for given year

corefile <- prepcorepcfile(as.character(paste("Data/core", "2015", "pf.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(P1TOTREV, na.rm = TRUE),

            Grants = sum(P1CONTPD, na.rm= TRUE),

            TotalExps = sum(P1TOTEXP, na.rm = TRUE),

            TotalAssets = sum(P2TASFMV, 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, Grants, TotalExps,TotalAssets)

#Arrange states in alphabetical order

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

#rename columns appropriately 

colnames(final)<- c("State", "Number of Organizations", "Total Revenue",

                  "Grants Paid", "Total Expenses and Disbursements", 

                  "Total Assets Fair Market Value")

#display table

kable(final, format.args = list(decimal.mark = '.', big.mark = ","))