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")head( index )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.
concordance %>%
filter( rdb_table == table.name )
| xpath | id |
|---|---|
| /Return/ReturnData/IRS990/ContractorCompensation/AddressOfContractor/AddressForeign/AddressLine1 | 1 |
| /Return/ReturnData/IRS990/ContractorCompensation/AddressOfContractor/AddressForeign/AddressLine2 | 2 |
| /Return/ReturnData/IRS990/ContractorCompensation/AddressOfContractor/AddressForeign/City | 3 |
| /Return/ReturnData/IRS990/ContractorCompensation/AddressOfContractor/AddressForeign/Country | 4 |
| /Return/ReturnData/IRS990/ContractorCompensation/AddressOfContractor/AddressForeign/PostalCode | 5 |
| /Return/ReturnData/IRS990/ContractorCompensation/AddressOfContractor/AddressForeign/ProvinceOrState | 6 |
| /Return/ReturnData/IRS990/ContractorCompensation/AddressOfContractor/AddressUS/AddressLine1 | 7 |
| /Return/ReturnData/IRS990/ContractorCompensation/AddressOfContractor/AddressUS/AddressLine2 | 8 |
| /Return/ReturnData/IRS990/ContractorCompensation/AddressOfContractor/AddressUS/City | 9 |
| /Return/ReturnData/IRS990/ContractorCompensation/AddressOfContractor/AddressUS/State | 10 |
| /Return/ReturnData/IRS990/ContractorCompensation/AddressOfContractor/AddressUS/ZIPCode | 11 |
| /Return/ReturnData/IRS990/ContractorCompensation/Compensation | 12 |
| /Return/ReturnData/IRS990/ContractorCompensation/DescriptionOfServices | 13 |
| /Return/ReturnData/IRS990/ContractorCompensation/NameOfContractor/NameBusiness/BusinessNameLine1 | 14 |
| /Return/ReturnData/IRS990/ContractorCompensation/NameOfContractor/NameBusiness/BusinessNameLine2 | 15 |
| /Return/ReturnData/IRS990/ContractorCompensation/NameOfContractor/NamePerson | 16 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/CompensationAmt | 17 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/ForeignAddress/AddressLine1 | 18 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/ForeignAddress/AddressLine1Txt | 19 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/ForeignAddress/AddressLine2 | 20 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/ForeignAddress/AddressLine2Txt | 21 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/ForeignAddress/City | 22 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/ForeignAddress/CityNm | 23 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/ForeignAddress/Country | 24 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/ForeignAddress/CountryCd | 25 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/ForeignAddress/ForeignPostalCd | 26 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/ForeignAddress/PostalCode | 27 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/ForeignAddress/ProvinceOrState | 28 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/ForeignAddress/ProvinceOrStateNm | 29 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/USAddress/AddressLine1 | 30 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/USAddress/AddressLine1Txt | 31 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/USAddress/AddressLine2 | 32 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/USAddress/AddressLine2Txt | 33 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/USAddress/City | 34 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/USAddress/CityNm | 35 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/USAddress/State | 36 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/USAddress/StateAbbreviationCd | 37 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/USAddress/ZIPCd | 38 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/USAddress/ZIPCode | 39 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorName/BusinessName/BusinessNameLine1 | 40 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorName/BusinessName/BusinessNameLine1Txt | 41 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorName/BusinessName/BusinessNameLine2 | 42 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorName/BusinessName/BusinessNameLine2Txt | 43 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorName/PersonNm | 44 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ServicesDesc | 45 |
| /Return/ReturnData/IRS990/Form990PartVIISectionB/ContractorCompensation/AddressOfContractor/AddressForeign/AddressLine1 | 46 |
| /Return/ReturnData/IRS990/Form990PartVIISectionB/ContractorCompensation/AddressOfContractor/AddressForeign/AddressLine2 | 47 |
| /Return/ReturnData/IRS990/Form990PartVIISectionB/ContractorCompensation/AddressOfContractor/AddressForeign/City | 48 |
| /Return/ReturnData/IRS990/Form990PartVIISectionB/ContractorCompensation/AddressOfContractor/AddressForeign/Country | 49 |
| /Return/ReturnData/IRS990/Form990PartVIISectionB/ContractorCompensation/AddressOfContractor/AddressForeign/PostalCode | 50 |
| /Return/ReturnData/IRS990/Form990PartVIISectionB/ContractorCompensation/AddressOfContractor/AddressForeign/ProvinceOrState | 51 |
| /Return/ReturnData/IRS990/Form990PartVIISectionB/ContractorCompensation/AddressOfContractor/AddressUS/AddressLine1 | 52 |
| /Return/ReturnData/IRS990/Form990PartVIISectionB/ContractorCompensation/AddressOfContractor/AddressUS/AddressLine2 | 53 |
| /Return/ReturnData/IRS990/Form990PartVIISectionB/ContractorCompensation/AddressOfContractor/AddressUS/City | 54 |
| /Return/ReturnData/IRS990/Form990PartVIISectionB/ContractorCompensation/AddressOfContractor/AddressUS/State | 55 |
| /Return/ReturnData/IRS990/Form990PartVIISectionB/ContractorCompensation/AddressOfContractor/AddressUS/ZIPCode | 56 |
| /Return/ReturnData/IRS990/Form990PartVIISectionB/ContractorCompensation/Compensation | 57 |
| /Return/ReturnData/IRS990/Form990PartVIISectionB/ContractorCompensation/DescriptionOfServices | 58 |
| /Return/ReturnData/IRS990/Form990PartVIISectionB/ContractorCompensation/NameOfContractor/NameBusiness/BusinessNameLine1 | 59 |
| /Return/ReturnData/IRS990/Form990PartVIISectionB/ContractorCompensation/NameOfContractor/NameBusiness/BusinessNameLine2 | 60 |
| /Return/ReturnData/IRS990/Form990PartVIISectionB/ContractorCompensation/NameOfContractor/NamePerson | 61 |
| /Return/ReturnData/IRS990EZ/CompOfHghstPaidCntrctProfSer/AddressForeign/AddressLine1 | 62 |
| /Return/ReturnData/IRS990EZ/CompOfHghstPaidCntrctProfSer/AddressForeign/AddressLine2 | 63 |
| /Return/ReturnData/IRS990EZ/CompOfHghstPaidCntrctProfSer/AddressForeign/City | 64 |
| /Return/ReturnData/IRS990EZ/CompOfHghstPaidCntrctProfSer/AddressForeign/Country | 65 |
| /Return/ReturnData/IRS990EZ/CompOfHghstPaidCntrctProfSer/AddressForeign/PostalCode | 66 |
| /Return/ReturnData/IRS990EZ/CompOfHghstPaidCntrctProfSer/AddressForeign/ProvinceOrState | 67 |
| /Return/ReturnData/IRS990EZ/CompOfHghstPaidCntrctProfSer/AddressUS/AddressLine1 | 68 |
| /Return/ReturnData/IRS990EZ/CompOfHghstPaidCntrctProfSer/AddressUS/AddressLine2 | 69 |
| /Return/ReturnData/IRS990EZ/CompOfHghstPaidCntrctProfSer/AddressUS/City | 70 |
| /Return/ReturnData/IRS990EZ/CompOfHghstPaidCntrctProfSer/AddressUS/State | 71 |
| /Return/ReturnData/IRS990EZ/CompOfHghstPaidCntrctProfSer/AddressUS/ZIPCode | 72 |
| /Return/ReturnData/IRS990EZ/CompOfHghstPaidCntrctProfSer/Compensation | 73 |
| /Return/ReturnData/IRS990EZ/CompOfHghstPaidCntrctProfSer/NameBusiness/BusinessNameLine1 | 74 |
| /Return/ReturnData/IRS990EZ/CompOfHghstPaidCntrctProfSer/NameBusiness/BusinessNameLine2 | 75 |
| /Return/ReturnData/IRS990EZ/CompOfHghstPaidCntrctProfSer/NamePerson | 76 |
| /Return/ReturnData/IRS990EZ/CompOfHghstPaidCntrctProfSer/TypeOfService | 77 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/BusinessName/BusinessNameLine1 | 78 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/BusinessName/BusinessNameLine1Txt | 79 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/BusinessName/BusinessNameLine2 | 80 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/BusinessName/BusinessNameLine2Txt | 81 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/CompensationAmt | 82 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/ForeignAddress/AddressLine1 | 83 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/ForeignAddress/AddressLine1Txt | 84 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/ForeignAddress/AddressLine2 | 85 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/ForeignAddress/AddressLine2Txt | 86 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/ForeignAddress/City | 87 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/ForeignAddress/CityNm | 88 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/ForeignAddress/Country | 89 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/ForeignAddress/CountryCd | 90 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/ForeignAddress/ForeignPostalCd | 91 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/ForeignAddress/PostalCode | 92 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/ForeignAddress/ProvinceOrState | 93 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/ForeignAddress/ProvinceOrStateNm | 94 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/PersonNm | 95 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/ServiceTypeTxt | 96 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/USAddress/AddressLine1 | 97 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/USAddress/AddressLine1Txt | 98 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/USAddress/AddressLine2 | 99 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/USAddress/AddressLine2Txt | 100 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/USAddress/City | 101 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/USAddress/CityNm | 102 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/USAddress/State | 103 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/USAddress/StateAbbreviationCd | 104 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/USAddress/ZIPCd | 105 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/USAddress/ZIPCode | 106 |
| /Return/ReturnData/IRS990EZ/PartVIAHghstPaidCntrctProfSer | 107 |
| /Return/ReturnData/IRS990EZ/PartVIHghstPdCntrctProfSrvcTxt | 108 |
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 ¦ ¦--ContractorCompensation
## 5 ¦ ¦ ¦--AddressOfContractor
## 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 ¦ ¦ ¦--Compensation
## 20 ¦ ¦ ¦--DescriptionOfServices
## 21 ¦ ¦ °--NameOfContractor
## 22 ¦ ¦ ¦--NameBusiness
## 23 ¦ ¦ ¦ ¦--BusinessNameLine1
## 24 ¦ ¦ ¦ °--BusinessNameLine2
## 25 ¦ ¦ °--NamePerson
## 26 ¦ ¦--ContractorCompensationGrp
## 27 ¦ ¦ ¦--CompensationAmt
## 28 ¦ ¦ ¦--ContractorAddress
## 29 ¦ ¦ ¦ ¦--ForeignAddress
## 30 ¦ ¦ ¦ ¦ ¦--AddressLine1
## 31 ¦ ¦ ¦ ¦ ¦--AddressLine1Txt
## 32 ¦ ¦ ¦ ¦ ¦--AddressLine2
## 33 ¦ ¦ ¦ ¦ ¦--AddressLine2Txt
## 34 ¦ ¦ ¦ ¦ ¦--City
## 35 ¦ ¦ ¦ ¦ ¦--CityNm
## 36 ¦ ¦ ¦ ¦ ¦--Country
## 37 ¦ ¦ ¦ ¦ ¦--CountryCd
## 38 ¦ ¦ ¦ ¦ ¦--ForeignPostalCd
## 39 ¦ ¦ ¦ ¦ ¦--PostalCode
## 40 ¦ ¦ ¦ ¦ ¦--ProvinceOrState
## 41 ¦ ¦ ¦ ¦ °--ProvinceOrStateNm
## 42 ¦ ¦ ¦ °--USAddress
## 43 ¦ ¦ ¦ ¦--AddressLine1
## 44 ¦ ¦ ¦ ¦--AddressLine1Txt
## 45 ¦ ¦ ¦ ¦--AddressLine2
## 46 ¦ ¦ ¦ ¦--AddressLine2Txt
## 47 ¦ ¦ ¦ ¦--City
## 48 ¦ ¦ ¦ ¦--CityNm
## 49 ¦ ¦ ¦ ¦--State
## 50 ¦ ¦ ¦ ¦--StateAbbreviationCd
## 51 ¦ ¦ ¦ ¦--ZIPCd
## 52 ¦ ¦ ¦ °--ZIPCode
## 53 ¦ ¦ ¦--ContractorName
## 54 ¦ ¦ ¦ ¦--BusinessName
## 55 ¦ ¦ ¦ ¦ ¦--BusinessNameLine1
## 56 ¦ ¦ ¦ ¦ ¦--BusinessNameLine1Txt
## 57 ¦ ¦ ¦ ¦ ¦--BusinessNameLine2
## 58 ¦ ¦ ¦ ¦ °--BusinessNameLine2Txt
## 59 ¦ ¦ ¦ °--PersonNm
## 60 ¦ ¦ °--ServicesDesc
## 61 ¦ °--Form990PartVIISectionB
## 62 ¦ °--ContractorCompensation
## 63 ¦ ¦--AddressOfContractor
## 64 ¦ ¦ ¦--AddressForeign
## 65 ¦ ¦ ¦ ¦--AddressLine1
## 66 ¦ ¦ ¦ ¦--AddressLine2
## 67 ¦ ¦ ¦ ¦--City
## 68 ¦ ¦ ¦ ¦--Country
## 69 ¦ ¦ ¦ ¦--PostalCode
## 70 ¦ ¦ ¦ °--ProvinceOrState
## 71 ¦ ¦ °--AddressUS
## 72 ¦ ¦ ¦--AddressLine1
## 73 ¦ ¦ ¦--AddressLine2
## 74 ¦ ¦ ¦--City
## 75 ¦ ¦ ¦--State
## 76 ¦ ¦ °--ZIPCode
## 77 ¦ ¦--Compensation
## 78 ¦ ¦--DescriptionOfServices
## 79 ¦ °--NameOfContractor
## 80 ¦ ¦--NameBusiness
## 81 ¦ ¦ ¦--BusinessNameLine1
## 82 ¦ ¦ °--BusinessNameLine2
## 83 ¦ °--NamePerson
## 84 °--IRS990EZ
## 85 ¦--CompOfHghstPaidCntrctProfSer
## 86 ¦ ¦--AddressForeign
## 87 ¦ ¦ ¦--AddressLine1
## 88 ¦ ¦ ¦--AddressLine2
## 89 ¦ ¦ ¦--City
## 90 ¦ ¦ ¦--Country
## 91 ¦ ¦ ¦--PostalCode
## 92 ¦ ¦ °--ProvinceOrState
## 93 ¦ ¦--AddressUS
## 94 ¦ ¦ ¦--AddressLine1
## 95 ¦ ¦ ¦--AddressLine2
## 96 ¦ ¦ ¦--City
## 97 ¦ ¦ ¦--State
## 98 ¦ ¦ °--ZIPCode
## 99 ¦ ¦--Compensation
## 100 ¦ °--... 3 nodes w/ 2 sub
## 101 °--... 3 nodes w/ 37 subSetGraphStyle( 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] "//ContractorCompensation" "//ContractorCompensationGrp"
## [3] "//Form990PartVIISectionB" "//CompensationOfHghstPdCntrctGrp"
## [5] "//CompOfHghstPaidCntrctProfSer" "//PartVIAHghstPaidCntrctProfSer"
## [7] "//PartVIHghstPdCntrctProfSrvcTxt"# new version
find_table_headers( table.name )## [1] "//ContractorCompensation/ContractorCompensation"
## [2] "//ContractorCompensation/Form990PartVIISectionB"
## [3] "//ContractorCompensationGrp/CompensationAmt"
## [4] "//ContractorCompensationGrp/ContractorAddress"
## [5] "//ContractorCompensationGrp/ContractorName"
## [6] "//ContractorCompensationGrp/ServicesDesc"
## [7] "//Form990PartVIISectionB/ContractorCompensation"
## [8] "//Form990PartVIISectionB/NumberOfContractorsGT100K"
## [9] "//CompensationOfHghstPdCntrctGrp/BusinessName"
## [10] "//CompensationOfHghstPdCntrctGrp/CompensationAmt"
## [11] "//CompensationOfHghstPdCntrctGrp/ForeignAddress"
## [12] "//CompensationOfHghstPdCntrctGrp/PersonNm"
## [13] "//CompensationOfHghstPdCntrctGrp/ServiceTypeTxt"
## [14] "//CompensationOfHghstPdCntrctGrp/USAddress"
## [15] "//CompOfHghstPaidCntrctProfSer/AddressForeign"
## [16] "//CompOfHghstPaidCntrctProfSer/AddressUS"
## [17] "//CompOfHghstPaidCntrctProfSer/Compensation"
## [18] "//CompOfHghstPaidCntrctProfSer/NameBusiness"
## [19] "//CompOfHghstPaidCntrctProfSer/NamePerson"
## [20] "//CompOfHghstPaidCntrctProfSer/TypeOfService"
## [21] "//PartVIAHghstPaidCntrctProfSer/"
## [22] "//PartVIHghstPdCntrctProfSrvcTxt/"# manual
if( ! is.null(table.headers) )
{table.headers}## [1] "//IRS990/ContractorCompensation"
## [2] "//IRS990/ContractorCompensationGrp"
## [3] "//IRS990/Form990PartVIISectionB/ContractorCompensation"
## [4] "//IRS990EZ/CompOfHghstPaidCntrctProfSer"
## [5] "//IRS990EZ/CompensationOfHghstPdCntrctGrp"
# 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-P07-T02-CONTRACTORS
## [1] "NUMBER OF RETURNS PROCESSED: 1000"
## [1] "TOTAL NUMBER OF TABLE ROWS: 237"TOTAL RUN TIME:
## Time difference of 17.79517 mins
## Time difference of -0.2965861 hourswrite.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_07_COMP_KONTR_DESC_SVC" "F9_07_COMP_KONTR_COMP"
## [9] "F9_07_COMP_KONTR_NAME_ORG_L1" "F9_07_COMP_KONTR_ADDR_L1"
## [11] "F9_07_COMP_KONTR_ADDR_CITY" "F9_07_COMP_KONTR_ADDR_STATE"
## [13] "F9_07_COMP_KONTR_ADDR_ZIP" "F9_07_COMP_KONTR_NAME_PERS"
## [15] "AddressOfContractor" "ContractorAddress"
## [17] "F9_07_COMP_KONTR_ADDR_L2"
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/ContractorCompensation"
## [3] "/Return/ReturnData/IRS990/ContractorCompensation/AddressOfContractor"
## [4] "/Return/ReturnData/IRS990/ContractorCompensation/AddressOfContractor/AddressUS"
## [5] "/Return/ReturnData/IRS990/ContractorCompensation/NameOfContractor"
## [6] "/Return/ReturnData/IRS990/ContractorCompensation/NameOfContractor/NameBusiness"
## [7] "/Return/ReturnData/IRS990/ContractorCompensationGrp"
## [8] "/Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress"
## [9] "/Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/ForeignAddress"
## [10] "/Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/USAddress"
## [11] "/Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorName"
## [12] "/Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorName/BusinessName"Capture to update the Concordance file.
these <- setdiff( all.xpaths, xpath.concordance )
cat( these, sep="\n", file=paste0( folder.name, "/MISSING-XPATHS.txt" ) )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 |
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 |
# library( data.tree )
nd <- FromDataFrameNetwork( network=el )
print( nd )## levelName
## 1 Return
## 2 °--ReturnData
## 3 °--IRS990
## 4 ¦--ContractorCompensation
## 5 ¦ ¦--AddressOfContractor
## 6 ¦ ¦ °--AddressUS
## 7 ¦ ¦ ¦--AddressLine1
## 8 ¦ ¦ ¦--AddressLine2
## 9 ¦ ¦ ¦--City
## 10 ¦ ¦ ¦--State
## 11 ¦ ¦ °--ZIPCode
## 12 ¦ ¦--Compensation
## 13 ¦ ¦--DescriptionOfServices
## 14 ¦ °--NameOfContractor
## 15 ¦ ¦--NameBusiness
## 16 ¦ ¦ °--BusinessNameLine1
## 17 ¦ °--NamePerson
## 18 °--ContractorCompensationGrp
## 19 ¦--CompensationAmt
## 20 ¦--ContractorAddress
## 21 ¦ ¦--ForeignAddress
## 22 ¦ ¦ ¦--AddressLine1
## 23 ¦ ¦ ¦--AddressLine1Txt
## 24 ¦ ¦ ¦--City
## 25 ¦ ¦ ¦--CityNm
## 26 ¦ ¦ ¦--Country
## 27 ¦ ¦ ¦--CountryCd
## 28 ¦ ¦ ¦--PostalCode
## 29 ¦ ¦ °--ProvinceOrStateNm
## 30 ¦ °--USAddress
## 31 ¦ ¦--AddressLine1
## 32 ¦ ¦--AddressLine1Txt
## 33 ¦ ¦--City
## 34 ¦ ¦--CityNm
## 35 ¦ ¦--State
## 36 ¦ ¦--StateAbbreviationCd
## 37 ¦ ¦--ZIPCd
## 38 ¦ °--ZIPCode
## 39 ¦--ContractorName
## 40 ¦ ¦--BusinessName
## 41 ¦ ¦ ¦--BusinessNameLine1
## 42 ¦ ¦ °--BusinessNameLine1Txt
## 43 ¦ °--PersonNm
## 44 °--ServicesDescSetGraphStyle( 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 )
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/ContractorCompensation/AddressOfContractor/AddressForeign/AddressLine1 | 1 |
| /Return/ReturnData/IRS990/ContractorCompensation/AddressOfContractor/AddressForeign/AddressLine2 | 2 |
| /Return/ReturnData/IRS990/ContractorCompensation/AddressOfContractor/AddressForeign/City | 3 |
| /Return/ReturnData/IRS990/ContractorCompensation/AddressOfContractor/AddressForeign/Country | 4 |
| /Return/ReturnData/IRS990/ContractorCompensation/AddressOfContractor/AddressForeign/PostalCode | 5 |
| /Return/ReturnData/IRS990/ContractorCompensation/AddressOfContractor/AddressForeign/ProvinceOrState | 6 |
| /Return/ReturnData/IRS990/ContractorCompensation/AddressOfContractor/AddressUS/AddressLine1 | 7 |
| /Return/ReturnData/IRS990/ContractorCompensation/AddressOfContractor/AddressUS/AddressLine2 | 8 |
| /Return/ReturnData/IRS990/ContractorCompensation/AddressOfContractor/AddressUS/City | 9 |
| /Return/ReturnData/IRS990/ContractorCompensation/AddressOfContractor/AddressUS/State | 10 |
| /Return/ReturnData/IRS990/ContractorCompensation/AddressOfContractor/AddressUS/ZIPCode | 11 |
| /Return/ReturnData/IRS990/ContractorCompensation/Compensation | 12 |
| /Return/ReturnData/IRS990/ContractorCompensation/DescriptionOfServices | 13 |
| /Return/ReturnData/IRS990/ContractorCompensation/NameOfContractor/NameBusiness/BusinessNameLine1 | 14 |
| /Return/ReturnData/IRS990/ContractorCompensation/NameOfContractor/NameBusiness/BusinessNameLine2 | 15 |
| /Return/ReturnData/IRS990/ContractorCompensation/NameOfContractor/NamePerson | 16 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/CompensationAmt | 17 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/ForeignAddress/AddressLine1 | 18 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/ForeignAddress/AddressLine1Txt | 19 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/ForeignAddress/AddressLine2 | 20 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/ForeignAddress/AddressLine2Txt | 21 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/ForeignAddress/City | 22 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/ForeignAddress/CityNm | 23 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/ForeignAddress/Country | 24 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/ForeignAddress/CountryCd | 25 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/ForeignAddress/ForeignPostalCd | 26 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/ForeignAddress/PostalCode | 27 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/ForeignAddress/ProvinceOrState | 28 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/ForeignAddress/ProvinceOrStateNm | 29 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/USAddress/AddressLine1 | 30 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/USAddress/AddressLine1Txt | 31 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/USAddress/AddressLine2 | 32 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/USAddress/AddressLine2Txt | 33 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/USAddress/City | 34 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/USAddress/CityNm | 35 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/USAddress/State | 36 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/USAddress/StateAbbreviationCd | 37 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/USAddress/ZIPCd | 38 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorAddress/USAddress/ZIPCode | 39 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorName/BusinessName/BusinessNameLine1 | 40 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorName/BusinessName/BusinessNameLine1Txt | 41 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorName/BusinessName/BusinessNameLine2 | 42 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorName/BusinessName/BusinessNameLine2Txt | 43 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ContractorName/PersonNm | 44 |
| /Return/ReturnData/IRS990/ContractorCompensationGrp/ServicesDesc | 45 |
| /Return/ReturnData/IRS990/Form990PartVIISectionB/ContractorCompensation/AddressOfContractor/AddressForeign/AddressLine1 | 46 |
| /Return/ReturnData/IRS990/Form990PartVIISectionB/ContractorCompensation/AddressOfContractor/AddressForeign/AddressLine2 | 47 |
| /Return/ReturnData/IRS990/Form990PartVIISectionB/ContractorCompensation/AddressOfContractor/AddressForeign/City | 48 |
| /Return/ReturnData/IRS990/Form990PartVIISectionB/ContractorCompensation/AddressOfContractor/AddressForeign/Country | 49 |
| /Return/ReturnData/IRS990/Form990PartVIISectionB/ContractorCompensation/AddressOfContractor/AddressForeign/PostalCode | 50 |
| /Return/ReturnData/IRS990/Form990PartVIISectionB/ContractorCompensation/AddressOfContractor/AddressForeign/ProvinceOrState | 51 |
| /Return/ReturnData/IRS990/Form990PartVIISectionB/ContractorCompensation/AddressOfContractor/AddressUS/AddressLine1 | 52 |
| /Return/ReturnData/IRS990/Form990PartVIISectionB/ContractorCompensation/AddressOfContractor/AddressUS/AddressLine2 | 53 |
| /Return/ReturnData/IRS990/Form990PartVIISectionB/ContractorCompensation/AddressOfContractor/AddressUS/City | 54 |
| /Return/ReturnData/IRS990/Form990PartVIISectionB/ContractorCompensation/AddressOfContractor/AddressUS/State | 55 |
| /Return/ReturnData/IRS990/Form990PartVIISectionB/ContractorCompensation/AddressOfContractor/AddressUS/ZIPCode | 56 |
| /Return/ReturnData/IRS990/Form990PartVIISectionB/ContractorCompensation/Compensation | 57 |
| /Return/ReturnData/IRS990/Form990PartVIISectionB/ContractorCompensation/DescriptionOfServices | 58 |
| /Return/ReturnData/IRS990/Form990PartVIISectionB/ContractorCompensation/NameOfContractor/NameBusiness/BusinessNameLine1 | 59 |
| /Return/ReturnData/IRS990/Form990PartVIISectionB/ContractorCompensation/NameOfContractor/NameBusiness/BusinessNameLine2 | 60 |
| /Return/ReturnData/IRS990/Form990PartVIISectionB/ContractorCompensation/NameOfContractor/NamePerson | 61 |
| /Return/ReturnData/IRS990EZ/CompOfHghstPaidCntrctProfSer/AddressForeign/AddressLine1 | 62 |
| /Return/ReturnData/IRS990EZ/CompOfHghstPaidCntrctProfSer/AddressForeign/AddressLine2 | 63 |
| /Return/ReturnData/IRS990EZ/CompOfHghstPaidCntrctProfSer/AddressForeign/City | 64 |
| /Return/ReturnData/IRS990EZ/CompOfHghstPaidCntrctProfSer/AddressForeign/Country | 65 |
| /Return/ReturnData/IRS990EZ/CompOfHghstPaidCntrctProfSer/AddressForeign/PostalCode | 66 |
| /Return/ReturnData/IRS990EZ/CompOfHghstPaidCntrctProfSer/AddressForeign/ProvinceOrState | 67 |
| /Return/ReturnData/IRS990EZ/CompOfHghstPaidCntrctProfSer/AddressUS/AddressLine1 | 68 |
| /Return/ReturnData/IRS990EZ/CompOfHghstPaidCntrctProfSer/AddressUS/AddressLine2 | 69 |
| /Return/ReturnData/IRS990EZ/CompOfHghstPaidCntrctProfSer/AddressUS/City | 70 |
| /Return/ReturnData/IRS990EZ/CompOfHghstPaidCntrctProfSer/AddressUS/State | 71 |
| /Return/ReturnData/IRS990EZ/CompOfHghstPaidCntrctProfSer/AddressUS/ZIPCode | 72 |
| /Return/ReturnData/IRS990EZ/CompOfHghstPaidCntrctProfSer/Compensation | 73 |
| /Return/ReturnData/IRS990EZ/CompOfHghstPaidCntrctProfSer/NameBusiness/BusinessNameLine1 | 74 |
| /Return/ReturnData/IRS990EZ/CompOfHghstPaidCntrctProfSer/NameBusiness/BusinessNameLine2 | 75 |
| /Return/ReturnData/IRS990EZ/CompOfHghstPaidCntrctProfSer/NamePerson | 76 |
| /Return/ReturnData/IRS990EZ/CompOfHghstPaidCntrctProfSer/TypeOfService | 77 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/BusinessName/BusinessNameLine1 | 78 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/BusinessName/BusinessNameLine1Txt | 79 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/BusinessName/BusinessNameLine2 | 80 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/BusinessName/BusinessNameLine2Txt | 81 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/CompensationAmt | 82 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/ForeignAddress/AddressLine1 | 83 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/ForeignAddress/AddressLine1Txt | 84 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/ForeignAddress/AddressLine2 | 85 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/ForeignAddress/AddressLine2Txt | 86 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/ForeignAddress/City | 87 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/ForeignAddress/CityNm | 88 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/ForeignAddress/Country | 89 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/ForeignAddress/CountryCd | 90 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/ForeignAddress/ForeignPostalCd | 91 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/ForeignAddress/PostalCode | 92 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/ForeignAddress/ProvinceOrState | 93 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/ForeignAddress/ProvinceOrStateNm | 94 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/PersonNm | 95 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/ServiceTypeTxt | 96 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/USAddress/AddressLine1 | 97 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/USAddress/AddressLine1Txt | 98 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/USAddress/AddressLine2 | 99 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/USAddress/AddressLine2Txt | 100 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/USAddress/City | 101 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/USAddress/CityNm | 102 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/USAddress/State | 103 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/USAddress/StateAbbreviationCd | 104 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/USAddress/ZIPCd | 105 |
| /Return/ReturnData/IRS990EZ/CompensationOfHghstPdCntrctGrp/USAddress/ZIPCode | 106 |
| /Return/ReturnData/IRS990EZ/PartVIAHghstPaidCntrctProfSer | 107 |
| /Return/ReturnData/IRS990EZ/PartVIHghstPdCntrctProfSrvcTxt | 108 |
Identify all unique groups used in xpaths.
if( is.null(table.headers) )
{ find_table_headers( table.name ) } else
table.headers## [1] "//IRS990/ContractorCompensation"
## [2] "//IRS990/ContractorCompensationGrp"
## [3] "//IRS990/Form990PartVIISectionB/ContractorCompensation"
## [4] "//IRS990EZ/CompOfHghstPaidCntrctProfSer"
## [5] "//IRS990EZ/CompensationOfHghstPdCntrctGrp"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 ## NULLMapping 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()}
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