How to Fix: columns overlap but no suffix specified


One error you may encounter when using pandas is:

ValueError: columns overlap but no suffix specified: Index(['column'], dtype='object')

This error occurs when you attempt to join together two data frames that share at least one common column name and a suffix is not provided for either the left or right data frame to distinguish between the columns in the new data frame.

There are two ways to fix this error:

Solution 1: Provide suffix names.

df1.join(df2, how = 'left', lsuffix='left', rsuffix='right')

Solution 2: Use the merge function instead.

df1.merge(df2, how = 'left')

The following example shows how to fix this error in practice.

How to Reproduce the Error

Suppose we attempt to join together the following two data frames:

import pandas as pd

#create first data frame
df1 = pd.DataFrame({'player': ['A', 'B', 'C', 'D', 'E', 'F'],
                    'points': [5, 7, 7, 9, 12, 9],
                    'assists': [11, 8, 10, 6, 6, 5]})

#create second data frame
df2 = pd.DataFrame({'player': ['A', 'B', 'C', 'D', 'E', 'F'],
                    'rebounds': [4, 4, 6, 9, 13, 16],
                    'steals': [2, 2, 1, 4, 3, 2]})

#attempt to perform left join on data frames
df1.join(df2, how = 'left')

ValueError: columns overlap but no suffix specified: Index(['player'], dtype='object')

We receive an error because the two data frames both share the “player” column, but there is no suffix provided for either the left or right data frame to distinguish between the columns in the new data frame.

How to Fix the Error

One way to fix this error is to provide a suffix name for either the left or right data frame:

#perform left join on data frames with suffix provided
df1.join(df2, how = 'left', lsuffix='left', rsuffix='right')

        playerleft points assists playerright rebounds	steals
0	A	   5	  11	  A	      4	        2
1	B	   7	  8	  B	      4	        2
2	C	   7	  10	  C	      6	        1
3	D	   9	  6	  D	      9	        4
4	E	   12	  6	  E	     13	        3
5	F	   9	  5	  F	     16	        2

Another way to fix this error is to simply use the merge() function, which doesn’t encounter this problem when joining two data frames together:

#merge two data frames
df1.merge(df2, how = 'left')

	player	points	assists	rebounds steals
0	A	5	11	4	 2
1	B	7	8	4	 2
2	C	7	10	6	 1
3	D	9	6	9	 4
4	E	12	6	13	 3
5	F	9	5	16	 2

Notice that the merge() function simply drops any names from the second data frame that already belong to the first data frame.

Additional Resources

How to Merge Two Pandas DataFrames on Index
How to Merge Pandas DataFrames on Multiple Columns
How to Add a Numpy Array to a Pandas DataFrame

Leave a Reply

Your email address will not be published.