How to remove duplication of columns names using Pandas Merge function

user_16746676 0
Viewed 58

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')
pandas dataframe merge python
1 Answers

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
user_154978880
Related