Largest Active and Reporting Health Care Public Charities by Assets

9.5.2018
Deondre' Jones

More from this project:

Largest Active and Reporting Health Care Public Charities by Assets

  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. #Create NTEE grouping categories
  9. arts <- c("A")
  10. highered <- c("B4", "B5")
  11. othered <- c("B")
  12. envanimals <- c("C", "D")
  13. hospitals <- c('E20','E21','E22','E23','E24','F31','E30','E31','E32')
  14. otherhlth <- c("E", "F", "G", "H")
  15. humanserv <- c("I", "J", "K", "L", "M", "N", "O", "P")
  16. intl <- c("Q")
  17. pubben <- c("R", "S", "T", "U", "V", "W", "Y", "Z")
  18. relig <- c("X")
  19.  
  20. #Import the Reduced NCCS Data Archive
  21. nteedocalleins <- read.csv("Data/nteedocalleins.csv")
  22.  
  23. #convert variable names to upper case
  24. names(nteedocalleins) <- toupper(names(nteedocalleins))
  25.  
  26. #This function will apply the most common NTEE Grouping categories to your data.
  27. NTEEclassify <- function(dataset) {
  28.   #merge in Master NTEE look up file
  29.   dataset <- dataset %>%
  30.     left_join(nteedocalleins, by = "EIN")
  31.   #create NTEEGRP classifications
  32.   dataset$NTEEGRP <- "  "
  33.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% arts ] <- "Arts"
  34.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% othered ] <- "Education: Other"
  35.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,2) %in% highered ] <- "Education: Higher"
  36.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% envanimals] <- "Environment and Animals"
  37.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% otherhlth] <- "Health Care: Other"
  38.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,3) %in% hospitals] <- "Health Care: Hospitals and primary care facilities"
  39.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% humanserv] <- "Human Services"
  40.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% intl] <- "International"
  41.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% pubben] <- "Other Public and social benefit"
  42.   dataset$NTEEGRP[str_sub(dataset$NTEEFINAL,1,1) %in% relig] <- "Religion related"
  43.   dataset$NTEEGRP[is.na(dataset$NTEEFINAL)] <- "Other Public and social benefit"
  44.   return(dataset)
  45. }
  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.  
  80. #Import NCCS Core File for given year
  81. corefile <- prepcorepcfile(as.character(paste("Data/core", "2015", "pc.csv", sep="")))
  82.  
  83. #Add NTEE Classifications to the Core File
  84. corefile <- NTEEclassify(corefile)
  85.  
  86. #Filter out of scope organizations 
  87. corefile <- corefile %>%
  88.   filter((OUTNCCS != "OUT")) %>%
  89.   filter((FNDNCD != "02" & FNDNCD!= "03" & FNDNCD != "04")) %>%
  90.   filter((NTEEGRP == "Health Care: Other" | NTEEGRP == "Health Care: Hospitals and primary care facilities"))
  91.  
  92. #Sort the corefile in descending order by assets
  93. LargestAssets <- corefile[with(corefile,order(-ASS_EOY)),]
  94.  
  95. #Limit the list to 10
  96. LargestAssets <- LargestAssets[1:10,]
  97.  
  98. #Select the appropriate columns, drop the rest
  99. LargestAssets <- LargestAssets %>% 
  100.   select(EIN, NTEEFINAL, NTEEGRP, NAME, ASS_EOY)
  101.  
  102. #Rename columns appropriately
  103. colnames(LargestAssets) <- c("EIN", "NTEE Code", "NTEE Group", "Name", "Total Assets")
  1. #display table
  2. kable(LargestAssets, format.args = list(decimal.mark = '.', big.mark = ","))
EIN NTEE Code NTEE Group Name Total Assets
941105628 E21 Health Care: Hospitals and primary care facilities KAISER FOUNDATION HOSPITALS 43,991,189,809
590735717 H90 Health Care: Other HOWARD HUGHES MEDICAL INSTITUTE 21,693,736,438
941340523 E31 Health Care: Hospitals and primary care facilities KAISER FOUNDATION HEALTH PLAN INC 19,173,143,251
900656139 E21 Health Care: Hospitals and primary care facilities PARTNERS HEALTHCARE SYSTEM INC AFFILIATES GROUP RETURN 14,983,611,002
941196203 E21 Health Care: Hospitals and primary care facilities DIGNITY HEALTH 14,252,350,035
912153073 E21 Health Care: Hospitals and primary care facilities CLEVELAND CLINIC FOUNDATION GROUP RETURN 12,621,778,142
510216586 E21 Health Care: Hospitals and primary care facilities PROVIDENCE HEALTH AND SERVICES WASHINGTON 10,159,130,383
416011702 E21 Health Care: Hospitals and primary care facilities MAYO CLINIC 10,051,482,383
912154267 E24 Health Care: Hospitals and primary care facilities MEMORIAL SLOAN KETTERING CANCER CENTER GROUP RETURN 9,586,730,000
450233470 E21 Health Care: Hospitals and primary care facilities BANNER HEALTH 9,499,405,153

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