🔧 Fix Census & demographics Data cleaning Data sources & downloads Census & Demographics · step 17 Notebook By · Updated

Fixing Census API Values Like −666666666 and Other Sentinels

On this page

Problem statement

The median household income column from the American Community Survey looks fine until you summarise it:

est = tracts["B19013_E001"]
print(f"min {est.min():,}  mean {est.mean():,.0f}  median {est.median():,.0f}  max {est.max():,}")
min -666,666,666  mean -11,997,152  median 74,071  max 250,001

A mean income of −11,997,152 dollars across US tracts. The column contains 1,547 values of −666666666, the Census Bureau's code for "the estimate could not be computed because there were an insufficient number of sample observations". The margin-of-error column carries its own codes, −222222222 and −333333333, and the population table carries −555555555 in the margins of 3,090 counties.

These annotation values are valid integers, so nothing in pandas, NumPy or a map library rejects them. The median barely moves — 74,071 against 74,875 once cleaned — which is exactly why they survive a quick look. Then a colour classification puts 83,834 of 85,381 tracts in one class.

Quick answer

Replace every annotation code with a missing value when the table is loaded, and keep the reason:

import pandas as pd

ANNOTATIONS = {
    -666666666: "estimate not computed: too few sample observations",
    -999999999: "estimate or MOE not displayed: too few sample cases",
    -888888888: "not applicable or not available",
    -555555555: "MOE not appropriate: estimate controlled",
    -333333333: "MOE not computed: median in an open-ended interval",
    -222222222: "MOE not computed: too few sample observations",
}

for col in ["B19013_E001", "B19013_M001"]:
    tracts[f"{col}_note"] = tracts[col].map(ANNOTATIONS)
    tracts[col] = tracts[col].mask(tracts[col].isin(list(ANNOTATIONS)))
print(f"mean {tracts['B19013_E001'].mean():,.0f}, min {tracts['B19013_E001'].min():,.0f}")
tracts = raw[raw.GEO_ID.str.startswith("1400000US")].set_index("GEO_ID")     # back to the raw table
mean 83,552, min 2,499

The mean of tract median incomes then becomes 83,552, and the minimum becomes 2,499.

The six ACS annotation codes, the symbol data.census.gov shows for each, and what each means. ACS annotation valuescodeshown asmeans-666666666-estimate not computed-999999999Ntoo few cases to display-888888888(X)not applicable-555555555*****controlled: no MOE-333333333***median at open end-222222222**MOE not computedAll are valid integers, so no library rejects them spatialworkflow.io
The meanings are from the Census Bureau's notes on ACS estimate and annotation values.

Know the codes, count them, replace them with reasons

1. Know the codes

The Census Bureau publishes the list in its notes on ACS estimate and annotation values. The same codes appear in API responses and in the bulk summary files; data.census.gov shows them as symbols instead:

code          shown as   meaning
-666666666    -          estimate could not be computed: insufficient sample observations
-999999999    N          estimate or MOE cannot be displayed: insufficient sample cases in the area
-888888888    (X)        estimate or MOE not applicable or not available
-555555555    *****      MOE not appropriate: estimate controlled to an independent estimate
-333333333    ***        MOE could not be computed: median in the lowest or highest open-ended interval
-222222222    **         MOE could not be computed: insufficient sample observations

By their definitions, −666666666 applies to estimates, −555555555, −333333333 and −222222222 to margins, and −999999999 and −888888888 to either.

2. Count them before doing anything else

A report per column shows what you are dealing with (Example 1). For tract median household income:

     column       code  rows   meaning
B19013_E001 -666666666  1547   estimate not computed: too few sample observations
B19013_M001 -333333333   492   MOE not computed: median in an open-ended interval
B19013_M001 -222222222  1547   MOE not computed: too few sample observations

Each −666666666 estimate is paired with a −222222222 margin. The 492 margins coded −333333333 are the tracts whose median sits at the edge of the income distribution: 483 estimates of 250001 and 9 of 2499.

3. Replace them with missing values

mask replaces values where a condition holds, leaving NaN. Do it once, at load time, for every estimate and margin column, before any arithmetic, sort, classification or join. Keep a note column if the reason matters downstream — a controlled estimate and a suppressed one call for different handling.

4. Handle top- and bottom-coded values separately

250001 and 2499 are not annotation codes. They mean "250,000 or more" and "2,500 or less": real estimates at the limits of the published distribution. Leave them in, but know that means and differences involving them are bounds rather than values. The −333333333 margins beside them say the same thing.

5. Check where the codes are concentrated

Across the whole income table — every summary level, 543,541 rows — there were 38,246 estimates of −666666666:

block groups                 17,906
places within counties        4,521
places                        3,750
county subdivisions           3,164
ZIP Code Tabulation Areas     3,154
tracts                        1,547

Small geographies lose estimates first. Among states, the share of tracts without an income estimate was highest in Hawaii (9.1%), Puerto Rico (6.5%), Michigan (4.9%), New York (4.0%) and New Mexico (2.9%).

6. Do not assume the affected areas are empty

Many suppressed tracts have nobody living in them — the median population of the 1,547 was 0, and 849 had no residents. But 452 had more than 1,000 residents, and the largest had 13,134. A missing estimate is a gap in the data about real people, which a map should show as "no estimate" rather than hide.

7. Rebuild classifications after cleaning

A single code dominates any classification that looks at the range:

equal interval, raw     counts [1547, 0, 0, 0, 83834]
equal interval, clean   counts [17052, 45438, 15506, 4175, 1663]

Natural breaks gave the codes a class of their own: 1,547 tracts below −666666666 as the first class, leaving four classes for every real income. Quantiles survived best, because the codes fall in the lowest class and nudge its upper break from 51,720 to 50,396 — which is why the error can reach a map without anyone noticing.

Equal-interval class counts for tract median household income with and without annotation codes. five equal-interval classes, 85,381 tractscodes left incodes removedclass 1✕1,54717,052class 2✕045,438class 3✕015,506class 4✕04,175class 5✕83,8341,663Mean −11,997,152 with the codes in; 83,552 without spatialworkflow.io
With the codes left in, the range is dominated by −666,666,666 and four of the five classes are empty or nearly so.

Reporting and cleaning the codes in ACS tables

The data used on this page

The 2023 ACS 5-year table-based summary files for B19013 (median household income) and B01003 (total population): one pipe-delimited file per table, every geography, with the annotation codes exactly as the API returns them. They need no key; the Census API itself now does.

import os
import urllib.request

import pandas as pd

BASE = "https://www2.census.gov/programs-surveys/acs/summary_file/2023/table-based-SF/data/5YRData/"
for table in ("b19013", "b01003"):
    if not os.path.exists(f"acsdt5y2023-{table}.dat"):
        urllib.request.urlretrieve(BASE + f"acsdt5y2023-{table}.dat", f"acsdt5y2023-{table}.dat")
raw = pd.read_csv("acsdt5y2023-b19013.dat", sep="|", dtype={"GEO_ID": str})
tracts = raw[raw.GEO_ID.str.startswith("1400000US")].set_index("GEO_ID")
pop = pd.read_csv("acsdt5y2023-b01003.dat", sep="|", dtype={"GEO_ID": str}).set_index("GEO_ID")
print(f"{len(raw):,} rows in B19013, {len(tracts):,} tracts")
543,541 rows in B19013, 85,381 tracts

Example 1 — a report of the codes in each column

def sentinel_report(frame, columns):
    """Count each annotation code in each column."""
    rows = []
    for col in columns:
        counts = frame[col].value_counts()
        for code, meaning in ANNOTATIONS.items():
            if code in counts.index:
                rows.append((col, code, int(counts[code]), meaning))
    report = pd.DataFrame(rows, columns=["column", "code", "rows", "meaning"])
    print(report.to_string(index=False))
    return report
raw = pd.read_csv("acsdt5y2023-b19013.dat", sep="|", dtype={"GEO_ID": str})
tracts = raw[raw.GEO_ID.str.startswith("1400000US")].set_index("GEO_ID")
sentinel_report(tracts, ["B19013_E001", "B19013_M001"])
     column       code  rows                                            meaning
B19013_E001 -666666666  1547 estimate not computed: too few sample observations
B19013_M001 -333333333   492 MOE not computed: median in an open-ended interval
B19013_M001 -222222222  1547      MOE not computed: too few sample observations

Run it on every new table. A column that reports codes you did not expect — −555555555 in an estimate, say — is a sign that the column is not what you think it is.

Example 2 — clean, and keep the reason

def clean_acs(frame, columns):
    """Replace annotation codes with NaN and keep the reason in a companion column."""
    out = frame.copy()
    for col in columns:
        code = out[col].where(out[col].isin(list(ANNOTATIONS)))
        out[f"{col}_note"] = code.map(ANNOTATIONS)
        out[col] = out[col].mask(code.notna())
    return out
clean = clean_acs(tracts, ["B19013_E001", "B19013_M001"])
est = tracts["B19013_E001"]
print(f"mean {est.mean():,.0f} -> {clean.B19013_E001.mean():,.0f}; "
      f"median {est.median():,.0f} -> {clean.B19013_E001.median():,.0f}; "
      f"min {est.min():,.0f} -> {clean.B19013_E001.min():,.0f}")
mean -11,997,152 -> 83,552; median 74,071 -> 74,875; min -666,666,666 -> 2,499

The note column survives joins and exports, so a map can style suppressed and controlled areas differently.

Example 3 — what the codes do to a classification

import mapclassify

for name, classify in (("quantiles", lambda v: mapclassify.Quantiles(v, k=5)),
                       ("natural breaks", lambda v: mapclassify.NaturalBreaks(v, k=5)),
                       ("equal interval", lambda v: mapclassify.EqualInterval(v, k=5))):
    for label, values in (("raw", est.to_numpy()), ("clean", clean.B19013_E001.dropna().to_numpy())):
        c = classify(values)
        print(f"{name:15} {label:5} bins {[round(b) for b in c.bins]} counts {c.counts.tolist()}")
quantiles       raw   bins [50396, 66250, 83022, 109918, 250001] counts [17077, 17112, 17040, 17076, 17076]
quantiles       clean bins [51720, 67027, 83702, 110651, 250001] counts [16767, 16768, 16766, 16766, 16767]
natural breaks  raw   bins [-666666666, 64946, 103240, 160625, 250001] counts [1547, 31215, 32294, 15767, 4558]
natural breaks  clean bins [56557, 85840, 122218, 176698, 250001] counts [21734, 30333, 19488, 9293, 2986]
equal interval  raw   bins [-533283333, -399899999, -266516666, -133133332, 250001] counts [1547, 0, 0, 0, 83834]
equal interval  clean bins [51999, 101500, 151000, 200501, 250001] counts [17052, 45438, 15506, 4175, 1663]

Natural breaks starts from a random state, so its break values vary slightly between runs; the sentinel class does not. Any classification computed from a column that still holds codes should be treated as wrong, including the quantile one that looks plausible.

Example 4 — where the codes are, and who lives there

counties = pop[pop.index.str.startswith("0500000US")]
print(f"B01003 margins coded -555555555 in {(counties.B01003_M001 == -555555555).sum():,} of {len(counties):,} counties")
missing = raw.B19013_E001 == -666666666
print(f"{len(raw):,} rows, {missing.sum():,} estimates of -666666666; by summary level:")
print(raw[missing].GEO_ID.str[:3].value_counts().head(6).to_string())

state = tracts.index.str[9:11]
share = (tracts.B19013_E001 == -666666666).groupby(state).mean().sort_values(ascending=False)
print("share of tracts without an estimate, top five states (FIPS):", share.head(5).round(3).to_dict())
top = tracts.B19013_E001[tracts.B19013_M001 == -333333333].value_counts()
print(f"estimates beside -333333333 margins: {top.to_dict()}")

people = pop.B01003_E001.reindex(tracts.index[tracts.B19013_E001 == -666666666])
print(f"population of suppressed tracts: median {people.median():.0f}, zero in {(people == 0).sum()}, "
      f"over 1,000 in {(people > 1000).sum()}, largest {people.max():,}")
B01003 margins coded -555555555 in 3,090 of 3,222 counties
543,541 rows, 38,246 estimates of -666666666; by summary level:
GEO_ID
150    17906
155     4521
160     3750
060     3164
860     3154
140     1547
share of tracts without an estimate, top five states (FIPS): {'15': 0.091, '72': 0.065, '26': 0.049, '36': 0.04, '35': 0.029}
estimates beside -333333333 margins: {250001: 483, 2499: 9}
population of suppressed tracts: median 0, zero in 849, over 1,000 in 452, largest 13,134

The first three characters of a GEO_ID are the summary level: 150 is a block group, 155 a place within a county, 160 a place, 060 a county subdivision, 860 a ZIP Code Tabulation Area and 140 a tract.

Why missing values are numbers

Why the Census Bureau uses numbers for missing values

The API and the summary files deliver every estimate column as a single numeric type. A special value has to be representable in that type, and a code such as −666666666 cannot be mistaken for a real count, dollar amount or margin by a person reading it. It is designed to be recognisable, not to be safe in arithmetic.

Why the median hides the problem

A median depends on order, not on magnitude. The 1,547 codes all sort to the bottom, so they shift the middle of 85,381 values by less than a thousand dollars. A mean, a sum, a regression or an equal-interval classification depends on magnitude, and one value of −666,666,666 outweighs thousands of real incomes. Checking a column with its median and a histogram of the middle is exactly the check that misses it.

Why each code needs its own decision

The codes describe different situations. −666666666 and −999999999 mean the survey cannot say; the area is a gap. −888888888 means the question does not apply. −555555555 in a margin means the estimate has no sampling error to report, so the estimate itself is fine to use. −333333333 in a margin means the estimate is a bound at the end of the distribution. Collapsing them all into NaN without a note loses the difference between "unknown" and "known with no margin".

Why suppressed areas are not all empty

Estimates are suppressed for lack of sample observations, not lack of population. A tract where the survey received too few responses to compute a median can still hold thousands of people. Treating every suppressed area as uninhabited — or dropping it from a map — removes real places from an analysis without saying so.

Median household income estimates coded −666666666 at six ACS summary levels. B19013 estimates that could not be computedblock groups17,906places in counties4,521places3,750county subdivisions3,164ZCTAs3,154tracts1,547019k38,246 across all 543,541 rows of the table spatialworkflow.io
The smaller the geography, the more estimates cannot be computed.

Margins, controlled estimates and bounded values

  • Codes appear in both estimate and margin columns. Clean every _E and _M column, not only the one you plan to map.
  • A controlled estimate is usable. −555555555 in a margin means no sampling error to report, not a missing estimate.
  • 250001 and 2499 are real, bounded values. They are top and bottom codes, not annotation values.
  • The API returns numbers as strings. Convert with pd.to_numeric first, then compare against integer codes.
  • Annotation variables accompany the values. Each estimate and margin in an API table group has a companion variable ending in EA or MA; request them when you need the annotation alongside the number.
  • Aggregating a code poisons the sum. One −666666666 in a county's tracts makes the county total meaningless.
  • Codes survive to Parquet and databases. Clean before writing, or every consumer inherits the problem.
  • Other agencies use other conventions. Do not reuse this list outside ACS data; check each source's own documentation.

FAQ

What does −666666666 mean in census data?

The estimate could not be computed because there were too few sample observations. In the 2023 ACS 5-year tract table of median household income, 1,547 estimates had that value.

What does −555555555 mean in a margin of error?

That a margin of error is not appropriate because the estimate is controlled to an independent population or housing estimate. The estimate is usable; it simply has no sampling margin.

How do I remove these values in pandas?

Mask them to NaN when the table is loaded, for example column.mask(column.isin(codes)), and keep a note column with the reason if different codes need different treatment.

Why does my median look normal when my mean is negative?

The codes sort to the bottom, so they barely move the median: 74,071 with them and 74,875 without. The mean is dominated by their magnitude and fell to −11,997,152.

Are 250001 and 2499 also sentinel values?

No. They are top and bottom codes meaning 250,000 or more and 2,500 or less. Their margins carry −333333333 because a median at the edge of the distribution has no computable margin.

Do the tracts with −666666666 have no people?

Not necessarily. Their median population was 0, but 452 had more than 1,000 residents and the largest had 13,134. Show them as having no estimate rather than dropping them.