1. Downloading and assembling efile tables
downloading-tables.Rmdpanel990 retrieves IRS 990 efile tables from the NCCS S3 bucket, merges the tables that describe a filing, and stacks years into one panel. This tutorial specifies everything with direct argument values – no sample frame yet.
The download chunks below are shown but not executed in this vignette; they require network access to the NCCS bucket. Run them in a live session.
Table abbreviations
The efile data is split into ~125 tables, each corresponding to a section of the 990. panel990 ships short aliases for the most common ones. Preview them with table_catalog():
table_catalog()
#> table alias cardinality
#> 1 F9-P00-T00-HEADER P00 1x1
#> 2 F9-P01-T00-SUMMARY P01 1x1
#> 3 F9-P01-T00-SUMMARY-EZ <NA> 1x1
#> 4 F9-P02-T00-SIGNATURE <NA> 1x1
#> 5 F9-P03-T00-MISSION <NA> 1x1
#> 6 F9-P03-T00-PROGRAM-ONE <NA> 1x1
#> 7 F9-P03-T00-PROGRAM-THREE <NA> 1x1
#> 8 F9-P03-T00-PROGRAM-TWO <NA> 1x1
#> 9 F9-P03-T00-PROGRAMS <NA> 1x1
#> 10 F9-P03-T01-PROGRAMS-OTHER <NA> 1xm
#> 11 F9-P03-T02-PROGRAMS-EZ <NA> 1xm
#> 12 F9-P04-T00-REQUIRED-SCHEDULES <NA> 1x1
#> 13 F9-P04-T00-REQUIRED-SCHEDULES-EZ <NA> 1x1
#> 14 F9-P05-T00-OTHER-IRS-FILING <NA> 1x1
#> 15 F9-P06-T00-GOVERNANCE <NA> 1x1
#> 16 F9-P06-T00-GOVERNANCE-EZ <NA> 1x1
#> 17 F9-P07-T00-DIR-TRUST-KEY <NA> 1x1
#> 18 F9-P07-T01-COMPENSATION <NA> 1xm
#> 19 F9-P07-T01-COMPENSATION-HCE-EZ <NA> 1xm
#> 20 F9-P07-T02-CONTRACTORS <NA> 1xm
#> 21 F9-P08-T00-REVENUE P08 1x1
#> 22 F9-P08-T01-REVENUE-PROGRAMS <NA> 1xm
#> 23 F9-P08-T02-REVENUE-MISC <NA> 1xm
#> 24 F9-P09-T00-EXPENSES P09 1x1
#> 25 F9-P09-T01-EXPENSES-OTHER <NA> 1xm
#> 26 F9-P10-T00-BALANCE-SHEET P10 1x1
#> 27 F9-P11-T00-ASSETS P11 1x1
#> 28 F9-P12-T00-FINANCIAL-REPORTING P12 1x1
#> 29 SA-P00-T00-HEADER <NA> 1x1
#> 30 SA-P01-T00-PUBLIC-CHARITY-STATUS A01 1x1
#> 31 SA-P01-T01-PUBLIC-CHARITY-STATUS <NA> 1xm
#> 32 SA-P02-T00-SUPPORT_SCHEDULE_170 <NA> 1x1
#> 33 SA-P03-T00-SUPPORT_SCHEDULE_509 <NA> 1x1
#> 34 SA-P04-T00-SUPPORT-ORGS <NA> 1x1
#> 35 SA-P05-T00-SUPPORT-ORGS <NA> 1x1
#> 36 SA-P06-T99-SUPPLEMENTAL-INFO <NA> supplemental
#> 37 SB-P01-T01-CONTRIBUTORS <NA> 1xm
#> 38 SC-P01-T00-LOBBY <NA> 1x1
#> 39 SC-P01-T01-POLITICAL-ORGS-INFO <NA> 1xm
#> 40 SC-P02-T00-LOBBY <NA> 1x1
#> 41 SC-P03-T00-LOBBY <NA> 1x1
#> 42 SC-P04-T99-SUPPLEMENTAL-INFO <NA> supplemental
#> 43 SD-P01-T00-ORGS-DONOR-ADVISED-FUNDS-OTH <NA> 1x1
#> 44 SD-P02-T00-CONSERV-EASEMENTS <NA> 1x1
#> 45 SD-P03-T00-ORGS-COLLECT-ART-HIST-TREASURE-OTH <NA> 1x1
#> 46 SD-P04-T00-ESCROW-CUSTODIAL-ARRANGEMENTS <NA> 1x1
#> 47 SD-P05-T00-ENDOWMENT <NA> 1x1
#> 48 SD-P06-T00-LAND-BLDG-EQUIP <NA> 1x1
#> 49 SD-P07-T00-INVESTMENTS-SECURITIES <NA> 1x1
#> 50 SD-P07-T01-INVESTMENTS-OTH-DERIVATIVES <NA> 1xm
#> 51 SD-P07-T01-INVESTMENTS-OTH-EQUITY <NA> 1xm
#> 52 SD-P07-T01-INVESTMENTS-OTH-SECURITIES <NA> 1xm
#> 53 SD-P08-T00-INVESTMENTS-PROG-RLTD <NA> 1x1
#> 54 SD-P08-T01-INVESTMENTS-PROG-RLTD <NA> 1xm
#> 55 SD-P09-T00-OTH-ASSETS <NA> 1x1
#> 56 SD-P09-T01-OTH-ASSETS <NA> 1xm
#> 57 SD-P10-T00-OTH-LIABILITIES <NA> 1x1
#> 58 SD-P10-T01-OTH-LIABILITIES <NA> 1xm
#> 59 SD-P11-T00-RECONCILIATION-REVENUE <NA> 1x1
#> 60 SD-P12-T00-RECONCILIATION-EXPENSES <NA> 1x1
#> 61 SD-P13-T99-SUPPLEMENTAL-INFO <NA> supplemental
#> 62 SD-P99-T00-RECONCILIATION-NETASSETS <NA> 1x1
#> 63 SE-P01-T00-SCHOOLS <NA> 1x1
#> 64 SE-P02-T99-SUPPLEMENTAL-INFO <NA> supplemental
#> 65 SF-P01-T00-FRGN-ACTS <NA> 1x1
#> 66 SF-P01-T01-FRGN-ACTS-BY-REGION <NA> 1xm
#> 67 SF-P02-T00-FRGN-ORG-GRANTS <NA> 1x1
#> 68 SF-P02-T01-FRGN-ORG-GRANTS <NA> 1xm
#> 69 SF-P03-T01-FRGN-INDIV-GRANTS <NA> 1xm
#> 70 SF-P04-T00-FRGN-INTERESTS <NA> 1x1
#> 71 SF-P05-T99-EXPLANATION-TEXT <NA> supplemental
#> 72 SF-P99-T00-FRGN-ORG-GRANTS <NA> 1x1
#> 73 SG-P01-T00-FUNDRAISING-ACTS <NA> 1x1
#> 74 SG-P01-T01-FUNDRAISERS-INFO <NA> 1xm
#> 75 SG-P02-T00-FUNDRAISING-EVENTS <NA> 1x1
#> 76 SG-P02-T01-FUNDRAISING-EVENTS <NA> 1xm
#> 77 SG-P03-T00-GAMING <NA> 1x1
#> 78 SG-P04-T99-SUPPLEMENTAL-INFO <NA> supplemental
#> 79 SH-P01-T00-FAP-COMMUNITY-BENEFIT-POLICY <NA> 1x1
#> 80 SH-P02-T00-FAP-COMMUNITY-BENEFIT-POLICY <NA> 1x1
#> 81 SH-P03-T00-FAP-COMMUNITY-BENEFIT-POLICY <NA> 1x1
#> 82 SH-P04-T01-COMPANY-JOINT-VENTURES <NA> 1xm
#> 83 SH-P05-T00-FAP-COMMUNITY-BENEFIT-POLICY <NA> 1x1
#> 84 SH-P05-T01-HOSPITAL-FACILITY <NA> 1xm
#> 85 SH-P05-T02-NON-HOSPITAL-FACILITY <NA> 1xm
#> 86 SH-P05-T99-SUPPLEMENTAL-INFO <NA> supplemental
#> 87 SH-P06-T99-SUPPLEMENTAL-INFO <NA> supplemental
#> 88 SH-P99-T00-FAP-COMMUNITY-BENEFIT-POLICY <NA> 1x1
#> 89 SI-P01-T00-GRANTS-INFO <NA> 1x1
#> 90 SI-P02-T00-GRANTS-US-ORGS-GOVTS <NA> 1x1
#> 91 SI-P02-T01-GRANTS-US-ORGS-GOVTS <NA> 1xm
#> 92 SI-P03-T01-GRANTS-US-INDIV <NA> 1xm
#> 93 SI-P04-T99-SUPPLEMENTAL-INFO <NA> supplemental
#> 94 SI-P99-T00-GRANTS-US-ORGS-GOVTS <NA> 1x1
#> 95 SJ-P01-T00-COMPENSATION <NA> 1x1
#> 96 SJ-P02-T01-COMPENSATION-DTK <NA> 1xm
#> 97 SJ-P03-T99-SUPPLEMENTAL-INFO <NA> supplemental
#> 98 SK-P01-T01-BOND-ISSUES <NA> 1xm
#> 99 SK-P02-T01-BOND-PROCEEDS <NA> 1xm
#> 100 SK-P03-T01-BOND-PRIVATE-BIZ-USE <NA> 1xm
#> 101 SK-P04-T01-BOND-ARBITRAGE <NA> 1xm
#> 102 SK-P05-T01-PROCEDURE-CORRECTIVE-ACT <NA> 1xm
#> 103 SK-P06-T99-SUPPLEMENTAL-INFO <NA> supplemental
#> 104 SL-P01-T00-EXCESS-BENEFIT-TRANSAC <NA> 1x1
#> 105 SL-P01-T01-EXCESS-BENEFIT-TRANSAC <NA> 1xm
#> 106 SL-P02-T00-LOANS-INTERESTED-PERS <NA> 1x1
#> 107 SL-P02-T01-LOANS-INTERESTED-PERS <NA> 1xm
#> 108 SL-P03-T01-GRANTS-INTERESTED-PERS <NA> 1xm
#> 109 SL-P04-T01-BIZ-TRANSAC-INTERESTED-PERS <NA> 1xm
#> 110 SL-P05-T99-SUPPLEMENTAL-INFO <NA> supplemental
#> 111 SM-P01-T00-NONCASH-CONTRIBUTIONS <NA> 1x1
#> 112 SM-P01-T01-NONCASH-CONTRIBUTIONS <NA> 1xm
#> 113 SM-P02-T99-SUPPLEMENTAL-INFO <NA> supplemental
#> 114 SN-P01-T00-LIQUIDATION-TERMINATION-DISSOLUTION <NA> 1x1
#> 115 SN-P01-T01-LIQUIDATION-TERMINATION-DISSOLUTION <NA> 1xm
#> 116 SN-P02-T00-DISPOSITION-OF-ASSETS <NA> 1x1
#> 117 SN-P02-T01-DISPOSITION-OF-ASSETS <NA> 1xm
#> 118 SN-P03-T99-SUPPLEMENTAL-INFO <NA> supplemental
#> 119 SN-P99-T00-LIQUIDATION-TERMINATION-DISSOLUTION <NA> 1x1
#> 120 SO-T99-SUPPLEMENTAL-INFO <NA> supplemental
#> 121 SR-P01-T01-ID-DISREGARDED-ENTITIES <NA> 1xm
#> 122 SR-P02-T01-ID-RLTD-TAX-EXEMPED-ORGS <NA> 1xm
#> 123 SR-P03-T01-ID-RLTD-ORGS-TAXABLE-PARTNERSHIP <NA> 1xm
#> 124 SR-P04-T01-ID-RLTD-ORGS-TAXABLE-CORPORATION <NA> 1xm
#> 125 SR-P05-T00-TRANSACTIONS-RLTD-ORGS <NA> 1x1
#> 126 SR-P05-T01-TRANSACTIONS-RLTD-ORGS <NA> 1xm
#> 127 SR-P06-T01-UNRLTD-ORGS-TAXABLE-PARTNERSHIP <NA> 1xm
#> 128 SR-P07-T99-SUPPLEMENTAL-INFO <NA> supplemental-
alias – the short token you pass to
tables=(e.g."P08"). - table – the canonical file name it resolves to.
-
cardinality –
1x1is one row per filing (safe to merge side-by-side);1xmis one-to-many (kept separate unless you opt in).
You are not limited to the aliases: any literal table name (for a schedule or a newly published table) is accepted as-is. resolve_tables() shows how a request resolves:
resolve_tables(c("P00", "P08", "SB-P01-T00-CONTRIBUTORS"))
#> request table
#> F9-P00-T00-HEADER P00 F9-P00-T00-HEADER
#> F9-P08-T00-REVENUE P08 F9-P08-T00-REVENUE
#> SB-P01-T00-CONTRIBUTORS SB-P01-T00-CONTRIBUTORS SB-P01-T00-CONTRIBUTORS
#> is_alias cardinality known
#> F9-P00-T00-HEADER TRUE 1x1 TRUE
#> F9-P08-T00-REVENUE TRUE 1x1 TRUE
#> SB-P01-T00-CONTRIBUTORS FALSE 1x1 FALSEThe data source
data_source() configures where files come from. The default points at the NCCS bucket, so you rarely change it; pass a local directory to read from disk.
src <- data_source()
src$root
#> [1] "https://nccs-efile.s3.us-east-1.amazonaws.com/public/efile_v2_1/"Downloading two tables for two years
download_tables() fetches (or reuses) the CSVs. Give it the years and tables directly:
dl <- download_tables(
years = 2019:2020,
tables = c("P00", "P08"), # header + revenue
cache = "retain", # keep files in `path`; use "temporary" to discard
path = "efdata"
)
dl$manifest # one row per table-year: status, bytes, pathEvery table-year is recorded in a manifest, so you can see exactly what was downloaded, reused, or failed.
Read, merge, and stack
Reading turns the CSVs into data frames; merging joins the tables within a year on the filing keys; stacking binds the years together.
rd <- read_tables(dl) # optionally columns=, filters=
mg <- merge_tables(rd) # join P00 + P08 per year by filing keysmerge_tables() joins the 1x1 tables side-by-side (header fields next to revenue fields, one row per filing) and reports each join in a manifest. The years are then stacked into a single data frame.
The one-shot form
panelize() runs download -> read -> merge -> stack in a single call and returns a panel object. The filing keys (EIN2 / TAX_YEAR / OBJECTID) are assigned automatically:
p <- panelize(tables = c("P00", "P08"), years = 2019:2020)
p
#> <panel> ... rows x ... cols years 2019-2020
#> sample frame: panel[P00,P08 | 2019-2020] (0 rules, ... log entries)
#> panel labels: stale / not computed
df <- as.data.frame(p)Two tables, two years: the package downloads four files, merges the two tables in each year, and stacks 2019 on top of 2020.
Adding BMF fields, then filtering by state
Geography, NTEE, and organization type are not on the 990 itself – they live in the Business Master File (BMF). To filter by state you first join the BMF, then subset. bmf_merge() downloads and attaches the native BMF fields by EIN2:
df <- bmf_merge(df) # appends geo_state_abbr, subsection_code, ntee_*, ...
bmf_vars() # the fields it adds by defaultNow the state column exists, so a plain base-R subset works:
ga <- df[df$geo_state_abbr == "GA", ]That is the whole manual pipeline: pick tables and years, download, merge, stack, attach the BMF, and filter. The next tutorial shows how a sample frame captures these same choices as a reusable, self-documenting object.