Registered 501(c)(3) Private Foundations by Age and Status

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

                                           RULEDATE =col_integer(),

                                           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 out of scope orgs and non 501c3s from the BMF

bmf <- bmf2016 %>%

  filter((OUTNCCS != "OUT")) %>%

  filter((FNDNCD == "02" | FNDNCD== "03" | FNDNCD == "04")) %>%

  #Filter out non 501(c)(3)s

  filter(SUBSECCD =="03")

#Exclude orgs established after 1969 and total

a <- bmf  %>%

  filter(RULEDATE <= "196912") %>%

  summarize( a = n())

#Repeat this process for each time period of interest

b <- bmf %>%

  filter(RULEDATE > "196912") %>%

  filter(RULEDATE <= "197912")%>%

  summarize( a = n())

c <- bmf %>%

  filter(RULEDATE > "197912") %>%

  filter(RULEDATE <= "198912")%>%

  summarize( a = n())

d <- bmf %>%

  filter(RULEDATE > "198912") %>%

  filter(RULEDATE <= "199412")%>%

  summarize( a = n())

e <- bmf %>%

  filter(RULEDATE > "199412") %>%

  filter(RULEDATE <= "199912")%>%

  summarize( a = n())

f <- bmf %>%

  filter(RULEDATE > "199912") %>%

  filter(RULEDATE <= "200112")%>%

  summarize( a = n())

g <- bmf %>%

  filter(RULEDATE > "200112") %>%

  filter(RULEDATE <= "200312")%>%

  summarize( a = n())

h <- bmf %>%

  filter(RULEDATE > "200312") %>%

  filter(RULEDATE <= "200512")%>%

  summarize( a = n())

i <- bmf %>%

  filter(RULEDATE > "200512") %>%

  filter(RULEDATE <= "200712")%>%

  summarize( a = n())

j <- bmf %>%

  filter(RULEDATE >= "200801") %>%

  filter(RULEDATE <= "200812")%>%

  summarize( a = n())

k <- bmf %>%

  filter(RULEDATE >= "200901") %>%

  filter(RULEDATE <= "200912")%>%

  summarize( a = n())

l <- bmf %>%

  filter(RULEDATE >= "201001") %>%

  filter(RULEDATE <= "201012")%>%

  summarize( a = n())

m <- bmf %>%

  filter(RULEDATE >= "201101") %>%

  filter(RULEDATE <= "201112")%>%

  summarize( a = n())

n <- bmf %>%

  filter(RULEDATE >= "201201") %>%

  filter(RULEDATE <= "201212")%>%

  summarize( a = n())

o <- bmf %>%

  filter(RULEDATE >= "201301") %>%

  filter(RULEDATE <= "201312")%>%

  summarize( a = n())

p <- bmf %>%

  filter(RULEDATE >= "201401") %>%

  filter(RULEDATE <= "201412")%>%

  summarize( a = n())

q <- bmf %>%

  filter(RULEDATE >= "201501") %>%

  filter(RULEDATE <= "201512")%>%

  summarize( a = n())

r <- bmf %>%

  filter(RULEDATE >= "201601") %>%

  filter(RULEDATE <= "201612")%>%

  summarize( a = n())

#Combine all of your outputs into one table

final <- rbind(a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r, deparse.level = 1)

#Define a new column containing the time period labels

final$dates <- c("1969 or earlier","1970-1979","1980-1989",

                  "1990-1994","1995-1999","2001-2001",

                  "2002-2003","2004-2005","2006-2007",

                  "2008","2009","2010","2011","2012",

                  "2013","2014","2015","2016")

#Filter out of scope orgs and non 501c3s from the bmf1

bmf1 <- bmf2016 %>%

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

#Exclude orgs established after 1969 and total

a1 <- bmf1  %>%

  filter(RULEDATE <= "196912") %>%

  summarize( b = n())

#Repeat this process for each time period of interest

b1 <- bmf1 %>%

  filter(RULEDATE > "196912") %>%

  filter(RULEDATE <= "197912")%>%

  summarize( b = n())

c1 <- bmf1 %>%

  filter(RULEDATE > "197912") %>%

  filter(RULEDATE <= "198912")%>%

  summarize( b = n())

d1 <- bmf1 %>%

  filter(RULEDATE > "198912") %>%

  filter(RULEDATE <= "199412")%>%

  summarize( b = n())

e1 <- bmf1 %>%

  filter(RULEDATE > "199412") %>%

  filter(RULEDATE <= "199912")%>%

  summarize( b = n())

f1 <- bmf1 %>%

  filter(RULEDATE > "199912") %>%

  filter(RULEDATE <= "200112")%>%

  summarize( b = n())

g1 <- bmf1 %>%

  filter(RULEDATE > "200112") %>%

  filter(RULEDATE <= "200312")%>%

  summarize( b = n())

h1 <- bmf1 %>%

  filter(RULEDATE > "200312") %>%

  filter(RULEDATE <= "200512")%>%

  summarize( b = n())

i1 <- bmf1 %>%

  filter(RULEDATE > "200512") %>%

  filter(RULEDATE <= "200712")%>%

  summarize( b = n())

j1 <- bmf1 %>%

  filter(RULEDATE >= "200801") %>%

  filter(RULEDATE <= "200812")%>%

  summarize( b = n())

k1 <- bmf1 %>%

  filter(RULEDATE >= "200901") %>%

  filter(RULEDATE <= "200912")%>%

  summarize( b = n())

l1 <- bmf1 %>%

  filter(RULEDATE >= "201001") %>%

  filter(RULEDATE <= "201012")%>%

  summarize( b = n())

m1 <- bmf1 %>%

  filter(RULEDATE >= "201101") %>%

  filter(RULEDATE <= "201112")%>%

  summarize( b = n())

n1 <- bmf1 %>%

  filter(RULEDATE >= "201201") %>%

  filter(RULEDATE <= "201212")%>%

  summarize( b = n())

o1 <- bmf1 %>%

  filter(RULEDATE >= "201301") %>%

  filter(RULEDATE <= "201312")%>%

  summarize( b = n())

p1 <- bmf1 %>%

  filter(RULEDATE >= "201401") %>%

  filter(RULEDATE <= "201412")%>%

  summarize( b = n())

q1 <- bmf1 %>%

  filter(RULEDATE >= "201501") %>%

  filter(RULEDATE <= "201512")%>%

  summarize( b = n())

r1 <- bmf1 %>%

  filter(RULEDATE >= "201601") %>%

  filter(RULEDATE <= "201612")%>%

  summarize( b = n())

#Combine all of your outputs into one table

final1 <- rbind(a1,b1,c1,d1,e1,f1,g1,h1,i1,j1,k1,l1,m1,n1,o1,p1,q1,r1, deparse.level = 1)

#Define a new column containing the time period labels

final1$dates <- c("1969 or earlier","1970-1979","1980-1989",

                  "1990-1994","1995-1999","2001-2001",

                  "2002-2003","2004-2005","2006-2007",

                  "2008","2009","2010","2011","2012",

                  "2013","2014","2015","2016")

final3<- left_join(final,final1, copy = FALSE, suffix = c(".x", ".y"))

#Reorder the columns

final3 <- final3 %>%

  select(dates,a,b)

#Rename the columns

colnames(final3) <- c("Date Created","Number of Organizations","Number Filing Annually")

#display table

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