Merge two Pandas DataFrames on certain columns

Let's learn how to merge two Pandas DataFrames on certain columns using merge function.

The merge function in Pandas is used to combine two DataFrames based on a common column or index.

merge Function Syntax: DataFrame.merge(right, how='inner', on=None, left_on=None, right_on=None, left_index=False, right_index=False, sort=False, copy=True, indicator=False, validate=None)

The merge function returns a new DataFrame containing the merged data.

Merge Two Pandas DataFrames with Inner Join

An inner join combines only the rows that have matching values in both DataFrames. In this example, we will merge df1 and df2 on the 'Name' column using an inner join:

Output:

Merge Two DataFrames with Left Join

A left join retains all rows from the left DataFrame and includes matching rows from the right DataFrame. If there’s no match, it fills with NaN:

Output:

Merge Two DataFrames with Right Join

A right join includes all rows from the right DataFrame and the matching rows from the left DataFrame. Non-matching rows from the left DataFrame will be filled with NaN:

Output:

 Name Marks Grade Rank Gender
0 Raju 80.0 A 3 Male
1 Divya NaN A 1 Female
2 Geeta 75.0 B 4 Female
3 Sita 88.0 A 2 Female

Merge Two DataFrames with Outer Join

An outer join combines all rows from both DataFrames, filling in NaN where there’s no match:

Output:

 Name Marks Grade Rank Gender
0 Divya NaN A 1.0 Female
1 Geeta 75.0 B 4.0 Female
2 Raju 80.0 A 3.0 Male
3 Rani 90.0 NaN NaN NaN
4 Sita 88.0 A 2.0 Female
5 Sohit 59.0 NaN NaN NaN

Merge Two DataFrames Using Concatenation

Concatenation combines two DataFrames along a particular axis. In this case, we'll combine df1 and df2 horizontally (side by side):

Output:

 Name Marks Name Grade Rank Gender
0 Raju 80 Raju A 3.0 Male
1 Rani 90 Divya A 1.0 Female
2 Geeta 75 Geeta B 4.0 Female
3 Sita 88 Sita A 2.0 Female
4 Sohit 59 NaN NaN NaN NaN

Merge Two DataFrames Using a Column Subset

You can merge a specific subset of columns from one DataFrame with another using the merge function. Here's an example where we merge the 'Marks' column of df1 with df2:

Output: