Registered Public Charities by Age

8.27.2018
Brice McKeever

More from this project:

Number of Registered 501(c)(3) Public Charities by Age

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

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