How to remove duplication of columns names using Pandas Merge function

Question

When we merge two dataframes using pandas merge function, is it possible to ensure the key(s) based on which the two dataframes are merged is not repeated twice in the result? For e.g., I tried to merge two DFs with a column named 'isin_code' in the left DF and a column named 'isin' in the right DF. Even though the column/header names are different, the values of both the columns are same. In, the eventual result though, I get to see both 'isin_code' column and 'isin' column, which I am trying to avoid.

Code used:

result = pd.merge(df1,df2[['isin','issue_date']],how='left',left_on='isin_code',right_on = 'isin')

Since the column_names are different, how is pandas suppose to know, which one you wand to keep and which needs to be dropped, so you would have to do that manually by either dropping the column you don't want or renaming the column, so that both matches and only one is retained in the output. — heretolearn
– heretolearn, Commented Aug 25, 2021 at 4:30

Henry Ecker · Accepted Answer · 2021-08-25 04:12:39Z

3

Either rename the columns to match before merge to uniform the column names and specify only on:

result = pd.merge(
    df1,
    df2[['isin', 'issue_date']].rename(columns={'isin': 'isin_code'}),
    on='isin_code',
    how='left'
)

OR drop the duplicate column after merge:

result = pd.merge(
    df1,
    df2[['isin', 'issue_date']],
    how='left',
    left_on='isin_code',
    right_on='isin'
).drop(columns='isin')

Sample DataFrames and output:

import pandas as pd

df1 = pd.DataFrame({'isin_code': [1, 2, 3], 'a': [4, 5, 6]})
df2 = pd.DataFrame({'isin': [1, 3], 'issue_date': ['2021-01-02', '2021-03-04']})

df1:

   isin_code  a
0          1  4
1          2  5
2          3  6

df2:

   isin  issue_date
0     1  2021-01-02
1     3  2021-03-04

result:

   isin_code  a  issue_date
0          1  4  2021-01-02
1          2  5         NaN
2          3  6  2021-03-04

answered Aug 25, 2021 at 4:12

Henry Ecker♦

35.8k19 gold badges48 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user16746676 Over a year ago

Thanks for the solution above. I did not want to go down the renaming columns option (i.e., option 1 you highlighted), but the second one seems sensible enough.

Collectives™ on Stack Overflow

How to remove duplication of columns names using Pandas Merge function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related