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

9.5.2018
Deondre' Jones

More from this project:

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

  1. library(tidyverse)
  2. library(httr)
  3. library(stringr)
  4. library(knitr)
  5. library(reshape2)
  6. library(extrafont)
  7. source('https://raw.githubusercontent.com/UrbanInstitute/urban_R_theme/master/urban_theme_windows.R')
  8.  
  9. #Create a function to filter out unneccesary columns from the Business Master File 
  10. prepbmffile <- function(bmffilepath) {
  11.   output <- read_csv(bmffilepath,
  12.                      col_types = cols_only(EIN = col_character(),
  13.                                            NTEECC = col_character(),
  14.                                            STATE = col_character(),                                         
  15.                                            OUTNCCS = col_character(),
  16.                                            RULEDATE =col_integer(),
  17.                                            SUBSECCD = col_character(),
  18.                                            FNDNCD = col_character(),
  19.                                            CFILER = col_character(),
  20.                                            CZFILER = col_character(),
  21.                                            CTAXPER = col_character(),
  22.                                            CTOTREV = col_double(),
  23.                                            CASSETS = col_double()
  24.                      ))
  25.   names(output) <- toupper(names(output))
  26.   return(output)
  27. }
  28.  
  29. #Run the function on the BMF
  30. bmf2016 <- prepbmffile("Data/bm1608.csv")
  31.  
  32.  
  33. #Filter out of scope orgs and non 501c3s from the BMF
  34. bmf <- bmf2016 %>%
  35.   filter((OUTNCCS != "OUT")) %>%
  36.   filter((FNDNCD == "02" | FNDNCD== "03" | FNDNCD == "04")) %>%
  37.   #Filter out non 501(c)(3)s
  38.   filter(SUBSECCD =="03")
  39.  
  40. #Exclude orgs established after 1969 and total
  41. a <- bmf  %>%
  42.   filter(RULEDATE <= "196912") %>%
  43.   summarize( a = n())
  44.  
  45. #Repeat this process for each time period of interest
  46. b <- bmf %>%
  47.   filter(RULEDATE > "196912") %>%
  48.   filter(RULEDATE <= "197912")%>%
  49.   summarize( a = n())
  50.  
  51. c <- bmf %>%
  52.   filter(RULEDATE > "197912") %>%
  53.   filter(RULEDATE <= "198912")%>%
  54.   summarize( a = n())
  55.  
  56. d <- bmf %>%
  57.   filter(RULEDATE > "198912") %>%
  58.   filter(RULEDATE <= "199412")%>%
  59.   summarize( a = n())
  60.  
  61. e <- bmf %>%
  62.   filter(RULEDATE > "199412") %>%
  63.   filter(RULEDATE <= "199912")%>%
  64.   summarize( a = n())
  65.  
  66. f <- bmf %>%
  67.   filter(RULEDATE > "199912") %>%
  68.   filter(RULEDATE <= "200112")%>%
  69.   summarize( a = n())
  70.  
  71. g <- bmf %>%
  72.   filter(RULEDATE > "200112") %>%
  73.   filter(RULEDATE <= "200312")%>%
  74.   summarize( a = n())
  75.  
  76. h <- bmf %>%
  77.   filter(RULEDATE > "200312") %>%
  78.   filter(RULEDATE <= "200512")%>%
  79.   summarize( a = n())
  80.  
  81. i <- bmf %>%
  82.   filter(RULEDATE > "200512") %>%
  83.   filter(RULEDATE <= "200712")%>%
  84.   summarize( a = n())
  85.  
  86. j <- bmf %>%
  87.   filter(RULEDATE >= "200801") %>%
  88.   filter(RULEDATE <= "200812")%>%
  89.   summarize( a = n())
  90.  
  91. k <- bmf %>%
  92.   filter(RULEDATE >= "200901") %>%
  93.   filter(RULEDATE <= "200912")%>%
  94.   summarize( a = n())
  95.  
  96. l <- bmf %>%
  97.   filter(RULEDATE >= "201001") %>%
  98.   filter(RULEDATE <= "201012")%>%
  99.   summarize( a = n())
  100.  
  101. m <- bmf %>%
  102.   filter(RULEDATE >= "201101") %>%
  103.   filter(RULEDATE <= "201112")%>%
  104.   summarize( a = n())
  105.  
  106. n <- bmf %>%
  107.   filter(RULEDATE >= "201201") %>%
  108.   filter(RULEDATE <= "201212")%>%
  109.   summarize( a = n())
  110.  
  111. o <- bmf %>%
  112.   filter(RULEDATE >= "201301") %>%
  113.   filter(RULEDATE <= "201312")%>%
  114.   summarize( a = n())
  115.  
  116. p <- bmf %>%
  117.   filter(RULEDATE >= "201401") %>%
  118.   filter(RULEDATE <= "201412")%>%
  119.   summarize( a = n())
  120.  
  121. q <- bmf %>%
  122.   filter(RULEDATE >= "201501") %>%
  123.   filter(RULEDATE <= "201512")%>%
  124.   summarize( a = n())
  125.  
  126. r <- bmf %>%
  127.   filter(RULEDATE >= "201601") %>%
  128.   filter(RULEDATE <= "201612")%>%
  129.   summarize( a = n())
  130.  
  131. #Combine all of your outputs into one table
  132. final <- rbind(a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r, deparse.level = 1)
  133.  
  134. #Define a new column containing the time period labels
  135. final$dates <- c("1969 or earlier","1970-1979","1980-1989",
  136.                   "1990-1994","1995-1999","2001-2001",
  137.                   "2002-2003","2004-2005","2006-2007",
  138.                   "2008","2009","2010","2011","2012",
  139.                   "2013","2014","2015","2016")
  140.  
  141. #Filter out of scope orgs and non 501c3s from the bmf1
  142. bmf1 <- bmf2016 %>%
  143.   filter((OUTNCCS != "OUT")) %>%
  144.   filter((FNDNCD == "02" | FNDNCD== "03" | FNDNCD == "04")) %>%
  145.   #Filter out non 501(c)(3)s
  146.   filter(SUBSECCD =="03")%>%
  147.   #Filter out orgs that haven't filed taxes in the last 2 years
  148.   filter(CFILER == "Y")
  149.  
  150. #Exclude orgs established after 1969 and total
  151. a1 <- bmf1  %>%
  152.   filter(RULEDATE <= "196912") %>%
  153.   summarize( b = n())
  154.  
  155. #Repeat this process for each time period of interest
  156. b1 <- bmf1 %>%
  157.   filter(RULEDATE > "196912") %>%
  158.   filter(RULEDATE <= "197912")%>%
  159.   summarize( b = n())
  160.  
  161. c1 <- bmf1 %>%
  162.   filter(RULEDATE > "197912") %>%
  163.   filter(RULEDATE <= "198912")%>%
  164.   summarize( b = n())
  165.  
  166. d1 <- bmf1 %>%
  167.   filter(RULEDATE > "198912") %>%
  168.   filter(RULEDATE <= "199412")%>%
  169.   summarize( b = n())
  170.  
  171. e1 <- bmf1 %>%
  172.   filter(RULEDATE > "199412") %>%
  173.   filter(RULEDATE <= "199912")%>%
  174.   summarize( b = n())
  175.  
  176. f1 <- bmf1 %>%
  177.   filter(RULEDATE > "199912") %>%
  178.   filter(RULEDATE <= "200112")%>%
  179.   summarize( b = n())
  180.  
  181. g1 <- bmf1 %>%
  182.   filter(RULEDATE > "200112") %>%
  183.   filter(RULEDATE <= "200312")%>%
  184.   summarize( b = n())
  185.  
  186. h1 <- bmf1 %>%
  187.   filter(RULEDATE > "200312") %>%
  188.   filter(RULEDATE <= "200512")%>%
  189.   summarize( b = n())
  190.  
  191. i1 <- bmf1 %>%
  192.   filter(RULEDATE > "200512") %>%
  193.   filter(RULEDATE <= "200712")%>%
  194.   summarize( b = n())
  195.  
  196. j1 <- bmf1 %>%
  197.   filter(RULEDATE >= "200801") %>%
  198.   filter(RULEDATE <= "200812")%>%
  199.   summarize( b = n())
  200.  
  201. k1 <- bmf1 %>%
  202.   filter(RULEDATE >= "200901") %>%
  203.   filter(RULEDATE <= "200912")%>%
  204.   summarize( b = n())
  205.  
  206. l1 <- bmf1 %>%
  207.   filter(RULEDATE >= "201001") %>%
  208.   filter(RULEDATE <= "201012")%>%
  209.   summarize( b = n())
  210.  
  211. m1 <- bmf1 %>%
  212.   filter(RULEDATE >= "201101") %>%
  213.   filter(RULEDATE <= "201112")%>%
  214.   summarize( b = n())
  215.  
  216. n1 <- bmf1 %>%
  217.   filter(RULEDATE >= "201201") %>%
  218.   filter(RULEDATE <= "201212")%>%
  219.   summarize( b = n())
  220.  
  221. o1 <- bmf1 %>%
  222.   filter(RULEDATE >= "201301") %>%
  223.   filter(RULEDATE <= "201312")%>%
  224.   summarize( b = n())
  225.  
  226. p1 <- bmf1 %>%
  227.   filter(RULEDATE >= "201401") %>%
  228.   filter(RULEDATE <= "201412")%>%
  229.   summarize( b = n())
  230.  
  231. q1 <- bmf1 %>%
  232.   filter(RULEDATE >= "201501") %>%
  233.   filter(RULEDATE <= "201512")%>%
  234.   summarize( b = n())
  235.  
  236. r1 <- bmf1 %>%
  237.   filter(RULEDATE >= "201601") %>%
  238.   filter(RULEDATE <= "201612")%>%
  239.   summarize( b = n())
  240.  
  241. #Combine all of your outputs into one table
  242. final1 <- rbind(a1,b1,c1,d1,e1,f1,g1,h1,i1,j1,k1,l1,m1,n1,o1,p1,q1,r1, deparse.level = 1)
  243.  
  244. #Define a new column containing the time period labels
  245. final1$dates <- c("1969 or earlier","1970-1979","1980-1989",
  246.                   "1990-1994","1995-1999","2001-2001",
  247.                   "2002-2003","2004-2005","2006-2007",
  248.                   "2008","2009","2010","2011","2012",
  249.                   "2013","2014","2015","2016")
  250.  
  251. final3<- left_join(final,final1, copy = FALSE, suffix = c(".x", ".y"))
  252.  
  253. #Reorder the columns
  254. final3 <- final3 %>%
  255.   select(dates,a,b)
  256.  
  257. #Rename the columns
  258. colnames(final3) <- c("Date Created","Number of Organizations","Number Filing Annually")
  1. #display table
  2. 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