GeoPandas Merge Returns NaN or No Matches: How to Fix It

On this page

Problem statement

Attaching a table to a spatial layer β€” population to counties, sales to territories β€” is a pandas merge() on a key, and it can run without an error and still leave you with columns full of NaN, fewer matches than expected, or a result that has lost its geometry. The example on this page is the one that catches almost everyone: the US Census Bureau's county boundaries carry a five-character GEOID such as "08001", and the Bureau's own population estimates CSV carries the same code as two numeric columns, STATE and COUNTY, which pandas reads as integers.

In practice, a merge goes wrong for one of these reasons:

  • leading-zero codes were read as integers, so "06037" became 6037
  • the join keys have different dtypes β€” which, in current pandas, raises an error rather than failing silently
  • the key values differ by whitespace or letter case
  • the wrong column was used in on, left_on, or right_on
  • the how argument hides or drops unmatched rows
  • the keys genuinely do not overlap between the two datasets

This guide shows how to diagnose each cause and apply the right fix while keeping the geometry intact.

Quick answer

If a merge() produces NaN values or no matches:

  1. inspect both key columns with .head() and .dtypes
  2. read code columns as text in the first place (dtype={"STATE": str}), or make the dtypes match with .astype(str)
  3. clean the strings with .str.strip() and a consistent case
  4. restore leading zeros with .str.zfill() if codes were turned into integers
  5. validate the join with indicator=True and check the _merge column
import geopandas as gpd
import pandas as pd

counties = gpd.read_file("data/cb_2024_us_county_500k.zip")           # GEOID "08001"
estimates = pd.read_csv("data/co-est2024-alldata.csv", encoding="latin-1",
                        dtype={"STATE": str, "COUNTY": str})           # keep "08", "001"
estimates["GEOID"] = estimates["STATE"] + estimates["COUNTY"]

merged = counties.merge(estimates[["GEOID", "POPESTIMATE2024"]], on="GEOID", how="left")
print(type(merged).__name__, len(merged), "counties,",
      merged["POPESTIMATE2024"].isna().sum(), "without an estimate")
GeoDataFrame 3235 counties, 91 without an estimate

The 91 left without an estimate are not a key problem: they are Puerto Rico's municipios and the island areas, which the boundary file includes and this estimates table does not. Step 6 shows how to tell those apart from real mismatches.

Diagnosing a failed merge

Failed merge: diagnosisDo the key dtypes match?Cast both with astype(str)NoYesMatch after strip and case?str.strip() and normalize thecaseNoYesLeading zeros lost?str.zfill(width) to restore themYesNoCheck on/how; validate with indicator=Truespatialworkflow.io
Diagnosing a merge that returns NaN or no matches.

Inspect keys, match dtypes, strip, restore zeros

Repairing a join key: compare, align dtypes, trim, restore zeros, then merge. fix the key before you blame the merge1Compare the two key columns side by sidevalue_counts()2Make the dtypes matchastype(str)3Strip whitespace and normalise case.str.strip().str.upper()4Restore lost leading zeroszfill(5)5Merge with validate=validate="m:1"A merge never "fails" β€” it faithfully reports that nothing matched spatialworkflow.io
Four of these five steps happen before the merge call β€” that is where the bug lives.

Step 1: Inspect both key columns before merging

Before changing anything, look at the actual values and types on both sides. Read the CSV the way most people do first β€” with no options:

try:
    pd.read_csv("data/co-est2024-alldata.csv")
except UnicodeDecodeError as err:
    print("UnicodeDecodeError:", str(err)[:80])

raw = pd.read_csv("data/co-est2024-alldata.csv", encoding="latin-1")
print(raw[["SUMLEV", "STATE", "COUNTY", "CTYNAME"]].head(3))
print(raw[["STATE", "COUNTY"]].dtypes.to_dict(), "| boundary GEOID:", counties["GEOID"].dtype,
      counties["GEOID"].head(2).tolist())
UnicodeDecodeError: 'utf-8' codec can't decode byte 0xf1 in position 227887: invalid continuation by
   SUMLEV  STATE  COUNTY         CTYNAME
0      40      1       0         Alabama
1      50      1       1  Autauga County
2      50      1       3  Baldwin County
{'STATE': dtype('int64'), 'COUNTY': dtype('int64')} | boundary GEOID: str ['01069', '01023']

Two things before the merge even starts. The file is Latin-1, not UTF-8: byte 0xf1 is the Γ± in DoΓ±a Ana County, New Mexico. And STATE and COUNTY came back as integers, so Alabama is 1 rather than "01", while the boundary file's GEOID is a five-character string. A code that prints as 1001 on one side and 01001 on the other will never match.

Step 2: Make the key dtypes match

Building a key from the integer columns and merging it on the string GEOID does not fail silently in current pandas β€” it refuses:

raw["GEOID"] = raw["STATE"] * 1000 + raw["COUNTY"]
for key in [raw["GEOID"], raw["GEOID"].astype(float)]:
    try:
        counties.merge(raw.assign(GEOID=key)[["GEOID", "POPESTIMATE2024"]], on="GEOID", how="left")
    except ValueError as err:
        print("ValueError:", err)
ValueError: You are trying to merge on str and int64 columns for key 'GEOID'. If you wish to proceed you should use pd.concat
ValueError: You are trying to merge on str and float64 columns for key 'GEOID'. If you wish to proceed you should use pd.concat

That error is the helpful case. The unhelpful one is what most people try next β€” casting the integer to a string β€” which makes the dtypes match and the merge run:

raw["GEOID"] = raw["GEOID"].astype(str)
naive = counties.merge(raw[["GEOID", "POPESTIMATE2024"]], on="GEOID", how="left")
lost = naive["POPESTIMATE2024"].isna()
print(lost.sum(), "of", len(naive), "counties got NaN")
zero = lost & naive["GEOID"].str.startswith("0")
print(zero.sum(), "of them have a GEOID starting with 0:", sorted(naive.loc[zero, "STUSPS"].unique()))
409 of 3235 counties got NaN
318 of them have a GEOID starting with 0: ['AK', 'AL', 'AR', 'AZ', 'CA', 'CO', 'CT']

No error, and 409 counties empty: the 318 counties of the seven states whose FIPS code starts with 0 β€” Alabama (01) to Connecticut (09) β€” plus the 91 areas that have no estimate at all. Matching dtypes is necessary, not sufficient.

Counties left without a population estimate after each attempt to build the join key. 3,235 county boundaries, counties left with NaNint key on str GEOIDraises ValueErrorastype(str)409astype(str).str.zfill(5)91read with dtype=str910100020003235The last 91 are territories with no estimate: not a key problem spatialworkflow.io
Casting to string makes the error go away and leaves 409 counties empty. Restoring the zeros leaves only the 91 territories the estimates never covered.

Step 3: Strip whitespace and normalize case

Even with matching dtypes, values like "NY " and "NY" or "abc" and "ABC" will not match. For text keys, clean both sides the same way:

for frame in (counties, raw):
    frame["STATE_KEY"] = frame["STATE_NAME" if "STATE_NAME" in frame else "STNAME"].str.strip().str.upper()
print(sorted(set(counties["STATE_KEY"]) - set(raw["STATE_KEY"])))
['AMERICAN SAMOA', 'COMMONWEALTH OF THE NORTHERN MARIANA ISLANDS', 'GUAM', 'PUERTO RICO', 'UNITED STATES VIRGIN ISLANDS']

A quick way to see whether values overlap at all is to compare the two sets, as above: the only state-level names in the boundary file and not in the estimates are the territories. If the set of shared keys is empty, the merge cannot match anything and you need to look more closely at the values.

Step 4: Restore leading zeros on code columns

This is the classic cause of a broken merge. When a CSV column like "06037" is read as a number, it becomes 6037. Pad it back to a fixed width with zfill:

raw["GEOID"] = (raw["STATE"] * 1000 + raw["COUNTY"]).astype(str).str.zfill(5)
print(raw.loc[raw["CTYNAME"] == "Autauga County", ["STATE", "COUNTY", "GEOID"]])
   STATE  COUNTY  GEOID
1      1       1  01001

zfill(5) pads each value to five characters with leading zeros, so 1001 becomes 01001. Use the width that matches your code system: 2 for state FIPS, 5 for counties, 11 for census tract GEOIDs. Better still, avoid losing the zeros: dtype={"STATE": str, "COUNTY": str} in read_csv, as in the quick answer, keeps them as written.

Step 5: Use the correct on, left_on, and right_on

If the key columns have different names, you cannot use on. Use left_on and right_on instead:

by_name = counties.merge(raw[["GEOID", "CTYNAME", "POPESTIMATE2024"]].rename(columns={"GEOID": "fips"}),
                         left_on="GEOID", right_on="fips", how="left")
print(by_name[["GEOID", "fips", "NAMELSAD", "CTYNAME"]].head(3))
   GEOID   fips        NAMELSAD         CTYNAME
0  01069  01069  Houston County  Houston County
1  01023  01023  Choctaw County  Choctaw County
2  01113  01113  Russell County  Russell County

Both key columns are kept. Passing the wrong column name to on raises a KeyError; passing a column that exists but is not the real key produces a silent flood of NaN instead.

Step 6: Choose the right how and validate

The how argument decides which rows survive. An outer join with an indicator shows every key that failed to match, from both sides:

check = counties.merge(raw[["GEOID", "SUMLEV", "CTYNAME", "POPESTIMATE2024"]],
                       on="GEOID", how="outer", indicator=True)
print(check["_merge"].value_counts())
print("left only, by state:", check.loc[check["_merge"] == "left_only", "STUSPS"].value_counts().to_dict())
print("right only, by SUMLEV:", check.loc[check["_merge"] == "right_only", "SUMLEV"].value_counts().to_dict())
_merge
both          3144
left_only       91
right_only      51
Name: count, dtype: int64
left only, by state: {'PR': 78, 'AS': 5, 'MP': 4, 'VI': 3, 'GU': 1}
right only, by SUMLEV: {40.0: 51}

indicator=True adds a _merge column with values both, left_only, and right_only, which turns a silent failure into something you can count. Here all 91 unmatched boundaries are in Puerto Rico and the island areas, and all 51 unmatched rows of the CSV have summary level 40 β€” they are the state totals (county code 000) that sit in the same file as the counties. Neither is a key-cleaning problem; both need a decision. Filter the CSV to SUMLEV == 50 before merging, and decide whether the territories belong on your map.

US counties merged without losing geometry

An outer merge with indicator=True between Census county boundaries and population estimates. where every row ended upbothleft_onlyright_only_mergematchedboundary onlyCSV onlyrows3,1449151what they areβœ“counties!PR + island areas!state totalsUnmatched rows needed decisions, not cleaning spatialworkflow.io
After the key is fixed, indicator=True accounts for every row: 91 territories without estimates and 51 state-total rows in the CSV.

The data used on this page

Two files from the US Census Bureau, downloaded by the block below:

  • County boundaries β€” the 2024 cartographic boundary file at 1:500,000 (cb_2024_us_county_500k), 3,235 counties and equivalents including Puerto Rico and the island areas, with a text GEOID.
  • County population estimates β€” the Vintage 2024 estimates, co-est2024-alldata.csv, one row per county plus one per state, with the FIPS code split across STATE and COUNTY.
import os

import geopandas as gpd
import pandas as pd
import requests

os.makedirs("data", exist_ok=True)
files = {
    "cb_2024_us_county_500k.zip": "https://www2.census.gov/geo/tiger/GENZ2024/shp/cb_2024_us_county_500k.zip",
    "co-est2024-alldata.csv": "https://www2.census.gov/programs-surveys/popest/datasets/2020-2024/counties/totals/"
                              "co-est2024-alldata.csv",
}
for name, url in files.items():
    if not os.path.exists("data/" + name):
        reply = requests.get(url, timeout=300)
        reply.raise_for_status()
        open("data/" + name, "wb").write(reply.content)

counties = gpd.read_file("data/cb_2024_us_county_500k.zip")
print(len(counties), "boundaries,", len(pd.read_csv("data/co-est2024-alldata.csv", encoding="latin-1")), "estimate rows")
3235 boundaries, 3195 estimate rows

Example 1: A working attribute merge that keeps geometry

This is the standard pattern: read the codes as text, keep only county rows, and call merge() on the GeoDataFrame so the result stays a GeoDataFrame.

import geopandas as gpd
import pandas as pd

counties = gpd.read_file("data/cb_2024_us_county_500k.zip")
estimates = pd.read_csv("data/co-est2024-alldata.csv", encoding="latin-1", dtype={"STATE": str, "COUNTY": str})
estimates = estimates[estimates["SUMLEV"] == 50].assign(GEOID=lambda d: d["STATE"] + d["COUNTY"])

merged = counties.merge(estimates[["GEOID", "POPESTIMATE2024"]], on="GEOID", how="left", validate="one_to_one")

print(type(merged).__name__, merged.crs)
print(merged.nlargest(3, "POPESTIMATE2024")[["NAMELSAD", "STUSPS", "POPESTIMATE2024"]].to_string(index=False))
GeoDataFrame EPSG:4269
          NAMELSAD STUSPS  POPESTIMATE2024
Los Angeles County     CA        9757179.0
       Cook County     IL        5182617.0
     Harris County     TX        5009302.0

validate="one_to_one" makes pandas raise if either side has a repeated key, so a join that would multiply rows fails loudly instead.

Example 2: The call order decides whether geometry survives

import geopandas as gpd
import pandas as pd

counties = gpd.read_file("data/cb_2024_us_county_500k.zip")
estimates = pd.read_csv("data/co-est2024-alldata.csv", encoding="latin-1", dtype={"STATE": str, "COUNTY": str})
estimates["GEOID"] = estimates["STATE"] + estimates["COUNTY"]

left_geo = counties.merge(estimates[["GEOID", "POPESTIMATE2024"]], on="GEOID")
left_table = estimates[["GEOID", "POPESTIMATE2024"]].merge(counties, on="GEOID")
print(type(left_geo).__name__, "|", type(left_table).__name__, "- has a geometry column:", "geometry" in left_table)
print(gpd.GeoDataFrame(left_table, geometry="geometry", crs=counties.crs).geom_type.iloc[0])
GeoDataFrame | DataFrame - has a geometry column: True
Polygon

Both results hold the same rows. The second is a plain DataFrame carrying shapes in an ordinary column; wrap it in GeoDataFrame(..., geometry="geometry", crs=...) to get spatial behaviour back.

Example 3: Validate matches with an indicator

import geopandas as gpd
import pandas as pd

counties = gpd.read_file("data/cb_2024_us_county_500k.zip")
estimates = pd.read_csv("data/co-est2024-alldata.csv", encoding="latin-1", dtype={"STATE": str, "COUNTY": str})
estimates = estimates[estimates["SUMLEV"] == 50].assign(GEOID=lambda d: d["STATE"] + d["COUNTY"])

merged = counties.merge(estimates[["GEOID", "POPESTIMATE2024"]], on="GEOID", how="outer", indicator=True)
print(merged["_merge"].value_counts().to_dict())
print(merged.loc[merged["_merge"] != "both", "STATE_NAME"].value_counts().to_dict())
{'both': 3144, 'left_only': 91, 'right_only': 0}
{'Puerto Rico': 78, 'American Samoa': 5, 'Commonwealth of the Northern Mariana Islands': 4, 'United States Virgin Islands': 3, 'Guam': 1}

With the state totals filtered out, nothing is left on the right; everything unmatched on the left is a territory, which you can now drop or map as "no data" on purpose.

Example 4: Different column names with left_on and right_on

import geopandas as gpd
import pandas as pd

counties = gpd.read_file("data/cb_2024_us_county_500k.zip")          # key column: GEOID
estimates = pd.read_csv("data/co-est2024-alldata.csv", encoding="latin-1", dtype={"STATE": str, "COUNTY": str})
estimates["fips"] = estimates["STATE"] + estimates["COUNTY"]          # key column: fips

merged = counties.merge(estimates[["fips", "CTYNAME", "POPESTIMATE2024"]],
                        left_on="GEOID", right_on="fips", how="inner")
mismatched_names = (merged["NAMELSAD"] != merged["CTYNAME"]).sum()
print(len(merged), "matched;", mismatched_names, "where the two files spell the county differently")
3144 matched; 0 where the two files spell the county differently

Comparing a second column after a code join is a cheap consistency check: here the Census boundary file and the estimates agree on every county name β€” including DoΓ±a Ana, once the CSV is read as Latin-1.

A merge matches equal values only

A merge() is a key-based join. pandas aligns rows by matching values in the key columns, and a match requires the values to be equal. In current pandas (3.0 here), a key that is an integer on one side and a string on the other is refused outright with a ValueError; the silent failures are between two string keys that merely look alike: "1001" and "01001", or "NY " and "NY". When no rows match, pandas still produces output: with a left join, every left row is kept and the columns from the right frame are filled with NaN. That is why a broken merge looks like a successful one with empty columns.

Four ways two key columns can look identical and still fail to match. why "0123" never equals 123left keyright keylooks equal?matches?"0123"123yesno β€” dtype"Oslo ""Oslo"yesno β€” space"oslo""Oslo"yesno β€” case"01234""1234"nono β€” zerosPrint repr() of both keys β€” it makes every one of these visible instantlyPrinted side by side these look fine; compared by pandas they are four different values spatialworkflow.io
Screen output hides all four β€” which is why an empty merge is so puzzling at first.

The geometry question is separate but important. merge() returns the type of the object it is called on. If you call regions.merge(stats, ...) where regions is a GeoDataFrame, the result is a GeoDataFrame and the geometry column and CRS are preserved. If you call stats.merge(regions, ...) where stats is a plain DataFrame, the result is a plain DataFrame and you lose the GeoDataFrame behaviour even though a geometry column is present (Example 2). The rule is simple: keep the GeoDataFrame on the left and call merge() on it.

Because the key failure and the geometry loss are both silent, the fix is always the same shape: inspect the keys, normalize them on both sides, then validate the result with an indicator before trusting it.

Leading zeros, float keys and invisible characters

Leading zeros in codes

Postal codes, FIPS codes, and many ID systems use leading zeros. CSV and Excel readers often strip them by turning the column into an integer β€” pandas did exactly that to the Census estimates' STATE and COUNTY. Cast the column to string and pad it with .str.zfill(width) to restore them. Do this on whichever side lost the zeros, and make sure both sides use the same width.

Float keys

If a key column was read as a float β€” which pandas does to an integer column that contains any missing value β€” values appear as 6037.0. Merging that on a string key raises ValueError, and casting it straight to string gives "6037.0", which silently matches nothing. Convert to a nullable integer first, then to string: col.astype("Int64").astype(str).

Many-to-many duplicates

If the right frame has duplicate keys, the merge multiplies rows: each left row matches every duplicate, so your row count grows. Check for duplicates with stats["code"].duplicated().sum() before merging, and use validate="one_to_many" or validate="one_to_one" to make pandas raise an error instead of silently expanding the result.

Hidden whitespace and invisible characters

.str.strip() removes leading and trailing spaces, but not interior characters such as non-breaking spaces or a trailing tab. If keys still fail to match after stripping, print repr() of a few values to reveal hidden characters.

Keys that genuinely do not overlap

Sometimes the data simply does not share keys. The Census estimates file mixes 51 state-total rows in with the counties, and the boundary file includes 91 territories the estimates leave out; no amount of cleaning makes those match. Compare the two key sets directly β€” an outer merge with indicator=True does it β€” before debugging dtypes.

For the standard attribute join workflow, see:

If you need a location-based join instead of a key-based one, see:

To summarize data after joining, see How to Aggregate Spatial Data by Region in GeoPandas.

FAQ

Why does my GeoPandas merge return all NaN values?

The join keys do not match. The most common reasons are leading zeros stripped when a code column was read as a number and then cast back to text, whitespace or case differences in the values, or keys that genuinely do not overlap. (A plain integer-versus-string mismatch raises a ValueError in current pandas rather than returning NaN.) Inspect both key columns and normalize them on each side before merging.

How do I keep the geometry after a merge?

Call merge() on the GeoDataFrame, not on the plain DataFrame. regions.merge(stats, ...) keeps the geometry and CRS, while stats.merge(regions, ...) returns a plain DataFrame and loses the GeoDataFrame behavior.

How can I check which rows failed to match?

Add indicator=True to the merge and inspect the resulting _merge column. Values of left_only are rows from the left frame with no match, and right_only are unmatched rows from the right frame.

Why did my row count grow after merging?

The right frame has duplicate keys, so each left row matches multiple right rows. Check with df["code"].duplicated().sum() and pass validate="one_to_many" or validate="one_to_one" to make pandas raise an error on unexpected duplicates.

How do I merge on multiple key columns at once?

Pass a list to on, for example regions.merge(stats, on=["state", "county"], how="left"). Every column in the list must match in dtype and content on both sides, so normalize each one before merging.

Why does merge() add _x and _y suffixes to my columns?

Both frames contain a non-key column with the same name, so pandas disambiguates them with _x (left) and _y (right) suffixes. Drop or rename the duplicate before merging, or set custom labels with suffixes=("_region", "_stats").

My keys are numeric IDs with no leading zeros, so why are they still NaN?

A common cause is that one side was read as a float (6037.0) because the column had a missing value, and was then cast to string as "6037.0". Cast through a nullable integer instead: col.astype("Int64").astype(str).