Active and Reporting 501(c)(3) Private Foundations by State

9.5.2018
Deondre' Jones

More from this project:

Active and Reporting 501(c)(3) Private Foundations by State - Annual Filers Only

  1. library(tidyverse)
  2. library(knitr)
  3. library(stringr)
  4. library(scales)
  5. source('https://raw.githubusercontent.com/UrbanInstitute/urban_R_theme/master/urban_theme_windows.R')
  6.  
  7. #Import reduced NCCS Core File Function
  8. prepcorepcfile <- function(corefilepath) {
  9.   output <- read_csv(corefilepath,
  10.                      col_types = cols_only(EIN = col_character(),
  11.                                            NTEEFINAL = col_character(),
  12.                                            NAME = col_character(),
  13.                                            STATE = col_character(),
  14.                                            CITY = col_character(),
  15.                                            OUTNCCS = col_character(),
  16.                                            SUBSECCD = col_character(),
  17.                                            FNDNCD = col_character(),
  18.                                            P1TOTREV = col_double(),
  19.                                            P1TOTEXP = col_double(),
  20.                                            P2TASFMV = col_double(),
  21.                                            P1CONTPD = col_double()
  22.  
  23.                      ))
  24.   names(output) <- toupper(names(output))
  25.   return(output)
  26. }
  27.  
  28. #Import NCCS Core File for given year
  29. corefile <- prepcorepcfile(as.character(paste("Data/core", "2015", "pf.csv", sep="")))
  30.  
  31. #Filter out of scope organizations
  32. corefile <- corefile %>%
  33.   filter((OUTNCCS != "OUT")) %>%
  34.   filter((FNDNCD == "02" | FNDNCD== "03" | FNDNCD == "04"))
  35.  
  36. #Summarize the variables of interest and group by State
  37. final <- corefile %>%
  38.   group_by(STATE)%>%
  39.   summarize(TotalOrgs = n(),
  40.             TotalRev = sum(P1TOTREV, na.rm = TRUE),
  41.             Grants = sum(P1CONTPD, na.rm= TRUE),
  42.             TotalExps = sum(P1TOTEXP, na.rm = TRUE),
  43.             TotalAssets = sum(P2TASFMV, na.rm = TRUE))
  44.  
  45. #Add in full state names
  46. final <- final %>%
  47.   mutate(State = state.name[match(STATE, state.abb)])
  48.  
  49. #DC isn't recognized as a state and displays as N/A. This function renames it.
  50. final$State[final$STATE =="DC"] <- "Washington, DC"
  51.  
  52. #Rearrange the table and drop the state abbreviation column
  53. final <- final %>%
  54.   select(State, TotalOrgs, TotalRev, Grants, TotalExps,TotalAssets)
  55.  
  56. #Arrange states in alphabetical order
  57. final <- final[order(final$State),]
  58.  
  59. #rename columns appropriately 
  60. colnames(final)<- c("State", "Number of Organizations", "Total Revenue",
  61.                   "Grants Paid", "Total Expenses and Disbursements", 
  62.                   "Total Assets Fair Market Value")
  1. #display table
  2. kable(final, format.args = list(decimal.mark = '.', big.mark = ","))
State Number of Organizations Total Revenue Grants Paid Total Expenses and Disbursements Total Assets Fair Market Value
Alabama 1,114 295,096,785 194,421,491 250,725,477 2,923,124,255
Alaska 116 87,670,482 35,855,357 63,919,347 980,322,593
Arizona 1,138 587,901,930 293,532,175 552,827,749 4,993,485,471
Arkansas 446 1,688,325,854 710,450,924 1,018,988,803 5,993,020,926
California 10,448 15,192,040,508 7,530,418,907 10,298,139,072 125,594,378,518
Colorado 1,773 1,090,961,445 629,622,973 872,519,982 11,051,121,162
Connecticut 1,822 2,129,087,494 982,984,003 1,146,816,291 10,010,134,523
Delaware 1,513 1,244,041,049 920,451,644 1,081,697,361 11,777,986,947
Florida 6,511 2,869,645,085 1,749,284,555 2,290,917,811 28,033,309,545
Georgia 1,996 1,704,326,167 1,067,393,288 1,378,563,496 15,673,845,913
Hawaii 478 275,842,371 107,399,809 196,599,455 3,099,073,387
Idaho 346 179,689,723 83,789,098 119,728,812 1,805,273,633
Illinois 5,869 5,841,702,370 3,976,135,069 4,580,613,153 39,280,498,674
Indiana 1,382 1,848,311,193 901,769,350 1,508,664,202 19,133,980,779
Iowa 1,056 381,563,899 179,177,836 241,398,173 3,418,393,570
Kansas 882 423,926,957 244,242,716 311,196,086 3,938,520,255
Kentucky 710 302,335,048 160,864,700 208,391,178 2,757,404,188
Louisiana 753 485,641,301 187,190,364 288,346,334 3,665,787,835
Maine 451 225,291,771 150,333,201 210,127,308 2,491,304,573
Maryland 1,847 2,047,885,177 854,667,146 1,167,287,141 17,043,614,925
Massachusetts 3,576 3,127,002,187 1,504,472,608 2,023,231,857 22,152,638,263
Michigan 2,594 3,679,551,343 2,193,793,149 2,647,243,791 21,453,933,182
Minnesota 1,569 2,289,624,186 1,283,047,552 1,566,725,784 16,833,631,394
Mississippi 378 181,715,717 89,526,482 131,311,049 1,408,241,284
Missouri 1,554 1,371,315,518 584,621,838 858,466,995 10,958,841,149
Montana 337 269,499,587 90,844,799 111,158,034 1,927,816,481
Nebraska 683 1,090,766,320 817,361,918 903,019,280 7,761,542,137
Nevada 734 831,962,013 371,560,622 447,066,917 4,621,245,799
New Hampshire 523 450,510,390 171,830,006 244,987,647 2,354,590,498
New Jersey 3,419 5,737,285,467 4,239,864,041 5,021,903,377 25,014,432,775
New Mexico 393 259,092,838 84,555,272 135,826,456 1,725,884,259
New York 11,767 18,910,189,630 11,626,741,695 14,247,292,744 151,677,763,015
North Carolina 4,200 1,874,091,510 1,163,128,649 1,423,804,830 17,634,654,131
North Dakota 133 62,954,579 21,909,633 37,549,897 358,117,357
Ohio 3,697 1,767,749,147 1,215,011,394 1,613,472,879 16,899,079,887
Oklahoma 1,022 675,454,923 628,960,782 796,127,723 9,974,839,857
Oregon 908 687,130,424 333,329,580 449,408,712 5,404,557,019
Pennsylvania 5,427 4,931,482,897 2,989,326,212 3,596,656,415 43,193,416,998
Rhode Island 1,836 427,751,127 308,789,689 390,862,897 5,936,223,503
South Carolina 710 227,834,547 137,724,710 181,720,438 2,074,523,029
South Dakota 195 167,459,398 41,589,437 70,532,687 954,136,311
Tennessee 1,116 1,022,326,735 484,895,931 617,000,211 6,793,078,113
Texas 6,069 5,911,720,111 3,056,425,046 3,964,265,282 47,833,802,718
Utah 894 296,975,934 253,455,029 314,257,973 3,969,491,463
Vermont 275 149,714,848 50,118,650 90,060,627 812,655,051
Virginia 1,934 1,007,103,395 604,660,676 786,136,317 8,521,662,987
Washington 1,633 11,579,353,987 11,044,008,182 12,313,754,839 92,299,688,224
Washington, DC 508 1,275,479,831 391,201,074 610,863,266 9,701,429,096
West Virginia 348 121,153,607 64,463,150 95,109,768 1,119,851,644
Wisconsin 2,438 1,294,078,426 715,495,036 887,953,964 13,088,719,899
Wyoming 298 160,202,980 121,653,583 147,104,583 1,467,432,418

Source: NCCS 501(c)(3) Private Foundations Core File 2015