Pandas DataFrame is a two-dimensional (2D) data structure thatis aligned in a tabular format. These DataFrames can be combined using different methods such as concat (), merge (), and joins. Pandas have high performance, and full-featured join operations that are resembled with SQL relational database. Using the merge function, join operations can be implemented between DataFrames objects.
We will explore the uses of merge function, concat function, and different types of joins operations in Pandas python in this article. All examples will be executed through the pycharm editor. Let’s start with the details!
Use of Merge Function
The basic commonly used syntax of merge () function is given-below:
pd.merge(df_obj1, df_obj2, how=‘inner’, on=None, left_on=None, right_on=None)
Let’s explain the details of the parameters:
The first two df_obj1 and df_obj2 arguments are the names of the DataFrame objects or tables.
The “how” parameter is used for different types of join operations such as “left, right, outer, and inner”. The merge function uses “inner” join operation by default.
The argument “on” contains the column name on which the join operation is performed. This column must be present in both DataFrame objects.
In the “left_on” and “right_on” arguments, “left_on” is the name of the column name as the key in the left DataFrame. The “right_on” is the name of the column used as a key from the right DataFrame.
To elaborate on the concept of joining DataFrames, we have taken two DataFrame objects- product and customer. The following details are present in the product DataFrame:
product=pd.DataFrame({
‘Product_ID’:[101,102,103,104,105,106,107],
‘Product_Name’:[‘headphones’,‘Bag’,‘Shoes’,‘Smartphone’,‘Teeth brush’,‘wrist watch’,‘Laptop’],
‘Category’:[‘Electronics’,‘Fashion’,‘Fashion’,‘Electronics’,‘Grocery’,‘Fashion’,‘Electronics’],
‘Price’:[300.0,1000.50,2000.0,21999.0,145.0,1500.0,90999.0],
‘Seller_City’:[‘Islamabad’,‘Lahore’,‘Karachi’,‘Rawalpindi’,‘Islamabad’,‘Karachi’,‘Faisalabad’]
})
The customer DataFrame contains the following details:
customer=pd.DataFrame({
‘ID’:[1,2,3,4,5,6,7,8,9],
‘Customer_Name’:[‘Sara’,‘Sana’,‘Ali’,‘Raees’,‘Mahwish’,‘Umar’,‘Mirha’,‘Asif’,‘Maria’],
‘Age’:[20,21,15,10,31,52,15,18,16],
‘Product_ID’:[101,0,106,0,103,104,0,0,107],
‘Product_Purchased’:[‘headphones’,‘NA’,‘wrist watch’,‘NA’,‘Shoes’,‘Smartphone’,‘NA’,‘NA’,‘Laptop’],
‘Customer_City’:[‘Lahore’,‘Islamabad’,‘Faisalabad’,‘Karachi’,‘Karachi’,‘Islamabad’,‘Rawalpindi’,‘Islamabad’,
‘Lahore’]
})
Join DataFrames on a Key
We can easily find products sold online and the customers who purchased them. So, based on a key “Product_ID”, we have performed inner join operation on both DataFrames as follows:
# import Pandas library
import pandas as pd
product=pd.DataFrame({
‘Product_ID’:[101,102,103,104,105,106,107],
‘Product_Name’:[‘headphones’,‘Bag’,‘Shoes’,‘Smartphone’,‘Teeth brush’,‘wrist watch’,‘Laptop’],
‘Category’:[‘Electronics’,‘Fashion’,‘Fashion’,‘Electronics’,‘Grocery’,‘Fashion’,‘Electronics’],
‘Price’:[300.0,1000.50,2000.0,21999.0,145.0,1500.0,90999.0],
‘Seller_City’:[‘Islamabad’,‘Lahore’,‘Karachi’,‘Rawalpindi’,‘Islamabad’,‘Karachi’,‘Faisalabad’]
})
customer=pd.DataFrame({
‘ID’:[1,2,3,4,5,6,7,8,9],
‘Customer_Name’:[‘Sara’,‘Sana’,‘Ali’,‘Raees’,‘Mahwish’,‘Umar’,‘Mirha’,‘Asif’,‘Maria’],
‘Age’:[20,21,15,10,31,52,15,18,16],
‘Product_ID’:[101,0,106,0,103,104,0,0,107],
‘Product_Purchased’:[‘headphones’,‘NA’,‘wrist watch’,‘NA’,‘Shoes’,‘Smartphone’,‘NA’,‘NA’,‘Laptop’],
‘City’:[‘Lahore’,‘Islamabad’,‘Faisalabad’,‘Karachi’,‘Karachi’,‘Islamabad’,‘Rawalpindi’,‘Islamabad’,
‘Lahore’]
})
print (pd.merge(product,customer,on=‘Product_ID’))
The following output displays on the window after running the above code:
<img data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/02/echo/word-image-18.png" data-lazy- height="997" src="data:image/svg xml,” width=”1533″>
If the columns are different in both DataFrames then, explicitly write the name of each column by the left_on and right_on arguments as follows:
import pandas as pd
product=pd.DataFrame({
‘Product_ID’:[101,102,103,104,105,106,107],
‘Product_Name’:[‘headphones’,‘Bag’,‘Shoes’,‘Smartphone’,‘Teeth brush’,‘wrist watch’,‘Laptop’],
‘Category’:[‘Electronics’,‘Fashion’,‘Fashion’,‘Electronics’,‘Grocery’,‘Fashion’,‘Electronics’],
‘Price’:[300.0,1000.50,2000.0,21999.0,145.0,1500.0,90999.0],
‘Seller_City’:[‘Islamabad’,‘Lahore’,‘Karachi’,‘Rawalpindi’,‘Islamabad’,‘Karachi’,‘Faisalabad’]
})
customer=pd.DataFrame({
‘ID’:[1,2,3,4,5,6,7,8,9],
‘Customer_Name’:[‘Sara’,‘Sana’,‘Ali’,‘Raees’,‘Mahwish’,‘Umar’,‘Mirha’,‘Asif’,‘Maria’],
‘Age’:[20,21,15,10,31,52,15,18,16],
‘Product_ID’:[101,0,106,0,103,104,0,0,107],
‘Product_Purchased’:[‘headphones’,‘NA’,‘wrist watch’,‘NA’,‘Shoes’,‘Smartphone’,‘NA’,‘NA’,‘Laptop’],
‘City’:[‘Lahore’,‘Islamabad’,‘Faisalabad’,‘Karachi’,‘Karachi’,‘Islamabad’,‘Rawalpindi’,‘Islamabad’,
‘Lahore’]
})
print (pd.merge(product,customer,left_on=‘Product_Name’,right_on=‘Product_Purchased’))
The following output will show on the screen:
<img data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/02/echo/word-image-19.png" data-lazy- height="1033" src="data:image/svg xml,” width=”1469″>
Join DataFrames using How Argument
In the following examples, we will explain four types of Joins operations on Pandas DataFrames:
- Inner Join
- Outer Join
- Left Join
- Right Join
Inner Join in Pandas
We can perform an inner join on multiple keys. To display more details about the product sales, take Product_ID, Seller_City from the product DataFrame and Product_ID, and “Customer_City” from the customer DataFrame to find that either seller or customer belongs to the same city. Implement the following lines of code:
# import Pandas library
import pandas as pd
product=pd.DataFrame({
‘Product_ID’:[101,102,103,104,105,106,107],
‘Product_Name’:[‘headphones’,‘Bag’,‘Shoes’,‘Smartphone’,‘Teeth brush’,‘wrist watch’,‘Laptop’],
‘Category’:[‘Electronics’,‘Fashion’,‘Fashion’,‘Electronics’,‘Grocery’,‘Fashion’,‘Electronics’],
‘Price’:[300.0,1000.50,2000.0,21999.0,145.0,1500.0,90999.0],
‘Seller_City’:[‘Islamabad’,‘Lahore’,‘Karachi’,‘Rawalpindi’,‘Islamabad’,‘Karachi’,‘Faisalabad’]
})
customer=pd.DataFrame({
‘ID’:[1,2,3,4,5,6,7,8,9],
‘Customer_Name’:[‘Sara’,‘Sana’,‘Ali’,‘Raees’,‘Mahwish’,‘Umar’,‘Mirha’,‘Asif’,‘Maria’],
‘Age’:[20,21,15,10,31,52,15,18,16],
‘Product_ID’:[101,0,106,0,103,104,0,0,107],
‘Product_Purchased’:[‘headphones’,‘NA’,‘wrist watch’,‘NA’,‘Shoes’,‘Smartphone’,‘NA’,‘NA’,‘Laptop’],
‘Customer_City’:[‘Lahore’,‘Islamabad’,‘Faisalabad’,‘Karachi’,‘Karachi’,‘Islamabad’,‘Rawalpindi’,‘Islamabad’,
‘Lahore’]
})
print (pd.merge(product,customer,how=‘inner’,left_on=[‘Product_ID’,‘Seller_City’],right_on=[‘Product_ID’,‘Customer_City’]))
The following result shows on the window after running the above code:
<img data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/02/echo/word-image-20.png" data-lazy- height="938" src="data:image/svg xml,” width=”1743″>
Full/outer join in Pandas
Outer joins return both right and left DataFrames values, which either have matches. So, to implement the outer join, set the “how” argument as outer. Let’s modify the above example by using the outer join concept. In the below code, it will return all values of both left and right DataFrames.
# import Pandas library
import pandas as pd
product=pd.DataFrame({
‘Product_ID’:[101,102,103,104,105,106,107],
‘Product_Name’:[‘headphones’,‘Bag’,‘Shoes’,‘Smartphone’,‘Teeth brush’,‘wrist watch’,‘Laptop’],
‘Category’:[‘Electronics’,‘Fashion’,‘Fashion’,‘Electronics’,‘Grocery’,‘Fashion’,‘Electronics’],
‘Price’:[300.0,1000.50,2000.0,21999.0,145.0,1500.0,90999.0],
‘Seller_City’:[‘Islamabad’,‘Lahore’,‘Karachi’,‘Rawalpindi’,‘Islamabad’,‘Karachi’,‘Faisalabad’]
})
customer=pd.DataFrame({
‘ID’:[1,2,3,4,5,6,7,8,9],
‘Customer_Name’:[‘Sara’,‘Sana’,‘Ali’,‘Raees’,‘Mahwish’,‘Umar’,‘Mirha’,‘Asif’,‘Maria’],
‘Age’:[20,21,15,10,31,52,15,18,16],
‘Product_ID’:[101,0,106,0,103,104,0,0,107],
‘Product_Purchased’:[‘headphones’,‘NA’,‘wrist watch’,‘NA’,‘Shoes’,‘Smartphone’,‘NA’,‘NA’,‘Laptop’],
‘Customer_City’:[‘Lahore’,‘Islamabad’,‘Faisalabad’,‘Karachi’,‘Karachi’,‘Islamabad’,‘Rawalpindi’,‘Islamabad’,
‘Lahore’]
})
print (pd.merge(product,customer,on=‘Product_ID’,how=‘outer’))
Set the indicator argument as “True”s. You will notice that the new “_merge” column is added at the end.
# import Pandas library
import pandas as pd
product=pd.DataFrame({
‘Product_ID’:[101,102,103,104,105,106,107],
‘Product_Name’:[‘headphones’,‘Bag’,‘Shoes’,‘Smartphone’,‘Teeth brush’,‘wrist watch’,‘Laptop’],
‘Category’:[‘Electronics’,‘Fashion’,‘Fashion’,‘Electronics’,‘Grocery’,‘Fashion’,‘Electronics’],
‘Price’:[300.0,1000.50,2000.0,21999.0,145.0,1500.0,90999.0],
‘Seller_City’:[‘Islamabad’,‘Lahore’,‘Karachi’,‘Rawalpindi’,‘Islamabad’,‘Karachi’,‘Faisalabad’]
})
customer=pd.DataFrame({
‘ID’:[1,2,3,4,5,6,7,8,9],
‘Customer_Name’:[‘Sara’,‘Sana’,‘Ali’,‘Raees’,‘Mahwish’,‘Umar’,‘Mirha’,‘Asif’,‘Maria’],
‘Age’:[20,21,15,10,31,52,15,18,16],
‘Product_ID’:[101,0,106,0,103,104,0,0,107],
‘Product_Purchased’:[‘headphones’,‘NA’,‘wrist watch’,‘NA’,‘Shoes’,‘Smartphone’,‘NA’,‘NA’,‘Laptop’],
‘Customer_City’:[‘Lahore’,‘Islamabad’,‘Faisalabad’,‘Karachi’,‘Karachi’,‘Islamabad’,‘Rawalpindi’,‘Islamabad’,
‘Lahore’]
})
print (pd.merge(product,customer,on=‘Product_ID’,how=‘outer’,indicator=True))
As you can see in the below screenshot, the merge column values explain which row belongs to which DataFrame.
<img data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/02/echo/word-image-22.png" data-lazy- height="1003" src="data:image/svg xml,” width=”1726″>
Left Join in Pandas
Left join only display rows of the left DataFrame. It is similar to the outer join. So, change the ‘how’ argument value with “left”. Try the following code to implement the idea of Left join:
# import Pandas library
import pandas as pd
product=pd.DataFrame({
‘Product_ID’:[101,102,103,104,105,106,107],
‘Product_Name’:[‘headphones’,‘Bag’,‘Shoes’,‘Smartphone’,‘Teeth brush’,‘wrist watch’,‘Laptop’],
‘Category’:[‘Electronics’,‘Fashion’,‘Fashion’,‘Electronics’,‘Grocery’,‘Fashion’,‘Electronics’],
‘Price’:[300.0,1000.50,2000.0,21999.0,145.0,1500.0,90999.0],
‘Seller_City’:[‘Islamabad’,‘Lahore’,‘Karachi’,‘Rawalpindi’,‘Islamabad’,‘Karachi’,‘Faisalabad’]
})
customer=pd.DataFrame({
‘ID’:[1,2,3,4,5,6,7,8,9],
‘Customer_Name’:[‘Sara’,‘Sana’,‘Ali’,‘Raees’,‘Mahwish’,‘Umar’,‘Mirha’,‘Asif’,‘Maria’],
‘Age’:[20,21,15,10,31,52,15,18,16],
‘Product_ID’:[101,0,106,0,103,104,0,0,107],
‘Product_Purchased’:[‘headphones’,‘NA’,‘wrist watch’,‘NA’,‘Shoes’,‘Smartphone’,‘NA’,‘NA’,‘Laptop’],
‘Customer_City’:[‘Lahore’,‘Islamabad’,‘Faisalabad’,‘Karachi’,‘Karachi’,‘Islamabad’,‘Rawalpindi’,‘Islamabad’,
‘Lahore’]
})
print (pd.merge(product,customer,on=‘Product_ID’,how=‘left’))
<img data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/02/echo/word-image-23.png" data-lazy- height="965" src="data:image/svg xml,” width=”1730″>
Right Join in Pandas
The right join keeps all right DataFrame rows to the right along with the rows that are also common in the left DataFrame. In this case, the “how” argument is set as the “right” value. Run the following code to implement the right join concept:
# import Pandas library
import pandas as pd
product=pd.DataFrame({
‘Product_ID’:[101,102,103,104,105,106,107],
‘Product_Name’:[‘headphones’,‘Bag’,‘Shoes’,‘Smartphone’,‘Teeth brush’,‘wrist watch’,‘Laptop’],
‘Category’:[‘Electronics’,‘Fashion’,‘Fashion’,‘Electronics’,‘Grocery’,‘Fashion’,‘Electronics’],
‘Price’:[300.0,1000.50,2000.0,21999.0,145.0,1500.0,90999.0],
‘Seller_City’:[‘Islamabad’,‘Lahore’,‘Karachi’,‘Rawalpindi’,‘Islamabad’,‘Karachi’,‘Faisalabad’]
})
customer=pd.DataFrame({
‘ID’:[1,2,3,4,5,6,7,8,9],
‘Customer_Name’:[‘Sara’,‘Sana’,‘Ali’,‘Raees’,‘Mahwish’,‘Umar’,‘Mirha’,‘Asif’,‘Maria’],
‘Age’:[20,21,15,10,31,52,15,18,16],
‘Product_ID’:[101,0,106,0,103,104,0,0,107],
‘Product_Purchased’:[‘headphones’,‘NA’,‘wrist watch’,‘NA’,‘Shoes’,‘Smartphone’,‘NA’,‘NA’,‘Laptop’],
‘Customer_City’:[‘Lahore’,‘Islamabad’,‘Faisalabad’,‘Karachi’,‘Karachi’,‘Islamabad’,‘Rawalpindi’,‘Islamabad’,
‘Lahore’]
})
print (pd.merge(product,customer,on=‘Product_ID’,how=‘right’))
In the following screenshot, you can see the result after running the above code:
<img data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/02/echo/word-image-24.png" data-lazy- height="988" src="data:image/svg xml,” width=”1719″>
Joining of DataFrames using the Concat () function
Two DataFrames can be joined using the concat function. The basic syntax of the concatenation function is given below:
pd.concat([df_obj1, df_obj_2]))
Two DataFrames objects will pass as arguments.
Let’s join both DataFrames product and customer through the concat function. Run the following lines of code to join two DataFrames:
# import Pandas library
import pandas as pd
product=pd.DataFrame({
‘Product_ID’:[101,102,103,104,105,106,107],
‘Product_Name’:[‘headphones’,‘Bag’,‘Shoes’,‘Smartphone’,‘Teeth brush’,‘wrist watch’,‘Laptop’],
‘Category’:[‘Electronics’,‘Fashion’,‘Fashion’,‘Electronics’,‘Grocery’,‘Fashion’,‘Electronics’],
‘Price’:[300.0,1000.50,2000.0,21999.0,145.0,1500.0,90999.0],
‘Seller_City’:[‘Islamabad’,‘Lahore’,‘Karachi’,‘Rawalpindi’,‘Islamabad’,‘Karachi’,‘Faisalabad’]
})
customer=pd.DataFrame({
‘ID’:[1,2,3,4,5,6,7,8,9],
‘Customer_Name’:[‘Sara’,‘Sana’,‘Ali’,‘Raees’,‘Mahwish’,‘Umar’,‘Mirha’,‘Asif’,‘Maria’],
‘Age’:[20,21,15,10,31,52,15,18,16],
‘Product_ID’:[101,0,106,0,103,104,0,0,107],
‘Product_Purchased’:[‘headphones’,‘NA’,‘wrist watch’,‘NA’,‘Shoes’,‘Smartphone’,‘NA’,‘NA’,‘Laptop’],
‘Customer_City’:[‘Lahore’,‘Islamabad’,‘Faisalabad’,‘Karachi’,‘Karachi’,‘Islamabad’,‘Rawalpindi’,‘Islamabad’,
‘Lahore’]
})
print (pd.concat([product,customer]))
<img data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/02/echo/word-image-25.png" data-lazy- height="988" src="data:image/svg xml,” width=”1721″>
Conclusion:
In this article, we have discussed the implementation of merge () function, concat () functions, and joins operation in Pandas python. Using the above methods, you can easily join two DataFrames and learned. how to implement the Join operations “inner, outer, left, and right” in Pandas. Hopefully, this tutorial will guide you in implementing the join operations on different types of DataFrames. Please let us know about your difficulties in case of any error.
About the author
<img alt="Avatar" data-lazy-src="https://secure.gravatar.com/avatar/49ac9cf0c583f1d7e06205a87f549d76?s=112&r=g" height="112" src="data:image/svg xml,” width=”112″>
Samreena Aslam
Samreena Aslam holds a master’s degree in Software Engineering. Currently, she’s working as a Freelancer & Technical writer. She’s a Linux enthusiast and has written various articles on Computer programming, different Linux flavors including Ubuntu, Debian, CentOS, and Mint.
![How to Call a Function in Python [With Examples] programming language Python](https://kirelos.com/wp-content/uploads/2023/12/26798/how-to-call-a-function-in-python-with-examples-600x450.jpg "Kirelos Blog How to Call a Function in Python [With Examples]")
