100. Data download

Downloads the BCBS Texas machine-readable files and extracts a Texas-only, menu-only table of negotiated prices into 01_raw/_extracted/. Raw compressed files stream through a local scratch folder and are deleted — never stored on Google Drive.

Contents

How the download works (overview with examples)

The BCBS Texas machine-readable files are compressed JSON, tens to hundreds of gigabytes when uncompressed. The pipeline never opens a whole file; it STREAMS each one (reads a little at a time), keeps only our menu procedures and Texas providers, and writes small CSVs. Three Python stages run automatically (Stata calls them in Step 1):

  1. Discover (01_discover.py) — read the published index, list the Texas-network files, and de-duplicate their web addresses.
  2. Extract (02_extract.py) — stream each unique file; keep target codes and Texas providers; collapse to one row per distinct price (see the PDF guide, section "unit of analysis").
  3. Orchestrate (run_all.py) — loop discover then extract over both coverage segments and combine the results.

Why de-duplication matters: for two sample employers the index listed 255 in-network file references, but those pointed to only a handful of distinct national files. After Texas scoping the whole state is about 35 unique files (~12 GB compressed).

One entry in the published index looks like this (a single network file; the link is time-limited):

{"description": "Blue Choice PPO in-network file 0001 of 30", "location": "https://bcbstx.mrf.bcbs.com/2026-06_..._in-network-rates_0001_of_30.json.gz?Expires=..."}

After extraction, one row of the resulting prices.csv looks like this (one distinct price, with provider counts):

network_name=Blue Choice PPO | region=2/3 | procedure_code=99214 | provider_type=professional | rate_basis=negotiated | negotiated_amount=103.14 | n_provider_groups=49 | n_tax_ids=49

Step 0 — Texas provider crosswalk (quarterly)

Texas providers are defined as any NPI whose NPPES practice-location state is Texas. The crosswalk is built once per quarter from the NPPES monthly file; build it before the first download if missing. Click to see whether it is present and, if not, the exact build commands.

. capture confirm file "${xwalk}"

. if _rc {
.     di as err "Texas NPI crosswalk NOT found: ${xwalk}"
.     di as txt "Build it (downloads ~1.1GB NPPES monthly file to scratch, then scans):"
.     di as txt `"  cd "${pipeline}/scripts""'
.     di as txt `"  CSV=\$(${PY} 00a_fetch_nppes.py --dest "${scratch}/nppes")"'
.     di as txt `"  ${PY} 00_build_tx_npi.py --nppes "\$CSV" --out "${xwalk}" --define practice"'
. }

. else di as txt "Texas NPI crosswalk present: ${xwalk}"
Texas NPI crosswalk present: /Users/ericbooth/Library/CloudStorage/GoogleDrive-eric.booth@texas2036.org/Shared drives/Da
> ta and Research Team/_datashare/Insurance_PriceTransparency/BCBSTX/01_raw/npi_tx_xwalk.csv

Step 1 — discover, download, extract (medical + Rx)

One command per file type discovers the current indexes, keeps only Texas networks, de-duplicates the shared national files, and extracts collapsed prices + a slim provider directory. GUARDED so a website rebuild does not re-pull.

Verify the extract

After the pipeline runs (or is skipped because the output already exists), this checks that each expected CSV landed in 01_raw/_extracted/: the inventory of files seen, the collapsed medical prices, the slim per-network provider directory, and the prescription rx_prices. Click to see the check and the file inventory.

.     di as txt "Expected extract files:"
Expected extract files:

.     foreach f in inventory prices providers provider_org_directory rx_prices {
  2.         capture confirm file "${extracted}/`f'.csv"
  3.         if _rc  di as err  "  MISSING: `f'.csv"
  4.         else    di as txt  "  OK:      `f'.csv"
  5.     }
  OK:      inventory.csv
  OK:      prices.csv
  MISSING: providers.csv
  OK:      provider_org_directory.csv
  OK:      rx_prices.csv

.     di as txt _n "Files now in 01_raw/_extracted/ (names only):"

Files now in 01_raw/_extracted/ (names only):

.     local _fl : dir "${extracted}" files "*.csv"

.     foreach _f of local _fl {
  2.         di as txt "  `_f'"
  3.     }
  rx_prices.csv
  prices.csv
  provider_org_directory.csv
  inventory.csv

Open questions / notes