Python Joins: A Guide to Merge Pandas and Learn Different Join Methods

Abhishek Sharma Last Updated : 05 Nov, 2024
9 min read

“I have two different tables in join Python, but I’m not sure how to join them. What criteria should I consider? What are the different ways I can join these tables using merge pandas dataframes?”

Does this sound familiar? I have encountered this question plenty of times on online discussion forums. Working with one table is fairly straightforward, but things become challenging when we have data spread across two or more tables. We can use different programming languages to work with tables, but today, we will focus on Python.

Python programming already has functions for basic tasks like string joins and concatenating lists, but performing these tasks becomes much easier if we use packages. One such package is the pandas library, which you must have heard of if you are learning Python programming or working on machine learning projects.

This is where the concept of Joins comes in. I cannot emphasize the number of times I have used these Joins in Pandas! They’ve come in especially handy during data science hackathons when I needed to join multiple tables quickly. This article will mainly introduce you to the different types of joins in pandas and how to use them. It will also tell you how to deal with redundancy or duplicate values in the resulting dataframes.

Learning Objectives

  • Get acquainted with the different types of python joins in Pandas
  • Learn how to join strings in Python pandas
  • Learn how to handle redundancy and duplication in Joins

Note: If you’re new to the world of Pandas and Python, I recommend taking the below free courses:

Looking to learn SQL joins? We have you covered! Head over here to learn all about SQL joins.

How to Use Join in Python?

The join() method is like a friendly string conductor that combines individual strings into a harmonious whole. Imagine you have a group of strings (like a band), and you want to join them using a specific separator (like a musical note). That’s where join() steps in!

What Does join() Do?

  • The join() method takes all the items in an iterable (like a list or tuple) and joins them into a single string.
  • You get to choose the separator (the glue that holds the strings together).

Syntax:

Pythonresult_string = separator.join(iterable) AI-generated code. Review and use carefully.

Parameters

  • separator: A string that acts as the glue between the items.
  • iterable: Any collection of strings (e.g., a list, tuple, or even a string itself).

Understanding Python Joins

I’m sure you’re quite familiar with e-commerce sites like Amazon and Flipkart. We are bombarded by their advertisements when visiting non-related websites – that’s the power of targeted marketing!

We’ll take a simple problem from a related marketing brand here. We are given two tables – one containing product data and the other with customer-level information.

We will use these tables to understand hands-on how the different types of joins work using Merge Pandas.

Inner Join in Pandas

inner join pandas

Inner join is the most common type of join you’ll be working with. It returns a dataframe with only those rows that have common characteristics.

An inner join requires each row in the two joined dataframes to have matching column values. This is similar to the intersection of two sets.
Let’s start by importing the Pandas library:

import pandas as pd

For this tutorial, we have two dataframes – product and customer. The product dataframe contains product details like Product_ID, Product_name, Category, Price, and Seller_City. The customer dataframe contains details like id, name, age, Product_ID, Purchased_Product, and City.

Our task is to use our joining skills and generate meaningful information from the data. I encourage you to follow along with the code we’ll cover in this tutorial.

Python Code

import pandas as pd

product=pd.DataFrame({
    'Product_ID':[101,102,103,104,105,106,107],
    'Product_name':['Watch','Bag','Shoes','Smartphone','Books','Oil','Laptop'],
    'Category':['Fashion','Fashion','Fashion','Electronics','Study','Grocery','Electronics'],
    'Price':[299.0,1350.50,2999.0,14999.0,145.0,110.0,79999.0],
    'Seller_City':['Delhi','Mumbai','Chennai','Kolkata','Delhi','Chennai','Bengalore']
})

print(product)
product dataframe
customer=pd.DataFrame({
    'id':[1,2,3,4,5,6,7,8,9],
    'name':['Olivia','Aditya','Cory','Isabell','Dominic','Tyler','Samuel','Daniel','Jeremy'],
    'age':[20,25,15,10,30,65,35,18,23],
    'Product_ID':[101,0,106,0,103,104,0,0,107],
    'Purchased_Product':['Watch','NA','Oil','NA','Shoes','Smartphone','NA','NA','Laptop'],
    'City':['Mumbai','Delhi','Bangalore','Chennai','Chennai','Delhi','Kolkata','Delhi','Mumbai']
})
customer dataframe

In the above code snippet, we have passed a dict in the pandas DataFrame constructor. The dictionary has key-value pairs, where keys are Python strings, and the values are iterable objects; these could be lists, tuples, json, etc.

We want to know about all the products sold online and who purchased them. Using an inner join, we can easily do this.

The merge() function

Pandas is our friend here. By default, the merge pandas function performs an inner join. The merge function only takes series or dataframe objects as input for merging; if we pass a different data type, it will result in a typeerror. The two dataframe / series objects are passed in the merge function with a comma as the separator. For example:

pd.merge(product,customer,on='Product_ID')
inner join

The return value type of the merge function is dataframe.

There is another function in pandas called the concat function. The name suggests we can concatenate strings using this function, but it just concatenates two dataframes.

Here, I have performed inner join on the product and customer dataframes on the ‘Product_ID’ column.

But what if the column name strings are different in the two dataframes? Then, we have to mention both column names str explicitly.

‘left_on’ and ‘right_on’ are two parameters through which we can achieve this. ‘left_on’ is the name of the key in the left dataframe and ‘right_on’ in the right dataframe, so the

syntax

pd.merge(product,customer,left_on='Product_name',right_on='Purchased_Product')
inner join product

Also, we cannot pass empty strings in left_on or right_on arguments

Let’s try the above code in the live coding window below!!

import pandas as pd
product=pd.DataFrame({
    'Product_ID':[101,102,103,104,105,106,107],
    'Product_name':['Watch','Bag','Shoes','Smartphone','Books','Oil','Laptop'],
    'Category':['Fashion','Fashion','Fashion','Electronics','Study','Grocery','Electronics'],
    'Price':[299.0,1350.50,2999.0,14999.0,145.0,110.0,79999.0],
    'Seller_City':['Delhi','Mumbai','Chennai','Kolkata','Delhi','Chennai','Bengalore']
})
print("Product Dataframe: \n\n")
print(product.head())

customer=pd.DataFrame({
    'id':[1,2,3,4,5,6,7,8,9],
    'name':['Olivia','Aditya','Cory','Isabell','Dominic','Tyler','Samuel','Daniel','Jeremy'],
    'age':[20,25,15,10,30,65,35,18,23],
    'Product_ID':[101,0,106,0,103,104,0,0,107],
    'Purchased_Product':['Watch','NA','Oil','NA','Shoes','Smartphone','NA','NA','Laptop'],
    'City':['Mumbai','Delhi','Bangalore','Chennai','Chennai','Delhi','Kolkata','Delhi','Mumbai']
})
print("\n\n Customer Dataframe: \n\n")
print(customer.head())
print("\n\n Merged Dataframe: \n\n")
print(pd.merge(product,customer,on='Product_ID'))

Let’s take things up a notch. The leadership team now wants more details about the products sold. They want to know about all the products the seller sells to the same city, i.e., both the seller and the customer belong to the same city.

In this case, we have to perform an inner join on both Product_ID and Seller_City of the product and Product_ID and City columns of the customer dataframe.

So, how can we do this?

Don’t scratch your head! We only passed single strings in the left_on or right_on arguments in the above example. We can pass an iterable object of column names to the left_on and right_on arguments, which can be a list or a tuple, as they are iterable in left_on or right_on:

pd.merge(product,customer,how='inner',left_on=['Product_ID','Seller_City'],right_on=['Product_ID','City'])

Here the list items are column names, which are the names of the columns on which we want to join two dataframes.

Full Join in Pandas

full outer join

Here’s another interesting task for you. We have to combine both dataframes to find all the products that are not sold and all the customers who didn’t purchase anything from us.

We can use Full Join for this purpose.

Full Join, also known as Full Outer Join, returns all those records which either have a match in the left or right dataframe.

When rows in both the dataframes do not match, the resulting dataframe will have NaN for every dataframe column that lacks a matching row.

We can perform Full join by passing the how parameter as ‘outer’ to the merge() function, which takes input python strings as arguments:

pd.merge(product,customer,on='Product_ID',how='outer')
full outer join

We cannot pass non-string values in the how parameter.

Did you notice what happened here? All the non-matching rows of both the dataframes have NaN values for the columns of other dataframes. But wait – we still don’t know which row belongs to which dataframe.

For this, Pandas provides us with a fantastic solution. By mentioning the indicator argument as True in the function, a new column of name _merge will be created in the resulting dataframe:

pd.merge(product,customer,on='Product_ID',how='outer',indicator=True)
full outer join indicator

As you can see, the _merge column mentions which row belongs to which dataframe.

Left Join in Pandas

left join

Let’s say the leadership team wants information about only those customers who bought something from us. You guessed it—we can use the Left Join concept here.

Left join, also known as Left Outer Join, returns a dataframe containing all the rows of the left dataframe.

All the non-matching rows of the left dataframe contain NaN for the columns in the right dataframe. It is simply an inner join plus all the non-matching rows of the left dataframe filled with NaN for columns of the right dataframe.

Performing a left join is actually quite similar to a full join. Just change the how argument to a different str that is ‘left’:

pd.merge(product,customer,on='Product_ID',how='left')
left join

Here, you can clearly see that all the unsold products contain NaN for the columns belonging to the customer dataframe.

Right Join in Pandas

right join

Similarly, we can use the right join to create a table of customers, including information about the products they bought.

Right join, also known as Right Outer Join, is similar to the Left Outer Join. The only difference is that all the rows of the right dataframe are taken as it is and only those of the left dataframe that are common in both.

Similar to other joins, we can perform a right join by changing the how argument to ‘right’:

pd.merge(product,customer,on='Product_ID',how='right')
right join pandas

Take a look carefully at the above dataframe – we have NaN values for columns of the product dataframe. Pretty straightforward, right?

Handling Redundancy/Duplicates in Python Joins

Duplicate values can be tricky obstacles. They can cause problems while performing joins. These values won’t give an error but will create redundancy in our resulting dataframe. I’m sure you can imagine how harmful that can be!

Here, we have a new dataframe product_dup with duplicate details about products:

product_dup=pd.DataFrame({
'Product_ID':[101,102,103,104,105,106,107,103,107],
'Product_name':['Watch','Bag','Shoes','Smartphone','Books','Oil','Laptop','Shoes','Laptop',
'Category':['Fashion','Fashion','Fashion','Electronics','Study','Grocery','Electronics','Fashion','Electronics'],
'Price':[299.0,1350.50,2999.0,14999.0,145.0,110.0,79999.0,2999.0,79999.0],
'Seller_City':['Delhi','Mumbai','Chennai','Kolkata','Delhi','Chennai','Bengalore','Chennai','Bengalore']
})

Let’s see what happens if we perform an inner join on this dataframe:

pd.merge(product_dup,customer,how='inner',on='Product_ID')
duplicate joins in pandas

As you can see, we have duplicate rows in the resulting dataset as well. To solve this, there is a validate argument in the merge() function, which we can set to ‘one_to_one,’ ‘one_to_many,’ ‘many_to_one,’ and‘many_to_many.’

This ensures that there exists only a particular mapping across both the dataframes. If the mapping condition is not satisfied, then it throws a MergeError. To solve this, we can delete duplicates before applying join:

pd.merge(product_dup.drop_duplicates(),customer,how='inner',on='Product_ID')
duplicate joins in pandas

But, if you want to keep these duplicates, then you can give validate values as per your requirements, and it will not throw an error:

pd.merge(product_dup,customer,how='inner',on='Product_ID',validate='many_to_many')

Conclusion

By now, you must have strong expertise in the one-of-a-kind types of joins in Pandas and how to utilize the merge() feature to mix dataframes successfully. Mastering those joins will beautify your potential to control and analyze records, making your data science and gadget learning tasks more efficient and insightful.

Remember, at the same time as joins are powerful, they could introduce complexity, particularly while managing large datasets or redundant facts. Pandas also have other functions, such as concat() and append(), that can merge dataframes. I encourage you to explore and apply them in your next project alongside what you’ve learned about joins in this tutorial.

Key Takeaways

  • Joins in pandas refer to the many different ways functions in Python are used to join two dataframes.
  • The four main types of joins in pandas are Left join, Right join, Inner join, and Cross join.
  • The validate argument in the merge() function can detect and remove duplicate values in the newly formed dataset.

Courses:

Python Tutorials:

Frequently Asked Questions

Q1. How to join 2 DataFrames in pandas?

A. We can perform the join operation on two dataframes in Pandas using multiple functions, such as the Pandas join function. We can also use the pandas merge function to do a merge operation on the dataframes. So, there are multiple ways to join two dataframes in pandas.

Q2. What are the different types of joins?

A. Mainly, there are 4 types of joins for a dataframe. They are Left join, Right join, Inner join, and Cross join.
The most frequently used joins for dataframes and tables are left join and inner join. In the inner join, all the records with matching keys are present, whereas in the left join, all the records from the left table are present.

Q3 What is the difference between inner and outer join in pandas?

A. The inner join only finds and returns all matching rows; it returns no unmatched rows. However, the outer join returns all matching rows and some unmatched ones as well. There are two types of outer join: the left outer join and the right outer join.

He is a data science aficionado, who loves diving into data and generating insights from it. He is always ready for making machines to learn through code and writing technical blogs. His areas of interest include Machine Learning and Natural Language Processing still open for something new and exciting.

Responses From Readers

Clear

Erika K
Erika K

Thanks! This was a helpful and clear explanation.

Joseph Malisov
Joseph Malisov

Helpful! Is there a way I could do something like: A left outer join B, where B.name contains A.name (but does not equal it)?

Yusof A
Yusof A

Very nicely explained!

Srinivas PNV
Srinivas PNV

It is very nice explained!! thank you so much.

sme
sme

Thanks Abhishek! And how to have this sql script in pandas: select * from t1 left join t2 on t1_id = t2_id where id2 is null this selects rows ids that are available 'only' on left table

Swetha
Swetha

very nice expaination. cleared my queries on joins in pandas

Shrish Shukla
Shrish Shukla

Loved this article. Simple and easy to understand 🤗

Ryan Tan
Ryan Tan

Very clear and concise explanation. Removed all my doubts. Thank you very much, Abishek Sharma! :-)

Flash Card

What is an Inner Join in Pandas?

An inner join is one of the most common ways to combine two dataframes in Pandas. It only keeps the rows that have matching values in both dataframes, so you end up with a result that shows just the common data.

Think of it like finding what two groups have in common—it’s all about that overlap. For an inner join to work, the rows in the two dataframes need to match up in the columns you’re joining on. It’s like taking the intersection of two sets and only keeping the shared parts.

What is an Inner Join in Pandas?

Quiz

What does an inner join do when combining two dataframes in Pandas?

Flash Card

How does a full join differ from an inner join in Pandas?

A full join, also known as a full outer join, returns all records that have a match in either the left or right dataframe. Unlike an inner join, it includes all rows from both dataframes, filling in NaN for columns where there is no match. This means that even if there is no commonality between the dataframes, all rows are included in the result.

How does a full join differ from an inner join in Pandas?

Quiz

What is a key difference between a full join and an inner join in Pandas?

Flash Card

What is the purpose of a left join in Pandas, and what does it return?

A left join, or left outer join, returns a dataframe containing all the rows from the left dataframe. For rows in the left dataframe that do not have a matching row in the right dataframe, NaN is filled in for the right dataframe's columns. It combines the features of an inner join with the inclusion of all non-matching rows from the left dataframe.

What is the purpose of a left join in Pandas, and what does it return?

Quiz

What does a left join in Pandas return?

Flash Card

Explain the right join in Pandas and how it is similar to the left join.

A right join, or right outer join, is similar to a left join but focuses on the right dataframe. It returns all rows from the right dataframe and only those from the left dataframe that have a match. This join ensures that all rows from the right dataframe are included, with NaN filled in for non-matching left dataframe columns.

Explain the right join in Pandas and how it is similar to the left join.

Quiz

What does a right join in Pandas return?

Flash Card

How does the 'merge()' function in Pandas facilitate joining dataframes?

The 'merge()' function in Pandas is used to perform joins between dataframes or series. By default, it performs an inner join, requiring the input to be series or dataframe objects. If a different data type is passed, it results in a type error, ensuring only compatible data types are merged.

How does the 'merge()' function in Pandas facilitate joining dataframes?

Quiz

What is the default join type performed by the 'merge()' function in Pandas?

Flash Card

What role does the 'validate' argument play in handling redundancy during joins?

The 'validate' argument in the 'merge()' function helps manage redundancy by ensuring a specific mapping between dataframes. It can be set to 'one_to_one,' 'one_to_many,' 'many_to_one,' or 'many_to_many' to control the join relationship. If the specified mapping condition is not met, a MergeError is thrown, prompting the user to address duplicates before joining.

What role does the 'validate' argument play in handling redundancy during joins?

Quiz

What does the 'validate' argument in the 'merge()' function do?

Flash Card

Describe the use of the 'indicator' argument in the 'merge()' function.

The 'indicator' argument, when set to True, adds a new column named '_merge' to the resulting dataframe. This column indicates the origin of each row, specifying whether it belongs to the left, right, or both dataframes. It provides clarity on the source of data in the merged dataframe, aiding in data analysis.

Describe the use of the 'indicator' argument in the 'merge()' function.

Quiz

What does setting the 'indicator' argument to True in the 'merge()' function do?

Flash Card

Why is mastering joins in Pandas important for data analysis?

Mastering joins in Pandas enhances the ability to control and analyze data efficiently, crucial for data science and machine learning tasks. Joins allow for the combination of datasets, providing deeper insights and more comprehensive analysis. While powerful, joins can introduce complexity, especially with large datasets or redundant data, necessitating careful management.

Why is mastering joins in Pandas important for data analysis?

Quiz

Why is mastering joins in Pandas crucial for data analysis?

Flash Card

What are some alternative functions to 'merge()' for combining dataframes in Pandas?

Besides 'merge()', Pandas offers functions like 'concat()' and 'append()' for combining dataframes. 'concat()' is used to concatenate dataframes along a particular axis, while 'append()' adds rows to a dataframe. These functions provide flexibility in how dataframes are combined, depending on the specific requirements of the analysis.

What are some alternative functions to 'merge()' for combining dataframes in Pandas?

Quiz

Which functions can be used as alternatives to 'merge()' for combining dataframes in Pandas?

Congratulations, You Did It!
Well Done on Completing Your Learning Journey. Stay curious and keep exploring!

We use cookies essential for this site to function well. Please click to help us improve its usefulness with additional cookies. Learn about our use of cookies in our Privacy Policy & Cookies Policy.

Show details