Please add force_suffixes to pandas.merge()

[sorenwacker]

Code Sample, a copy-pastable example if possible

import pandas as pd

A = pd.DataFrame({'colname': [1, 2]})

B = pd.DataFrame({'colname': [1, 2]})

C = pd.DataFrame({'colname': [1, 2]})

D = pd.merge(A, B, right_index=True, left_index=True, suffixes=('_A', '_B'))

print(pd.merge(D, B, right_index=True, left_index=True, suffixes=('', '_C'))

>   colname_A  colname_B  colname
> 0          1          1        1
> 1          2          2        2

Problem description

When using pandas.merge() suffixes are only added to the column-names if a column-name appears in both data frames. With more than two data frames that can lead to the above situation. The first merge adds suffixes. When the resulting table is merged with a third table the column-name does not appear twice and the suffix is not added. The suffix then needs to be added manually. This is overhead, especially when larger numbers of data-frames are merged. An option "force_suffixes" would be appreciated that ensures the suffix is added.

Expected Output

>   colname_A  colname_B  colname_C
> 0          1          1        1
> 1          2          2        2

[gfyoung]

@soerendip : Thanks for reporting this! Could you explain why you would want the suffix to be forcefully added, even if duplicates don't exist? I'm having some difficulty understanding the use-case.

[sorenwacker]

@gfyoung: Say you have several tables with the identical column names. You want to combine them into one table while keeping track of the source. As in my example above, the third table did not get the label. You could use the f.add_suffix('_some_suffix'), I suppose, and merge the table afterwards.

[ash13]

Hi,
I want to work on this, can anyone help me? I am a beginner.

[jreback]

@ash13 I would try a different issue; this request has not gotten much commentary; i am -0 on it nowhere

[ThomasProctor]

This isn't coming from nowhere - there's a stackoverflow question on it. As of October 31, 2017, it has 10 up-votes.

[gfyoung]

@ThomasProctor : Thanks for pointing that out. I'm still not convinced that we should do this. You can easily call df.rename to rename any confusing columns.

[ThomasProctor]

Here's the kludge I've been using to do this. Maybe it will help with a fix. I make no claims to it being remotely well-engineered

def merge_force_suffix(left, right, **kwargs):
    on_col = kwargs['on']
    suffix_tupple = kwargs['suffixes']
    
    def suffix_col(col, suffix):
        if col != on_col:
            return str(col) + suffix
        else:
            return col
            
    left_suffixed = left.rename(columns=lambda x: suffix_col(x, suffix_tupple[0]))
    right_suffixed = right.rename(columns=lambda x: suffix_col(x, suffix_tupple[1]))
    del kwargs['suffixes']
    return pd.merge(left_suffixed, right_suffixed, **kwargs)

[gfyoung]

Maybe it will help with a fix.

@ThomasProctor : Thanks for sharing this. I think this would be a reasonable workaround for people to use if they so need to.

[ThomasProctor]

@gfyoung My use case right now is merging together 10 or so DataFrames, all with possible redundant column names. Trying to keep track of where columns came from is just annoying.