Extracting One-to-Many Tables

Overview

A one-to-many (1:M) table has many rows per filing: a grant list, a roster of officers and their compensation, a schedule of foreign activities. The same set of fields repeats once per entry. Reassembling those entries correctly is the whole job — and it hinges on the TABLE_ID column produced during flattening.

Our example filing’s Schedule I lists 83 grants, and its Part VII lists 15 officers and directors — two clean one-to-many tables.

library(ef2)
library(dplyr)
library(tidyr)

# Start from the flattened table shipped with the package
# (produced in "Flattening IRS 990 e-file XML").
flat <- readRDS(system.file("extdata", "sample_flat.rds", package = "ef2"))

The repeating group

We’ll extract Schedule I, Part II — grants to U.S. organizations, SI-P02-T01-GRANTS-US-ORGS-GOVTS:

grants_long <- flat |>
  filter(TYPE == "terminal", RDB_TABLE == "SI-P02-T01-GRANTS-US-ORGS-GOVTS")

n_distinct(grants_long$TABLE_ID)   # number of grants in this filing
#> [1] 83

grants_long |>
  select(TABLE_ID, VARIABLE_NAME, VALUE) |>
  head(10)
#>     TABLE_ID                  VARIABLE_NAME                  VALUE
#> 1  TID-00001     SI_02_GRANT_US_ORG_NAME_L1        100WOMEN STRONG
#> 2  TID-00001     SI_02_GRANT_US_ORG_ADDR_L1 714 EAST MARKET STREET
#> 3  TID-00001   SI_02_GRANT_US_ORG_ADDR_CITY               LEESBURG
#> 4  TID-00001  SI_02_GRANT_US_ORG_ADDR_STATE                     VA
#> 5  TID-00001    SI_02_GRANT_US_ORG_ADDR_ZIP                  20178
#> 6  TID-00001         SI_02_GRANT_US_ORG_EIN              541950727
#> 7  TID-00001 SI_02_GRANT_US_ORG_IRC_SECTION        INTERFUND GRANT
#> 8  TID-00001    SI_02_GRANT_US_ORG_AMT_CASH                  40250
#> 9  TID-00001  SI_02_GRANT_US_ORG_AMT_NONCSH                      0
#> 10 TID-00001     SI_02_GRANT_US_ORG_PURPOSE         HUMAN SERVICES

Look at the TABLE_ID column: the first grant’s fields are all TID-00001, the second grant’s are all TID-00002, and so on. That is the entire trick of 1:M extraction — the positional index [N] from the XPath, captured as TABLE_ID, tells us which fields belong to the same entry.

Pivot — keyed by filing and `TABLE_ID`

The 1:1 pivot keyed only on OBJECTID. For 1:M we key on OBJECTID and TABLE_ID, so each entry becomes its own row:

grants_wide <- grants_long |>
  select(OBJECTID, TABLE_ID, VARIABLE_NAME, VALUE) |>
  pivot_wider(names_from = VARIABLE_NAME, values_from = VALUE, values_fill = "")

dim(grants_wide)   # one row per grant
#> [1] 83 12

grants_wide |>
  select(TABLE_ID, SI_02_GRANT_US_ORG_EIN, SI_02_GRANT_US_ORG_AMT_CASH) |>
  head(5)
#> # A tibble: 5 × 3
#>   TABLE_ID  SI_02_GRANT_US_ORG_EIN SI_02_GRANT_US_ORG_AMT_CASH
#>   <chr>     <chr>                  <chr>                      
#> 1 TID-00001 541950727              40250                      
#> 2 TID-00002 811191778              37729                      
#> 3 TID-00003 453081114              77417                      
#> 4 TID-00004 271118675              63008                      
#> 5 TID-00005 541515133              350000

This mirrors build_rdb_table(), which does the same pivot against the DuckDB FLATXML table and carries TABLE_ID through so the rows stay separated.

Why TABLE_ID is essential: drop it from the key and every grant’s values pile into a single cell. pivot_wider() can’t store thousands of EINs in one position, so it produces list-columns instead of a clean table:

grants_long |>
  select(OBJECTID, VARIABLE_NAME, VALUE) |>
  pivot_wider(names_from = VARIABLE_NAME, values_from = VALUE) |>
  summarise(across(everything(), ~ class(.x)[1]))
#> Warning: Values from `VALUE` are not uniquely identified; output will contain list-cols.
#> • Use `values_fn = list` to suppress this warning.
#> • Use `values_fn = {summary_fun}` to summarise duplicates.
#> • Use the following dplyr code to identify duplicates.
#>   {data} |>
#>   dplyr::summarise(n = dplyr::n(), .by = c(OBJECTID, VARIABLE_NAME)) |>
#>   dplyr::filter(n > 1L)
#> # A tibble: 1 × 11
#>   OBJECTID  SI_02_GRANT_US_ORG_NAME_L1 SI_02_GRANT_US_ORG_ADDR_L1 SI_02_GRANT_US_ORG_ADDR_CITY
#>   <chr>     <chr>                      <chr>                      <chr>                       
#> 1 character list                       list                       list                        
#> # ℹ 7 more variables: SI_02_GRANT_US_ORG_ADDR_STATE <chr>, SI_02_GRANT_US_ORG_ADDR_ZIP <chr>,
#> #   SI_02_GRANT_US_ORG_EIN <chr>, SI_02_GRANT_US_ORG_IRC_SECTION <chr>,
#> #   SI_02_GRANT_US_ORG_AMT_CASH <chr>, SI_02_GRANT_US_ORG_AMT_NONCSH <chr>,
#> #   SI_02_GRANT_US_ORG_PURPOSE <chr>

Each column comes back as a list — the tell-tale sign that a repeating group was pivoted without its instance key.

Header variants across schema versions

The full pipeline doesn’t match a single XPath. Because the IRS renamed nodes over the years, get_table_headers() stores several candidate header paths per table, and build_rdb_table() matches any of them:

get_table_headers()[["SI-P02-T01-GRANTS-US-ORGS-GOVTS"]]
#> [1] "//Form990ScheduleIPartII/RecipientTable" "//IRS990ScheduleI/RecipientTable"

Whichever variant a given filing used, its rows land in the same logical table.

Edge case: ragged entries

Entries in a 1:M group are not required to carry the same fields — one grant may list a street address, another only a city, a third may add a non-cash valuation. Pivoting yields a ragged table, and values_fill = "" backfills the gaps so every row has the same columns:

# Fill rate of every grant field across all entries: 1 = always present.
grants_wide |>
  summarise(across(-c(OBJECTID, TABLE_ID), ~ round(mean(.x != ""), 2))) |>
  glimpse()
#> Rows: 1
#> Columns: 10
#> $ SI_02_GRANT_US_ORG_NAME_L1     <dbl> 1
#> $ SI_02_GRANT_US_ORG_ADDR_L1     <dbl> 1
#> $ SI_02_GRANT_US_ORG_ADDR_CITY   <dbl> 1
#> $ SI_02_GRANT_US_ORG_ADDR_STATE  <dbl> 1
#> $ SI_02_GRANT_US_ORG_ADDR_ZIP    <dbl> 1
#> $ SI_02_GRANT_US_ORG_EIN         <dbl> 0.99
#> $ SI_02_GRANT_US_ORG_IRC_SECTION <dbl> 1
#> $ SI_02_GRANT_US_ORG_AMT_CASH    <dbl> 1
#> $ SI_02_GRANT_US_ORG_AMT_NONCSH  <dbl> 1
#> $ SI_02_GRANT_US_ORG_PURPOSE     <dbl> 1

What you learned