Registered 501(c)(3) Private Foundations by State

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

                                           CASSETS = col_double()

                     ))

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

  return(output)

}

#Run the function on the BMF

bmf2016 <- prepbmffile("Data/bm1608.csv")

#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 state

  group_by(STATE)%>%

  summarise(TotalActive = n())

#Filter the bmf to isolate number of nonprofits by state

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 state

  group_by(STATE) %>%

  summarize(TotalOrgs = n())

#join the two output files to a single table

final <- left_join(output, output2, by = "STATE")

#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, TotalActive)

#Arrange states in alphabetical order

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

#rename columns appropriately 

colnames(final)<- c("State", "Number of Organizations", "Number Filing Annually")

#display table

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