Removing extra info after merging two dataframes

Question

The following code adds x after Name and Price as well as two additional columns Name_y and Price_y. How can I remove these items?

import pandas as pd df = pd.DataFrame ({ 'IP':['1.1.1.1','2.2.2.2','3.3.3.3','4.4.4.4','5.5.5.5'], 'ID':['101','202','303','404','505'], 'Name':['aqua','noctua','ytube','tech','logi'], 'Price':[100,200,300,400,500] }) df1 = pd.DataFrame ({ 'IP':['1.1.1.1','2.2.2.2','3.3.3.3','4.4.4.4','6.6.6.6'], 'ID':['101','202','303','404','606'], 'Name':['atlas','noctua','ytube','tech','smash'], 'Price':[600,700,800,900,990] }) print(df) IP ID Name Price 0 1.1.1.1 101 aqua 100 1 2.2.2.2 202 noctua 200 2 3.3.3.3 303 ytube 300 3 4.4.4.4 404 tech 400 4 5.5.5.5 505 logi 500 print(df1) IP ID Name Price 0 1.1.1.1 101 atlas 600 1 2.2.2.2 202 noctua 700 2 3.3.3.3 303 ytube 800 3 4.4.4.4 404 tech 900 4 6.6.6.6 606 smash 990 new=df1.merge(df,indicator=True,how='left', on=['IP', 'ID']).loc[lambda x : x['_merge']=='left_only'].drop('_merge',1) print(new) IP ID Name_x Price_x Name_y Price_y 4 6.6.6.6 606 smash 990 NaN NaN

The expected result is:

 IP ID Name Price 4 6.6.6.6 606 smash 990

I simply don't know what needs to be changed... Thank you!

just as an aside, am I right in saying that u r looking for items in df1 where IP and ID are not in df? — sammywemmy
– sammywemmy, Commented Apr 14, 2020 at 11:48

jezrael · Accepted Answer · 2020-04-14 11:24:20Z

Idea is filter only columns for merge, here IP and ID for df:

new=(df1.merge(df[['IP','ID']],indicator=True,how='left', on=['IP', 'ID']) .loc[lambda x : x['_merge']=='left_only'] .drop('_merge',1))

Or if removed on parameter then merge by intersection of columns names, here IP, ID:

new=(df1.merge(df[['IP','ID']],indicator=True,how='left') .loc[lambda x : x['_merge']=='left_only'] .drop('_merge',1))

print(new) IP ID Name Price 4 6.6.6.6 606 smash 990

Collectives™ on Stack Overflow

Removing extra info after merging two dataframes

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related