Registered 501(c)(3) Private Foundations By Age

9.6.2018
Brice McKeever

More from this project:

Number of Registered 501(c)(3) Private Foundations by Age

  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. #Filter out of scope orgs and non 501c3s from the BMF
  33. bmf <- bmf2016 %>%
  34.   filter((OUTNCCS != "OUT")) %>%
  35.   filter((FNDNCD == "02" | FNDNCD== "03" | FNDNCD == "04")) %>%
  36.   #Filter out non 501(c)(3)s
  37.   filter(SUBSECCD =="03")
  38.  
  39. #Exclude orgs established after 1969 and total
  40. a <- bmf  %>%
  41.   filter(RULEDATE <= "196912") %>%
  42.   summarize( a = n())
  43.  
  44. #Repeat this process for each time period of interest
  45. b <- bmf %>%
  46.   filter(RULEDATE > "196912") %>%
  47.   filter(RULEDATE <= "197912")%>%
  48.   summarize( a = n())
  49.  
  50. c <- bmf %>%
  51.   filter(RULEDATE > "197912") %>%
  52.   filter(RULEDATE <= "198912")%>%
  53.   summarize( a = n())
  54.  
  55. d <- bmf %>%
  56.   filter(RULEDATE > "198912") %>%
  57.   filter(RULEDATE <= "199412")%>%
  58.   summarize( a = n())
  59.  
  60. e <- bmf %>%
  61.   filter(RULEDATE > "199412") %>%
  62.   filter(RULEDATE <= "199912")%>%
  63.   summarize( a = n())
  64.  
  65. f <- bmf %>%
  66.   filter(RULEDATE > "199912") %>%
  67.   filter(RULEDATE <= "200112")%>%
  68.   summarize( a = n())
  69.  
  70. g <- bmf %>%
  71.   filter(RULEDATE > "200112") %>%
  72.   filter(RULEDATE <= "200312")%>%
  73.   summarize( a = n())
  74.  
  75. h <- bmf %>%
  76.   filter(RULEDATE > "200312") %>%
  77.   filter(RULEDATE <= "200512")%>%
  78.   summarize( a = n())
  79.  
  80. i <- bmf %>%
  81.   filter(RULEDATE > "200512") %>%
  82.   filter(RULEDATE <= "200712")%>%
  83.   summarize( a = n())
  84.  
  85. j <- bmf %>%
  86.   filter(RULEDATE >= "200801") %>%
  87.   filter(RULEDATE <= "200812")%>%
  88.   summarize( a = n())
  89.  
  90. k <- bmf %>%
  91.   filter(RULEDATE >= "200901") %>%
  92.   filter(RULEDATE <= "200912")%>%
  93.   summarize( a = n())
  94.  
  95. l <- bmf %>%
  96.   filter(RULEDATE >= "201001") %>%
  97.   filter(RULEDATE <= "201012")%>%
  98.   summarize( a = n())
  99.  
  100. m <- bmf %>%
  101.   filter(RULEDATE >= "201101") %>%
  102.   filter(RULEDATE <= "201112")%>%
  103.   summarize( a = n())
  104.  
  105. n <- bmf %>%
  106.   filter(RULEDATE >= "201201") %>%
  107.   filter(RULEDATE <= "201212")%>%
  108.   summarize( a = n())
  109.  
  110. o <- bmf %>%
  111.   filter(RULEDATE >= "201301") %>%
  112.   filter(RULEDATE <= "201312")%>%
  113.   summarize( a = n())
  114.  
  115. p <- bmf %>%
  116.   filter(RULEDATE >= "201401") %>%
  117.   filter(RULEDATE <= "201412")%>%
  118.   summarize( a = n())
  119.  
  120. q <- bmf %>%
  121.   filter(RULEDATE >= "201501") %>%
  122.   filter(RULEDATE <= "201512")%>%
  123.   summarize( a = n())
  124.  
  125. r <- bmf %>%
  126.   filter(RULEDATE >= "201601") %>%
  127.   filter(RULEDATE <= "201612")%>%
  128.   summarize( a = n())
  129.  
  130. #Combine all of your outputs into one table
  131. final <- rbind(a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r, deparse.level = 1)
  132.  
  133. #Define a new column containing the time period labels
  134. final$dates <- c("1969 or earlier","1970-1979","1980-1989",
  135.                  "1990-1994","1995-1999","2001-2001",
  136.                  "2002-2003","2004-2005","2006-2007",
  137.                  "2008","2009","2010","2011","2012",
  138.                  "2013","2014","2015","2016")
  139.  
  140. #Reorder the columns
  141. final <- final %>%
  142.       select(dates,a)
  143.  
  144. #Rename the columns
  145. colnames(final) <- c("Date Created","Number of Organizations")
  1. #display table
  2. kable(final, format.args = list(decimal.mark = '.', big.mark = ","))
Date Created Number of Organizations
1969 or earlier 9,927
1970-1979 4,523
1980-1989 9,663
1990-1994 8,338
1995-1999 16,151
2001-2001 8,506
2002-2003 6,491
2004-2005 5,416
2006-2007 6,552
2008 3,179
2009 3,022
2010 2,373
2011 2,208
2012 2,146
2013 2,022
2014 6,236
2015 5,316
2016 3,336

Source: Internal Revenue Service Business Master Files, Exempt Organizations, August, 2016