National Inventory of Dams Columns That Will Not Sum: Numbers Stored as Text

Problem statement

Read the National Inventory of Dams GeoPackage and ask for the tallest dam, and pandas answers with a dam 99 feet high, while dams of several hundred feet sit lower in the list. Sum the storage and you get an error, or a string thousands of digits long. Height, length, storage, area, discharge and even the completion year are stored in the GeoPackage as text, so sorting compares them character by character and arithmetic either fails or concatenates. This page shows the symptoms and converts the columns, checking that nothing is lost in the conversion.

Quick answer

Convert every measurement column with pd.to_numeric(errors="coerce"), then check that no value that was present became NaN:

raw = gpd.read_file("data/nid.gpkg", columns=NUMERIC, ignore_geometry=True)
print(raw.dtypes.value_counts().to_string())
num = raw.apply(pd.to_numeric, errors="coerce")
lost = (raw.notna() & num.isna()).sum()
print(f"values that failed to convert: {lost.sum()}; columns now numeric: {num.dtypes.map(pd.api.types.is_numeric_dtype).sum()} of {len(NUMERIC)}")

str    13
values that failed to convert: 0; columns now numeric: 13 of 13

All 13 measurement columns come back as str. Converted, none of the values that were present is lost, so the text holds clean numbers that were simply written to the GeoPackage as strings. If lost ever shows a value, look at it before coercing: it would be a unit or a note inside a number.

The symptoms

1. Sorting as text

print(raw.nidHeight.sort_values(ascending=False).head(4).tolist())
print(num.nidHeight.sort_values(ascending=False).head(4).tolist())

['99', '99', '99', '99']
[789, 780, 770, 760]

Sorted as text, the tallest dams are all 99 feet, because "99" comes after "789" character by character. Sorted as numbers, the tallest is 789 feet.

2. Summing as text

try:
    total = raw.nidStorage.head(3).sum()
    print(type(total).__name__, repr(total)[:40])
except TypeError as err:
    print("TypeError", err)
print(f"as numbers: {num.nidStorage.head(3).sum():,.0f}")

str '505086.8'
as numbers: 187

On text, sum doesn't fail. It joins the strings, so three dams of 50, 50 and 86.8 acre-feet "add up" to '505086.8'. A total computed this way can look like a plausible large number. As numbers, the three hold 187 acre-feet.

3. Comparing as text

print(f"rows with 'nidHeight > \"50\"' as text: {(raw.nidHeight > '50').sum():,}; as numbers > 50: {(num.nidHeight > 50).sum():,}")

rows with 'nidHeight > "50"' as text: 8,815; as numbers > 50: 6,317

A text comparison with "50" keeps every height that sorts after "50", including 6, 7, 8 and 9 feet, so 8,815 rows pass a filter that 6,317 should. A filter like this raises no error and returns a plausible table.

Zeros and blanks after converting

The data used on this page

The US Army Corps of Engineers' National Inventory of Dams (NID), the whole inventory as a GeoPackage from its API, downloaded on 19 October 2026 and saved on this site, because the inventory is updated as states and agencies send revisions. Heights are in feet, storage in acre-feet and areas in acres, as the NID gives them.

from pathlib import Path
import zipfile

import geopandas as gpd
import numpy as np
import pandas as pd
import requests

Path("data").mkdir(exist_ok=True)
# US Army Corps of Engineers, National Inventory of Dams: the whole inventory as a GeoPackage, and the Census
# Bureau's 2024 cartographic boundary counties. Saved 19 October 2026, because the inventory is updated continuously.
# Live: https://nid.sec.usace.army.mil/api/nation/gpkg
ARCHIVE = "usace-nid-2026-10-19.zip"
if not Path(f"data/{ARCHIVE}").exists():
    Path(f"data/{ARCHIVE}").write_bytes(requests.get(f"https://data.spatialworkflow.io/{ARCHIVE}", timeout=900).content)
Z = zipfile.ZipFile(f"data/{ARCHIVE}")
if not Path("data/nid.gpkg").exists():
    Z.extract("nid.gpkg", "data")
NUMERIC = ["damHeight", "hydraulicHeight", "structuralHeight", "nidHeight", "damLength", "volume", "yearCompleted",
           "nidStorage", "maxStorage", "normalStorage", "surfaceArea", "drainageArea", "maxDischarge"]
nid = gpd.read_file("data/nid.gpkg")
nid[NUMERIC] = nid[NUMERIC].apply(pd.to_numeric, errors="coerce")       # the GeoPackage stores them as text
print(f"National Inventory of Dams: {len(nid):,} rows, {nid.nidId.nunique():,} NID ids")

National Inventory of Dams: 92,626 rows, 91,840 NID ids

Example 1: zero where nothing was measured

zeros = (nid[["nidHeight", "nidStorage", "damLength", "yearCompleted", "surfaceArea"]] == 0).sum()
print(zeros.to_string())

nidHeight         477
nidStorage       1265
damLength         705
yearCompleted       2
surfaceArea       995

After conversion some values are 0: 477 heights, 1,265 storage figures and 995 surface areas. A dam with no height is a missing value written as 0, not a zero-height dam. Replace zeros with NaN before averages and minimums, as the age page does with yearCompleted.

Example 2: the tallest dams, properly sorted

print(nid.nlargest(5, "nidHeight")[["name", "state", "nidHeight", "yearCompleted"]].to_string(index=False))

                            name         state  nidHeight  yearCompleted
                   Thompson Mtis         Idaho        789         1982.0
Chess Refuse Disposal Area No. 1 West Virginia        780            NaN
                    Oroville Dam    California        770         1968.0
    Delbarton Slurry Impoundment West Virginia        760         2004.0
                      Hoover Dam        Nevada        730         1935.0

With numbers, the tallest structures in the inventory are not the famous dams. Two of the top four are coal-mine waste impoundments in West Virginia, then Oroville Dam at 770 feet and Hoover Dam at 730. The NID's height is the structure's, so tall tailings and refuse dams rank with the great concrete dams.

Internal links

• Reading the inventory — the setup that converts on load
• Ranking reservoirs — a ranking that text sorting breaks
• Round completion years — the year column once it is a number
• Hazard potential explained — the text columns that are text by design