Largest Active and Reporting Arts Public Charities by End of Year Assets

9.5.2018
Deondre' Jones

More from this project:

Largest Active and Reporting Arts Public Charities by End of Year 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.  
  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 == "Arts"))
  90.  
  91.  
  92. #Sort the corefile in descending order by EOY Assets
  93. LargestExpenses <- corefile[with(corefile,order(-ASS_EOY)),]
  94.  
  95. #Limit the list to 10
  96. LargestExpenses <- LargestExpenses[1:10,]
  97.  
  98. #Select the appropriate columns, drop the rest
  99. LargestExpenses <- LargestExpenses %>% 
  100.   select(EIN, NTEEFINAL, NTEEGRP, NAME, ASS_EOY)
  101.  
  102. #Rename columns appropriately
  103. colnames(LargestExpenses) <- c("EIN", "NTEE Code", "NTEE Group", "Name", "End of Year Assets")
  1. #display table
  2. kable(LargestExpenses, format.args = list(decimal.mark = '.', big.mark = ","))
EIN NTEE Code NTEE Group Name End of Year Assets
131599108 A33 Arts CHANCELLOR MASTERS AND SCHOLARS OF THE UNIVERSITY OF CAMBRIDGE 7,283,671,077
131624086 A51 Arts THE METROPOLITAN MUSEUM OF ART 4,002,429,655
131624100 A51 Arts MUSEUM OF MODERN ART 1,717,946,613
741109655 A51 Arts MUSEUM OF FINE ARTS HOUSTON 1,575,446,456
540505888 A80 Arts COLONIAL WILLIAMSBURG FOUNDATION 1,534,434,518
136162659 A56 Arts AMERICAN MUSEUM OF NATURAL HISTORY 1,304,049,203
042103607 A51 Arts MUSEUM OF FINE ARTS 1,186,766,810
340714336 A51 Arts CLEVELAND MUSEUM OF ART 1,154,707,265
536001666 A51 Arts NATIONAL GALLERY OF ART 1,144,930,052
131789318 A51 Arts WHITNEY MUSEUM OF AMERICAN ART 888,573,000

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