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/IRS990ScheduleR/Form990ScheduleRPartII/AddressForeign/AddressLine1	1
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/AddressForeign/AddressLine2	2
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/AddressForeign/City	3
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/AddressForeign/Country	4
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/AddressForeign/PostalCode	5
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/AddressForeign/ProvinceOrState	6
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/AddressUS/AddressLine1	7
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/AddressUS/AddressLine2	8
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/AddressUS/City	9
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/AddressUS/State	10
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/AddressUS/ZIPCode	11
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/ControlledOrg	12
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/DirectControllingEntity/NameBusiness/BusinessNameLine1	13
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/DirectControllingEntity/NameBusiness/BusinessNameLine2	14
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/DirectControllingEntity/NotApplicable	15
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/DirectControllingEntityNA	16
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/DirectControllingEntityName/BusinessNameLine1	17
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/DirectControllingEntityName/BusinessNameLine2	18
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/EIN	19
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/ExemptCodeSection	20
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/LegalDomicile/ForeignCountry	21
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/LegalDomicile/State	22
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/LegalDomicileForeignCountry	23
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/LegalDomicileState	24
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/NameOfDisregardedEntity/BusinessNameLine1	25
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/NameOfDisregardedEntity/BusinessNameLine2	26
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/PrimaryActivities	27
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/PublicCharityStatus	28
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/ControlledOrganizationInd	29
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/DirectControllingEntityName/BusinessNameLine1	30
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/DirectControllingEntityName/BusinessNameLine1Txt	31
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/DirectControllingEntityName/BusinessNameLine2	32
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/DirectControllingEntityName/BusinessNameLine2Txt	33
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/DirectControllingNACd	34
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/DisregardedEntityName/BusinessNameLine1	35
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/DisregardedEntityName/BusinessNameLine1Txt	36
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/DisregardedEntityName/BusinessNameLine2	37
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/DisregardedEntityName/BusinessNameLine2Txt	38
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/EIN	39
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/ExemptCodeSectionTxt	40
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/ForeignAddress/AddressLine1	41
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/ForeignAddress/AddressLine1Txt	42
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/ForeignAddress/AddressLine2	43
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/ForeignAddress/AddressLine2Txt	44
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/ForeignAddress/City	45
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/ForeignAddress/CityNm	46
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/ForeignAddress/Country	47
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/ForeignAddress/CountryCd	48
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/ForeignAddress/ForeignPostalCd	49
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/ForeignAddress/PostalCode	50
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/ForeignAddress/ProvinceOrState	51
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/ForeignAddress/ProvinceOrStateNm	52
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/LegalDomicileForeignCountryCd	53
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/LegalDomicileStateCd	54
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/PrimaryActivitiesTxt	55
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/PublicCharityStatusTxt	56
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/USAddress/AddressLine1	57
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/USAddress/AddressLine1Txt	58
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/USAddress/AddressLine2	59
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/USAddress/AddressLine2Txt	60
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/USAddress/City	61
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/USAddress/CityNm	62
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/USAddress/State	63
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/USAddress/StateAbbreviationCd	64
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/USAddress/ZIPCd	65
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/USAddress/ZIPCode	66

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

##                                        levelName
## 1  Return                                       
## 2   °--ReturnData                               
## 3       °--IRS990ScheduleR                      
## 4           ¦--Form990ScheduleRPartII           
## 5           ¦   ¦--AddressForeign               
## 6           ¦   ¦   ¦--AddressLine1             
## 7           ¦   ¦   ¦--AddressLine2             
## 8           ¦   ¦   ¦--City                     
## 9           ¦   ¦   ¦--Country                  
## 10          ¦   ¦   ¦--PostalCode               
## 11          ¦   ¦   °--ProvinceOrState          
## 12          ¦   ¦--AddressUS                    
## 13          ¦   ¦   ¦--AddressLine1             
## 14          ¦   ¦   ¦--AddressLine2             
## 15          ¦   ¦   ¦--City                     
## 16          ¦   ¦   ¦--State                    
## 17          ¦   ¦   °--ZIPCode                  
## 18          ¦   ¦--ControlledOrg                
## 19          ¦   ¦--DirectControllingEntity      
## 20          ¦   ¦   ¦--NameBusiness             
## 21          ¦   ¦   ¦   ¦--BusinessNameLine1    
## 22          ¦   ¦   ¦   °--BusinessNameLine2    
## 23          ¦   ¦   °--NotApplicable            
## 24          ¦   ¦--DirectControllingEntityNA    
## 25          ¦   ¦--DirectControllingEntityName  
## 26          ¦   ¦   ¦--BusinessNameLine1        
## 27          ¦   ¦   ¦--BusinessNameLine2        
## 28          ¦   ¦   ¦--BusinessNameLine1Txt     
## 29          ¦   ¦   °--BusinessNameLine2Txt     
## 30          ¦   ¦--EIN                          
## 31          ¦   ¦--ExemptCodeSection            
## 32          ¦   ¦--LegalDomicile                
## 33          ¦   ¦   ¦--ForeignCountry           
## 34          ¦   ¦   °--State                    
## 35          ¦   ¦--LegalDomicileForeignCountry  
## 36          ¦   ¦--LegalDomicileState           
## 37          ¦   ¦--NameOfDisregardedEntity      
## 38          ¦   ¦   ¦--BusinessNameLine1        
## 39          ¦   ¦   °--BusinessNameLine2        
## 40          ¦   ¦--PrimaryActivities            
## 41          ¦   °--PublicCharityStatus          
## 42          °--IdRelatedTaxExemptOrgGrp         
## 43              ¦--ControlledOrganizationInd    
## 44              ¦--DirectControllingEntityName  
## 45              ¦   ¦--BusinessNameLine1        
## 46              ¦   ¦--BusinessNameLine2        
## 47              ¦   ¦--BusinessNameLine1Txt     
## 48              ¦   °--BusinessNameLine2Txt     
## 49              ¦--DirectControllingNACd        
## 50              ¦--DisregardedEntityName        
## 51              ¦   ¦--BusinessNameLine1        
## 52              ¦   ¦--BusinessNameLine1Txt     
## 53              ¦   ¦--BusinessNameLine2        
## 54              ¦   °--BusinessNameLine2Txt     
## 55              ¦--EIN                          
## 56              ¦--ExemptCodeSectionTxt         
## 57              ¦--ForeignAddress               
## 58              ¦   ¦--AddressLine1             
## 59              ¦   ¦--AddressLine1Txt          
## 60              ¦   ¦--AddressLine2             
## 61              ¦   ¦--AddressLine2Txt          
## 62              ¦   ¦--City                     
## 63              ¦   ¦--CityNm                   
## 64              ¦   ¦--Country                  
## 65              ¦   ¦--CountryCd                
## 66              ¦   ¦--ForeignPostalCd          
## 67              ¦   ¦--PostalCode               
## 68              ¦   ¦--ProvinceOrState          
## 69              ¦   °--ProvinceOrStateNm        
## 70              ¦--LegalDomicileForeignCountryCd
## 71              ¦--LegalDomicileStateCd         
## 72              ¦--PrimaryActivitiesTxt         
## 73              ¦--PublicCharityStatusTxt       
## 74              °--USAddress                    
## 75                  ¦--AddressLine1             
## 76                  ¦--AddressLine1Txt          
## 77                  ¦--AddressLine2             
## 78                  ¦--AddressLine2Txt          
## 79                  ¦--City                     
## 80                  ¦--CityNm                   
## 81                  ¦--State                    
## 82                  ¦--StateAbbreviationCd      
## 83                  ¦--ZIPCd                    
## 84                  °--ZIPCode

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] "//Form990ScheduleRPartII"   "//IdRelatedTaxExemptOrgGrp"

# new version
find_table_headers( table.name )

##  [1] "//Form990ScheduleRPartII/AddressForeign"                 
##  [2] "//Form990ScheduleRPartII/AddressUS"                      
##  [3] "//Form990ScheduleRPartII/EIN"                            
##  [4] "//Form990ScheduleRPartII/NameOfDisregardedEntity"        
##  [5] "//Form990ScheduleRPartII/PrimaryActivities"              
##  [6] "//Form990ScheduleRPartII/LegalDomicile"                  
##  [7] "//Form990ScheduleRPartII/LegalDomicileForeignCountry"    
##  [8] "//Form990ScheduleRPartII/LegalDomicileState"             
##  [9] "//Form990ScheduleRPartII/ExemptCodeSection"              
## [10] "//Form990ScheduleRPartII/PublicCharityStatus"            
## [11] "//Form990ScheduleRPartII/DirectControllingEntityNA"      
## [12] "//Form990ScheduleRPartII/DirectControllingEntity"        
## [13] "//Form990ScheduleRPartII/DirectControllingEntityName"    
## [14] "//Form990ScheduleRPartII/ControlledOrg"                  
## [15] "//IdRelatedTaxExemptOrgGrp/ForeignAddress"               
## [16] "//IdRelatedTaxExemptOrgGrp/USAddress"                    
## [17] "//IdRelatedTaxExemptOrgGrp/EIN"                          
## [18] "//IdRelatedTaxExemptOrgGrp/DisregardedEntityName"        
## [19] "//IdRelatedTaxExemptOrgGrp/PrimaryActivitiesTxt"         
## [20] "//IdRelatedTaxExemptOrgGrp/LegalDomicileForeignCountryCd"
## [21] "//IdRelatedTaxExemptOrgGrp/LegalDomicileStateCd"         
## [22] "//IdRelatedTaxExemptOrgGrp/ExemptCodeSectionTxt"         
## [23] "//IdRelatedTaxExemptOrgGrp/PublicCharityStatusTxt"       
## [24] "//IdRelatedTaxExemptOrgGrp/DirectControllingNACd"        
## [25] "//IdRelatedTaxExemptOrgGrp/DirectControllingEntityName"  
## [26] "//IdRelatedTaxExemptOrgGrp/ControlledOrganizationInd"

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

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: SR-P02-T01-ID-RLTD-TAX-EXEMPED-ORGS

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

TOTAL RUN TIME:

## Time difference of 1.324332 hours
## Time difference of -1.324332 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] "SR_02_RLTD_ORG_EIN"         "SR_02_RLTD_ORG_PRIMARY_ACT"
##  [9] "SR_02_RLTD_ORG_DMCL_STATE"  "SR_02_RLTD_ORG_EXEMPT_CODE"
## [11] "SR_02_RLTD_ORG_PCSTAT"      "SR_02_RLTD_ORG_NAME_L1"    
## [13] "SR_02_RLTD_ORG_ADDR_L1"     "SR_02_RLTD_ORG_ADDR_CITY"  
## [15] "SR_02_RLTD_ORG_ADDR_STATE"  "SR_02_RLTD_ORG_ADDR_ZIP"   
## [17] "SR_02_RLTD_ORG_NAME_L1.1"   "SR_02_RLTD_ORG_CTRL_512_X" 
## [19] "SR_02_RLTD_ORG_CTRL_NA"     "SR_02_RLTD_ORG_DMCL_CNTR"  
## [21] "SR_02_RLTD_ORG_ADDR_CNTR"   "SR_02_RLTD_ORG_NAME_L2"    
## [23] "SR_02_RLTD_ORG_ADDR_L2"     "SR_02_RLTD_ORG_NAME_L2.1"

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/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/DirectControllingEntityName"
## [3] "/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/DisregardedEntityName"      
## [4] "/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/ForeignAddress"             
## [5] "/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/USAddress"

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")
}

## [1] "ALL URLs ARE VALID"

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       °--IRS990ScheduleR                      
## 4           °--IdRelatedTaxExemptOrgGrp         
## 5               ¦--ControlledOrganizationInd    
## 6               ¦--DirectControllingEntityName  
## 7               ¦   ¦--BusinessNameLine1Txt     
## 8               ¦   °--BusinessNameLine2Txt     
## 9               ¦--DirectControllingNACd        
## 10              ¦--DisregardedEntityName        
## 11              ¦   ¦--BusinessNameLine1Txt     
## 12              ¦   °--BusinessNameLine2Txt     
## 13              ¦--EIN                          
## 14              ¦--ExemptCodeSectionTxt         
## 15              ¦--ForeignAddress               
## 16              ¦   ¦--AddressLine1Txt          
## 17              ¦   ¦--CityNm                   
## 18              ¦   ¦--CountryCd                
## 19              ¦   ¦--ForeignPostalCd          
## 20              ¦   °--ProvinceOrStateNm        
## 21              ¦--LegalDomicileForeignCountryCd
## 22              ¦--LegalDomicileStateCd         
## 23              ¦--PrimaryActivitiesTxt         
## 24              ¦--PublicCharityStatusTxt       
## 25              °--USAddress                    
## 26                  ¦--AddressLine1Txt          
## 27                  ¦--AddressLine2Txt          
## 28                  ¦--CityNm                   
## 29                  ¦--StateAbbreviationCd      
## 30                  °--ZIPCd

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/IRS990ScheduleR/Form990ScheduleRPartII/AddressForeign/AddressLine1	1
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/AddressForeign/AddressLine2	2
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/AddressForeign/City	3
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/AddressForeign/Country	4
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/AddressForeign/PostalCode	5
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/AddressForeign/ProvinceOrState	6
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/AddressUS/AddressLine1	7
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/AddressUS/AddressLine2	8
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/AddressUS/City	9
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/AddressUS/State	10
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/AddressUS/ZIPCode	11
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/ControlledOrg	12
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/DirectControllingEntity/NameBusiness/BusinessNameLine1	13
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/DirectControllingEntity/NameBusiness/BusinessNameLine2	14
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/DirectControllingEntity/NotApplicable	15
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/DirectControllingEntityNA	16
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/DirectControllingEntityName/BusinessNameLine1	17
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/DirectControllingEntityName/BusinessNameLine2	18
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/EIN	19
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/ExemptCodeSection	20
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/LegalDomicile/ForeignCountry	21
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/LegalDomicile/State	22
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/LegalDomicileForeignCountry	23
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/LegalDomicileState	24
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/NameOfDisregardedEntity/BusinessNameLine1	25
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/NameOfDisregardedEntity/BusinessNameLine2	26
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/PrimaryActivities	27
/Return/ReturnData/IRS990ScheduleR/Form990ScheduleRPartII/PublicCharityStatus	28
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/ControlledOrganizationInd	29
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/DirectControllingEntityName/BusinessNameLine1	30
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/DirectControllingEntityName/BusinessNameLine1Txt	31
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/DirectControllingEntityName/BusinessNameLine2	32
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/DirectControllingEntityName/BusinessNameLine2Txt	33
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/DirectControllingNACd	34
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/DisregardedEntityName/BusinessNameLine1	35
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/DisregardedEntityName/BusinessNameLine1Txt	36
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/DisregardedEntityName/BusinessNameLine2	37
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/DisregardedEntityName/BusinessNameLine2Txt	38
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/EIN	39
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/ExemptCodeSectionTxt	40
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/ForeignAddress/AddressLine1	41
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/ForeignAddress/AddressLine1Txt	42
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/ForeignAddress/AddressLine2	43
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/ForeignAddress/AddressLine2Txt	44
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/ForeignAddress/City	45
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/ForeignAddress/CityNm	46
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/ForeignAddress/Country	47
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/ForeignAddress/CountryCd	48
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/ForeignAddress/ForeignPostalCd	49
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/ForeignAddress/PostalCode	50
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/ForeignAddress/ProvinceOrState	51
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/ForeignAddress/ProvinceOrStateNm	52
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/LegalDomicileForeignCountryCd	53
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/LegalDomicileStateCd	54
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/PrimaryActivitiesTxt	55
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/PublicCharityStatusTxt	56
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/USAddress/AddressLine1	57
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/USAddress/AddressLine1Txt	58
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/USAddress/AddressLine2	59
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/USAddress/AddressLine2Txt	60
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/USAddress/City	61
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/USAddress/CityNm	62
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/USAddress/State	63
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/USAddress/StateAbbreviationCd	64
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/USAddress/ZIPCd	65
/Return/ReturnData/IRS990ScheduleR/IdRelatedTaxExemptOrgGrp/USAddress/ZIPCode	66

Grouping Variables

Identify all unique groups used in xpaths.

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

##  [1] "//Form990ScheduleRPartII/AddressForeign"                 
##  [2] "//Form990ScheduleRPartII/AddressUS"                      
##  [3] "//Form990ScheduleRPartII/EIN"                            
##  [4] "//Form990ScheduleRPartII/NameOfDisregardedEntity"        
##  [5] "//Form990ScheduleRPartII/PrimaryActivities"              
##  [6] "//Form990ScheduleRPartII/LegalDomicile"                  
##  [7] "//Form990ScheduleRPartII/LegalDomicileForeignCountry"    
##  [8] "//Form990ScheduleRPartII/LegalDomicileState"             
##  [9] "//Form990ScheduleRPartII/ExemptCodeSection"              
## [10] "//Form990ScheduleRPartII/PublicCharityStatus"            
## [11] "//Form990ScheduleRPartII/DirectControllingEntityNA"      
## [12] "//Form990ScheduleRPartII/DirectControllingEntity"        
## [13] "//Form990ScheduleRPartII/DirectControllingEntityName"    
## [14] "//Form990ScheduleRPartII/ControlledOrg"                  
## [15] "//IdRelatedTaxExemptOrgGrp/ForeignAddress"               
## [16] "//IdRelatedTaxExemptOrgGrp/USAddress"                    
## [17] "//IdRelatedTaxExemptOrgGrp/EIN"                          
## [18] "//IdRelatedTaxExemptOrgGrp/DisregardedEntityName"        
## [19] "//IdRelatedTaxExemptOrgGrp/PrimaryActivitiesTxt"         
## [20] "//IdRelatedTaxExemptOrgGrp/LegalDomicileForeignCountryCd"
## [21] "//IdRelatedTaxExemptOrgGrp/LegalDomicileStateCd"         
## [22] "//IdRelatedTaxExemptOrgGrp/ExemptCodeSectionTxt"         
## [23] "//IdRelatedTaxExemptOrgGrp/PublicCharityStatusTxt"       
## [24] "//IdRelatedTaxExemptOrgGrp/DirectControllingNACd"        
## [25] "//IdRelatedTaxExemptOrgGrp/DirectControllingEntityName"  
## [26] "//IdRelatedTaxExemptOrgGrp/ControlledOrganizationInd"

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: SR-P02-T01-ID-RLTD-TAX-EXEMPED-ORGS

Last compiled on 05 April, 2023