CODEWITHSUNDEEP
pythonpandasdataframe
user517696
user517696

Reputation: 2672

How to merge two dataframes by using a common column?

I have two dataframes namely high_scores1 and high_scores2 like this:

match_id    inn1    batting_team    bowling_team    runs1
1              1    KKR                 RCB         222
2              1    CSK                 KXIP        240
3              1    RR                  DD          129
4              1    MI                  RCB         165


match_id    inn2    batting_team    bowling_team    runs2
    1         2     RCB              KKR            82
    2         2     KXIP             CSK            207
    3         2     DD               RR             132
    4         2     RCB              MI             166

Now i need to merge these two using the column match_id such that the new dataframe looks something like this:

match_id  inn   batting_team    bowling_team    runs1   inn2  runs2
1          1    KKR                 RCB         222     2     82
2          1    CSK                 KXIP        240     2     207
3          1    RR                  DD          129     2     132
4          1    MI                  RCB         165     2     166

I tried the following code:

high_scores1[['match_id','inn1','batting_team','bowling_team','runs1']].merge(high_scores2, left_on = 'match_id', right_on = 'match_id', how = 'left')

But it did not work. How do i merge the dataframes?

Upvotes: 4

Views: 174

Answers (2)

miradulo
miradulo

Reputation: 29690

Just merge on a subset of the second DataFrame's columns with

high_scores1.merge(high_scores2[['match_id','inn2', 'runs2']], on='match_id')

Demo

>>> high_scores1.merge(high_scores2[['match_id','inn2', 'runs2']], on='match_id')
  batting_team bowling_team  inn1  match_id  runs1  inn2  runs2
0          KKR          RCB     1         1    222     2     82
1          CSK         KXIP     1         2    240     2    207
2           RR           DD     1         3    129     2    132
3           MI          RCB     1         4    165     2    166

Upvotes: 2

Vaishali
Vaishali

Reputation: 38415

You need

scores = high_scores1.merge(high_scores2[['match_id', 'inn2', 'runs2']], on = 'match_id')

Upvotes: 2

Related Questions