Table of Contents
If two columns overlap with each other but no suffix has been specified, it means that the columns do not have an identifiable difference between them. This can cause problems as it can make it difficult to distinguish between the two columns. To fix this issue, a suffix should be assigned to each column so that they can be easily differentiated.
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.