Row wise merge dataframes with overlapping data in python/ pandas

Question

I have two large data frames which I need to merge row-wise. These two dataframes may have a few overlapping rows.

Example:

data1
    key name               patent
    11  Alphagrep          112344
    12  Citrix             112345

data2
    Sr  name               patents
    11  Alphagrep          112344
    13  Taj                112322

I want to merge these dataframes row wise on [Key,Sr] & [patent, patents]

Which is: If row-data1[key]==row-data2[Sr] & row-data1[patent]==row-data2[patents], Merge or else append.

Result should be:

data1 + data2
    key name               patent
    11  Alphagrep          112344
    12  Citrix             112345
    13  Taj                112322

How should one do this in pandas?

Mohammad Yusuf · Accepted Answer · 2016-12-26 12:07:48Z

5

You can do something like this:

print data1, '\n', '\n', data2, '\n'
data2.columns = data1.columns.tolist()
data3 = pd.concat([data1, data2]).drop_duplicates()
print data3

Output:

   key       name  patent
0   11  Alphagrep  112344
1   12     Citrix  112345 

   Sr       name  patents
0  11  Alphagrep   112344
1  13        Taj   112322 

   key       name  patent
0   11  Alphagrep  112344
1   12     Citrix  112345
1   13        Taj  112322

answered Dec 26, 2016 at 12:07

Mohammad Yusuf

17.1k12 gold badges60 silver badges88 bronze badges

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

3 Comments

user248884 Over a year ago

This is a nice hack to get around this problem. However, say we have a situation wherein the columns cannot be homogenized ( number of columns not equal), how would we work then? Is there a way that we provide parameters and only those columns be checked

MaxU - stand with Ukraine Over a year ago

@user248884, could you post reproducible sample data sets and a desired resulting data set? Or even better - open a new question...

Mohammad Yusuf Over a year ago

Yeah. Create a specific case and post it as a new question. As this solution works for the existing problem you may accept it :)

piRSquared · Accepted Answer · 2016-12-26 14:04:18Z

1

set_index and combine_first

c1 = ['key', 'patent']
c2 = ['Sr', 'patents']

data1.set_index(c1) \
    .combine_first(
        data2.set_index(c2).rename_axis(c1)
    ).reset_index()

   key  patent       name
0   11  112344  Alphagrep
1   12  112345     Citrix
2   13  112322        Taj

answered Dec 26, 2016 at 14:04

piRSquared

296k68 gold badges509 silver badges654 bronze badges

Collectives™ on Stack Overflow

Row wise merge dataframes with overlapping data in python/ pandas

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related