add a suffix when col names are similar

I am merging two dataframes and both of them have a col called “man”. After the join, one col is called “man_x” and the second is called “man_y”. Is it possible to append the table name or any other string instead of x, y when column names are the same?

df_merged = df['first'].merge(df['second'], 
                                        left_on=['posnr'],
                                        right_on=['fk_eakopf_posnr'],
                                        how='inner')

After this, If I add another table to this df_merged, the third man col will remain man. So it’s a bit hard to debug, which is why I would like to add proper suffixes everywhere.

Answer

Use the suffixes= parameter of .merge() to specify your desired suffixes.

suffixes list-like, default is (“_x”, “_y”)

A length-2 sequence where each element is optionally a string indicating the suffix to add to overlapping column names in left and right respectively. Pass a value of None instead of a string to indicate that the column name from left or right should be left as-is, with no suffix. At least one of the values must not be None.

The default is suffixes=('_x', '_y')

You can specify e.g. suffixes=('_df1', '_df2') for merging 2 dataframes df1 and df2 to know the source dataframes.