Combining DataFrames: SQL-Style Relational Merges

Master relational database-style joins in Pandas using pd.merge(): inner, outer, left, and right joins, join keys, indicator flags, and custom suffixes.

8 minutesVideo LessonPDF notes

Watch Combining DataFrames: SQL-Style Relational Merges on YouTube

🎯 Free Guest Mode: You are learning for free. Sign in to save your completion progress and quiz answers.

Loading progress...

Key moments

Setup and Goal — The instructor introduces two DataFrames (temperature and humidity) and the goal of combining them into a single table.
Basic pd.merge() — The basic syntax for `pd.merge()` is shown, demonstrating that it matches data by key value (`city`) rather than row index.
Inner Join Default — The default behavior is shown to be an inner join (intersection), keeping only the keys common to both DataFrames.
Outer Join (Union) — The `how='outer'` argument is introduced to perform a union, preserving all keys and filling non-matching columns with NaN.
Left and Right Joins — Left join preserves all rows from the first DataFrame, while right join preserves all rows from the second DataFrame.
Tracking Row Origin — Setting `indicator=True` adds the `_merge` column, which specifies if a row originated from the left, right, or both DataFrames.
Handling Column Conflicts — When non-key columns are repeated, Pandas automatically appends `_x` and `_y` suffixes to distinguish them.
Custom Suffixes — The `suffixes` argument is used to override the default `_x` and `_y` with custom, descriptive labels.

PDF notes

Bottom line: You will learn to combine disparate datasets using Pandas' powerful `merge()` function, mirroring SQL join logic. You will be able to select the appropriate join type (inner, outer, left, right) to control which rows are preserved and how to resolve column name conflicts in the resulting DataFrame.

This lesson on Combining DataFrames: SQL-Style Relational Merges is hands-on and example-driven. You will learn to combine disparate datasets using Pandas' powerful merge() function, mirroring SQL join logic. You will be able to select the appropriate join type (inner, outer, left, right) to control which rows are preserved and how to resolve column name conflicts in the resulting DataFrame.

What You'll Be Able To Do

Execute SQL-style joins between two Pandas DataFrames using a common key.
Select the appropriate how parameter (inner, outer, left, right) to achieve desired row preservation.
Identify and handle missing data (NaN) resulting from non-matching keys in outer joins.
Resolve duplicate column names by specifying custom suffixes during the merge operation.
Utilize the indicator flag to track the origin (left, right, or both) of merged rows.

Detailed Concept Walkthrough

1. Relational Merging Fundamentals

pd.merge() combines two DataFrames based on shared values in a specified key column, acting like a database join. This ensures related data is accurately paired regardless of row index.

Mechanism: The function requires two DataFrames and the on parameter, which specifies the column containing the common key used for matching rows.
Under the Hood: Unlike simple concatenation or index alignment, merge explicitly looks up and matches key values across the two tables.
Best Practice: Ensure the key column specified in on has consistent data types and spelling across both DataFrames for accurate matching.

import pandas as pd
df1 = pd.DataFrame({'city': ['New York', 'Chicago'], 'temp': [21, 20]})
df2 = pd.DataFrame({'city': ['New York', 'Chicago'], 'humidity': [68, 65]})

# Basic merge on the 'city' column
merged_df = pd.merge(df1, df2, on='city')
print(merged_df)

Key Takeaway: Merging uses key values, not row indices, to accurately combine related data from different sources.

2. Controlling Row Preservation (Join Types)

The how parameter determines which rows are included in the result based on whether their keys exist in the left, right, or both input DataFrames. This controls the scope of the resulting dataset.

Mechanism: The default how='inner' performs an intersection, keeping only rows where the key exists in both DataFrames.
Mental Model: how='outer' performs a union, keeping all rows from both DataFrames and filling non-matching columns with NaN (Not a Number).
Execution Flow: how='left' preserves all rows from the first (left) DataFrame (df1), while how='right' preserves all rows from the second (right) DataFrame (df2).
Syntax Rule: The how argument must be explicitly set to 'outer', 'left', or 'right' if the default 'inner' behavior is not desired.

df1 = pd.DataFrame({'city': ['NY', 'Orlando'], 'temp': [21, 35]})
df2 = pd.DataFrame({'city': ['NY', 'SF'], 'humidity': [68, 75]})

# Outer join preserves all keys, filling missing data with NaN
merged_outer = pd.merge(df1, df2, on='city', how='outer')
print(merged_outer)

Key Takeaway: Choose the join type based on whether you need to preserve data unique to the left, right, or both input tables.

3. Suffixes and Indicators

When DataFrames share column names (other than the merge key), Pandas automatically appends suffixes to distinguish them; the indicator flag tracks row origin.

Mechanism: Pandas automatically appends _x (left) and _y (right) to conflicting non-key column names by default.
Best Practice: Use the suffixes argument to provide meaningful labels (e.g., suffixes=('_A', '_B')) for improved readability.
Practical Use: Setting indicator=True adds a column named _merge to the result, showing if the row came from 'left_only', 'right_only', or 'both'.

df_a = pd.DataFrame({'city': ['NY'], 'temp': [21]})
df_b = pd.DataFrame({'city': ['NY'], 'temp': [25]})

# Custom suffixes resolve the conflicting 'temp' column
result = pd.merge(df_a, df_b, on='city', suffixes=('_temp_A', '_temp_B'))
print(result)

Key Takeaway: Always anticipate column conflicts and use suffixes for clarity, and indicator=True for tracking data provenance.

Topics Covered in Combining DataFrames: SQL-Style Relational Merges

Setup and Goal (0:00 - 0:45) — The instructor introduces two DataFrames (temperature and humidity) and the goal of combining them into a single table.
Basic pd.merge() (0:45 - 1:30) — The basic syntax for pd.merge() is shown, demonstrating that it matches data by key value (city) rather than row index.
Inner Join Default (1:30 - 2:50) — The default behavior is shown to be an inner join (intersection), keeping only the keys common to both DataFrames.
Outer Join (Union) (2:50 - 3:55) — The how='outer' argument is introduced to perform a union, preserving all keys and filling non-matching columns with NaN.
Left and Right Joins (3:55 - 5:20) — Left join preserves all rows from the first DataFrame, while right join preserves all rows from the second DataFrame.
Tracking Row Origin (5:20 - 6:10) — Setting indicator=True adds the _merge column, which specifies if a row originated from the left, right, or both DataFrames.
Handling Column Conflicts (6:10 - 7:30) — When non-key columns are repeated, Pandas automatically appends _x and _y suffixes to distinguish them.
Custom Suffixes (7:30 - 8:00) — The suffixes argument is used to override the default _x and _y with custom, descriptive labels.

Python Cheat Sheet

pd.merge(df1, df2, on='key') — Combines two DataFrames on a common column
```
pd.merge(df1, df2, on='city')
```
how='inner' — Keeps only rows with keys in both DataFrames
```
pd.merge(df1, df2, on='city', how='inner')
```
how='outer' — Keeps all rows from both DataFrames (union)
```
pd.merge(df1, df2, on='city', how='outer')
```
how='left' — Keeps all rows from the first (left) DataFrame
```
pd.merge(df1, df2, on='city', how='left')
```
suffixes=('_x', '_y') — Customizes labels for conflicting column names
```
pd.merge(df1, df2, on='city', suffixes=('_temp', '_hum'))
```
indicator=True — Adds a column showing the origin of each row
```
pd.merge(df1, df2, on='city', how='outer', indicator=True)
```

Comparison Table

Join Type	Set Theory Equivalent	Rows Included	Missing Data
Inner (Default)	Intersection	Keys present in both	None
Outer	Union	Keys present in either	NaN in non-matching columns
Left	Left set plus intersection	All rows from left DF	NaN for unmatched right data
Right	Right set plus intersection	All rows from right DF	NaN for unmatched left data

Common Pitfalls

Mistake: Assuming pd.merge uses row index alignment by default. Avoid: Always specify the common key using the on='column' parameter.
Mistake: Forgetting that 'left' and 'right' are determined by argument order. Avoid: Remember df1 is left and df2 is right in pd.merge(df1, df2, ...).
Mistake: Ignoring repeated column names in the source DataFrames. Avoid: Use the suffixes parameter to clearly label columns like temp_A and temp_B.
Mistake: Using an inner join when trying to find unmatched records. Avoid: Use how='outer' or how='left'/'right' to preserve non-matching keys.

FAQs

What is the default join type if I don't specify how? The default is how='inner', which only returns rows where the key exists in both DataFrames (intersection).
Why do I see NaN values in my merged DataFrame? NaN appears when you use an outer, left, or right join, and a row from one table has no matching key in the other table.
How can I tell which DataFrame a specific row came from after an outer merge? Set indicator=True in the merge call; this adds a column named _merge showing 'left_only', 'right_only', or 'both'.
If both DataFrames have a 'temp' column, how does Pandas handle the conflict? Pandas automatically appends _x to the left column and _y to the right column, unless you specify custom suffixes.

Common pitfalls

Mistake: Assuming `pd.merge` uses row index alignment by default. Avoid: Always specify the common key using the `on='column'` parameter.
Mistake: Forgetting that 'left' and 'right' are determined by argument order. Avoid: Remember `df1` is left and `df2` is right in `pd.merge(df1, df2, ...)`.
Mistake: Ignoring repeated column names in the source DataFrames. Avoid: Use the `suffixes` parameter to clearly label columns like `temp_A` and `temp_B`.
Mistake: Using an inner join when trying to find unmatched records. Avoid: Use `how='outer'` or `how='left'`/`'right'` to preserve non-matching keys.

Hey guys, welcome to code basics coding tutorial. Today we are going to talk about merge in pandas. As usual, I have launched my Jupyter notebook by running Jupyter notebook command on my command prompt and it launched my notebook here. And what I'm going to do is create a new notebook. Now to save some time on recording, I have some code snippet which I'm going to just copy directly. So I have this first data frame which contains temperature information on different cities and then I have this second data frame which contains data on humidity. Now often I want to join these two data frames to create a one single data frame which contains both temperature and humidity and you can do that using Panda's merge function. So you can say pd dot mermer merge your first data frame your second data frame and the column on which you want to perform this merge. Okay. And when you print your data frame uh it's going to look like this. So now it has temperature and humidity and you can see that New York has 68 humidity which is 68 here and 21 temperature. So it is truly it's not just going by the row index. It is truly looking into the value of the city and performing this join. Now if you are aware about database joins then this is exactly same. It's like doing a join between two tables using uh this column such as uh which which is basically city. Okay. Now here you had all three cities present in both the data frames. What if you have let's say one extra city here Baltimore where the average temperature is let's say 32 and then you don't have Orlando here instead of that you have San Francisco okay so let's say this is how your data frames are set up okay now when you perform this join you will notice that the result has only New York and Chicago. So whatever was common between these two data frames you are seeing it here. Now it's missing Orlando, Baltimore and San Francisco. The reason is uh it as you already figured it out it did an inter intersection. So if you remember just the set theory as you can see in the diagram intersection is basically taking common elements between the between the two sets. Okay. Also in a database world there is this concept of inner join. So that's what this is inner join or intersection. Okay. Now the second kind of join that you have in database is basically outer join. Uh if you want to use set theory term terminology then it will be a union taking union of uh two uh sets basically. Okay. So the argument that you would use is how. So in how if you say outer then it's going to take it's going to be a union of these two tables. So you'll see Orlando Baltimore and San Francisco and the columns for which it doesn't have information is going to put nan over here. Okay. So you we saw outer join then inner join is default is whatever you see when you don't supply how column. So if you do pandas merged documentation uh you will see that by default how is inner. Okay. All right. Another kind of join is left join. So again as you can see in the picture left will take all the common elements between the two sets and also the remaining elements from the left data frame. So you can see that now I have Orlando and Baltimore. Okay, these two are present but I don't have San Francisco because San Francisco is my right data frame. And by the way, left and right is decided by the order uh in in your merge call. So DF1 is left and DF2 is right. Okay. Now let me do a right inner join. Not right inner join really right join. So in right join you will see elements from right data frame. So you can see San Francisco and of course the common elements but you don't see now Orlando and Baltimore. Going back to outer join when you have this result data frame sometimes you might want to know from which data frame these element come in. So for example this has an end. So you know either it came from left or right. Okay. So in order to know that you can use indicator flag. So by default it is set to false. But if you set it to true, you will get this additional merge column where it where it will say if you know where this data came from. So for example, this was present in both the data frames. So it says both then it says left only which means Orlando was present only in the left data frame. So this could be useful sometimes. We also have one more argument called suffixes. So let's say you have common columns in both the data frames. So again I'm going to copy some code snippet here. Okay. So let's say this is your data frame which has both humidity and temperature. And then you have your second data frame here which also have humidity and temperature. Okay. Now when you join these two so I will again do like PD do merge df1 df2 and your on would be city. Okay. So what you'll notice is uh it's going to automatically append underscorex and underscore y because humidity column and temperature column were repeated between these two data frames. Hence uh when it shows the result it needs to somehow distinguish uh these two columns. So it appended these suffixes. Now if you want your own suffix suffixes then you can use this suffix column where you can say okay my left column and my my left data frame and right data frame. So when you do that by default it is using underscorex and underscore y but when you explicitly supply these suffixes it's going to use that as you can see it here. Okay. So that's all I had for this tutorial. Thank you very much for watching. You can find the link of the Jupyter notebook used in this tutorial in the video description below. Uh I will see you in uh next tutorial. Thanks again. bye.

Frequently asked questions

What is the default join type if I don't specify `how`?

The default is `how='inner'`, which only returns rows where the key exists in both DataFrames (intersection).

Why do I see `NaN` values in my merged DataFrame?

`NaN` appears when you use an outer, left, or right join, and a row from one table has no matching key in the other table.

How can I tell which DataFrame a specific row came from after an outer merge?

Set `indicator=True` in the `merge` call; this adds a column named `_merge` showing 'left_only', 'right_only', or 'both'.

If both DataFrames have a 'temp' column, how does Pandas handle the conflict?

Pandas automatically appends `_x` to the left column and `_y` to the right column, unless you specify custom suffixes.

How was this lesson?

Your feedback helps us refine explanations and catch bugs.