Active and Reporting Public Charities by State

8.27.2018
Brice McKeever

More from this project:

Number of Active and Reporting 501(c)(3) Public Charities by State

  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.                                            TOTREV = col_double(),
  19.                                            EXPS = col_double(),
  20.                                            ASS_EOY = col_double(),
  21.                                            GRREC = col_double(),
  22.                                            CONT = col_double(),
  23.                                            PROGREV = col_double()
  24.  
  25.                      ))
  26.   names(output) <- toupper(names(output))
  27.   return(output)
  28. }
  29.  
  30. #Import NCCS Core File for given year
  31. corefile <- prepcorepcfile(as.character(paste("Data/core", "2015", "pc.csv", sep="")))
  32.  
  33. #Filter out of scope organizations
  34. corefile <- corefile %>%
  35.   filter((OUTNCCS != "OUT")) %>%
  36.   filter((FNDNCD != "02" & FNDNCD!= "03" & FNDNCD != "04"))
  37.  
  38. #Summarize the variables of interest and group by State
  39. final <- corefile %>%
  40.     group_by(STATE)%>%
  41.     summarize(TotalOrgs = n(),
  42.               TotalRev = sum(TOTREV, na.rm = TRUE),
  43.               Contributions = sum(CONT, na.rm = TRUE),
  44.               ProgRev = sum(PROGREV, na.rm= TRUE),
  45.               TotalExps = sum(EXPS, na.rm = TRUE),
  46.               TotalAssets = sum(ASS_EOY, na.rm = TRUE))
  47.  
  48. #Add in full state names
  49. final <- final %>%
  50.   mutate(State = state.name[match(STATE, state.abb)])
  51.  
  52. #DC isn't recognized as a state and displays as N/A. This function renames it.
  53. final$State[final$STATE =="DC"] <- "Washington, DC"
  54.  
  55. #Rearrange the table and drop the state abbreviation column
  56. final <- final %>%
  57.   select(State, TotalOrgs, TotalRev, Contributions, ProgRev, TotalExps,TotalAssets)
  58.  
  59. #Arrange states in alphabetical order
  60. final <- final[order(final$State),]
  61.  
  62. #rename columns appropriately 
  63. colnames(final)<- c("State", "Number of Organizations", "Total Revenue","Private Contributions and Government Grants",
  64.                     "Program Service Revenue", "Total Expenses", "Total Assets")
  1. #display table
  2. 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