Pandas – pd.merge() – How to merge dataframe in pandas.

In this post, you will learn –

1 . What is pd.merge() in pandas?

2 . How to do inner join in pandas?

3 . How to do left join in pandas?

4 . How to do right join in pandas?

5 . How to do outer join in pandas?

6 . What does left_on and right_on does?

1 . What is pd.merge() in pandas?

pd.merge() method in pandas let’s you do SQL type joins in pandas. You can combine dataframes using inner, left, right, outer joins. It is useful when the data for the analysis are not in one dataframe but spread across multiple dataframes.

2 . How to do inner join in pandas?

Let’ create a dataframe to work with.

sales = pd.DataFrame({"Date":['2021-11-01', '2021-11-01', '2021-11-02','2021-11-02'],
                     "Product ID":[1, 2, 1 , 2],
                     "Branch ID": [100, 200, 100, 200 ],
                     "Price":[10, 15, 12, 20],
                     "Quantity":[120, 450, 300, 500 ]})

branch = pd.DataFrame({"Branch ID":[100, 200, 300],
                      "City":["Mumbai","Pune","Banglore"]})

Sales –

Branch –

What is Inner Join ?

Inner join only return the matching rows between two tables and non matching rows gets eliminated.

How to perform left join in pandas –

inner_join = pd.merge(left=sales,right=branch, how="inner",on="Branch ID")
inner_join

As you can see that there is no data relate to branch id 300 in the left sales table so, it’s gets eliminated when we did inner join. Only matching rows between the two tables are returned.

The on parameter tells pandas on which column we want to join the tables, since Branch ID is a common column between these two dataframe so we specify this column to join on and the how parameter tells how we want to join the tables. The left and right tells pandas which dataframe is the left table and which one is the right table.

3 . How to do left join in pandas?

Left join returns all the matching rows between both the tables as well as non matching rows from the left table.

How to perform left join in pandas –

To do a left join we have to set how=’left’ in the merge method and everything else will remain same.

left_join = pd.merge(sales, branch, how="left", on="Branch ID")
left_join

4 . How to do right join in pandas?

Right join returns all the matching rows between both the tables as well as all non matching rows from the right table.

How to perform right join in pandas –

right_join = pd.merge(left=sales, right=branch,how="right",on="Branch ID")
right_join

Since the left dataframe does not have any records for the Branch ID 300, it get filled with missing values (NaN).

5 . How to do outer join in Pandas?

Outer join returns all the matching rows from the left table and right table as well as all non matching rows from both the table.

How to perform outer join in pandas –

outer_join = pd.merge(left=sales,right=branch,how="outer", on="Branch ID")
outer_join

6. What does left_on and right_on do?

Sometimes the columns that you want to join on have different names in the tables. Let’s say the name of branch id in the left table is “Branch ID” and in the right table it is named as just ID. Now we can not use the on Parameter as column names are different in both the dataframes. This is where we can use the left_on and right_on parameter to combine the dataframes.

# rename column
branch.rename(columns={"Branch ID": "ID"}, inplace=True)

# join both the dataframe
outer_join = pd.merge(sales, branch, how="outer", left_on="Branch ID", right_on="ID")
outer_join