Active and Reporting 501(c)(3) Private Foundations by State
9.5.2018
More from this project:
Active and Reporting 501(c)(3) Private Foundations by State - Annual Filers Only
library(tidyverse)
library(knitr)
library(stringr)
library(scales)
source('https://raw.githubusercontent.com/UrbanInstitute/urban_R_theme/master/urban_theme_windows.R')
#Import reduced NCCS Core File Function
prepcorepcfile <- function(corefilepath) {
output <- read_csv(corefilepath,
col_types = cols_only(EIN = col_character(),
NTEEFINAL = col_character(),
NAME = col_character(),
STATE = col_character(),
CITY = col_character(),
OUTNCCS = col_character(),
SUBSECCD = col_character(),
FNDNCD = col_character(),
P1TOTREV = col_double(),
P1TOTEXP = col_double(),
P2TASFMV = col_double(),
P1CONTPD = col_double()
))
names(output) <- toupper(names(output))
return(output)
}
#Import NCCS Core File for given year
corefile <- prepcorepcfile(as.character(paste("Data/core", "2015", "pf.csv", sep="")))
#Filter out of scope organizations
corefile <- corefile %>%
filter((OUTNCCS != "OUT")) %>%
filter((FNDNCD == "02" | FNDNCD== "03" | FNDNCD == "04"))
#Summarize the variables of interest and group by State
final <- corefile %>%
group_by(STATE)%>%
summarize(TotalOrgs = n(),
TotalRev = sum(P1TOTREV, na.rm = TRUE),
Grants = sum(P1CONTPD, na.rm= TRUE),
TotalExps = sum(P1TOTEXP, na.rm = TRUE),
TotalAssets = sum(P2TASFMV, na.rm = TRUE))
#Add in full state names
final <- final %>%
mutate(State = state.name[match(STATE, state.abb)])
#DC isn't recognized as a state and displays as N/A. This function renames it.
final$State[final$STATE =="DC"] <- "Washington, DC"
#Rearrange the table and drop the state abbreviation column
final <- final %>%
select(State, TotalOrgs, TotalRev, Grants, TotalExps,TotalAssets)
#Arrange states in alphabetical order
final <- final[order(final$State),]
#rename columns appropriately
colnames(final)<- c("State", "Number of Organizations", "Total Revenue",
"Grants Paid", "Total Expenses and Disbursements",
"Total Assets Fair Market Value")
#display table
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