GeoPandas "Columns Overlap but No Suffix Specified" Error: How to Fix It
On this page
Problem statement
A common GeoPandas task is combining two tables: attaching census figures to ward polygons, adding a lookup table to a layer, or attaching polygon attributes to points with a spatial join. Sooner or later one of those calls stops with:
ValueError: columns overlap but no suffix specified: Index(['name'], dtype='str')
The message means both frames have a column with the same name, the call was not told how to tell them apart, and pandas refused to guess. (Older pandas prints dtype='object' at the end; the meaning is the same.)
Which call raises it is less obvious than the message suggests, because the three ways of combining tables behave differently when a name is shared:
DataFrame.join()raises it by default. It has no default suffixes, so any shared column other than the index stops it. This is the usual source of the error.merge()does not raise it by default. It silently renames the pair toname_xandname_y. It raises only when you pass empty suffixes, such assuffixes=("", "")or(None, None).gpd.sjoin()does not raise it either: its default suffixes turn a sharednameintoname_leftandname_right. It raises a differentValueError—'index_right' cannot be a column name in the frames being joined— when you spatially join a result that already went throughsjoin().
So the fix is either to supply suffixes, or to remove the overlap before joining — and, for merge() and sjoin(), to notice the renaming that happened without an error.
Quick answer
If join() or merge() raises columns overlap but no suffix specified:
- find the shared names with
left.columns.intersection(right.columns) - drop the columns from one side you do not need, or rename them
- or pass
lsuffix=/rsuffix=tojoin(), orsuffixes=with two non-empty strings tomerge() - after
sjoin(), dropindex_rightbefore the next spatial join
The data here is real: Rutland's 15 wards from the ONS boundary service, with code and name, and the 2021 Census population by ward from Nomis, which also carries code and name.
import geopandas as gpd
import pandas as pd
wards = gpd.read_file("data/wards.gpkg") # code, name, geometry
census = pd.read_csv("data/ward_population.csv") # code, name, population
try:
wards.join(census.set_index("code"), on="code")
except ValueError as err:
print("ValueError:", err)
joined = wards.join(census.set_index("code"), on="code", rsuffix="_census")
print(joined.columns.tolist())
ValueError: columns overlap but no suffix specified: Index(['name'], dtype='str')
['code', 'name', 'geometry', 'name_census', 'population']
The shared name column from the census side becomes name_census, and the join succeeds.
Choosing the fix
Find the colliding columns and name them
Find which columns actually overlap
Before fixing anything, list the shared column names so you know what is colliding.
overlap = wards.columns.intersection(census.columns)
print("Overlapping columns:", list(overlap))
Overlapping columns: ['code', 'name']
This includes the key you are joining on. With merge(on="code"), code is consumed by the join and becomes one column; with join(on="code") the right side's key is its index, so it is not a column at all. The columns that trigger the error are the other shared ones — here name.
Check whether the shared column actually agrees
Before deciding whether to keep both copies, look at them side by side. Two sources that publish the "same" name often do not:
both = wards.merge(census, on="code", suffixes=("_ons", "_census"))
print(both.loc[both["name_ons"] != both["name_census"], ["code", "name_ons", "name_census"]])
code name_ons name_census
8 E05012640 Normanton Normanton (Rutland)
The ONS 2024 boundary file calls ward E05012640 Normanton; the census tables, published by the same office, call it Normanton (Rutland). Neither is wrong, but if you had kept only one you would not know the other existed — and if you later joined a third table on the name, one of them would fail to match.
Add suffixes to merge()
merge() never needs to raise this error, because its default suffixes are ("_x", "_y"):
print(wards.merge(census, on="code").columns.tolist())
print(wards.merge(census, on="code", suffixes=("_ons", "_census")).columns.tolist())
['code', 'name_x', 'geometry', 'name_y', 'population']
['code', 'name_ons', 'geometry', 'name_census', 'population']
The first call succeeded without complaint, which is the risk: name_x and name_y say nothing about which source is which, and code that later asks for name gets a KeyError. Choose suffixes that describe the source. The geometry column comes from the left GeoDataFrame and is kept.
Only empty suffixes bring the error back:
for suffixes in [("", ""), (None, None)]:
try:
wards.merge(census, on="code", suffixes=suffixes)
except ValueError as err:
print(suffixes, "->", err)
print(wards.merge(census, on="code", suffixes=("", "_census")).columns.tolist())
('', '') -> columns overlap but no suffix specified: Index(['name'], dtype='str')
(None, None) -> columns overlap but no suffix specified: Index(['name'], dtype='str')
['code', 'name', 'geometry', 'name_census', 'population']
One empty suffix is allowed: the left column keeps its name and only the right one is renamed.
Use lsuffix and rsuffix for .join()
DataFrame.join() does not accept suffixes. It uses separate lsuffix and rsuffix arguments, joins on the right frame's index, and uses the left frame's index unless you name a column with on=.
by_code = wards.set_index("code").join(census.set_index("code"), lsuffix="_ons", rsuffix="_census")
print(by_code.columns.tolist())
['name_ons', 'geometry', 'name_census', 'population']
Either suffix on its own is enough, as the quick answer showed with rsuffix alone.
Rename duplicate columns before joining
If you want clean, explicit names instead of generated suffixes, rename the columns on one side first.
renamed = census.rename(columns={"name": "census_name"})
merged = wards.merge(renamed, on="code") # nothing left to collide
print(merged.columns.tolist())
['code', 'name', 'geometry', 'census_name', 'population']
After renaming, there is no overlap, so merge() and join() work without suffix arguments.
Drop columns you do not need before joining
Often the overlapping column on one side is redundant. Dropping it removes the conflict and keeps the result narrow.
merged = wards.merge(census[["code", "population"]], on="code")
print(merged.drop(columns="geometry").head(3))
code name population
0 E05012632 Barleythorpe 2934
1 E05012633 Braunston & Martinsthorpe 2406
2 E05012634 Cottesmore 3648
Selecting only needed columns is usually cleaner than carrying everything and suffixing afterward.
Handle sjoin(): suffixes by default, and index_right
A spatial join has the same problem with a twist. Here the left layer is the 64 towns, villages and hamlets in OpenStreetMap for Rutland (from the Geofabrik extract). That layer has its own name, its own population (OSM's tag, filled in for two villages), and a column called code — Geofabrik's numeric class code, nothing to do with the ONS ward code.
places = gpd.read_file("data/places.gpkg") # osm_id, code, fclass, population, name
wards_pop = wards.merge(census[["code", "population"]], on="code")
print("shared:", list(places.columns.intersection(wards_pop.columns)))
in_ward = gpd.sjoin(places, wards_pop, how="left", predicate="within")
print(in_ward.columns.tolist())
shared: ['code', 'population', 'name', 'geometry']
['osm_id', 'code_left', 'fclass', 'population_left', 'name_left', 'geometry', 'index_right', 'code_right', 'name_right', 'population_right']
No error. geometry is shared too, but sjoin() keeps only the left layer's; every other shared column was renamed with _left and _right, and index_right was added, holding the matched row of the right frame. Three columns called code, name and population now each exist twice with meanings that differ by source, and population_left is a village's OSM tag while population_right is the whole ward's census count. Name them for what they are:
in_ward = gpd.sjoin(places, wards_pop, how="left", predicate="within", lsuffix="place", rsuffix="ward")
print(in_ward[["name_place", "fclass", "name_ward", "population_ward"]].head())
name_place fclass name_ward population_ward
0 Ryhall village Ryhall & Casterton 2936.0
1 Pickworth hamlet Greetham 2474.0
2 Cottesmore village Cottesmore 3648.0
3 Seaton village Lyddington 1341.0
4 Thorpe by Water village Lyddington 1341.0
rsuffix also renames the index column: it is index_ward now, not index_right.
The error sjoin() does raise comes from joining its own output again. With the default suffixes, the index_right column from the first join blocks the second:
first = gpd.sjoin(places, wards_pop, how="left", predicate="within")
try:
gpd.sjoin(first, wards_pop, how="left", predicate="within")
except ValueError as err:
print("ValueError:", err)
second = gpd.sjoin(first.drop(columns="index_right"), wards_pop, how="left", predicate="within")
print(len(second), "rows after dropping index_right")
ValueError: 'index_right' cannot be a column name in the frames being joined
64 rows after dropping index_right
Three joins on Rutland's wards
The data used on this page
Three real layers for Rutland, all downloaded by the block below:
- Wards — the 15 electoral wards from the ONS Wards (December 2024) Boundaries UK BGC service, with
WD24CDandWD24NMrenamed tocodeandname. - Ward population — usual residents at the 2021 Census (table TS001) for the same ward codes, from the Nomis API, as a CSV with
code,nameandpopulation. - Places — the settlement points (towns, villages and hamlets) from Geofabrik's OpenStreetMap shapefile extract for Rutland. OpenStreetMap changes daily, so the count may drift by one or two.
import io
import os
import zipfile
import geopandas as gpd
import pandas as pd
import requests
pd.set_option("display.width", 120)
pd.set_option("display.max_columns", None)
os.makedirs("data", exist_ok=True)
if not os.path.exists("data/wards.gpkg"):
reply = requests.get("https://services1.arcgis.com/ESMARspQHYMw9BZ9/arcgis/rest/services/"
"Wards_December_2024_Boundaries_UK_BGC/FeatureServer/0/query",
params={"where": "LAD24NM='Rutland'", "outFields": "WD24CD,WD24NM",
"outSR": 4326, "f": "geojson"}, timeout=120)
reply.raise_for_status()
wards = gpd.read_file(io.BytesIO(reply.content))
wards = wards.rename(columns={"WD24CD": "code", "WD24NM": "name"}).sort_values("code")
wards[["code", "name", "geometry"]].to_file("data/wards.gpkg")
if not os.path.exists("data/ward_population.csv"):
codes = gpd.read_file("data/wards.gpkg")["code"]
reply = requests.get("https://www.nomisweb.co.uk/api/v01/dataset/NM_2021_1.data.csv", timeout=120,
params={"date": "latest", "geography": ",".join(codes), "c2021_restype_3": 0,
"measures": 20100, "select": "geography_code,geography_name,obs_value"})
reply.raise_for_status()
census = pd.read_csv(io.StringIO(reply.text))
census.columns = ["code", "name", "population"]
census.to_csv("data/ward_population.csv", index=False)
if not os.path.exists("data/places.gpkg"):
extract = requests.get("https://download.geofabrik.de/europe/united-kingdom/england/"
"rutland-260101-free.shp.zip", timeout=300)
extract.raise_for_status()
zipfile.ZipFile(io.BytesIO(extract.content)).extractall("osm")
places = gpd.read_file("osm/gis_osm_places_free_1.shp")
places[places["fclass"].isin(["town", "village", "hamlet"])].to_file("data/places.gpkg")
for name in ("wards.gpkg", "ward_population.csv", "places.gpkg"):
table = gpd.read_file("data/" + name) if name.endswith(".gpkg") else pd.read_csv("data/" + name)
print(f"{name:20} {len(table):>3} rows {[c for c in table.columns if c != 'geometry']}")
wards.gpkg 15 rows ['code', 'name']
ward_population.csv 15 rows ['code', 'name', 'population']
places.gpkg 64 rows ['osm_id', 'code', 'fclass', 'population', 'name']
Example 1: see which call raises and which renames
The same pair of frames, combined four ways:
import geopandas as gpd
import pandas as pd
wards = gpd.read_file("data/wards.gpkg")
census = pd.read_csv("data/ward_population.csv")
attempts = {
"merge(on=)": lambda: wards.merge(census, on="code"),
"merge(suffixes=('', ''))": lambda: wards.merge(census, on="code", suffixes=("", "")),
"join(on=)": lambda: wards.join(census.set_index("code"), on="code"),
"join(rsuffix=)": lambda: wards.join(census.set_index("code"), on="code", rsuffix="_census"),
}
for label, attempt in attempts.items():
try:
print(f"{label:26} {[c for c in attempt().columns if 'name' in c]}")
except ValueError as err:
print(f"{label:26} ValueError: {err}")
merge(on=) ['name_x', 'name_y']
merge(suffixes=('', '')) ValueError: columns overlap but no suffix specified: Index(['name'], dtype='str')
join(on=) ValueError: columns overlap but no suffix specified: Index(['name'], dtype='str')
join(rsuffix=) ['name', 'name_census']
Example 2: rename before merging for clean column names
When you control the column names, renaming is clearer than reading generated suffixes later.
import geopandas as gpd
import pandas as pd
wards = gpd.read_file("data/wards.gpkg")
census = pd.read_csv("data/ward_population.csv").rename(
columns={"name": "census_name", "population": "residents_2021"})
merged = wards.merge(census, on="code", validate="one_to_one")
print(merged[["code", "name", "census_name", "residents_2021"]].head(3))
code name census_name residents_2021
0 E05012632 Barleythorpe Barleythorpe 2934
1 E05012633 Braunston & Martinsthorpe Braunston & Martinsthorpe 2406
2 E05012634 Cottesmore Cottesmore 3648
validate="one_to_one" makes merge() raise if either side repeats a key, which catches a different joining mistake at the same moment.
Example 3: spatial join with named suffixes, then a second join
This names the shared columns by source and clears the index column before the result is joined again.
import geopandas as gpd
import pandas as pd
places = gpd.read_file("data/places.gpkg")
wards = gpd.read_file("data/wards.gpkg").merge(
pd.read_csv("data/ward_population.csv")[["code", "population"]], on="code")
joined = gpd.sjoin(places, wards, how="left", predicate="within", lsuffix="place", rsuffix="ward")
joined = joined.drop(columns="index_ward")
print(joined.groupby("name_ward")["name_place"].count().sort_values(ascending=False).head(5))
print(joined["name_ward"].isna().sum(), "places outside every ward")
name_ward
Braunston & Martinsthorpe 13
Exton 7
Ryhall & Casterton 7
Lyddington 7
Ketton 7
Name: name_place, dtype: int64
2 places outside every ward
With how="left" every place is kept, matched or not. Two are outside every ward — Allexton and Borderville, 106 m and 54 m beyond the generalised ward boundaries, because Geofabrik cuts its extracts with a polygon a little larger than the county — so their ward columns are missing, and population_ward became a float column to hold the gap.
Example 4: two GeoDataFrames and geometry_x
Merging two GeoDataFrames that both carry a geometry column suffixes the geometry like any other column — and the result no longer has a working active geometry:
import geopandas as gpd
wards = gpd.read_file("data/wards.gpkg")
centroids = wards.set_geometry(wards.to_crs(27700).centroid.to_crs(4326))[["code", "geometry"]]
both = wards.merge(centroids, on="code")
print(type(both).__name__, both.columns.tolist())
try:
both.geometry
except AttributeError as err:
print("AttributeError:", str(err).splitlines()[0])
fixed = wards.merge(centroids.rename(columns={"geometry": "centroid"}), on="code")
print(fixed.geometry.name, fixed.geom_type.iloc[0], "| centroid column:", fixed["centroid"].geom_type.iloc[0])
GeoDataFrame ['code', 'name', 'geometry_x', 'geometry_y']
AttributeError: You are calling a geospatial method on the GeoDataFrame, but the active geometry column ('geometry') is not present.
geometry Polygon | centroid column: Point
Rename the second geometry column before merging (or drop it) so the left one stays the active geometry.
Why pandas refuses ambiguous columns
When you combine two tables, pandas places columns from both inputs side by side in the result. If both inputs contain a column with the same name, the result would have two columns called, for example, name. That is ambiguous: later code that asks for df["name"] would not know which one to return.
pandas never keeps one copy and drops the other. What it does instead depends on the call. merge() and sjoin() have default suffixes, so they rename and carry on; join() has none, so it raises ValueError: columns overlap but no suffix specified and lists the colliding names. Passing empty suffixes to merge() puts it in the same position as join(), and it raises too.
You can resolve it in two ways. Either tell pandas how to rename the duplicates, with suffixes= on merge(), lsuffix/rsuffix on join() and sjoin(), or remove the overlap yourself by renaming or dropping columns before the join. The join key is not a problem: merge(on=...) makes it one column, and join() takes the right side's key from its index.
The error is the helpful case. The Rutland wards show why: the silent _x/_y rename from merge() produced a result with two ward names that disagree for one ward, and nothing said so.
sjoin() adds one wrinkle: it appends the right frame's index as a column named index_right (or index_<rsuffix>), and refuses to run on a frame that already has an index_right column, so a workflow that chains spatial joins drops or renames it between steps.
index_right and suffixes that stack up
sjoinalways addsindex_right: Even when no attribute columns overlap,sjoin()creates anindex_rightcolumn. Drop it before a secondsjoin()with default suffixes, which otherwise fails with'index_right' cannot be a column name in the frames being joined.- Repeated merges stack up, then fail: Merging the census onto the wards once gives
name_x/name_y; merging it a second time adds a plainnamebeside them and splitspopulationintopopulation_x/population_y, and a third time raises aMergeErrorbecause the suffixes would duplicatename_xandname_y. Rename between joins instead of relying on defaults. .joinvs.mergedifferences:.merge()joins on columns and usessuffixes=..join()joins on the right frame's index and useslsuffix/rsuffix. They are not interchangeable argument-for-argument.- Only non-key columns trigger the error: The column named in
on=is shared deliberately and is merged into one. The error lists the other shared columns. - Empty-string suffixes still collide:
suffixes=("", "")and(None, None)both raise the error when real duplicates exist. One empty suffix is fine. - Geometry column on both sides: Merging two GeoDataFrames that both have
geometryproducesgeometry_x/geometry_yand a GeoDataFrame whose active geometry column no longer exists (Example 4). Rename or drop the second geometry before merging, or usesjoin()for a spatial combination.
Internal links
- How to Join Attribute Data to a GeoDataFrame in Python
- How to Perform a Spatial Join in Python (GeoPandas)
- Spatial Join Returns Empty Results in GeoPandas: How to Fix It
- How to Select Features by Location in GeoPandas
- How to Aggregate Spatial Data by Region in GeoPandas
FAQ
Why does merge() complain about overlapping columns but not the join key?
The join key named in on= is merged into a single shared column, so it is not ambiguous. The error lists only the other columns that appear in both frames with the same name. Note that merge() only raises it when you pass empty suffixes; by default it renames the pair to _x and _y.
What is the difference between suffixes and lsuffix/rsuffix?
merge() takes a single suffixes=("_left", "_right") tuple, while join() takes separate lsuffix and rsuffix string arguments. They do the same thing but belong to different methods. sjoin() takes lsuffix and rsuffix too, without the leading underscore — rsuffix="ward" gives name_ward.
How do I remove the index_right column after sjoin()?
Drop it with joined = joined.drop(columns=["index_right"]). It holds the matched index from the right layer, which you usually do not need once the join is done.
Should I add suffixes or drop the duplicate columns?
Drop or rename when one side's column is redundant, since that keeps the result narrow and readable. Use suffixes when you genuinely need both versions of the column in the output.
Does concat raise the same overlapping-columns error?
No. pd.concat() stacks frames and allows duplicate column labels, so it will not raise this error. The error comes from join(), and from merge() with empty suffixes, which place columns side by side and require unique names.
How do I rename suffixed columns like name_left and name_right after the join?
Use .rename(columns={...}) once the join is done, for example merged.rename(columns={"name_x": "ward_name", "name_y": "census_name"}). This gives the result readable labels instead of generated suffixes.
Why do I get geometry_x and geometry_y after merging two GeoDataFrames?
Both inputs carried a geometry column, so merge() suffixed them like any other duplicate, and the result's active geometry column, geometry, no longer exists. Keep geometry on one side only by selecting non-geometry columns from the other frame before merging, or use sjoin() for a spatial combination.
Can I keep the right layer's index from sjoin() instead of dropping index_right?
Yes. The index_right column holds the matched index from the right layer, so you can keep it and pass rsuffix="ward" so it is called index_ward, or rename it to something meaningful if you need to trace matches back. Drop it only when it is not useful downstream.