Hello! In our last lesson, we took a deep dive into the critical distinction between correlation and causation, a conceptual cornerstone of statistical thinking. We established that while a statistical relationship might be present, it doesn't automatically imply a cause-and-effect link.
Today, we transition from interpreting relationships within a single, clean dataset to the practical challenge of creating such a dataset in the first place. In any real-world data science project, the information you need is rarely in one tidy file. It's often scattered across multiple tables, spreadsheets, or databases. This lesson focuses on the essential skill of combining these disparate datasets into a unified whole. You will learn the common techniques for data integration—specifically, joining and concatenating—which are fundamental operations for preparing data for analysis.
Given your computer science background, you may find these concepts familiar, as they are direct analogues to the JOIN and UNION operations in SQL and are rooted in the principles of relational algebra.
The Theory: Relational Algebra and Join Types
Before we dive into writing code, it's crucial to understand the conceptual framework behind combining datasets. The methods we'll use are implementations of relational algebra, a formal system for manipulating data, which forms the basis of most database systems.
The two primary ways to combine tables are:
- Joining: Merging datasets horizontally by adding new columns, based on a shared "key" column. For example, joining a
customerstable with anorderstable using a commoncustomer_id. - Concatenating: Stacking datasets vertically by adding new rows. For example, combining January's sales data with February's sales data.
Let's start by focusing on joins. There are several types of joins, each answering a different question about how to handle records that exist in one table but not the other.
To see these concepts animated and explained in the context of SQL, which you may have encountered before, please watch the following video. It provides a clear, conceptual overview of how different joins work.
6 SQL Joins you MUST know! (Animated + Practice)
This video from Anton Putra provides excellent animated explanations of the core join types. Focus on the logic, as it translates directly to the tools we'll use.
Watch the following segments to understand the four primary join operations: Inner Join: this section explains how to get only the records that have a match in both tables. Left Join: this part shows how to keep all records from the left table and matched records from the right. Right Join: this segment explains the inverse of a left join. Note the comment that a right join can always be rewritten as a left join. Full Outer Join: this section covers how to keep all records from both tables, regardless of whether they have a match.
The Practice: Joining and Merging in pandas
In the Python data analysis library pandas, these database-style operations are primarily handled by the pd.merge() function. It's a powerful and flexible tool that implements all the join logic we just saw.
The Python Data Science Handbook provides a great introduction to the topic, grounding it in the relational algebra concepts we mentioned.
Combining Datasets: Merge and Join
This reading from Jake VanderPlas's "Python Data Science Handbook" will introduce you to pd.merge() and the different categories of joins from a practical perspective.
Read from the beginning, through the section on Relational Algebra, and then continue through the examples of the three join categories: one-to-one, many-to-one, and many-to-many. This will show you how the same function call behaves differently based on the structure of your input data.
Implementing Join Types with pd.merge()
Now let's see how to explicitly control the type of join using the how parameter in pd.merge(). The Real Python article "Combining Data in pandas" has excellent, practical examples using real-world climate data. We'll walk through the main join types.
Combining Data in pandas With merge(), .join(), and concat()
This article will be our main guide for the practical implementation in pandas. We'll start by setting up the data and then executing the four main join types.
First, read the section pandas merge() to get an overview of the function and its main parameters like on, how, and suffixes. Then, read through the "Examples" section. Start by loading the data to understand the datasets we'll be working with. Afterward, read the subsections for each join type to see them in action: Inner Join Outer Join Left Join Right Join Focus on how the how parameter changes the output and how the shape of the resulting DataFrame is affected in each case.
As you can see, pd.merge() is the workhorse for combining data based on common values. By specifying the key(s) with the on parameter and the logic with the how parameter, you can perform any of the standard relational joins.
Concatenation: Stacking Datasets
Joining is about adding columns from one dataset to another based on a key. But what if you simply want to stack datasets on top of each other? For instance, if you have sales data for each month in separate files, you'd want to combine them by appending the rows. This operation is called concatenation.
The pd.concat() function in pandas is designed for this purpose. While merge stitches data together horizontally based on shared information, concat typically stitches data together vertically (or horizontally if you specify axis=1).
Let's return to the Real Python article to see how this works.
Combining Data in pandas With merge(), .join(), and concat()
This reading will cover the concat() function, showing how it differs from merging.
Please read the sections pandas concat(), focusing on the visual diagrams for concatenating along rows vs. columns. Then, read the "How to Use concat()" section and the Examples section to see how parameters like ignore_index and join work. The join='inner' usage within concat is particularly interesting, as it combines concatenation with join-like logic on the other axis.
A Note on .join()
You may also see the DataFrame.join() method used. It is closely related to pd.merge(), but with a key difference: .join() operates on the DataFrame's index by default, while pd.merge() operates on columns by default. While merge is generally more flexible and explicit, join can be a convenient shortcut when your data is already indexed by the key you want to join on.
For most general-purpose, SQL-style joining, sticking with pd.merge() is a robust and clear approach.
Conclusion
In this lesson, we explored the fundamental techniques for integrating data from multiple sources. You've learned the conceptual difference between joining and concatenating and how to implement these operations in pandas.
Key Takeaways:
- Relational Joins are used to combine data horizontally based on a shared key. The main types are Inner, Outer, Left, and Right.
pd.merge()is the primary pandas function for performing relational joins. You can control the join logic using thehowparameter and specify the key(s) with theonparameter.- Concatenation is used to stack data vertically (or horizontally). It's for combining datasets that have a similar structure.
pd.concat()is the function for this, primarily used for appending rows from one DataFrame to another.
In our next lesson, we will address a common consequence of combining datasets: messiness. As you saw in the examples, operations like left and outer joins often introduce NaN (Not a Number) values. Our next topic, Data Cleaning and Transformation, will equip you with the strategies to handle these missing values, deal with outliers, and rescale your data for analysis.