Setup
Build the Table
- Write to File
Diagnostics
- Missing or Extra XPATHS
- Failes URLs
XML Table Structure
- Create Network Edgelists
- Visualize Tree Structure
XPATH Summary

Setup

Load Packages

library( irs990efile )  # functions for parsing efile XMLs
library( dplyr )        # data wrangling
library( purrr )        # data wrangling 
library( pander )       # formatting
library( knitr )        # formatting
library( DT )           # table printing
library( httr )         # web requests 
library( xmltools )     # xml utilities
library( xml2 )         # xml utilities
library( XML )          # xml utilities 
library( data.tree )    # network visualization
library( networkD3 )    # network visualization
library( igraph )       # network visualization

source( "R/rdb-functions-v2.R")
source( "R/utils.R")

Data

head( index )

The Concordance File

The concordance is an RDB to XML Crosswalk that maps all xpaths onto a specified set of variables. It is located in the irs990efile package.

The full data dictionary is available on GitHub.

DATA DICTIONARY

concordance %>% 
  filter( rdb_table == table.name )

xpath	id
/Return/ReturnData/IRS990/Activity2/ActivityCode	1
/Return/ReturnData/IRS990/Activity2/Description	2
/Return/ReturnData/IRS990/Activity2/Expense	3
/Return/ReturnData/IRS990/Activity2/Grants	4
/Return/ReturnData/IRS990/Activity2/Revenue	5
/Return/ReturnData/IRS990/Activity3/ActivityCode	6
/Return/ReturnData/IRS990/Activity3/Description	7
/Return/ReturnData/IRS990/Activity3/Expense	8
/Return/ReturnData/IRS990/Activity3/Grants	9
/Return/ReturnData/IRS990/Activity3/Revenue	10
/Return/ReturnData/IRS990/ActivityCd	11
/Return/ReturnData/IRS990/ActivityCode	12
/Return/ReturnData/IRS990/ActivityOther/ActivityCode	13
/Return/ReturnData/IRS990/ActivityOther/Description	14
/Return/ReturnData/IRS990/ActivityOther/Expense	15
/Return/ReturnData/IRS990/ActivityOther/Grants	16
/Return/ReturnData/IRS990/ActivityOther/Revenue	17
/Return/ReturnData/IRS990/Desc	18
/Return/ReturnData/IRS990/Description	19
/Return/ReturnData/IRS990/Expense	20
/Return/ReturnData/IRS990/ExpenseAmt	21
/Return/ReturnData/IRS990/Form990PartIII/Activity2/ActivityCode	22
/Return/ReturnData/IRS990/Form990PartIII/Activity2/Description	23
/Return/ReturnData/IRS990/Form990PartIII/Activity2/Expense	24
/Return/ReturnData/IRS990/Form990PartIII/Activity2/Grants	25
/Return/ReturnData/IRS990/Form990PartIII/Activity2/Revenue	26
/Return/ReturnData/IRS990/Form990PartIII/Activity3/ActivityCode	27
/Return/ReturnData/IRS990/Form990PartIII/Activity3/Description	28
/Return/ReturnData/IRS990/Form990PartIII/Activity3/Expense	29
/Return/ReturnData/IRS990/Form990PartIII/Activity3/Grants	30
/Return/ReturnData/IRS990/Form990PartIII/Activity3/Revenue	31
/Return/ReturnData/IRS990/Form990PartIII/ActivityCode	32
/Return/ReturnData/IRS990/Form990PartIII/ActivityOther/ActivityCode	33
/Return/ReturnData/IRS990/Form990PartIII/ActivityOther/Description	34
/Return/ReturnData/IRS990/Form990PartIII/ActivityOther/Expense	35
/Return/ReturnData/IRS990/Form990PartIII/ActivityOther/Grants	36
/Return/ReturnData/IRS990/Form990PartIII/ActivityOther/Revenue	37
/Return/ReturnData/IRS990/Form990PartIII/Description	38
/Return/ReturnData/IRS990/Form990PartIII/Expense	39
/Return/ReturnData/IRS990/Form990PartIII/Grants	40
/Return/ReturnData/IRS990/Form990PartIII/Revenue	41
/Return/ReturnData/IRS990/Form990PartIII/TotalOfOtherProgramServiceExp	42
/Return/ReturnData/IRS990/Form990PartIII/TotalOfOtherProgramServiceGrnt	43
/Return/ReturnData/IRS990/Form990PartIII/TotalOfOtherProgramServiceRev	44
/Return/ReturnData/IRS990/GrantAmt	45
/Return/ReturnData/IRS990/Grants	46
/Return/ReturnData/IRS990/ProgSrvcAccomActy2Grp/ActivityCd	47
/Return/ReturnData/IRS990/ProgSrvcAccomActy2Grp/Desc	48
/Return/ReturnData/IRS990/ProgSrvcAccomActy2Grp/ExpenseAmt	49
/Return/ReturnData/IRS990/ProgSrvcAccomActy2Grp/GrantAmt	50
/Return/ReturnData/IRS990/ProgSrvcAccomActy2Grp/RevenueAmt	51
/Return/ReturnData/IRS990/ProgSrvcAccomActy3Grp/ActivityCd	52
/Return/ReturnData/IRS990/ProgSrvcAccomActy3Grp/Desc	53
/Return/ReturnData/IRS990/ProgSrvcAccomActy3Grp/ExpenseAmt	54
/Return/ReturnData/IRS990/ProgSrvcAccomActy3Grp/GrantAmt	55
/Return/ReturnData/IRS990/ProgSrvcAccomActy3Grp/RevenueAmt	56
/Return/ReturnData/IRS990/ProgSrvcAccomActyOtherGrp/ActivityCd	57
/Return/ReturnData/IRS990/ProgSrvcAccomActyOtherGrp/Desc	58
/Return/ReturnData/IRS990/ProgSrvcAccomActyOtherGrp/ExpenseAmt	59
/Return/ReturnData/IRS990/ProgSrvcAccomActyOtherGrp/GrantAmt	60
/Return/ReturnData/IRS990/ProgSrvcAccomActyOtherGrp/RevenueAmt	61
/Return/ReturnData/IRS990/ProgramServiceAccomplishments/DescriptionProgramServiceAccom	62
/Return/ReturnData/IRS990/ProgramServiceAccomplishments/GrantsAndAllocations	63
/Return/ReturnData/IRS990/ProgramServiceAccomplishments/ProgramServiceExpenses	64
/Return/ReturnData/IRS990/Revenue	65
/Return/ReturnData/IRS990/RevenueAmt	66
/Return/ReturnData/IRS990/TotalOfOtherProgramServiceExp	67
/Return/ReturnData/IRS990/TotalOfOtherProgramServiceGrnt	68
/Return/ReturnData/IRS990/TotalOfOtherProgramServiceRev	69
/Return/ReturnData/IRS990/TotalOtherProgSrvcExpenseAmt	70
/Return/ReturnData/IRS990/TotalOtherProgSrvcGrantAmt	71
/Return/ReturnData/IRS990/TotalOtherProgSrvcRevenueAmt	72
/Return/ReturnData/IRS990EZ/GrantAmt	73
/Return/ReturnData/IRS990EZ/Grants	74
/Return/ReturnData/IRS990EZ/ProgSrvcAccomActy2Grp/Desc	75
/Return/ReturnData/IRS990EZ/ProgSrvcAccomActy2Grp/GrantAmt	76
/Return/ReturnData/IRS990EZ/ProgSrvcAccomActy3Grp/Desc	77
/Return/ReturnData/IRS990EZ/ProgSrvcAccomActy3Grp/GrantAmt	78
/Return/ReturnData/IRS990EZ/ProgSrvcAccomActyOtherGrp/Desc	79
/Return/ReturnData/IRS990EZ/ProgSrvcAccomActyOtherGrp/GrantAmt	80
/Return/ReturnData/IRS990EZ/ProgramServiceAccomplishment/DescriptionProgramServiceAccom	81
/Return/ReturnData/IRS990EZ/ProgramServiceAccomplishment/GrantsAndAllocations	82
/Return/ReturnData/IRS990EZ/ProgramServiceAccomplishment/IncludesForeignGrants	83
/Return/ReturnData/IRS990EZ/ProgramServiceAccomplishment/ProgramServiceExpenses	84
/Return/ReturnData/IRS990EZ/ProgramSrvcAccomplishmentGrp/DescriptionProgramSrvcAccomTxt	85
/Return/ReturnData/IRS990EZ/ProgramSrvcAccomplishmentGrp/ForeignGrantsInd	86
/Return/ReturnData/IRS990EZ/ProgramSrvcAccomplishmentGrp/GrantsAndAllocationsAmt	87
/Return/ReturnData/IRS990EZ/ProgramSrvcAccomplishmentGrp/ProgramServiceExpensesAmt	88

xpaths <- as.character( t.xpaths$xpath )
el <- create_edgelist_v1( xpaths )
nd <- FromDataFrameNetwork( network=el )
print( nd )

##                                          levelName
## 1   Return                                        
## 2    °--ReturnData                                
## 3        ¦--IRS990                                
## 4        ¦   ¦--Activity2                         
## 5        ¦   ¦   ¦--ActivityCode                  
## 6        ¦   ¦   ¦--Description                   
## 7        ¦   ¦   ¦--Expense                       
## 8        ¦   ¦   ¦--Grants                        
## 9        ¦   ¦   °--Revenue                       
## 10       ¦   ¦--Activity3                         
## 11       ¦   ¦   ¦--ActivityCode                  
## 12       ¦   ¦   ¦--Description                   
## 13       ¦   ¦   ¦--Expense                       
## 14       ¦   ¦   ¦--Grants                        
## 15       ¦   ¦   °--Revenue                       
## 16       ¦   ¦--ActivityCd                        
## 17       ¦   ¦--ActivityCode                      
## 18       ¦   ¦--ActivityOther                     
## 19       ¦   ¦   ¦--ActivityCode                  
## 20       ¦   ¦   ¦--Description                   
## 21       ¦   ¦   ¦--Expense                       
## 22       ¦   ¦   ¦--Grants                        
## 23       ¦   ¦   °--Revenue                       
## 24       ¦   ¦--Desc                              
## 25       ¦   ¦--Description                       
## 26       ¦   ¦--Expense                           
## 27       ¦   ¦--ExpenseAmt                        
## 28       ¦   ¦--Form990PartIII                    
## 29       ¦   ¦   ¦--Activity2                     
## 30       ¦   ¦   ¦   ¦--ActivityCode              
## 31       ¦   ¦   ¦   ¦--Description               
## 32       ¦   ¦   ¦   ¦--Expense                   
## 33       ¦   ¦   ¦   ¦--Grants                    
## 34       ¦   ¦   ¦   °--Revenue                   
## 35       ¦   ¦   ¦--Activity3                     
## 36       ¦   ¦   ¦   ¦--ActivityCode              
## 37       ¦   ¦   ¦   ¦--Description               
## 38       ¦   ¦   ¦   ¦--Expense                   
## 39       ¦   ¦   ¦   ¦--Grants                    
## 40       ¦   ¦   ¦   °--Revenue                   
## 41       ¦   ¦   ¦--ActivityCode                  
## 42       ¦   ¦   ¦--ActivityOther                 
## 43       ¦   ¦   ¦   ¦--ActivityCode              
## 44       ¦   ¦   ¦   ¦--Description               
## 45       ¦   ¦   ¦   ¦--Expense                   
## 46       ¦   ¦   ¦   ¦--Grants                    
## 47       ¦   ¦   ¦   °--Revenue                   
## 48       ¦   ¦   ¦--Description                   
## 49       ¦   ¦   ¦--Expense                       
## 50       ¦   ¦   ¦--Grants                        
## 51       ¦   ¦   ¦--Revenue                       
## 52       ¦   ¦   ¦--TotalOfOtherProgramServiceExp 
## 53       ¦   ¦   ¦--TotalOfOtherProgramServiceGrnt
## 54       ¦   ¦   °--TotalOfOtherProgramServiceRev 
## 55       ¦   ¦--GrantAmt                          
## 56       ¦   ¦--Grants                            
## 57       ¦   ¦--ProgSrvcAccomActy2Grp             
## 58       ¦   ¦   ¦--ActivityCd                    
## 59       ¦   ¦   ¦--Desc                          
## 60       ¦   ¦   ¦--ExpenseAmt                    
## 61       ¦   ¦   ¦--GrantAmt                      
## 62       ¦   ¦   °--RevenueAmt                    
## 63       ¦   ¦--ProgSrvcAccomActy3Grp             
## 64       ¦   ¦   ¦--ActivityCd                    
## 65       ¦   ¦   ¦--Desc                          
## 66       ¦   ¦   ¦--ExpenseAmt                    
## 67       ¦   ¦   ¦--GrantAmt                      
## 68       ¦   ¦   °--RevenueAmt                    
## 69       ¦   ¦--ProgSrvcAccomActyOtherGrp         
## 70       ¦   ¦   ¦--ActivityCd                    
## 71       ¦   ¦   ¦--Desc                          
## 72       ¦   ¦   ¦--ExpenseAmt                    
## 73       ¦   ¦   ¦--GrantAmt                      
## 74       ¦   ¦   °--RevenueAmt                    
## 75       ¦   ¦--ProgramServiceAccomplishments     
## 76       ¦   ¦   ¦--DescriptionProgramServiceAccom
## 77       ¦   ¦   ¦--GrantsAndAllocations          
## 78       ¦   ¦   °--ProgramServiceExpenses        
## 79       ¦   ¦--Revenue                           
## 80       ¦   ¦--RevenueAmt                        
## 81       ¦   ¦--TotalOfOtherProgramServiceExp     
## 82       ¦   ¦--TotalOfOtherProgramServiceGrnt    
## 83       ¦   ¦--TotalOfOtherProgramServiceRev     
## 84       ¦   ¦--TotalOtherProgSrvcExpenseAmt      
## 85       ¦   ¦--TotalOtherProgSrvcGrantAmt        
## 86       ¦   °--TotalOtherProgSrvcRevenueAmt      
## 87       °--IRS990EZ                              
## 88           ¦--GrantAmt                          
## 89           ¦--Grants                            
## 90           ¦--ProgSrvcAccomActy2Grp             
## 91           ¦   ¦--ActivityCd                    
## 92           ¦   ¦--Desc                          
## 93           ¦   ¦--ExpenseAmt                    
## 94           ¦   ¦--GrantAmt                      
## 95           ¦   °--RevenueAmt                    
## 96           ¦--ProgSrvcAccomActy3Grp             
## 97           ¦   ¦--ActivityCd                    
## 98           ¦   ¦--Desc                          
## 99           ¦   ¦--ExpenseAmt                    
## 100          ¦   °--... 2 nodes w/ 0 sub          
## 101          °--... 3 nodes w/ 15 sub

SetGraphStyle( nd, rankdir = "LR")
SetEdgeStyle( nd, arrowhead = "vee", color = "grey20", penwidth = 2 )
SetNodeStyle( nd, 
              style = "filled,rounded", 
              shape = "box", 
              fillcolor = "LightBlue", 
              fontname = "helvetica", 
              fontcolor="black",
              tooltip = GetDefaultTooltip )

# SetNodeStyle(acme$IT, fillcolor = "LightBlue", penwidth = "5px")
plot( nd )

# old version
find_group_names( table.name )

##  [1] "//Activity2"                      "//Activity3"                     
##  [3] "//ActivityCode"                   "//ActivityOther"                 
##  [5] "//ActivityCd"                     "//ProgSrvcAccomActy2Grp"         
##  [7] "//ProgSrvcAccomActy3Grp"          "//ProgSrvcAccomActyOtherGrp"     
##  [9] "//Form990PartIII"                 "//ProgramServiceAccomplishment"  
## [11] "//ProgramSrvcAccomplishmentGrp"   "//Description"                   
## [13] "//Desc"                           "//ProgramServiceAccomplishments" 
## [15] "//Expense"                        "//TotalOfOtherProgramServiceExp" 
## [17] "//ExpenseAmt"                     "//TotalOtherProgSrvcExpenseAmt"  
## [19] "//GrantAmt"                       "//Grants"                        
## [21] "//TotalOfOtherProgramServiceGrnt" "//TotalOtherProgSrvcGrantAmt"    
## [23] "//Revenue"                        "//TotalOfOtherProgramServiceRev" 
## [25] "//RevenueAmt"                     "//TotalOtherProgSrvcRevenueAmt"

# new version
find_table_headers( table.name )

##  [1] "//Activity2/Activity2"                                         
##  [2] "//Activity2/Form990PartIII"                                    
##  [3] "//Activity3/Activity3"                                         
##  [4] "//Activity3/Form990PartIII"                                    
##  [5] "//ActivityCode/"                                               
##  [6] "//ActivityOther/ActivityOther"                                 
##  [7] "//ActivityOther/Form990PartIII"                                
##  [8] "//ActivityCd/"                                                 
##  [9] "//ProgSrvcAccomActy2Grp/ActivityCd"                            
## [10] "//ProgSrvcAccomActy2Grp/Desc"                                  
## [11] "//ProgSrvcAccomActy2Grp/ExpenseAmt"                            
## [12] "//ProgSrvcAccomActy2Grp/GrantAmt"                              
## [13] "//ProgSrvcAccomActy2Grp/RevenueAmt"                            
## [14] "//ProgSrvcAccomActy3Grp/ActivityCd"                            
## [15] "//ProgSrvcAccomActy3Grp/Desc"                                  
## [16] "//ProgSrvcAccomActy3Grp/ExpenseAmt"                            
## [17] "//ProgSrvcAccomActy3Grp/GrantAmt"                              
## [18] "//ProgSrvcAccomActy3Grp/RevenueAmt"                            
## [19] "//ProgSrvcAccomActyOtherGrp/ActivityCd"                        
## [20] "//ProgSrvcAccomActyOtherGrp/Desc"                              
## [21] "//ProgSrvcAccomActyOtherGrp/ExpenseAmt"                        
## [22] "//ProgSrvcAccomActyOtherGrp/GrantAmt"                          
## [23] "//ProgSrvcAccomActyOtherGrp/RevenueAmt"                        
## [24] "//Form990PartIII/MissionDescription"                           
## [25] "//Form990PartIII/SignificantNewProgramServices"                
## [26] "//Form990PartIII/SignificantChange"                            
## [27] "//Form990PartIII/Activity2"                                    
## [28] "//Form990PartIII/Activity3"                                    
## [29] "//Form990PartIII/ActivityCode"                                 
## [30] "//Form990PartIII/ActivityOther"                                
## [31] "//Form990PartIII/Description"                                  
## [32] "//Form990PartIII/Expense"                                      
## [33] "//Form990PartIII/TotalOfOtherProgramServiceExp"                
## [34] "//Form990PartIII/Grants"                                       
## [35] "//Form990PartIII/TotalOfOtherProgramServiceGrnt"               
## [36] "//Form990PartIII/Revenue"                                      
## [37] "//Form990PartIII/TotalOfOtherProgramServiceRev"                
## [38] "//Form990PartIII/TotalProgramServiceExpense"                   
## [39] "//ProgramServiceAccomplishment/DescriptionProgramServiceAccom" 
## [40] "//ProgramServiceAccomplishment/ProgramServiceExpenses"         
## [41] "//ProgramServiceAccomplishment/GrantsAndAllocations"           
## [42] "//ProgramServiceAccomplishment/IncludesForeignGrants"          
## [43] "//ProgramSrvcAccomplishmentGrp/DescriptionProgramSrvcAccomTxt" 
## [44] "//ProgramSrvcAccomplishmentGrp/ProgramServiceExpensesAmt"      
## [45] "//ProgramSrvcAccomplishmentGrp/GrantsAndAllocationsAmt"        
## [46] "//ProgramSrvcAccomplishmentGrp/ForeignGrantsInd"               
## [47] "//Description/"                                                
## [48] "//Desc/"                                                       
## [49] "//ProgramServiceAccomplishments/DescriptionProgramServiceAccom"
## [50] "//ProgramServiceAccomplishments/ProgramServiceExpenses"        
## [51] "//ProgramServiceAccomplishments/GrantsAndAllocations"          
## [52] "//Expense/"                                                    
## [53] "//TotalOfOtherProgramServiceExp/"                              
## [54] "//ExpenseAmt/"                                                 
## [55] "//TotalOtherProgSrvcExpenseAmt/"                               
## [56] "//GrantAmt/"                                                   
## [57] "//Grants/ForeignGrants"                                        
## [58] "//Grants/Form990PartIX"                                        
## [59] "//TotalOfOtherProgramServiceGrnt/"                             
## [60] "//TotalOtherProgSrvcGrantAmt/"                                 
## [61] "//Revenue/Form990PartVIII"                                     
## [62] "//Revenue/ProgramServiceRevenue"                               
## [63] "//Revenue/TotalOthProgramServiceRevenue"                       
## [64] "//Revenue/RoyaltiesRevenue"                                    
## [65] "//Revenue/MiscellaneousRevenue"                                
## [66] "//Revenue/OtherRevenue"                                        
## [67] "//Revenue/TotalRevenue"                                        
## [68] "//Revenue/DeferredRevenue"                                     
## [69] "//Revenue/Form990PartX"                                        
## [70] "//TotalOfOtherProgramServiceRev/"                              
## [71] "//RevenueAmt/"                                                 
## [72] "//TotalOtherProgSrvcRevenueAmt/"

# manual
if( ! is.null(table.headers) )
{table.headers}

##  [1] "//IRS990/Activity2"                     
##  [2] "//IRS990/Activity3"                     
##  [3] "//IRS990/ActivityOther"                 
##  [4] "//Form990PartIII/Activity2"             
##  [5] "//Form990PartIII/Activity3"             
##  [6] "//Form990PartIII/ActivityOther"         
##  [7] "//IRS990/ProgSrvcAccomActy2Grp"         
##  [8] "//IRS990/ProgSrvcAccomActy3Grp"         
##  [9] "//IRS990/ProgSrvcAccomActyOtherGrp"     
## [10] "//IRS990/ProgramServiceAccomplishments" 
## [11] "//IRS990EZ/ProgSrvcAccomActy2Grp"       
## [12] "//IRS990EZ/ProgSrvcAccomActy3Grp"       
## [13] "//IRS990EZ/ProgSrvcAccomActyOtherGrp"   
## [14] "//IRS990EZ/ProgramServiceAccomplishment"
## [15] "//IRS990EZ/ProgramSrvcAccomplishmentGrp"

Build the Table

# create new folder
dir.create( folder.name )
setwd( folder.name )

# create sample
set.seed( 1234 )
sample.index <- sample_n( index, sample.size )
write.csv( index, "sample-index.csv", row.names=F )
sample.urls <- sample.index$URL

# erase existing log files
file.create("XPATH-LOG.txt") 
file.create("FAIL-LOG.txt")


start.build.time <- Sys.time()    # --------------------

results.list <- list()
xpath.list <- list()


for( i in 1:length( sample.urls ) )
{
  
  url <- sample.urls[i]
  
  results.list[[i]] <- 
    build_rdb_table_v2( url, 
                        table.name, 
                        table.headers=table.headers )
  
  xpath.list[[i]]   <- 
    get_table_xpaths( url, 
                      table.name, 
                      table.headers=table.headers )
  
  if( i %% 100 == 0 ){ print(i) }
  
}


end.build.time <- Sys.time()      # --------------------


df <- dplyr::bind_rows( results.list )


fail.log <- readLines( "FAIL-LOG.txt" )
all.xpaths <- xpath.list %>% unlist() %>% unique() %>% sort()
cat( all.xpaths, sep="\n", file="XPATH-LOG.txt" )

TABLE: F9-P03-T01-PROGRAMS

## [1] "NUMBER OF RETURNS PROCESSED:   1000"
## [1] "TOTAL NUMBER OF TABLE ROWS:   790"

TOTAL RUN TIME:

## Time difference of 17.83983 mins
## Time difference of -0.2973305 hours

Write to File

write.csv( df, paste0( folder.name, "/", table.name, ".csv" ), row.names=F )
head(df)

Are all names standardized?

names(df)

##  [1] "OBJECT_ID"               "EIN"                    
##  [3] "NAME"                    "TAXYR"                  
##  [5] "FORMTYPE"                "URL"                    
##  [7] "F9_03_PROG_DESC"         "F9_03_PROG_GRANT"       
##  [9] "F9_03_PROG_EXP"          "F9_03_PROG_EXP.1"       
## [11] "F9_03_PROG_DESC.1"       "F9_03_PROG_GRANT_FRGN_X"
## [13] "F9_03_PROG_REV"          "F9_03_PROG_REV.1"       
## [15] "F9_03_PROG_EXP.2"        "F9_03_PROG_REV.2"       
## [17] "F9_03_PROG_DESC.2"       "F9_03_PROG_GRANT.1"     
## [19] "F9_03_PROG_GRANT.2"

Diagnostics

Missing or Extra XPATHS

Check for xpaths that were used in the tables but do not exist in the Concordance file.

c.table <- dplyr::filter( concordance, rdb_table == table.name ) 
xpath.concordance <- c.table$xpath
setdiff( all.xpaths, xpath.concordance )

## [1] ""                                                        
## [2] "/Return/ReturnData/IRS990/Activity2"                     
## [3] "/Return/ReturnData/IRS990/Activity3"                     
## [4] "/Return/ReturnData/IRS990/ActivityOther"                 
## [5] "/Return/ReturnData/IRS990/ProgSrvcAccomActy2Grp"         
## [6] "/Return/ReturnData/IRS990/ProgSrvcAccomActy3Grp"         
## [7] "/Return/ReturnData/IRS990/ProgSrvcAccomActyOtherGrp"     
## [8] "/Return/ReturnData/IRS990EZ/ProgramServiceAccomplishment"
## [9] "/Return/ReturnData/IRS990EZ/ProgramSrvcAccomplishmentGrp"

Capture to update the Concordance file.

these <- setdiff( all.xpaths, xpath.concordance )
cat( these, sep="\n", file=paste0( folder.name, "/MISSING-XPATHS.txt" ) )

Failes URLs

Check the status of failed URLs.

if( length(fail.log) > 0 )
{ 
  sapply( fail.log, get_message ) %>% 
  table() %>% 
  pander() 
} else {
  print("ALL URLs ARE VALID")
}

Client error: (404) Not Found
1

XML Table Structure

Create Network Edgelists

The create_edgelist() functions v1 and v2 are in R/utils.R.

V1 retains the root nodes Return and ReturnData.

V2 drops the root nodes and uses form versions “990” and “990EZ” as the root to visualize the sets of tables separately.

# KEEP ROOT NOTES Return & ReturnData
el <- create_edgelist_v1( all.xpaths )

Edgelist format:

V1	V2
Return	ReturnData
ReturnData	IRS990
IRS990	Form990PartVIISectionAGrp
Form990PartVIISectionAGrp	PersonNm
Form990PartVIISectionAGrp	TitleTxt
Form990PartVIISectionAGrp	AverageHoursPerWeekRt
Form990PartVIISectionAGrp	IndividualTrusteeOrDirectorInd
Form990PartVIISectionAGrp	OfficerInd
Form990PartVIISectionAGrp	ReportableCompFromOrgAmt
Form990PartVIISectionAGrp	ReportableCompFromRltdOrgAmt

Visualize Tree Structure

# library( data.tree )
nd <- FromDataFrameNetwork( network=el )
print( nd )

##                                         levelName
## 1  Return                                        
## 2   °--ReturnData                                
## 3       ¦--IRS990                                
## 4       ¦   ¦--Activity2                         
## 5       ¦   ¦   ¦--Description                   
## 6       ¦   ¦   ¦--Expense                       
## 7       ¦   ¦   ¦--Grants                        
## 8       ¦   ¦   °--Revenue                       
## 9       ¦   ¦--Activity3                         
## 10      ¦   ¦   ¦--Description                   
## 11      ¦   ¦   ¦--Expense                       
## 12      ¦   ¦   ¦--Grants                        
## 13      ¦   ¦   °--Revenue                       
## 14      ¦   ¦--ActivityOther                     
## 15      ¦   ¦   ¦--Description                   
## 16      ¦   ¦   ¦--Expense                       
## 17      ¦   ¦   ¦--Grants                        
## 18      ¦   ¦   °--Revenue                       
## 19      ¦   ¦--ProgSrvcAccomActy2Grp             
## 20      ¦   ¦   ¦--Desc                          
## 21      ¦   ¦   ¦--ExpenseAmt                    
## 22      ¦   ¦   ¦--GrantAmt                      
## 23      ¦   ¦   °--RevenueAmt                    
## 24      ¦   ¦--ProgSrvcAccomActy3Grp             
## 25      ¦   ¦   ¦--Desc                          
## 26      ¦   ¦   ¦--ExpenseAmt                    
## 27      ¦   ¦   ¦--GrantAmt                      
## 28      ¦   ¦   °--RevenueAmt                    
## 29      ¦   °--ProgSrvcAccomActyOtherGrp         
## 30      ¦       ¦--Desc                          
## 31      ¦       ¦--ExpenseAmt                    
## 32      ¦       ¦--GrantAmt                      
## 33      ¦       °--RevenueAmt                    
## 34      °--IRS990EZ                              
## 35          ¦--ProgramServiceAccomplishment      
## 36          ¦   ¦--DescriptionProgramServiceAccom
## 37          ¦   ¦--GrantsAndAllocations          
## 38          ¦   °--ProgramServiceExpenses        
## 39          °--ProgramSrvcAccomplishmentGrp      
## 40              ¦--DescriptionProgramSrvcAccomTxt
## 41              ¦--ForeignGrantsInd              
## 42              ¦--GrantsAndAllocationsAmt       
## 43              °--ProgramServiceExpensesAmt

SetGraphStyle( nd, rankdir = "LR")
SetEdgeStyle( nd, arrowhead = "vee", color = "grey20", penwidth = 2 )
SetNodeStyle( nd, 
              style = "filled,rounded", 
              shape = "box", 
              fillcolor = "LightBlue", 
              fontname = "helvetica", 
              fontcolor="black",
              tooltip = GetDefaultTooltip )

# SetNodeStyle(acme$IT, fillcolor = "LightBlue", penwidth = "5px")
plot( nd )

# library( networkD3 )
# net <- ToDataFrameNetwork( nd, "name" )
# simpleNetwork( net, fontSize = 12 )
simpleNetwork( el, fontSize = 12 )

#plot with networkD3
radnet <- ToListExplicit( nd, unname = TRUE )
radialNetwork( radnet, fontSize = 12 )

XPATH Summary

Unique Xpaths for this RDB Table

t.xpaths <- 
  concordance %>%
  filter( rdb_table == table.name ) %>% 
  select( xpath ) %>% 
  unique() %>% 
  arrange( xpath ) %>% 
  mutate( id=row_number() ) 

t.xpaths %>% 
  kable( align="l" )

xpath	id
/Return/ReturnData/IRS990/Activity2/ActivityCode	1
/Return/ReturnData/IRS990/Activity2/Description	2
/Return/ReturnData/IRS990/Activity2/Expense	3
/Return/ReturnData/IRS990/Activity2/Grants	4
/Return/ReturnData/IRS990/Activity2/Revenue	5
/Return/ReturnData/IRS990/Activity3/ActivityCode	6
/Return/ReturnData/IRS990/Activity3/Description	7
/Return/ReturnData/IRS990/Activity3/Expense	8
/Return/ReturnData/IRS990/Activity3/Grants	9
/Return/ReturnData/IRS990/Activity3/Revenue	10
/Return/ReturnData/IRS990/ActivityCd	11
/Return/ReturnData/IRS990/ActivityCode	12
/Return/ReturnData/IRS990/ActivityOther/ActivityCode	13
/Return/ReturnData/IRS990/ActivityOther/Description	14
/Return/ReturnData/IRS990/ActivityOther/Expense	15
/Return/ReturnData/IRS990/ActivityOther/Grants	16
/Return/ReturnData/IRS990/ActivityOther/Revenue	17
/Return/ReturnData/IRS990/Desc	18
/Return/ReturnData/IRS990/Description	19
/Return/ReturnData/IRS990/Expense	20
/Return/ReturnData/IRS990/ExpenseAmt	21
/Return/ReturnData/IRS990/Form990PartIII/Activity2/ActivityCode	22
/Return/ReturnData/IRS990/Form990PartIII/Activity2/Description	23
/Return/ReturnData/IRS990/Form990PartIII/Activity2/Expense	24
/Return/ReturnData/IRS990/Form990PartIII/Activity2/Grants	25
/Return/ReturnData/IRS990/Form990PartIII/Activity2/Revenue	26
/Return/ReturnData/IRS990/Form990PartIII/Activity3/ActivityCode	27
/Return/ReturnData/IRS990/Form990PartIII/Activity3/Description	28
/Return/ReturnData/IRS990/Form990PartIII/Activity3/Expense	29
/Return/ReturnData/IRS990/Form990PartIII/Activity3/Grants	30
/Return/ReturnData/IRS990/Form990PartIII/Activity3/Revenue	31
/Return/ReturnData/IRS990/Form990PartIII/ActivityCode	32
/Return/ReturnData/IRS990/Form990PartIII/ActivityOther/ActivityCode	33
/Return/ReturnData/IRS990/Form990PartIII/ActivityOther/Description	34
/Return/ReturnData/IRS990/Form990PartIII/ActivityOther/Expense	35
/Return/ReturnData/IRS990/Form990PartIII/ActivityOther/Grants	36
/Return/ReturnData/IRS990/Form990PartIII/ActivityOther/Revenue	37
/Return/ReturnData/IRS990/Form990PartIII/Description	38
/Return/ReturnData/IRS990/Form990PartIII/Expense	39
/Return/ReturnData/IRS990/Form990PartIII/Grants	40
/Return/ReturnData/IRS990/Form990PartIII/Revenue	41
/Return/ReturnData/IRS990/Form990PartIII/TotalOfOtherProgramServiceExp	42
/Return/ReturnData/IRS990/Form990PartIII/TotalOfOtherProgramServiceGrnt	43
/Return/ReturnData/IRS990/Form990PartIII/TotalOfOtherProgramServiceRev	44
/Return/ReturnData/IRS990/GrantAmt	45
/Return/ReturnData/IRS990/Grants	46
/Return/ReturnData/IRS990/ProgSrvcAccomActy2Grp/ActivityCd	47
/Return/ReturnData/IRS990/ProgSrvcAccomActy2Grp/Desc	48
/Return/ReturnData/IRS990/ProgSrvcAccomActy2Grp/ExpenseAmt	49
/Return/ReturnData/IRS990/ProgSrvcAccomActy2Grp/GrantAmt	50
/Return/ReturnData/IRS990/ProgSrvcAccomActy2Grp/RevenueAmt	51
/Return/ReturnData/IRS990/ProgSrvcAccomActy3Grp/ActivityCd	52
/Return/ReturnData/IRS990/ProgSrvcAccomActy3Grp/Desc	53
/Return/ReturnData/IRS990/ProgSrvcAccomActy3Grp/ExpenseAmt	54
/Return/ReturnData/IRS990/ProgSrvcAccomActy3Grp/GrantAmt	55
/Return/ReturnData/IRS990/ProgSrvcAccomActy3Grp/RevenueAmt	56
/Return/ReturnData/IRS990/ProgSrvcAccomActyOtherGrp/ActivityCd	57
/Return/ReturnData/IRS990/ProgSrvcAccomActyOtherGrp/Desc	58
/Return/ReturnData/IRS990/ProgSrvcAccomActyOtherGrp/ExpenseAmt	59
/Return/ReturnData/IRS990/ProgSrvcAccomActyOtherGrp/GrantAmt	60
/Return/ReturnData/IRS990/ProgSrvcAccomActyOtherGrp/RevenueAmt	61
/Return/ReturnData/IRS990/ProgramServiceAccomplishments/DescriptionProgramServiceAccom	62
/Return/ReturnData/IRS990/ProgramServiceAccomplishments/GrantsAndAllocations	63
/Return/ReturnData/IRS990/ProgramServiceAccomplishments/ProgramServiceExpenses	64
/Return/ReturnData/IRS990/Revenue	65
/Return/ReturnData/IRS990/RevenueAmt	66
/Return/ReturnData/IRS990/TotalOfOtherProgramServiceExp	67
/Return/ReturnData/IRS990/TotalOfOtherProgramServiceGrnt	68
/Return/ReturnData/IRS990/TotalOfOtherProgramServiceRev	69
/Return/ReturnData/IRS990/TotalOtherProgSrvcExpenseAmt	70
/Return/ReturnData/IRS990/TotalOtherProgSrvcGrantAmt	71
/Return/ReturnData/IRS990/TotalOtherProgSrvcRevenueAmt	72
/Return/ReturnData/IRS990EZ/GrantAmt	73
/Return/ReturnData/IRS990EZ/Grants	74
/Return/ReturnData/IRS990EZ/ProgSrvcAccomActy2Grp/Desc	75
/Return/ReturnData/IRS990EZ/ProgSrvcAccomActy2Grp/GrantAmt	76
/Return/ReturnData/IRS990EZ/ProgSrvcAccomActy3Grp/Desc	77
/Return/ReturnData/IRS990EZ/ProgSrvcAccomActy3Grp/GrantAmt	78
/Return/ReturnData/IRS990EZ/ProgSrvcAccomActyOtherGrp/Desc	79
/Return/ReturnData/IRS990EZ/ProgSrvcAccomActyOtherGrp/GrantAmt	80
/Return/ReturnData/IRS990EZ/ProgramServiceAccomplishment/DescriptionProgramServiceAccom	81
/Return/ReturnData/IRS990EZ/ProgramServiceAccomplishment/GrantsAndAllocations	82
/Return/ReturnData/IRS990EZ/ProgramServiceAccomplishment/IncludesForeignGrants	83
/Return/ReturnData/IRS990EZ/ProgramServiceAccomplishment/ProgramServiceExpenses	84
/Return/ReturnData/IRS990EZ/ProgramSrvcAccomplishmentGrp/DescriptionProgramSrvcAccomTxt	85
/Return/ReturnData/IRS990EZ/ProgramSrvcAccomplishmentGrp/ForeignGrantsInd	86
/Return/ReturnData/IRS990EZ/ProgramSrvcAccomplishmentGrp/GrantsAndAllocationsAmt	87
/Return/ReturnData/IRS990EZ/ProgramSrvcAccomplishmentGrp/ProgramServiceExpensesAmt	88

Grouping Variables

Identify all unique groups used in xpaths.

if( is.null(table.headers) )
{ find_table_headers( table.name ) } else
table.headers

##  [1] "//IRS990/Activity2"                     
##  [2] "//IRS990/Activity3"                     
##  [3] "//IRS990/ActivityOther"                 
##  [4] "//Form990PartIII/Activity2"             
##  [5] "//Form990PartIII/Activity3"             
##  [6] "//Form990PartIII/ActivityOther"         
##  [7] "//IRS990/ProgSrvcAccomActy2Grp"         
##  [8] "//IRS990/ProgSrvcAccomActy3Grp"         
##  [9] "//IRS990/ProgSrvcAccomActyOtherGrp"     
## [10] "//IRS990/ProgramServiceAccomplishments" 
## [11] "//IRS990EZ/ProgSrvcAccomActy2Grp"       
## [12] "//IRS990EZ/ProgSrvcAccomActy3Grp"       
## [13] "//IRS990EZ/ProgSrvcAccomActyOtherGrp"   
## [14] "//IRS990EZ/ProgramServiceAccomplishment"
## [15] "//IRS990EZ/ProgramSrvcAccomplishmentGrp"

Groups can then be used to extract the 1:M RDB tables:

if( is.null(table.headers) )
{ table.headers <- find_table_headers( table.name ) }
demo.url <- "https://nccs-efile.s3.us-east-1.amazonaws.com/xml/201913079349300206_public.xml"
doc <- xml2::read_xml( demo.url )
xml2::xml_ns_strip( doc )
df.demo <- get_table_v2( doc, table.name, table.headers )
df.demo

## NULL

Standardize Variable Names

Mapping of xml variable names to concordance variable names:

get_var_map( table.name=table.name )

A table with proper names:

v.map <- get_var_map( table.name=table.name )

if( ! is.null(df.demo) )
{re_name( df.demo, v.map ) %>% as.data.frame()}

Print Table Structure

Demo of functions - demo data only.

[[1]]
<Form990PartVIISectionAGrp>
  <PersonNm>
    {text}
  <TitleTxt>
    {text}
  <AverageHoursPerWeekRt>
    {text}
  <IndividualTrusteeOrDirectorInd>
    {text}
  <OfficerInd>
    {text}
  <ReportableCompFromOrgAmt>
    {text}
  <ReportableCompFromRltdOrgAmt>
    {text}
  <OtherCompensationAmt>
    {text}

node.list[[1]] %>% xml_parent() %>% xmltools::xml_view_trees()

 (1) ------------

 +-- Form990PartVIISectionAGrp
  +-- PersonNm
  +-- TitleTxt
  +-- AverageHoursPerWeekRt
  +-- IndividualTrusteeOrDirectorInd
  +-- OfficerInd
  +-- ReportableCompFromOrgAmt
  +-- ReportableCompFromRltdOrgAmt
  ++-- OtherCompensationAmt


 (2) ------------

 +-- Form990PartVIISectionAGrp
  +-- PersonNm
  +-- TitleTxt
  +-- AverageHoursPerWeekRt
  +-- IndividualTrusteeOrDirectorInd
  +-- OfficerInd
  +-- ReportableCompFromOrgAmt
  +-- ReportableCompFromRltdOrgAmt
  ++-- OtherCompensationAmt

TABLE NAME: F9-P03-T01-PROGRAMS

Last compiled on 04 April, 2023