Largest Active and Reporting Civil Rights, Social Action & Advocacy Public Charities by Expenses
9.13.2018
More from this project:
Largest Active and Reporting Civil Rights, Social Action & Advocacy Public Charities by Expenses
library(tidyverse)
library(knitr)
library(stringr)
library(scales)
library(httr)
source('https://raw.githubusercontent.com/UrbanInstitute/urban_R_theme/master/urban_theme_windows.R')
#Create NTEE grouping categories
arts <- c("A")
highered <- c("B4", "B5")
othered <- c("B")
envanimals <- c("C", "D")
hospitals <- c('E20','E21','E22','E23','E24','F31','E30','E31','E32')
otherhlth <- c("E", "F", "G", "H")
humanserv <- c("I", "J", "K", "L", "M", "N", "O", "P")
intl <- c("Q")
pubben <- c("R", "S", "T", "U", "V", "W", "Y", "Z")
relig <- c("X")
#link to NCCS Data Archive
nteedoc<- GET("http://nccs-data.urban.org/data/misc/nccs.nteedocAllEins.csv")
#pull only the most important columns (EIN, , NTEECC, Nteefinal)
nteedocalleins <-content(nteedoc, type = "text/csv",
col_types=cols_only(EIN = col_character(),
NteeCC = col_character(),
NteeFinal = col_character()))
#convert variable names to upper case
names(nteedocalleins) <- toupper(names(nteedocalleins))
#This function will apply the most common NTEE Grouping categories to your data.
NTEEclassify <- function(dataset) {
#merge in Master NTEE look up file
dataset <- dataset %>%
left_join(nteedocalleins, by = "EIN")
#create NTEEGRP classifications
dataset$NTEEGRP <- " "
dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% arts ] <- "Arts"
dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% othered ] <- "Education: Other"
dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,2) %in% highered ] <- "Education: Higher"
dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% envanimals] <- "Environment and Animals"
dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% otherhlth] <- "Health Care: Other"
dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,3) %in% hospitals] <- "Health Care: Hospitals and primary care facilities"
dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% humanserv] <- "Human Services"
dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% intl] <- "International"
dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% pubben] <- "Other Public and social benefit"
dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% relig] <- "Religion related"
dataset$NTEEGRP[is.na(dataset$NTEEFINAL)] <- "Other Public and social benefit"
return(dataset)
}
#Import reduced NCCS Core File Function
prepcorepcfile <- function(corefilepath) {
output <- read_csv(corefilepath,
col_types = cols_only(EIN = col_character(),
FISYR = col_integer(),
NAME = col_character(),
STATE = col_character(),
ADDRESS = col_character(),
CITY = col_character(),
ZIP = col_character(),
MSA_NECH = col_character(),
FIPS = col_character(),
PMSA = col_character(),
STYEAR = col_double(),
TAXPER = col_integer(),
OUTNCCS = col_character(),
OutNCCS = col_character(),
SUBSECCD = col_character(),
RULEDATE = col_character(),
FNDNCD = col_character(),
FRCD = col_character(),
TOTREV = col_double(),
EXPS = col_double(),
ASS_EOY = col_double(),
GRREC = 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="")))
#Add NTEE Classifications to the Core File
corefile <- NTEEclassify(corefile)
#Filter out of scope organizations
corefile <- corefile %>%
filter((OUTNCCS != "OUT")) %>%
filter((FNDNCD != "02" & FNDNCD!= "03" & FNDNCD != "04")) %>%
filter(str_detect(NTEEFINAL, "R"))
#Sort the corefile in descending order by expenses
LargestExpenses <- corefile[with(corefile,order(-EXPS)),]
#Limit the list to 10
LargestExpenses <- LargestExpenses[1:10,]
#Select the appropriate columns, drop the rest
LargestExpenses <- LargestExpenses %>%
select(EIN, NTEEFINAL, NTEEGRP, NAME, EXPS)
#Rename columns appropriately
colnames(LargestExpenses) <- c("EIN", "NTEE Code", "NTEE Group", "Name", "Expenses")
#display table
kable(LargestExpenses, format.args = list(decimal.mark = '.', big.mark = ","))
EIN | NTEE Code | NTEE Group | Name | Expenses |
---|---|---|---|---|
870807914 | R22 | Other Public and social benefit | CORE PHYSICIANS LLC | 88,884,305 |
136213516 | R60 | Other Public and social benefit | AMERICAN CIVIL LIBERTIES UNION FOUNDATION INC | 84,257,642 |
366110299 | R20 | Other Public and social benefit | AMERICAN BAR ASSOCIATION FUND FOR JUSTICE AND EDUCATION | 56,261,962 |
131818723 | R60 | Other Public and social benefit | ANTI DEFAMATION LEAGUE | 55,261,213 |
910873623 | R22 | Other Public and social benefit | ARCTIC SLOPE NATIVE ASSOCIATION LTD | 54,161,223 |
541660459 | R60 | Other Public and social benefit | ALLIANCE DEFENDING FREEDOM | 48,935,954 |
135563393 | R20 | Other Public and social benefit | AMERICAN JEWISH COMMITTEE | 48,596,183 |
521710886 | R0160 | Other Public and social benefit | THE NRA FOUNDATION INC | 45,577,207 |
131932384 | R25 | Other Public and social benefit | NATIONAL COUNCIL ON THE AGING INC | 43,994,236 |
943037261 | R60 | Other Public and social benefit | CHRISTIAN ADVOCATES SERVING EVANGELISM INC | 43,852,401 |
Source: NCCS 501(c)(3) Public Charities Core File 2015