DataFrame.merge() doesn't check if new suffixed column names clash with existing column names

[andyloop]

Code Sample

# Four DataFrames with same column names
# column_0 and column_3 contain values
# column_1 and column_2 are IDs
# objective is to merge all 4 DFs and then drop / rename columns at the end
columns = ['column_{}'.format(i) for i in range(4)]
df1 = pd.DataFrame(columns=columns)
df2 = pd.DataFrame(columns=columns)
df3 = pd.DataFrame(columns=columns)
df4 = pd.DataFrame(columns=columns)
np.random.seed(0)
for col in columns:
    df1[col] = np.random.randint(0, 3, size=5)
    df2[col] = np.random.randint(0, 3, size=5)
    df3[col] = np.random.randint(0, 3, size=5)
    df4[col] = np.random.randint(0, 3, size=5)
merge_df = (df1
            .merge(df2, on=['column_1', 'column_2'], how='outer')
            .merge(df3, on=['column_1', 'column_2'], how='outer')
            .merge(df4, on=['column_1', 'column_2'], how='outer')
           )
merge_df.columns
# Output:
# Index(['column_0_x', 'column_1', 'column_2', 'column_3_x', 'column_0_y',
#       'column_3_y', 'column_0_x', 'column_3_x', 'column_0_y', 'column_3_y'],
#      dtype='object')
merge_df.head()
#  Output:
#    column_0_x  column_1  column_2  column_3_x  column_0_y  column_3_y  \
# 0         0.0         0         2         1.0         0.0         1.0   
# 1         0.0         0         2         2.0         0.0         1.0   
# 2         1.0         1         0         2.0         0.0         2.0   
# 3         1.0         1         0         0.0         0.0         2.0   
# 4         1.0         0         0         2.0         NaN         NaN   

#   column_0_x  column_3_x  column_0_y  column_3_y  
# 0         NaN         NaN         NaN         NaN  
# 1         NaN         NaN         NaN         NaN  
# 2         0.0         0.0         NaN         NaN  
# 3         0.0         0.0         NaN         NaN  
# 4         1.0         2.0         1.0         0.0  

Problem description

Merging 4 DataFrames with same column names; using default suffix settings and no drops / renames in between merges (e.g. doing all 3 merges on one line).

The third merge creates duplicate column names because suffixes are appended which have already been used.

Expected Output

When adding suffixes, the method should check whether the new column names ('column_0_x', 'column_1_y', etc.) already exist in the DataFrame. This could either raise an error / warning, or add another suffix in format 'column_0_x_x'.

Output of pd.show_versions()

INSTALLED VERSIONS

commit: None
python: 3.6.6.final.0
python-bits: 64
OS: Darwin
OS-release: 17.7.0
machine: x86_64
processor: i386
byteorder: little
LC_ALL: None
LANG: en_GB.UTF-8
LOCALE: en_GB.UTF-8

pandas: 0.23.4
pytest: 3.8.0
pip: 10.0.1
setuptools: 40.2.0
Cython: 0.28.5
numpy: 1.15.1
scipy: 1.1.0
pyarrow: None
xarray: None
IPython: 6.5.0
sphinx: 1.7.9
patsy: 0.5.0
dateutil: 2.7.3
pytz: 2018.5
blosc: None
bottleneck: 1.2.1
tables: 3.4.4
numexpr: 2.6.8
feather: None
matplotlib: 2.2.3
openpyxl: 2.5.6
xlrd: 1.1.0
xlwt: 1.2.0
xlsxwriter: 1.1.0
lxml: 4.2.5
bs4: 4.6.3
html5lib: 1.0.1
sqlalchemy: 1.2.11
pymysql: None
psycopg2: None
jinja2: 2.10
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None

[simonjayhawkins]

confirmation that I can replicate this issue on master.

[phofl]

What should we do here? I think raising an error is not a good option here, because the suffixes are generated on the fly and should work independent from existing column names. Should we add an additional suffix to the new column or should we use another letter (like z) if there is already a column name which would equal a auto generated column name?

[simonjayhawkins]

What should we do here? I think raising an error is not a good option here, because the suffixes are generated on the fly and should work independent from existing column names.

The suffixes are the defaults for the suffixes parameter for pd.merge,

pandas/pandas/core/reshape/merge.py

Lines 59 to 73 in 08a0cb0

	def merge(
	left,
	right,
	how: str = "inner",
	on=None,
	left_on=None,
	right_on=None,
	left_index: bool = False,
	right_index: bool = False,
	sort: bool = False,
	suffixes=("_x", "_y"),
	copy: bool = True,
	indicator: bool = False,
	validate=None,
	) -> "DataFrame":

raising on duplicates probably makes more sense and alerts the user to pass appropriate suffixes for the problem in hand, instead of relying on the defaults.

maybe, to avoid breaking changes, we could add an additional errors parameter to pd.merge with 'ignore', 'raise' options to allow/disallow duplicate columns labels being generated.