Different Types of Joins in Pandas

Pandas is one of the popular libraries used to perform data analysis and data manipulation. There are many advanced features to work with the tabular data such as join multiple data frames into one depending upon the common columns or indices of columns.

In python, there are different types of joins available which can be performed by using the merge() function along with the how parameter of the pandas library. Following are the different joins.

• Inner Join

• Outer Join

• Left Join

• Right Join

• Cross Join

Inner Join

An Inner Join in the pandas library will return the rows which have the same values in both defined data frames and in the output. We can observe the columns of the dataframe that have the same columns. The following is the syntax for applying the inner join on the data frames.

 import pandas as pd pd.merge(dataframe1,dataframe2,how='inner' 

Where,

• pandas is the name of the library.

• pd is the alias name.

• merge is the function to perform the join operation.

• how specifies the type of join operation.

Example

In this example, we will pass two data frames to the merge() function along with the ?how' parameter by assigning inner join. Following is the code.

 import pandas as pd dic1 = {"Name": ["John","Mark","Nancy","Sita","Ram"], "Age": [20,40,78,90,38], "Language" : ["Python","Java","C","Python","Salesforce"]} dic2 = {"Name": ["John","Mark","Nancy","Sita","Ram"], "Age": [20,40,78,90,38], "class" : [10,9,12,9,5]} data1 = pd.DataFrame(dic1) print(data1) data2 = pd.DataFrame(dic2) print(data2) inner_join = pd.merge(data1,data2,how = "inner") inner_join.head() 

Output

 Name Age Language 0 John 20 Python 1 Mark 40 Java 2 Nancy 78 C 3 Sita 90 Python 4 Ram 38 Salesforce Name Age class 0 John 20 10 1 Mark 40 9 2 Nancy 78 12 3 Sita 90 9 4 Ram 38 5 

Outer Join

The outer join will return all the rows of both the data frames and if any missing data in the rows then it fills the null values. Following syntax is used for applying the outer join on data frames.

 import pandas as pd pd.merge(dataframe1,dataframe2,how='outer' 

Example

 import pandas as pd dic1 = {"Name": ["John","Mark","Nancy","Sita","Ram"], "Age": [20,40,78,90,38], "Language" : ["Python","Java","C","Python","Salesforce"]} dic2 = {"Name": ["John","Nancy","Sita","Ram"], "Age": [20,40,70,38], "class" : [10,12,9,5]} data1 = pd.DataFrame(dic1) print(data1) data2 = pd.DataFrame(dic2) print(data2) inner_join = pd.merge(data1,data2,how = "outer") inner_join.head() 

Output

 Name Age Language 0 John 20 Python 1 Mark 40 Java 2 Nancy 78 C 3 Sita 90 Python 4 Ram 38 Salesforce Name Age class 0 John 20 10 1 Nancy 40 12 2 Sita 70 9 3 Ram 38 5 

Left Join

The left join returns the entire rows of the left dataframe and all the matching rows of the right dataframe. If in case, there are no matching rows in the right data frame then the output data frame will be filled with the null values in the corresponding columns. The following is the syntax for applying the left join.

 import pandas as pd pd.merge(dataframe1,dataframe2,how='left' 

Example

Here in this example, we will apply the left join on the two input data frames then the output of the left join will be returned.

 import pandas as pd dic1 = {"Name": ["John","Mark","Nancy","Sita","Ram"], "Age": [2,40,78,90,38], "Language" : ["Python","Java","C","Python","Salesforce"]} dic2 = {"Name": ["John","Nancy","Sita","Ram"], "Age": [20,40,70,38], "class" : [10,12,9,5]} data1 = pd.DataFrame(dic1) print(data1) data2 = pd.DataFrame(dic2) print(data2) inner_join = pd.merge(data1,data2,how = "left") inner_join.head() 

Output

 Name Age Language 0 John 2 Python 1 Mark 40 Java 2 Nancy 78 C 3 Sita 90 Python 4 Ram 38 Salesforce Name Age class 0 John 20 10 1 Nancy 40 12 2 Sita 70 9 3 Ram 38 5 

Right Join

The right join returns the entire rows of the right dataframe and all the matching rows of the left dataframe. If in case there are no matching rows in the left data frame then the output data frame will be filled with the null values in the corresponding columns. The following is the syntax for applying the right join.

 import pandas as pd pd.merge(dataframe1,dataframe2,how='right' 

Example

In this example, we will apply the right join on the two input data frames then the output of the right join will be returned.

 import pandas as pd dic1 = {"Name": ["John","Mark","Nancy","Sita","Ram"], "Age": [2,40,78,90,38], "Language" : ["Python","Java","C","Python","Salesforce"]} dic2 = {"Name": ["John","Nancy","Sita","Ram"], "Age": [20,40,70,38], "class" : [10,12,9,5]} data1 = pd.DataFrame(dic1) print(data1) data2 = pd.DataFrame(dic2) print(data2) inner_join = pd.merge(data1,data2,how = "right") inner_join.head() 

Output

 Name Age Language 0 John 2 Python 1 Mark 40 Java 2 Nancy 78 C 3 Sita 90 Python 4 Ram 38 Salesforce Name Age class 0 John 20 10 1 Nancy 40 12 2 Sita 70 9 3 Ram 38 5 

Cross Join

The cross join returns the Cartesian product of the two data frames; meaning it returns all the possible combinations of rows from both data frames.