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"became6037 - 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, orright_on - the
howargument 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:
- inspect both key columns with
.head()and.dtypes - read code columns as text in the first place (
dtype={"STATE": str}), or make the dtypes match with.astype(str) - clean the strings with
.str.strip()and a consistent case - restore leading zeros with
.str.zfill()if codes were turned into integers - validate the join with
indicator=Trueand check the_mergecolumn
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
Inspect keys, match dtypes, strip, restore zeros
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.
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
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 textGEOID. - County population estimates β the Vintage 2024 estimates,
co-est2024-alldata.csv, one row per county plus one per state, with the FIPS code split acrossSTATEandCOUNTY.
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.
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.
Internal links
For the standard attribute join workflow, see:
- How to Join Attribute Data to a GeoDataFrame in Python
- What Is a GeoDataFrame? Structure and Key Concepts
If you need a location-based join instead of a key-based one, see:
- How to Perform a Spatial Join in Python (GeoPandas)
- Spatial Join Returns Empty Results in GeoPandas: How to Fix It
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).