🔧 Fix Census & demographics Data cleaning Data sources & downloads Census & Demographics · step 17 of 20 Updated

Fixing Census API Values Like −666666666 and Other Sentinels

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)))

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

Table of the six ACS annotation codes with the symbol data.census.gov shows for each and what each means.
The meanings are from the Census Bureau's notes on ACS estimate and annotation values.

Step-by-step solution

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.

Table of equal-interval class counts for tract median household income with sentinel codes included and removed.
With the codes left in, the range is dominated by −666,666,666 and four of the five classes are empty or nearly so.

Code examples

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"])

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, 62708, 99286, 150603, 250001] counts [1547, 28846, 32365, 16732, 5891]
natural breaks  clean bins [58047, 88024, 124757, 178750, 250001] counts [23296, 30437, 18635, 8640, 2826]
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.

Explanation

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.

Bar chart of the number of median household income estimates coded −666666666 at each ACS summary level.
The smaller the geography, the more estimates cannot be computed.

Edge cases or notes

  • 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.