Largest Active and Reported Religion Related Public Charities by Expenses

9.7.2018
Deondre' Jones

More from this project:

Largest Active and Reported Religion Related Public Charities by Expenses

  1. library(tidyverse)
  2. library(knitr)
  3. library(stringr)
  4. library(scales)
  5. library(httr)
  6. source('https://raw.githubusercontent.com/UrbanInstitute/urban_R_theme/master/urban_theme_windows.R')
  7.  
  8.  
  9. #Create NTEE grouping categories
  10. arts <- c("A")
  11. highered <- c("B4", "B5")
  12. othered <- c("B")
  13. envanimals <- c("C", "D")
  14. hospitals <- c('E20','E21','E22','E23','E24','F31','E30','E31','E32')
  15. otherhlth <- c("E", "F", "G", "H")
  16. humanserv <- c("I", "J", "K", "L", "M", "N", "O", "P")
  17. intl <- c("Q")
  18. pubben <- c("R", "S", "T", "U", "V", "W", "Y", "Z")
  19. relig <- c("X")
  20.  
  21. #Import the Reduced NCCS Data Archive
  22. nteedocalleins <- read.csv("Data/nteedocalleins.csv")
  23.  
  24. #convert variable names to upper case
  25. names(nteedocalleins) <- toupper(names(nteedocalleins))
  26.  
  27. #This function will apply the most common NTEE Grouping categories to your data.
  28. NTEEclassify <- function(dataset) {
  29.   #merge in Master NTEE look up file
  30.   dataset <- dataset %>%
  31.     left_join(nteedocalleins, by = "EIN")
  32.   #create NTEEGRP classifications
  33.   dataset$NTEEGRP <- "  "
  34.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% arts ] <- "Arts"
  35.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% othered ] <- "Education: Other"
  36.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,2) %in% highered ] <- "Education: Higher"
  37.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% envanimals] <- "Environment and Animals"
  38.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% otherhlth] <- "Health Care: Other"
  39.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,3) %in% hospitals] <- "Health Care: Hospitals and primary care facilities"
  40.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% humanserv] <- "Human Services"
  41.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% intl] <- "International"
  42.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% pubben] <- "Other Public and social benefit"
  43.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% relig] <- "Religion related"
  44.   dataset$NTEEGRP[is.na(dataset$NTEEFINAL)] <- "Other Public and social benefit"
  45.   return(dataset)
  46. }
  47.  
  48. #Import reduced NCCS Core File Function
  49. prepcorepcfile <- function(corefilepath) {
  50.   output <- read_csv(corefilepath,
  51.                      col_types = cols_only(EIN = col_character(),
  52.                                            FISYR = col_integer(),
  53.                                            NAME = col_character(),
  54.                                            STATE = col_character(),
  55.                                            ADDRESS = col_character(),
  56.                                            CITY = col_character(),
  57.                                            ZIP = col_character(),
  58.                                            MSA_NECH = col_character(),
  59.                                            FIPS = col_character(),
  60.                                            PMSA = col_character(),
  61.                                            STYEAR = col_double(),
  62.                                            TAXPER = col_integer(),
  63.                                            OUTNCCS = col_character(),
  64.                                            OutNCCS = col_character(),
  65.                                            SUBSECCD = col_character(),
  66.                                            RULEDATE = col_character(),
  67.                                            FNDNCD = col_character(),
  68.                                            FRCD = col_character(),
  69.                                            TOTREV = col_double(),
  70.                                            EXPS = col_double(),
  71.                                            ASS_EOY = col_double(),
  72.                                            GRREC = col_double()
  73.  
  74.                      ))
  75.   names(output) <- toupper(names(output))
  76.   return(output)
  77. }
  78.  
  79. #Import NCCS Core File for given year
  80. corefile <- prepcorepcfile(as.character(paste("Data/core", "2015", "pc.csv", sep="")))
  81.  
  82. #Add NTEE Classifications to the Core File
  83. corefile <- NTEEclassify(corefile)
  84.  
  85. #Filter out of scope organizations 
  86. corefile <- corefile %>%
  87.   filter((OUTNCCS != "OUT")) %>%
  88.   filter((FNDNCD != "02" & FNDNCD!= "03" & FNDNCD != "04")) %>%
  89.   filter((NTEEGRP == "Religion related"))
  90.  
  91. #Sort the corefile in descending order by expenses
  92. LargestExpenses <- corefile[with(corefile,order(-EXPS)),]
  93.  
  94. #Limit the list to 10
  95. LargestExpenses <- LargestExpenses[1:10,]
  96.  
  97. #Select the appropriate columns, drop the rest
  98. LargestExpenses <- LargestExpenses %>% 
  99.   select(EIN, NTEEFINAL, NTEEGRP, NAME, EXPS)
  100.  
  101. #Rename columns appropriately
  102. colnames(LargestExpenses) <- c("EIN", "NTEE Code", "NTEE Group", "Name", "Expenses")
  1. #display table
  2. kable(LargestExpenses, format.args = list(decimal.mark = '.', big.mark = ","))
EIN NTEE Code NTEE Group Name Expenses
461551319 X20 Religion related MERCY MARICOPA INTEGRATED CARE 1,074,289,724
581493949 X1220 Religion related NATIONAL CHRISTIAN CHARITABLE FOUNDATION INC 1,001,592,651
460833951 X99 Religion related CL HEALTHCARE INC 421,565,520
540678752 X80 Religion related CHRISTIAN BROADCASTING NETWORK INC 314,780,034
391490371 X20 Religion related MINISTRY HEALTH CARE INC 312,494,766
952844062 X82 Religion related TRINITY CHRISTIAN CENTER OF SANTA ANA INC KTBN 156,436,473
222565278 X99 Religion related CATHOLIC HEALTH SYSTEM INC 140,199,728
621697490 X20 Religion related OPERATION COMPASSION A TENNESSEE NON PROFIT CORPORATION 137,740,648
204326440 X11 Religion related NCF CHARITABLE TRUST 132,057,221
341964742 X83 Religion related CHRISTIAN HEALTHCARE MINISTRIES INC 128,882,695

Source: NCCS 501(c)(3) Public Charities Core File 2015