Copy rows of one data frame to another if values of a particular column match in both data frames

Question

I provide here sample data to explain my problem as the original data set is large:

import pandas as pd

data_a = {'Buyer':['Company1','Company2','Company3','Company4','Company5','Company6','Company7','Company8'], 
          'Seller':['Company9','Company10','Company11','Company12','Company13','Company14','Company15',
                    'Company16']}
a_df = pd.DataFrame(data_a)

data_b = {'Buyer':['Company7','Company2','Company1','Company3','Company5'], 
          'Seller':['Company15','Company7','Company9','Company11','Company10'], 
          'Company_Number':[1,2,3,4,5],'Date':['01-01-11','02-02-12','03-03-13','04-04-14','05-05-15'],
          'Deal':['Success','Failure','Success','Success','Ongoing']}
b_df = pd.DataFrame(data_b)
print(b_df)

Output from console:

a_df = 
      Buyer     Seller
0  Company1   Company9
1  Company2  Company10
2  Company3  Company11
3  Company4  Company12
4  Company5  Company13
5  Company6  Company14
6  Company7  Company15
7  Company8  Company16

b_df = 
      Buyer     Seller  Company_Number      Date     Deal
0  Company7  Company15               1  01-01-11  Success
1  Company2   Company7               2  02-02-12  Failure
2  Company1   Company9               3  03-03-13  Success
3  Company3  Company11               4  04-04-14  Success
4  Company5  Company10               5  05-05-15  Ongoing

Now I want to copy the rows 'Company_Number', 'Date' & 'Deal' from the data frame 'b_df' to 'a_df' if the 'Buyer' & 'Seller' match. Note that the match index need not be the same in both data frames. The expected result should be as follows:

a_df =
      Buyer     Seller Company_Number      Date     Deal
0  Company1   Company9              3  03-03-13  Success
1  Company2  Company10            NaN       NaN      NaN
2  Company3  Company11              4  04-04-14  Success
3  Company4  Company12            NaN       NaN      NaN
4  Company5  Company13            NaN       NaN      NaN
5  Company6  Company14            NaN       NaN      NaN
6  Company7  Company15              1  01-01-11  Success
7  Company8  Company16            NaN       NaN      NaN

score 1 · Accepted Answer · answered Nov 11 '19 at 14:28

You can use dataFrame.merge() function for this.

pd.merge(a_df,b_df,how='left')

OutPut :

      Buyer     Seller  Company_Number      Date     Deal
0  Company1   Company9             3.0  03-03-13  Success
1  Company2  Company10             NaN       NaN      NaN
2  Company3  Company11             4.0  04-04-14  Success
3  Company4  Company12             NaN       NaN      NaN
4  Company5  Company13             NaN       NaN      NaN
5  Company6  Company14             NaN       NaN      NaN
6  Company7  Company15             1.0  01-01-11  Success
7  Company8  Company16             NaN       NaN      NaN

Copy rows of one data frame to another if values of a particular column match in both data frames

1 Answers1