Lesson 27 · Mastering Pandas

Mastering DataFrame Merges in Pandas: Inner, Outer, Left & Right Joins Explained

Combining multiple datasets is a key skill in real-world data science. Today, we will learn to use pandas to merge and join tables in Python. We will cover…

⬇ Download notebook Open in Colab ↗

CourseMastering Pandas
Lesson27 of 44
Video36 min
FormatJupyter notebook · 13 code cells

pandas NumPy

Data

No separate download needed — the notebook creates or downloads everything it uses.

📓 Full notebook

Download .ipynb

Lesson Overview: Merging and Joining DataFrames in Pandas#

Combining multiple datasets is a key skill in real-world data science. Today, we will learn to use pandas to merge and join tables in Python.

We will cover the basics using small, friendly examples, introduce the types of joins (inner, outer, left, right), and build to a hands-on mini-project.

These techniques help you combine information, uncover new insights, and prepare for more complex data problems.

import warnings
warnings.filterwarnings("ignore")

# Let us import pandas for our work
import pandas as pd
import numpy as np
np.random.seed(42)

Why merging matters#

Most interesting data science tasks need information from more than one source.

For example, we might want to combine Titanic passenger info with a table mapping tickets to different cabins.

Merging data helps us answer richer questions and spot new patterns and connections.

# Data setup (Titanic Dataset)
import pandas as pd
url = 'https://raw.githubusercontent.com/datasciencedojo/datasets/master/titanic.csv'
df = pd.read_csv(url)
print(df.shape)
print(df.head(3))

(891, 12)
   PassengerId  Survived  Pclass  \
0            1         0       3   
1            2         1       1   
2            3         1       3   

                                                Name     Sex   Age  SibSp  \
0                            Braund, Mr. Owen Harris    male  22.0      1   
1  Cumings, Mrs. John Bradley (Florence Briggs Th...  female  38.0      1   
2                             Heikkinen, Miss. Laina  female  26.0      0   

   Parch            Ticket     Fare Cabin Embarked  
0      0         A/5 21171   7.2500   NaN        S  
1      0          PC 17599  71.2833   C85        C  
2      0  STON/O2. 3101282   7.9250   NaN        S

# Mini lookup table: tickets to cabin/region
ticket_lookup = pd.DataFrame({
    'Ticket': ['A/5 21171', 'PC 17599', 'STON/O2. 3101282', '113803'],
    'Region': ['Deck F', 'First Class', 'Steerage', 'First Class']
})
ticket_lookup

	Ticket	Region
0	A/5 21171	Deck F
1	PC 17599	First Class
2	STON/O2. 3101282	Steerage
3	113803	First Class

Inner Join: getting only matching rows#

An inner join keeps only rows with matching ticket numbers in both tables.

This is the most common default for merge in pandas.

# Merge with inner join (only matching tickets)
merged_inner = df.merge(ticket_lookup, how='inner', left_on='Ticket', right_on='Ticket')
print(merged_inner[['Name', 'Ticket', 'Region']])

                                                Name            Ticket  \
0                            Braund, Mr. Owen Harris         A/5 21171   
1  Cumings, Mrs. John Bradley (Florence Briggs Th...          PC 17599   
2                             Heikkinen, Miss. Laina  STON/O2. 3101282   
3       Futrelle, Mrs. Jacques Heath (Lily May Peel)            113803   
4                        Futrelle, Mr. Jacques Heath            113803   

        Region  
0       Deck F  
1  First Class  
2     Steerage  
3  First Class  
4  First Class

Left Join: keeping all original rows#

A left join means we keep ALL rows from the main (left) table, even if there is no matching row in the other (right) table.

Missing values from the right table fill in with NaN.

# Merge with left join (all Titanic rows; fill blank region)
merged_left = df.merge(ticket_lookup, how='left', left_on='Ticket', right_on='Ticket')
print(merged_left[['Name', 'Ticket', 'Region']].head(6))

                                                Name            Ticket  \
0                            Braund, Mr. Owen Harris         A/5 21171   
1  Cumings, Mrs. John Bradley (Florence Briggs Th...          PC 17599   
2                             Heikkinen, Miss. Laina  STON/O2. 3101282   
3       Futrelle, Mrs. Jacques Heath (Lily May Peel)            113803   
4                           Allen, Mr. William Henry            373450   
5                                   Moran, Mr. James            330877   

        Region  
0       Deck F  
1  First Class  
2     Steerage  
3  First Class  
4          NaN  
5          NaN

Right Join: keeping all lookup rows#

A right join is like left join but keeps all rows from the secondary (right) table.

Pandas fills in NaN for columns from the left table that could not be matched.

# Merge with right join (all region rows, even if there is no matching passenger)
merged_right = df.merge(ticket_lookup, how='right', left_on='Ticket', right_on='Ticket')
print(merged_right[['Name', 'Ticket', 'Region']])

                                                Name            Ticket  \
0                            Braund, Mr. Owen Harris         A/5 21171   
1  Cumings, Mrs. John Bradley (Florence Briggs Th...          PC 17599   
2                             Heikkinen, Miss. Laina  STON/O2. 3101282   
3       Futrelle, Mrs. Jacques Heath (Lily May Peel)            113803   
4                        Futrelle, Mr. Jacques Heath            113803   

        Region  
0       Deck F  
1  First Class  
2     Steerage  
3  First Class  
4  First Class

Outer Join: keeping everything (all possible rows)#

An outer join keeps every row from both tables, matching where possible.

Any missing values fill in as NaN.

# Merge with outer join (every ticket, match if possible)
merged_outer = df.merge(ticket_lookup, how='outer', left_on='Ticket', right_on='Ticket')
print(merged_outer[['Name', 'Ticket', 'Region']].tail(6))

                                        Name       Ticket Region
885  Ford, Mrs. Edward (Margaret Ann Watson)   W./C. 6608    NaN
886             Harknett, Miss. Alice Phoebe   W./C. 6609    NaN
887              Chaffee, Mr. Herbert Fuller  W.E.P. 5734    NaN
888                       Harris, Mr. Walter    W/C 14208    NaN
889                  Crosby, Miss. Harriet R    WE/P 5735    NaN
890             Crosby, Capt. Edward Gifford    WE/P 5735    NaN

Recap: Join Types#

Inner join: Only rows present in both tables.
Left join: All rows from the left/main table, matched if possible.
Right join: All rows from the right/lookup table.
Outer join: All rows from both tables, filling gaps with NaN.

The type you choose depends on whether missing data or strict matches matter for your project.

# Example: combining passenger info with a small family table
family = pd.DataFrame({
    'Name': ['Mr. Owen Harris Braund', 'Miss. Laina Heikkinen', 'Mr. Test Passenger'],
    'FamilyGroup': ['Braund', 'Heikkinen', 'Test']
})
result = df.merge(family, on='Name', how='left')
print(result[['Name', 'FamilyGroup']].head(5))

                                                Name FamilyGroup
0                            Braund, Mr. Owen Harris         NaN
1  Cumings, Mrs. John Bradley (Florence Briggs Th...         NaN
2                             Heikkinen, Miss. Laina         NaN
3       Futrelle, Mrs. Jacques Heath (Lily May Peel)         NaN
4                           Allen, Mr. William Henry         NaN

# Ensure column alignment and avoid confusion in joins
tickets_left = df[['Name', 'Ticket', 'Fare']].copy()
tickets_right = ticket_lookup.rename(columns={'Ticket': 'TicketNum'})
try_merge = tickets_left.merge(tickets_right, left_on='Ticket', right_on='TicketNum', how='left')
print(try_merge[['Name', 'Ticket', 'Region']].head(3))

                                                Name            Ticket  \
0                            Braund, Mr. Owen Harris         A/5 21171   
1  Cumings, Mrs. John Bradley (Florence Briggs Th...          PC 17599   
2                             Heikkinen, Miss. Laina  STON/O2. 3101282   

        Region  
0       Deck F  
1  First Class  
2     Steerage

# What happens when we merge on two keys?
cabin_lookup = pd.DataFrame({
    'Ticket': ['A/5 21171', 'PC 17599'],
    'Pclass': [3, 1],
    'CabinLoc': ['F', 'C']
})
merged_multi = df.merge(cabin_lookup, how='left', on=['Ticket', 'Pclass'])
print(merged_multi[['Name', 'Ticket', 'Pclass', 'CabinLoc']].head(4))

                                                Name            Ticket  \
0                            Braund, Mr. Owen Harris         A/5 21171   
1  Cumings, Mrs. John Bradley (Florence Briggs Th...          PC 17599   
2                             Heikkinen, Miss. Laina  STON/O2. 3101282   
3       Futrelle, Mrs. Jacques Heath (Lily May Peel)            113803   

   Pclass CabinLoc  
0       3        F  
1       1        C  
2       3      NaN  
3       1      NaN

Practice Challenge#

Make your own tiny DataFrame with two columns: a made-up 'Code' and some labels.
Try merging it into the Titanic data using left and inner joins.
Which rows get matched and how do the differences appear?

Experiment and see what happens!

# Real-world mini-project: join Titanic passengers and survival stats
survived_stats = df[['Pclass', 'Survived']].groupby('Pclass').mean().reset_index()
survived_stats.rename(columns={'Survived': 'SurvivalRate'}, inplace=True)
print(survived_stats)
merged_project = df.merge(survived_stats, on='Pclass', how='left')
print(merged_project[['Name', 'Pclass', 'Survived', 'SurvivalRate']].head(4))

   Pclass  SurvivalRate
0       1      0.629630
1       2      0.472826
2       3      0.242363
                                                Name  Pclass  Survived  \
0                            Braund, Mr. Owen Harris       3         0   
1  Cumings, Mrs. John Bradley (Florence Briggs Th...       1         1   
2                             Heikkinen, Miss. Laina       3         1   
3       Futrelle, Mrs. Jacques Heath (Lily May Peel)       1         1   

   SurvivalRate  
0      0.242363  
1      0.629630  
2      0.242363  
3      0.629630

# Try merging when keys do not match  does pandas warn us?
fake_lookup = pd.DataFrame({'Ticket': ['ZZZ', 'YYY'], 'Region': ['Lost', 'Mystery']})
result = df.merge(fake_lookup, how='inner', on='Ticket')
print(result.shape)

(0, 13)

# Troubleshooting: What if I try to merge without specifying key columns?
try:
    df.merge(ticket_lookup)
except Exception as e:
    print(type(e).__name__, e)

Summary: Key Takeaways#

Merging connects information across datasets. It is central to real-world analysis.
Use how='inner', left, right, and outer to control which rows appear
Always check your columns and preview merges before using results.
When things do not line up, review your column names or join keys.

Ready for more pandas? Try chaining more merges and see what questions you can answer by combining datasets!

If you enjoyed this lesson, please subscribe, like the video, and leave a comment with your pandas questions or merge tips!

Try building your own join with a favorite dataset and share your findings.

Thanks for learning merging and joining in pandas!

Found this useful?

All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.

☕ Buy me a coffee Other ways to help