USGS National Produced Waters Geochemical Database

Version notes: v3.0 introduced the FORMSIMPLE (standardized formation names) and PLAYTYPE (geological play classification) columns, added 23 new datasets focused on high-lithium brines and Appalachian Basin data, converted all ppm values to mg/L, and improved location data. The IDUSGS identifier is persistent across v2.3 and v3.0.

API Structure

There is no REST query API — the database is a bulk download from ScienceBase. The workflow is: discover files via ScienceBase API, download, filter locally.

ScienceBase File Discovery

# List files for v3.0 (Excel + shapefile)
curl -s "https://www.sciencebase.gov/catalog/item/64fa1e71d34ed30c2054ea11?format=json&fields=files,title" \
  | jq '.files[] | {name, size, url: .downloadUri}'

v3.0 files:

# List files for v2.3 (CSV available)
curl -s "https://www.sciencebase.gov/catalog/item/59d25d63e4b05fe04cc235f9?format=json&fields=files" \
  | jq '.files[] | {name, size, url: .downloadUri}'

v2.3 files:

Download the CSV (v2.3)

# Download v2.3 compiled CSV (~66 MB)
curl -L -o /tmp/USGSPWDBv2.3c.csv \
  "https://www.sciencebase.gov/catalog/file/get/59d25d63e4b05fe04cc235f9?f=__disk__11%2F1e%2F8e%2F111e8e3952b1637289254fd398af95a81f937389"

Download the Excel file (v3.0)

# Download v3.0 Excel workbook (~80 MB)
curl -L -o /tmp/USGS_NPWGDv3_excel.xlsx \
  "https://www.sciencebase.gov/catalog/file/get/64fa1e71d34ed30c2054ea11?f=__disk__e7%2Fef%2F17%2Fe7ef17fcb71c49e2241da4139ed775f8e328bdab"

Search ScienceBase for Related Datasets

# Search for other produced water datasets
curl -s "https://www.sciencebase.gov/catalog/items?q=produced+water+geochemical&format=json&max=10" \
  | jq '.items[] | {id, title}'

Key Columns

Full schema is in references/schema.md. The most important columns for lithium/magnesium research are:

Identification and Location

• IDUSGS — unique sample ID (persistent across versions)
• STATE, COUNTY, BASIN — geographic location
• LATITUDE, LONGITUDE — coordinates (some approximate; check COORDAPX)
• FIPCODE — 5-digit FIPS code for state+county

Geology

• FORMSIMPLE — standardized formation name (v3.0; use for filtering)
• FORMATION — formation name as reported (partially standardized)
• PLAYTYPE — play classification: Shale, Coal, Sedimentary, Geothermal, Injection (v3.0)
• BASIN — geologic basin (cleaned in v3.0)
• ERA, PERIOD, EPOCH — geologic age
• DEPTHUPPER, DEPTHLOWER — perforation interval (ft)

Chemistry (all in mg/L)

• Li — Lithium (key target for DLE)
• Mg — Magnesium (co-recovery target)
• TDS — Total dissolved solids (best available: measured, reported-calculated, or ion-sum)
• Na, Ca, K, Cl, SO4, HCO3 — major ions
• Ba, Sr, Br, Fe (FeTot), Mn, Si, B — minor/trace
• d7Li — lithium isotope ratio (per mil)

Physical Properties

• PH — pH
• TEMP — temperature (deg F)
• SG — specific gravity (reported or calculated)
• PRESSURE — formation pressure (psi)

Key Formations

Detailed formation data is in references/formations.md. Summary of highest-priority formations for Li/Mg research:

Economic threshold for DLE: approximately 100-150 mg/L Li depending on technology and brine volume. Emerging technologies may lower this to ~50 mg/L.

FORMSIMPLE values for filtering: Smackover, Marcellus, Utica, Point Pleasant, Bakken, Wolfcamp, Eagle Ford, Oriskany, Clinton, Devonian Shale, Tuscaloosa.

Workflow

Step 1 — Resolve Intent

Map the user's question to a filtering strategy:

Step 2 — Discover and Download Data

1. Check if a local copy exists (cached from prior use):

   ls -la /tmp/USGSPWDBv2.3c.csv 2>/dev/null || ls -la /tmp/USGS_NPWGDv3_excel.xlsx 2>/dev/null
   

2. If not cached, download from ScienceBase:

   # Prefer v3.0 — has FORMSIMPLE, PLAYTYPE, 23 new Li-brine datasets, standardized units
   # v3.0 is Excel-only; use openpyxl or convert to CSV after download
   curl -L -o /tmp/USGS_NPWGDv3_excel.xlsx \
     "https://www.sciencebase.gov/catalog/file/get/64fa1e71d34ed30c2054ea11?f=__disk__e7%2Fef%2F17%2Fe7ef17fcb71c49e2241da4139ed775f8e328bdab"
   # Fallback: v2.3 CSV if openpyxl unavailable
   # curl -L -o /tmp/USGSPWDBv2.3c.csv \
   #   "https://www.sciencebase.gov/catalog/file/get/59d25d63e4b05fe04cc235f9?f=__disk__11%2F1e%2F8e%2F111e8e3952b1637289254fd398af95a81f937389"
   

3. Warn the user about file size (~66 MB for v2.3 CSV, ~80 MB for v3.0 Excel).

Step 3 — Filter and Analyze

Use Python (or bash with awk/csvtool) to filter and compute statistics:

import csv, statistics

# Load CSV
with open("/tmp/USGSPWDBv2.3c.csv") as f:
    reader = csv.DictReader(f)
    rows = [r for r in reader if "marcellus" in r.get("FORMATION", "").lower()]

# Extract Li values
li_vals = [float(r["Li"]) for r in rows if r.get("Li") and r["Li"] not in ("", "-9999")]

# Statistics
print(f"Marcellus Li: n={len(li_vals)}, "
      f"median={statistics.median(li_vals):.1f}, "
      f"mean={statistics.mean(li_vals):.1f}, "
      f"max={max(li_vals):.1f} mg/L")

For common queries, use the ready-made client in references/python_example.py which provides filter_rows(), summary_stats(), and formatted output functions.

Common filtering patterns:

# Count samples by state (bash + awk on CSV)
awk -F',' 'NR==1{for(i=1;i<=NF;i++) if($i=="STATE") col=i}
           NR>1{count[$col]++}
           END{for(s in count) print count[s], s}' \
  /tmp/USGSPWDBv2.3c.csv | sort -rn | head -20

# Extract high-Li samples (Python one-liner)
python3 -c "
import csv
with open('/tmp/USGSPWDBv2.3c.csv') as f:
    r = csv.DictReader(f)
    hits = [row for row in r if row.get('Li') and float(row['Li'] or 0) > 100]
print(f'{len(hits)} samples with Li > 100 mg/L')
for h in sorted(hits, key=lambda x: float(x.get('Li',0)), reverse=True)[:10]:
    print(f\"  Li={h['Li']} mg/L  State={h.get('STATE','')}  Fm={h.get('FORMATION','')}\")
"

Step 4 — Produce Output

Format: Summary Statistics Table + Narrative

Present results as a markdown table (capped at ~20 rows for readability), followed by a narrative summary covering:

1. Dataset scope — how many samples matched the filter, from how many states/formations
2. Key statistics — median, mean, min, max, P25/P75 for the target analytes
3. Notable findings — highest-concentration samples, formation-level patterns
4. Economic context — comparison to DLE thresholds, co-production potential
5. Caveats — data quality notes, sampling bias, version differences

Output Format

Example 1: Formation Query

## Marcellus Shale — Produced Water Lithium Analysis

| Statistic | Li (mg/L) | Mg (mg/L) | TDS (mg/L) |
|-----------|-----------|-----------|------------|
| N         | 847       | 1,023     | 1,156      |
| Min       | 0.1       | 12        | 1,200      |
| P25       | 28        | 580       | 85,000     |
| Median    | 62        | 1,250     | 145,000    |
| Mean      | 75        | 1,380     | 155,000    |
| P75       | 105       | 2,100     | 210,000    |
| Max       | 235       | 3,200     | 345,000    |

**Summary:** The Marcellus Shale produced water database contains 1,156
samples across WV, PA, OH, and NY. Lithium concentrations (n=847) have a
median of 62 mg/L with 25% of samples exceeding 105 mg/L — above or near
the DLE economic threshold of 100-150 mg/L. The highest Li value (235 mg/L)
was reported from a well in WV. Mg averages 1,380 mg/L, making co-recovery
viable in high-TDS wells. Note: Li data coverage is incomplete (73% of
samples have Li measured). Sampling is biased toward PA and WV where
Marcellus production is highest.

Example 2: State Query

## West Virginia — Produced Water Chemistry by Formation

| Formation | N | Li Median (mg/L) | Li Max (mg/L) | TDS Median (mg/L) |
|-----------|---|-------------------|----------------|---------------------|
| Marcellus | 312 | 78 | 235 | 165,000 |
| Utica | 45 | 92 | 148 | 195,000 |
| Oriskany | 28 | 35 | 88 | 120,000 |
| Clinton | 19 | 22 | 41 | 95,000 |
| Devonian Shale | 67 | 18 | 52 | 68,000 |

**Summary:** West Virginia produced waters span multiple Appalachian Basin
formations. The Marcellus and Utica formations show the highest lithium
potential, with median Li of 78 and 92 mg/L respectively. The top Utica
samples approach DLE-economic concentrations (148 mg/L max). TDS correlates
with Li across all formations. Data availability is strongest for the
Marcellus (n=312) reflecting modern shale gas development.

Error Handling

Caveats

1. Sampling bias. The database is a compilation from published sources, not a statistically designed survey. Some formations and states are heavily overrepresented (Appalachian Basin, Texas) while others have few samples. Sample density does not reflect production volumes.

2. Temporal coverage. Samples span from the 1930s to 2020s. Older samples may use different analytical methods with lower precision. Comparison across eras requires caution.

3. Incomplete analyte coverage. Not all samples have all analytes measured. Lithium coverage is notably sparse in older datasets. Always report the N (number of non-null values) alongside statistics.

4. Approximate locations. Many samples have approximate coordinates (county centroid, field centroid, or state centroid). Check the COORDAPX column before using lat/lon for spatial analysis.

5. Units. All chemistry in v3.0 is mg/L (v3.0 converted all ppm using specific gravity). In v2.3, the UNITSORIG column indicates whether the original was mg/L or ppm.

6. TDS calculation. The TDS column is a "best available" value using measured TDS first, then reported-calculated TDS, then ion-sum-calculated TDS. Check TDSDESC for the method.

7. Formation name inconsistency. Use FORMSIMPLE (v3.0) for reliable formation-level grouping. The FORMATION column has many spelling variants and qualifiers that make grouping unreliable without normalization.

8. v2.3 vs v3.0 differences. v3.0 added 23 new datasets (especially high-Li brines), fixed location errors, and standardized formation names. For lithium research, v3.0 is strongly preferred. However, v3.0 is only available as Excel (not CSV), requiring openpyxl or similar to parse programmatically.

9. Self-reported industry data. Some source datasets are from industry reports. Quality control varies by source. The CHARGEBAL column (charge balance) can be used as a data quality indicator — values far from 0% suggest analytical errors.

10. Not a reserves estimate. Concentration data alone does not determine economic viability. Recovery depends on water volume, flow rates, brine chemistry complexity, infrastructure, and DLE technology efficiency.

Implementation Notes

• Prefer bash_tool or python in Claude's environment to download and analyze
• Prefer v3.0 Excel — it has FORMSIMPLE, PLAYTYPE, 23 new high-Li datasets, and standardized mg/L units
• Fall back to v2.3 CSV only if openpyxl is unavailable and you cannot install it
• Cache downloads to /tmp/ to avoid re-downloading the 66-80 MB file
• Python client — see references/python_example.py for ready-made filtering and statistics
• Full column schema — see references/schema.md for all 150+ column definitions
• Formation reference — see references/formations.md for Li/Mg ranges by formation
• STATE uses full names (e.g., "West Virginia", not "WV")
• Missing data is represented as empty string or -9999 — always exclude from calculations