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/IRS990ScheduleN/Form990ScheduleNPartI/LiquidationTable/AddressForeign/AddressLine1	1
/Return/ReturnData/IRS990ScheduleN/Form990ScheduleNPartI/LiquidationTable/AddressForeign/AddressLine2	2
/Return/ReturnData/IRS990ScheduleN/Form990ScheduleNPartI/LiquidationTable/AddressForeign/City	3
/Return/ReturnData/IRS990ScheduleN/Form990ScheduleNPartI/LiquidationTable/AddressForeign/Country	4
/Return/ReturnData/IRS990ScheduleN/Form990ScheduleNPartI/LiquidationTable/AddressForeign/PostalCode	5
/Return/ReturnData/IRS990ScheduleN/Form990ScheduleNPartI/LiquidationTable/AddressForeign/ProvinceOrState	6
/Return/ReturnData/IRS990ScheduleN/Form990ScheduleNPartI/LiquidationTable/AddressUS/AddressLine1	7
/Return/ReturnData/IRS990ScheduleN/Form990ScheduleNPartI/LiquidationTable/AddressUS/AddressLine2	8
/Return/ReturnData/IRS990ScheduleN/Form990ScheduleNPartI/LiquidationTable/AddressUS/City	9
/Return/ReturnData/IRS990ScheduleN/Form990ScheduleNPartI/LiquidationTable/AddressUS/State	10
/Return/ReturnData/IRS990ScheduleN/Form990ScheduleNPartI/LiquidationTable/AddressUS/ZIPCode	11
/Return/ReturnData/IRS990ScheduleN/Form990ScheduleNPartI/LiquidationTable/DateOfDistribution	12
/Return/ReturnData/IRS990ScheduleN/Form990ScheduleNPartI/LiquidationTable/DescriptionOfAsset	13
/Return/ReturnData/IRS990ScheduleN/Form990ScheduleNPartI/LiquidationTable/EIN	14
/Return/ReturnData/IRS990ScheduleN/Form990ScheduleNPartI/LiquidationTable/FMVOfAsset	15
/Return/ReturnData/IRS990ScheduleN/Form990ScheduleNPartI/LiquidationTable/IRCSection	16
/Return/ReturnData/IRS990ScheduleN/Form990ScheduleNPartI/LiquidationTable/MethodOfFMVDetermination	17
/Return/ReturnData/IRS990ScheduleN/Form990ScheduleNPartI/LiquidationTable/NameBusiness/BusinessNameLine1	18
/Return/ReturnData/IRS990ScheduleN/Form990ScheduleNPartI/LiquidationTable/NameBusiness/BusinessNameLine2	19
/Return/ReturnData/IRS990ScheduleN/Form990ScheduleNPartI/LiquidationTable/NamePerson	20
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/AssetsDistriOrExpnssPaidDesc	21
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/BusinessName/BusinessNameLine1	22
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/BusinessName/BusinessNameLine1Txt	23
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/BusinessName/BusinessNameLine2	24
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/BusinessName/BusinessNameLine2Txt	25
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/DistributionDt	26
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/EIN	27
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/FairMarketValueOfAssetAmt	28
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/ForeignAddress/AddressLine1	29
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/ForeignAddress/AddressLine1Txt	30
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/ForeignAddress/AddressLine2	31
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/ForeignAddress/AddressLine2Txt	32
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/ForeignAddress/City	33
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/ForeignAddress/CityNm	34
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/ForeignAddress/Country	35
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/ForeignAddress/CountryCd	36
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/ForeignAddress/ForeignPostalCd	37
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/ForeignAddress/PostalCode	38
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/ForeignAddress/ProvinceOrState	39
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/ForeignAddress/ProvinceOrStateNm	40
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/IRCSectionTxt	41
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/MethodOfFMVDeterminationTxt	42
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/PersonNm	43
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/USAddress/AddressLine1	44
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/USAddress/AddressLine1Txt	45
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/USAddress/AddressLine2	46
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/USAddress/AddressLine2Txt	47
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/USAddress/City	48
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/USAddress/CityNm	49
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/USAddress/State	50
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/USAddress/StateAbbreviationCd	51
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/USAddress/ZIPCd	52
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/USAddress/ZIPCode	53
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/AddressForeign/AddressLine1	54
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/AddressForeign/AddressLine2	55
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/AddressForeign/City	56
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/AddressForeign/Country	57
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/AddressForeign/PostalCode	58
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/AddressForeign/ProvinceOrState	59
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/AddressUS/AddressLine1	60
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/AddressUS/AddressLine2	61
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/AddressUS/City	62
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/AddressUS/State	63
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/AddressUS/ZIPCode	64
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/DateOfDistribution	65
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/DescriptionOfAsset	66
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/EIN	67
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/FMVOfAsset	68
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/IRCSection	69
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/LiquidationDetail/AddressForeign/AddressLine1	70
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/LiquidationDetail/AddressForeign/AddressLine2	71
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/LiquidationDetail/AddressForeign/City	72
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/LiquidationDetail/AddressForeign/Country	73
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/LiquidationDetail/AddressForeign/PostalCode	74
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/LiquidationDetail/AddressForeign/ProvinceOrState	75
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/LiquidationDetail/AddressUS/AddressLine1	76
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/LiquidationDetail/AddressUS/AddressLine2	77
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/LiquidationDetail/AddressUS/City	78
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/LiquidationDetail/AddressUS/State	79
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/LiquidationDetail/AddressUS/ZIPCode	80
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/LiquidationDetail/DateOfDistribution	81
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/LiquidationDetail/DescriptionOfAsset	82
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/LiquidationDetail/EIN	83
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/LiquidationDetail/FMVOfAsset	84
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/LiquidationDetail/IRCSection	85
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/LiquidationDetail/MethodOfFMVDetermination	86
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/LiquidationDetail/NameBusiness/BusinessNameLine1	87
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/LiquidationDetail/NameBusiness/BusinessNameLine2	88
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/LiquidationDetail/NamePerson	89
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/MethodOfFMVDetermination	90
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/NameBusiness/BusinessNameLine1	91
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/NameBusiness/BusinessNameLine2	92
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/NamePerson	93

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

##                                            levelName
## 1   Return                                          
## 2    °--ReturnData                                  
## 3        °--IRS990ScheduleN                         
## 4            ¦--Form990ScheduleNPartI               
## 5            ¦   °--LiquidationTable                
## 6            ¦       ¦--AddressForeign              
## 7            ¦       ¦   ¦--AddressLine1            
## 8            ¦       ¦   ¦--AddressLine2            
## 9            ¦       ¦   ¦--City                    
## 10           ¦       ¦   ¦--Country                 
## 11           ¦       ¦   ¦--PostalCode              
## 12           ¦       ¦   °--ProvinceOrState         
## 13           ¦       ¦--AddressUS                   
## 14           ¦       ¦   ¦--AddressLine1            
## 15           ¦       ¦   ¦--AddressLine2            
## 16           ¦       ¦   ¦--City                    
## 17           ¦       ¦   ¦--State                   
## 18           ¦       ¦   °--ZIPCode                 
## 19           ¦       ¦--DateOfDistribution          
## 20           ¦       ¦--DescriptionOfAsset          
## 21           ¦       ¦--EIN                         
## 22           ¦       ¦--FMVOfAsset                  
## 23           ¦       ¦--IRCSection                  
## 24           ¦       ¦--MethodOfFMVDetermination    
## 25           ¦       ¦--NameBusiness                
## 26           ¦       ¦   ¦--BusinessNameLine1       
## 27           ¦       ¦   °--BusinessNameLine2       
## 28           ¦       ¦--NamePerson                  
## 29           ¦       °--LiquidationDetail           
## 30           ¦           ¦--AddressForeign          
## 31           ¦           ¦   ¦--AddressLine1        
## 32           ¦           ¦   ¦--AddressLine2        
## 33           ¦           ¦   ¦--City                
## 34           ¦           ¦   ¦--Country             
## 35           ¦           ¦   ¦--PostalCode          
## 36           ¦           ¦   °--ProvinceOrState     
## 37           ¦           ¦--AddressUS               
## 38           ¦           ¦   ¦--AddressLine1        
## 39           ¦           ¦   ¦--AddressLine2        
## 40           ¦           ¦   ¦--City                
## 41           ¦           ¦   ¦--State               
## 42           ¦           ¦   °--ZIPCode             
## 43           ¦           ¦--DateOfDistribution      
## 44           ¦           ¦--DescriptionOfAsset      
## 45           ¦           ¦--EIN                     
## 46           ¦           ¦--FMVOfAsset              
## 47           ¦           ¦--IRCSection              
## 48           ¦           ¦--MethodOfFMVDetermination
## 49           ¦           ¦--NameBusiness            
## 50           ¦           ¦   ¦--BusinessNameLine1   
## 51           ¦           ¦   °--BusinessNameLine2   
## 52           ¦           °--NamePerson              
## 53           ¦--LiquidationOfAssetsTableGrp         
## 54           ¦   °--LiquidationOfAssetsDetail       
## 55           ¦       ¦--AssetsDistriOrExpnssPaidDesc
## 56           ¦       ¦--BusinessName                
## 57           ¦       ¦   ¦--BusinessNameLine1       
## 58           ¦       ¦   ¦--BusinessNameLine1Txt    
## 59           ¦       ¦   ¦--BusinessNameLine2       
## 60           ¦       ¦   °--BusinessNameLine2Txt    
## 61           ¦       ¦--DistributionDt              
## 62           ¦       ¦--EIN                         
## 63           ¦       ¦--FairMarketValueOfAssetAmt   
## 64           ¦       ¦--ForeignAddress              
## 65           ¦       ¦   ¦--AddressLine1            
## 66           ¦       ¦   ¦--AddressLine1Txt         
## 67           ¦       ¦   ¦--AddressLine2            
## 68           ¦       ¦   ¦--AddressLine2Txt         
## 69           ¦       ¦   ¦--City                    
## 70           ¦       ¦   ¦--CityNm                  
## 71           ¦       ¦   ¦--Country                 
## 72           ¦       ¦   ¦--CountryCd               
## 73           ¦       ¦   ¦--ForeignPostalCd         
## 74           ¦       ¦   ¦--PostalCode              
## 75           ¦       ¦   ¦--ProvinceOrState         
## 76           ¦       ¦   °--ProvinceOrStateNm       
## 77           ¦       ¦--IRCSectionTxt               
## 78           ¦       ¦--MethodOfFMVDeterminationTxt 
## 79           ¦       ¦--PersonNm                    
## 80           ¦       °--USAddress                   
## 81           ¦           ¦--AddressLine1            
## 82           ¦           ¦--AddressLine1Txt         
## 83           ¦           ¦--AddressLine2            
## 84           ¦           ¦--AddressLine2Txt         
## 85           ¦           ¦--City                    
## 86           ¦           ¦--CityNm                  
## 87           ¦           ¦--State                   
## 88           ¦           ¦--StateAbbreviationCd     
## 89           ¦           ¦--ZIPCd                   
## 90           ¦           °--ZIPCode                 
## 91           °--LiquidationTable                    
## 92               ¦--AddressForeign                  
## 93               ¦   ¦--AddressLine1                
## 94               ¦   ¦--AddressLine2                
## 95               ¦   ¦--City                        
## 96               ¦   ¦--Country                     
## 97               ¦   ¦--PostalCode                  
## 98               ¦   °--ProvinceOrState             
## 99               ¦--AddressUS                       
## 100              ¦   °--... 5 nodes w/ 0 sub        
## 101              °--... 9 nodes w/ 30 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] "//Form990ScheduleNPartI"       "//LiquidationOfAssetsTableGrp"
## [3] "//LiquidationTable"

# new version
find_table_headers( table.name )

##  [1] "//Form990ScheduleNPartI/LiquidationTable"                  
##  [2] "//Form990ScheduleNPartI/DirectorOfSuccessor"               
##  [3] "//Form990ScheduleNPartI/EmployeeOfSuccessor"               
##  [4] "//Form990ScheduleNPartI/OwnerOfSuccessor"                  
##  [5] "//Form990ScheduleNPartI/ReceiveCompensation"               
##  [6] "//Form990ScheduleNPartI/AssetsDistributed"                 
##  [7] "//Form990ScheduleNPartI/RequiredToNotifyAG"                
##  [8] "//Form990ScheduleNPartI/AGNotified"                        
##  [9] "//Form990ScheduleNPartI/LiabilitiesPaid"                   
## [10] "//Form990ScheduleNPartI/BondsOutstanding"                  
## [11] "//Form990ScheduleNPartI/BondLiabilitiesDischarged"         
## [12] "//Form990ScheduleNPartI/DeterminationLetter"               
## [13] "//Form990ScheduleNPartI/DateOfLetter"                      
## [14] "//LiquidationOfAssetsTableGrp/AssetsDistriOrExpnssPaidDesc"
## [15] "//LiquidationOfAssetsTableGrp/DistributionDt"              
## [16] "//LiquidationOfAssetsTableGrp/FairMarketValueOfAssetAmt"   
## [17] "//LiquidationOfAssetsTableGrp/MethodOfFMVDeterminationTxt" 
## [18] "//LiquidationOfAssetsTableGrp/EIN"                         
## [19] "//LiquidationOfAssetsTableGrp/ForeignAddress"              
## [20] "//LiquidationOfAssetsTableGrp/USAddress"                   
## [21] "//LiquidationOfAssetsTableGrp/BusinessName"                
## [22] "//LiquidationOfAssetsTableGrp/PersonNm"                    
## [23] "//LiquidationOfAssetsTableGrp/IRCSectionTxt"               
## [24] "//LiquidationTable/Form990ScheduleNPartI"                  
## [25] "//LiquidationTable/LiquidationTable"

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

## [1] "//IRS990ScheduleN/Form990ScheduleNPartI/LiquidationTable"
## [2] "//IRS990ScheduleN/LiquidationOfAssetsTableGrp"           
## [3] "//IRS990ScheduleN/LiquidationTable"

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: SN-P01-T01-LIQUIDATION-TERMINATION-DISSOLUTION

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

TOTAL RUN TIME:

## Time difference of 21.10176 mins
## Time difference of -0.3516961 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] "SN_01_LTD_ASSET_DIST_DESC"       "SN_01_LTD_ASSET_DIST_DATE"      
##  [9] "SN_01_LTD_ASSET_DIST_FMV"        "SN_01_LTD_ASSET_DIST_FMV_METHOD"
## [11] "SN_01_LTD_RECIP_NAME_PERS"       "SN_01_LTD_RECIP_EIN"            
## [13] "SN_01_LTD_RECIP_IRC_SECTION"     "SN_01_LTD_RECIP_ADDR_L1"        
## [15] "SN_01_LTD_RECIP_ADDR_CITY"       "SN_01_LTD_RECIP_ADDR_STATE"     
## [17] "SN_01_LTD_RECIP_ADDR_ZIP"        "SN_01_LTD_RECIP_NAME_ORG_L1"    
## [19] "LiquidationOfAssetsTableGrp"     "DirectorOfSuccessor2Ind"        
## [21] "EmployeeOfSuccessor2Ind"         "OwnerOfSuccessor2Ind"           
## [23] "ReceiveCompensation2Ind"         "AssetsDistributedInd"           
## [25] "RequiredToNotifyAGInd"           "AttorneyGeneralNotifiedInd"     
## [27] "LiabilitiesPaidInd"              "BondsOutstandingInd"            
## [29] "BondLiabilitiesDischargedInd"    "DirectorOfSuccessorInd"         
## [31] "EmployeeOfSuccessorInd"          "OwnerOfSuccessorInd"            
## [33] "ReceiveCompensationInd"          "SN_01_LTD_RECIP_ADDR_L2"        
## [35] "SN_01_LTD_RECIP_ADDR_CNTR"       "SN_01_LTD_RECIP_NAME_ORG_L2"

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/IRS990ScheduleN/LiquidationOfAssetsTableGrp"                                         
## [3] "/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail"               
## [4] "/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/BusinessName"  
## [5] "/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/ForeignAddress"
## [6] "/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/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       °--IRS990ScheduleN                         
## 4           °--LiquidationOfAssetsTableGrp         
## 5               °--LiquidationOfAssetsDetail       
## 6                   ¦--AssetsDistriOrExpnssPaidDesc
## 7                   ¦--BusinessName                
## 8                   ¦   ¦--BusinessNameLine1Txt    
## 9                   ¦   °--BusinessNameLine2Txt    
## 10                  ¦--DistributionDt              
## 11                  ¦--EIN                         
## 12                  ¦--FairMarketValueOfAssetAmt   
## 13                  ¦--ForeignAddress              
## 14                  ¦   ¦--AddressLine1Txt         
## 15                  ¦   ¦--CityNm                  
## 16                  ¦   ¦--CountryCd               
## 17                  ¦   ¦--ForeignPostalCd         
## 18                  ¦   °--ProvinceOrStateNm       
## 19                  ¦--IRCSectionTxt               
## 20                  ¦--MethodOfFMVDeterminationTxt 
## 21                  ¦--PersonNm                    
## 22                  °--USAddress                   
## 23                      ¦--AddressLine1Txt         
## 24                      ¦--AddressLine2Txt         
## 25                      ¦--CityNm                  
## 26                      ¦--StateAbbreviationCd     
## 27                      °--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/IRS990ScheduleN/Form990ScheduleNPartI/LiquidationTable/AddressForeign/AddressLine1	1
/Return/ReturnData/IRS990ScheduleN/Form990ScheduleNPartI/LiquidationTable/AddressForeign/AddressLine2	2
/Return/ReturnData/IRS990ScheduleN/Form990ScheduleNPartI/LiquidationTable/AddressForeign/City	3
/Return/ReturnData/IRS990ScheduleN/Form990ScheduleNPartI/LiquidationTable/AddressForeign/Country	4
/Return/ReturnData/IRS990ScheduleN/Form990ScheduleNPartI/LiquidationTable/AddressForeign/PostalCode	5
/Return/ReturnData/IRS990ScheduleN/Form990ScheduleNPartI/LiquidationTable/AddressForeign/ProvinceOrState	6
/Return/ReturnData/IRS990ScheduleN/Form990ScheduleNPartI/LiquidationTable/AddressUS/AddressLine1	7
/Return/ReturnData/IRS990ScheduleN/Form990ScheduleNPartI/LiquidationTable/AddressUS/AddressLine2	8
/Return/ReturnData/IRS990ScheduleN/Form990ScheduleNPartI/LiquidationTable/AddressUS/City	9
/Return/ReturnData/IRS990ScheduleN/Form990ScheduleNPartI/LiquidationTable/AddressUS/State	10
/Return/ReturnData/IRS990ScheduleN/Form990ScheduleNPartI/LiquidationTable/AddressUS/ZIPCode	11
/Return/ReturnData/IRS990ScheduleN/Form990ScheduleNPartI/LiquidationTable/DateOfDistribution	12
/Return/ReturnData/IRS990ScheduleN/Form990ScheduleNPartI/LiquidationTable/DescriptionOfAsset	13
/Return/ReturnData/IRS990ScheduleN/Form990ScheduleNPartI/LiquidationTable/EIN	14
/Return/ReturnData/IRS990ScheduleN/Form990ScheduleNPartI/LiquidationTable/FMVOfAsset	15
/Return/ReturnData/IRS990ScheduleN/Form990ScheduleNPartI/LiquidationTable/IRCSection	16
/Return/ReturnData/IRS990ScheduleN/Form990ScheduleNPartI/LiquidationTable/MethodOfFMVDetermination	17
/Return/ReturnData/IRS990ScheduleN/Form990ScheduleNPartI/LiquidationTable/NameBusiness/BusinessNameLine1	18
/Return/ReturnData/IRS990ScheduleN/Form990ScheduleNPartI/LiquidationTable/NameBusiness/BusinessNameLine2	19
/Return/ReturnData/IRS990ScheduleN/Form990ScheduleNPartI/LiquidationTable/NamePerson	20
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/AssetsDistriOrExpnssPaidDesc	21
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/BusinessName/BusinessNameLine1	22
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/BusinessName/BusinessNameLine1Txt	23
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/BusinessName/BusinessNameLine2	24
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/BusinessName/BusinessNameLine2Txt	25
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/DistributionDt	26
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/EIN	27
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/FairMarketValueOfAssetAmt	28
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/ForeignAddress/AddressLine1	29
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/ForeignAddress/AddressLine1Txt	30
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/ForeignAddress/AddressLine2	31
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/ForeignAddress/AddressLine2Txt	32
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/ForeignAddress/City	33
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/ForeignAddress/CityNm	34
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/ForeignAddress/Country	35
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/ForeignAddress/CountryCd	36
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/ForeignAddress/ForeignPostalCd	37
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/ForeignAddress/PostalCode	38
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/ForeignAddress/ProvinceOrState	39
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/ForeignAddress/ProvinceOrStateNm	40
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/IRCSectionTxt	41
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/MethodOfFMVDeterminationTxt	42
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/PersonNm	43
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/USAddress/AddressLine1	44
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/USAddress/AddressLine1Txt	45
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/USAddress/AddressLine2	46
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/USAddress/AddressLine2Txt	47
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/USAddress/City	48
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/USAddress/CityNm	49
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/USAddress/State	50
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/USAddress/StateAbbreviationCd	51
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/USAddress/ZIPCd	52
/Return/ReturnData/IRS990ScheduleN/LiquidationOfAssetsTableGrp/LiquidationOfAssetsDetail/USAddress/ZIPCode	53
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/AddressForeign/AddressLine1	54
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/AddressForeign/AddressLine2	55
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/AddressForeign/City	56
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/AddressForeign/Country	57
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/AddressForeign/PostalCode	58
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/AddressForeign/ProvinceOrState	59
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/AddressUS/AddressLine1	60
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/AddressUS/AddressLine2	61
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/AddressUS/City	62
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/AddressUS/State	63
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/AddressUS/ZIPCode	64
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/DateOfDistribution	65
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/DescriptionOfAsset	66
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/EIN	67
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/FMVOfAsset	68
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/IRCSection	69
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/LiquidationDetail/AddressForeign/AddressLine1	70
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/LiquidationDetail/AddressForeign/AddressLine2	71
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/LiquidationDetail/AddressForeign/City	72
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/LiquidationDetail/AddressForeign/Country	73
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/LiquidationDetail/AddressForeign/PostalCode	74
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/LiquidationDetail/AddressForeign/ProvinceOrState	75
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/LiquidationDetail/AddressUS/AddressLine1	76
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/LiquidationDetail/AddressUS/AddressLine2	77
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/LiquidationDetail/AddressUS/City	78
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/LiquidationDetail/AddressUS/State	79
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/LiquidationDetail/AddressUS/ZIPCode	80
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/LiquidationDetail/DateOfDistribution	81
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/LiquidationDetail/DescriptionOfAsset	82
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/LiquidationDetail/EIN	83
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/LiquidationDetail/FMVOfAsset	84
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/LiquidationDetail/IRCSection	85
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/LiquidationDetail/MethodOfFMVDetermination	86
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/LiquidationDetail/NameBusiness/BusinessNameLine1	87
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/LiquidationDetail/NameBusiness/BusinessNameLine2	88
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/LiquidationDetail/NamePerson	89
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/MethodOfFMVDetermination	90
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/NameBusiness/BusinessNameLine1	91
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/NameBusiness/BusinessNameLine2	92
/Return/ReturnData/IRS990ScheduleN/LiquidationTable/NamePerson	93

Grouping Variables

Identify all unique groups used in xpaths.

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

## [1] "//IRS990ScheduleN/Form990ScheduleNPartI/LiquidationTable"
## [2] "//IRS990ScheduleN/LiquidationOfAssetsTableGrp"           
## [3] "//IRS990ScheduleN/LiquidationTable"

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: SN-P01-T01-LIQUIDATION-TERMINATION-DISSOLUTION

Last compiled on 05 April, 2023