Registered 501(c)(3) Private Foundations by Age and Status
9.5.2018
More from this project:
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 = ","))
Date Created | Number of Organizations | Number Filing Annually |
---|---|---|
1969 or earlier | 9,927 | 9,780 |
1970-1979 | 4,523 | 4,447 |
1980-1989 | 9,663 | 9,469 |
1990-1994 | 8,338 | 8,167 |
1995-1999 | 16,151 | 15,813 |
2001-2001 | 8,506 | 8,332 |
2002-2003 | 6,491 | 6,314 |
2004-2005 | 5,416 | 5,304 |
2006-2007 | 6,552 | 6,438 |
2008 | 3,179 | 3,131 |
2009 | 3,022 | 2,971 |
2010 | 2,373 | 2,314 |
2011 | 2,208 | 2,173 |
2012 | 2,146 | 2,090 |
2013 | 2,022 | 1,950 |
2014 | 6,236 | 5,118 |
2015 | 5,316 | 2,802 |
2016 | 3,336 | 769 |
Source: NCCS 501(c)(3) IRS Business Master FIle 2016