How to Fix: columns overlap but no suffix specified

In this article, we will fix the error: columns overlap but no suffix specified in Python.

This error occurs when we join two pandas data frames that have one or more same columns but there is no suffix specified to differentiate them.

Error:

ValueError: columns overlap but no suffix specified

Case of this error occurrence by an example:

Python3

# importing pandas
import pandas as pd
# importing numpy
import numpy as np


sepal_length = [5.1, 4.9, 4.7, 4.6, 5.0,
                5.4, 4.2, 5.3, 4.4, 4.8]
petal_length = [3.3, 4.6, 4.7, 5.6, 6.7,
                5.0, 4.8, 4.1, 3.6, 4.4]

# numpy array of length 7
petal_width = [3.6, 5.6, 5.4, 4.6, 4.4,
               5.0, 4.9, 5.6, 5.2, 4.4]

# DataFrame with 2 columns of length 10
df1 = pd.DataFrame({'sepal_length(cm)': sepal_length,
                    'petal_length(cm)': petal_length})

df2 = pd.DataFrame({'sepal_length(cm)': sepal_length,
                    'petal_width(cm)': petal_width})

print(df1.join(df2))

Output:

ValueError: columns overlap but no suffix specified: Index([‘sepal_length(cm)’], dtype=’object’)

Reason for the error :

The data frames df1 and df2 have the common column sepal_length(cm) that is the intersection of two data frames is not empty. To check if there are any intersection columns :

print(df1.columns.intersection(df2.columns))

Output:

Index(['sepal_length(cm)'], dtype='object')

sepal_length(cm) is the intersection column here.

Fixing the error:

This error can be fixed by using the join() or merge() method on the two data frames.

Method1 : Using join() method

The suffixes should be given while using the join method to avoid this error. By default, it does inner join on two tables. This can be changed by mentioning the parameters in join() function.

Syntax:

df1.join(df2, lsuffix='suffix_name', rsuffix='suffix_name')

where

• df1 is the first dataframe
• df2 is the second dataframe

Example:

Python3

# importing pandas
import pandas as pd
# importing numpy
import numpy as np


sepal_length = [5.1, 4.9, 4.7, 4.6, 5.0, 5.4,
                4.2, 5.3, 4.4, 4.8]
petal_length = [3.3, 4.6, 4.7, 5.6, 6.7, 5.0,
                4.8, 4.1, 3.6, 4.4]

# numpy array of length 7
petal_width = [3.6, 5.6, 5.4, 4.6, 4.4, 5.0,
               4.9, 5.6, 5.2, 4.4]

# DataFrame with 2 columns of length 10
df1 = pd.DataFrame({'sepal_length(cm)': sepal_length,
                    'petal_length(cm)': petal_length})

df2 = pd.DataFrame({'sepal_length(cm)': sepal_length,
                    'petal_width(cm)': petal_width})

print(df1.join(df2, lsuffix='_left', rsuffix='_right'))

Output:

Method 2:  Using merge() method

This merge() method considers the common column in the 2 data frames and drops the common column in one of the data frames.

Syntax:

pd.merge(df1,df2, how='join_type', on=None, left_on= None, 
right_on = None, left_index =boolean, 
right_index=boolean, sort=boolean)

where,

• df1 is the first dataframe
• df2 is the second dataframe

The type of joining and the names of left and right data frames common columns can also be mentioned as parameters in the merge() function.

Example:

Python3

# importing pandas
import pandas as pd
# importing numpy
import numpy as np


sepal_length = [5.1, 4.9, 4.7, 4.6, 5.0, 5.4,
                4.2, 5.3, 4.4, 4.8]
petal_length = [3.3, 4.6, 4.7, 5.6, 6.7, 5.0,
                4.8, 4.1, 3.6, 4.4]

# numpy array of length 7
petal_width = [3.6, 5.6, 5.4, 4.6, 4.4, 5.0,
               4.9, 5.6, 5.2, 4.4]

# DataFrame with 2 columns of length 10
df1 = pd.DataFrame({'sepal_length(cm)': sepal_length,
                    'petal_length(cm)': petal_length})

df2 = pd.DataFrame({'sepal_length(cm)': sepal_length,
                    'petal_width(cm)': petal_width})

print(pd.merge(df1, df2))

Output: