The 4 Types of DataFrame Joins Offered by Python’s pandas Library
As a data analyst, you will often face the need to combine multiple datasets. You’ll need to do this to complete your analysis and arrive at a conclusion for your business/stakeholders.
It’s often challenging to represent data when it’s stored in different tables. In such circumstances, joins prove their worth, irrespective of the programming language you are working on.
Python joins are like SQL joins: they combine data sets by matching their rows on a common index.
Create Two DataFrames for Reference
To follow the examples in this guide, you can create two sample DataFrames. Use the following code to create the first DataFrame, which contains an ID, first name, and last name.
For the first step, import thepandaslibrary. You can then use a variable,a, to store the result from the DataFrame constructor. Pass the constructor a dictionary containing your required values.
Finally, display the contents of the DataFrame value with the print function, to check everything looks as you’d expect.
Similarly, you may create another DataFrame,b, which contains an ID and salary values.
You can check the output in a console or an IDE. It should confirm the contents of your DataFrames:
How Are Joins Different From the Merge Function in Python?
The pandas library is one of the main libraries you can use to manipulate DataFrames. Since DataFrames contain multiple data sets, various functions are available in Python to join them.
Python offers the join and merge functions, amongst many others, which you can use to combine DataFrames. There is a stark difference between these two functions, which you must keep in mind before using either.
The join function joins two DataFrames based on their index values. Themerge function combines DataFramesbased on the index values and the columns.
What Do You Need to Know About Joins in Python?
Before discussing the types of joins available, here are some important things to note:
The default syntax for a Python join is as follows:
Invoke the join method on the first DataFrame and pass the second DataFrame as its first parameter,other. The remaining arguments are:
Learn to Use the Various Types of Joins in Python
Python has a few join options, which you can exercise, depending on the need of the hour. Here are the join types:
1. Left Join
The left join keeps the first DataFrame’s values intact while bringing in the matching values from the second one. For example, if you want to bring in the matching values fromb, you can define it as follows:
When the query executes, the output contains the following column references:
This join pulls the first three columns from the first DataFrame, and the last two columns from the second DataFrame. It has used thelsuffixandrsuffixvalues to rename the ID columns from both datasets, ensuring the resulting field names are unique.
The output is as follows:
2. Right Join
The right join keeps the second DataFrame’s values intact, while bringing in the matching values from the first table. For example, if you want to bring in the matching values froma, you can define it as follows:
If you review the code, there are a few evident changes. For example, the result includes the second DataFrame’s columns before those from the first DataFrame.
You should use a value ofrightfor thehowargument to specify a right join. Also, note how you’re able to switch thelsuffixandrsuffixvalues to reflect the nature of the right join.
In your regular joins, you might find yourself using left, inner and outer joins more frequently, as compared to the right join. However, the usage depends entirely on your data requirements.
3. Inner Join
An inner join delivers the matching entries from both the DataFrames. Since joins use the index numbers to match rows, an inner join only returns rows that match. For this illustration, let’s use the following two DataFrames:
You can use an inner join, as follows:
The resulting output contains only rows that exist in both input DataFrames:
4. Outer Join
An outer join returns all the values from both the DataFrames. For rows with no matching values, it produces a null value on the individual cells.
Using the same DataFrame as above, here’s the code for outer join:
Using Joins in Python
Joins, like their counterpart functions, merge and concat, offer way more than a simple join functionality. Given its series of options and functions, you could choose the options that meet your requirements.
it’s possible to sort the resulting datasets relatively easily, with or without the join function, with the flexible options that Python offers.
With your data in a DataFrame, courtesy of the pandas library, it’s easy to sort it using these different approaches.
Flagship price, mid-range phone.
Turn these settings on, and your iPhone will be so much better than before.
These plugins will make you wonder why you used Photoshop in the first place.
Taming data is easier than it looks.
Windows is great, but adding this makes it unstoppable.
