More from this project:
Number of Active and Reporting 501(c)(3) Public Charities by State
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(),
TOTREV = col_double(),
EXPS = col_double(),
ASS_EOY = col_double(),
GRREC = col_double(),
CONT = col_double(),
PROGREV = col_double()
))
names(output) <- toupper(names(output))
return(output)
}
#Import NCCS Core File for given year
corefile <- prepcorepcfile(as.character(paste("Data/core", "2015", "pc.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(TOTREV, na.rm = TRUE),
Contributions = sum(CONT, na.rm = TRUE),
ProgRev = sum(PROGREV, na.rm= TRUE),
TotalExps = sum(EXPS, na.rm = TRUE),
TotalAssets = sum(ASS_EOY, 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, Contributions, ProgRev, TotalExps,TotalAssets)
#Arrange states in alphabetical order
final <- final[order(final$State),]
#rename columns appropriately
colnames(final)<- c("State", "Number of Organizations", "Total Revenue","Private Contributions and Government Grants",
"Program Service Revenue", "Total Expenses", "Total Assets")
#display table
kable(final, format.args = list(decimal.mark = '.', big.mark = ","))
State | Number of Organizations | Total Revenue | Private Contributions and Government Grants | Program Service Revenue | Total Expenses | Total Assets |
---|---|---|---|---|---|---|
Alabama | 5,055 | 10,643,762,504 | 2,923,550,794 | 6,943,296,796 | 10,007,465,758 | 19,283,140,548 |
Alaska | 1,584 | 3,774,094,488 | 1,864,920,062 | 1,723,809,500 | 3,466,264,395 | 6,631,323,807 |
Arizona | 6,131 | 29,404,380,129 | 6,713,345,946 | 21,486,358,282 | 27,836,332,468 | 36,578,231,558 |
Arkansas | 3,374 | 10,052,030,058 | 2,132,323,021 | 7,398,850,811 | 9,483,892,871 | 16,015,915,994 |
California | 51,113 | 189,386,407,759 | 53,831,470,708 | 123,707,987,690 | 172,819,767,277 | 353,950,199,116 |
Colorado | 9,013 | 27,998,325,728 | 7,378,519,175 | 18,916,437,438 | 26,988,332,363 | 55,509,566,169 |
Connecticut | 6,362 | 33,186,463,771 | 6,788,398,583 | 21,667,378,808 | 29,624,151,548 | 86,308,528,758 |
Delaware | 1,432 | 3,812,613,651 | 1,260,388,977 | 2,241,905,105 | 3,393,041,017 | 8,117,285,816 |
Florida | 20,895 | 77,042,152,200 | 19,770,800,316 | 53,335,019,483 | 71,792,998,273 | 132,084,774,526 |
Georgia | 11,067 | 47,223,415,566 | 12,997,880,072 | 31,387,913,874 | 43,847,446,314 | 87,901,433,683 |
Hawaii | 2,188 | 6,877,863,145 | 1,391,401,060 | 4,310,853,974 | 6,052,667,768 | 21,093,423,863 |
Idaho | 1,848 | 5,044,963,489 | 776,025,520 | 4,062,876,124 | 4,744,170,998 | 6,915,232,009 |
Illinois | 16,552 | 84,420,192,096 | 17,550,991,063 | 60,842,325,482 | 79,248,665,369 | 177,169,085,072 |
Indiana | 8,417 | 38,360,359,788 | 5,795,909,459 | 29,438,920,395 | 34,078,776,734 | 82,117,217,131 |
Iowa | 4,988 | 15,120,799,812 | 2,440,994,631 | 11,668,962,731 | 14,117,523,769 | 31,799,698,917 |
Kansas | 3,916 | 10,424,653,406 | 2,045,590,128 | 7,753,704,156 | 9,904,520,168 | 18,401,953,953 |
Kentucky | 4,810 | 22,308,768,644 | 2,826,264,517 | 18,349,150,239 | 21,329,670,542 | 33,200,929,940 |
Louisiana | 4,705 | 22,361,019,248 | 3,270,605,613 | 18,247,777,086 | 21,504,832,148 | 31,901,335,783 |
Maine | 2,752 | 12,631,447,618 | 1,429,773,091 | 10,484,753,352 | 11,994,313,058 | 18,630,638,757 |
Maryland | 9,261 | 47,214,471,828 | 9,881,374,285 | 32,878,471,342 | 43,971,059,623 | 101,744,947,231 |
Massachusetts | 14,657 | 107,300,342,561 | 26,689,198,875 | 68,392,149,201 | 95,422,282,971 | 299,065,206,813 |
Michigan | 11,669 | 52,017,218,969 | 7,928,610,609 | 41,320,207,471 | 49,207,797,906 | 80,606,439,590 |
Minnesota | 9,399 | 48,725,475,377 | 7,503,000,735 | 38,860,842,420 | 46,314,030,670 | 83,303,458,869 |
Mississippi | 2,576 | 6,565,672,466 | 1,458,212,021 | 4,721,464,631 | 6,355,502,691 | 11,886,154,694 |
Missouri | 7,699 | 42,599,472,546 | 6,649,075,939 | 32,617,503,059 | 39,529,169,137 | 83,130,424,158 |
Montana | 2,352 | 5,943,773,498 | 1,080,471,481 | 4,602,065,819 | 5,481,221,001 | 9,425,372,819 |
Nebraska | 2,987 | 12,207,479,946 | 2,191,923,298 | 9,235,767,061 | 11,417,912,858 | 25,921,785,508 |
Nevada | 2,114 | 3,475,937,699 | 1,175,843,115 | 2,117,811,307 | 3,104,531,375 | 6,914,392,507 |
New Hampshire | 2,646 | 10,588,083,081 | 1,282,144,556 | 8,298,538,110 | 10,024,880,664 | 24,089,836,371 |
New Jersey | 12,799 | 45,349,313,560 | 7,513,838,942 | 34,033,863,138 | 42,059,387,795 | 94,830,886,570 |
New Mexico | 2,764 | 5,426,121,967 | 1,379,020,124 | 3,745,743,764 | 5,122,065,001 | 10,415,743,991 |
New York | 32,753 | 217,178,399,666 | 50,149,512,268 | 154,336,356,803 | 206,247,155,185 | 349,495,573,621 |
North Carolina | 12,068 | 51,915,465,819 | 12,387,104,108 | 35,340,275,800 | 47,925,623,763 | 105,485,727,399 |
North Dakota | 1,318 | 7,376,400,166 | 700,310,781 | 6,504,668,006 | 6,945,599,112 | 8,691,920,359 |
Ohio | 16,043 | 98,055,290,903 | 16,627,908,510 | 75,495,472,751 | 91,764,162,748 | 160,538,343,776 |
Oklahoma | 4,429 | 11,574,056,083 | 3,481,229,926 | 7,399,176,018 | 10,940,078,310 | 29,995,311,004 |
Oregon | 7,106 | 101,255,547,374 | 4,537,796,903 | 94,080,488,963 | 97,186,807,263 | 95,945,269,563 |
Pennsylvania | 19,338 | 109,888,805,097 | 17,614,546,086 | 84,149,831,318 | 102,128,903,810 | 225,037,025,359 |
Rhode Island | 1,826 | 11,332,054,405 | 2,390,473,910 | 8,385,852,498 | 10,803,981,021 | 19,396,886,993 |
South Carolina | 5,562 | 13,364,085,271 | 3,098,147,027 | 9,382,476,474 | 12,528,191,924 | 29,984,830,365 |
South Dakota | 1,464 | 5,178,738,850 | 717,560,450 | 4,239,300,210 | 4,889,672,150 | 10,764,437,418 |
Tennessee | 7,636 | 30,176,058,562 | 7,185,207,326 | 21,276,107,760 | 28,233,260,233 | 57,605,763,121 |
Texas | 26,611 | 85,630,731,177 | 21,255,663,377 | 57,718,509,198 | 78,207,735,711 | 188,657,474,427 |
Utah | 2,419 | 9,160,224,018 | 1,799,584,140 | 6,854,855,700 | 8,118,856,490 | 15,307,954,601 |
Vermont | 1,908 | 5,105,252,025 | 913,632,488 | 3,878,929,581 | 4,766,628,451 | 8,841,067,561 |
Virginia | 12,008 | 49,354,555,252 | 13,498,806,645 | 32,164,379,488 | 46,181,044,859 | 105,572,594,837 |
Washington | 9,747 | 45,819,376,440 | 8,305,222,182 | 32,852,028,133 | 43,173,769,732 | 75,314,172,931 |
Washington, DC | 4,627 | 37,675,911,880 | 22,105,221,482 | 12,993,474,921 | 32,993,585,275 | 60,896,372,739 |
West Virginia | 2,378 | 8,782,722,607 | 1,068,653,137 | 7,305,120,862 | 8,431,334,629 | 13,064,404,729 |
Wisconsin | 8,421 | 42,683,495,026 | 5,632,995,942 | 35,135,498,054 | 39,125,112,619 | 72,944,652,892 |
Wyoming | 1,158 | 1,250,896,248 | 557,788,394 | 603,606,551 | 1,139,772,433 | 3,053,199,345 |
Source: National Center for Charitable Statistics, Core Files (Public Charities), 2015