Joining on the Index
Use DataFrame.join() and set left_index/right_index=True in merge() to combine DataFrames aligned by their index.
Joining on the Index is a free Pandas & NumPy Academy lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the Pandas & NumPy Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
Index-Based Joins
So far you have merged DataFrames by matching values in regular columns. But sometimes the information you want to match on is stored in the DataFrame's index rather than in a column. Pandas supports index-based joins through DataFrame.join() and through pd.merge() with left_index=True or right_index=True parameters.
DataFrame.join() Method
DataFrame.join(other) is a convenience method that joins on the index of both DataFrames by default. It is equivalent to a pd.merge() call with left_index=True, right_index=True. The default join type is 'left', unlike pd.merge() which defaults to 'inner'. Both DataFrames must share meaningful index labels for the join to produce correct results.
import pandas as pd
profiles = pd.DataFrame(
{'age': [25, 30, 22]},
index=['alice', 'bob', 'carol']
)
scores = pd.DataFrame(
{'score': [88, 95, 70]},
index=['alice', 'carol', 'dave']
)
# join: left join on index (default)
result = profiles.join(scores)
print(result)
# age score
# alice 25 88.0
# bob 30 NaN <- bob has no score
# carol 22 95.0Controlling Join Type in join()
Pass the how parameter to join() to control which rows are preserved, just as with pd.merge(). Options are 'left' (default), 'right', 'inner', and 'outer'. For example, how='inner' keeps only indices that appear in both DataFrames, dropping alice's row if it had no matching index in the right table.
# Inner join on index: only rows present in BOTH indices
print(profiles.join(scores, how='inner'))
# age score
# alice 25 88
# carol 22 70
# Outer join: all indices from both
print(profiles.join(scores, how='outer'))
# age score
# alice 25.0 88.0
# bob 30.0 NaN
# carol 22.0 70.0
# dave NaN 95.0Joining Multiple DataFrames at Once
A major advantage of join() over pd.merge() is that it accepts a list of DataFrames, joining them all to the caller in one call. All DataFrames must be index-aligned. This is very convenient when you have many feature tables all indexed by the same key, such as user ID or date, and want to assemble them into one wide DataFrame.
emails = pd.DataFrame({'email': ['a@x.com', 'b@x.com', 'c@x.com']},
index=['alice', 'bob', 'carol'])
city = pd.DataFrame({'city': ['NY', 'LA', 'SF']},
index=['alice', 'bob', 'carol'])
# Join multiple DataFrames at once
result = profiles.join([emails, city])
print(result)
# age email city
# alice 25 a@x.com NY
# bob 30 b@x.com LA
# carol 22 c@x.com SFJoining on a Column in One Table and Index in Another
pd.merge() lets you mix column-based and index-based matching with left_on/right_index or left_index/right_on. This is useful when one DataFrame stores the key in a column but the other uses it as the index. You cannot achieve this with join() alone.
# orders has customer_id as a column; customers is indexed by customer_id
customers_indexed = pd.DataFrame(
{'name': ['Alice', 'Bob', 'Carol']},
index=[101, 102, 103]
)
orders = pd.DataFrame({
'order_id': [1, 2, 3],
'customer_id': [101, 102, 101]
})
result = pd.merge(orders, customers_indexed,
left_on='customer_id', right_index=True)
print(result)
# order_id customer_id name
# 0 1 101 Alice
# 2 3 101 Alice
# 1 2 102 BobUsing left_index and right_index in merge()
When both DataFrames have the key as their index, use pd.merge(left, right, left_index=True, right_index=True). This is equivalent to join() but with the default 'inner' join type and more explicit parameters. You also gain access to all other pd.merge() parameters like suffixes and indicator.
# Both DataFrames indexed by user_id
demog = pd.DataFrame({'age': [25, 30]}, index=[1, 2])
behav = pd.DataFrame({'clicks': [10, 5]}, index=[1, 2])
# Equivalent joins
result1 = demog.join(behav) # left join
result2 = pd.merge(demog, behav, left_index=True, right_index=True) # inner join
print(result1)
print(result2)Why Use Index-Based Joins?
Index-based joins are preferred when your DataFrames are naturally keyed by a meaningful identifier such as a user ID, product SKU, or date. Using the index for joins avoids cluttering your DataFrame with redundant key columns and can be faster because Pandas maintains sorted index structures for O(log n) look-ups. They are especially common in time series data where the DatetimeIndex is the natural join key.
# Time series example: join temperature and humidity by date index
import numpy as np
dates = pd.date_range('2024-01-01', periods=5)
temp = pd.DataFrame({'temp_c': [10, 12, 9, 11, 13]}, index=dates)
humid = pd.DataFrame({'humidity': [65, 70, 60, 75, 68]}, index=dates)
weather = temp.join(humid)
print(weather.head())Handling Overlapping Column Names
When both DataFrames have columns with the same name (other than the key), join() raises a ValueError by default unless you provide the lsuffix and rsuffix parameters. These parameters work like suffixes in pd.merge(), appending a string to the overlapping column names from the left and right DataFrames respectively.
df_a = pd.DataFrame({'value': [1, 2]}, index=['x', 'y'])
df_b = pd.DataFrame({'value': [10, 20]}, index=['x', 'y'])
# Without suffixes: raises ValueError
# result = df_a.join(df_b)
# With suffixes: disambiguate column names
result = df_a.join(df_b, lsuffix='_left', rsuffix='_right')
print(result)
# value_left value_right
# x 1 10
# y 2 20set_index Before Joining
A common workflow is to set a column as the index before joining, so you can use join() syntax. After the join, reset_index() brings the key back as a regular column. This pattern reads naturally: promote the key to index, join, demote the key back, which makes the intent clear to future readers of your code.
orders_col = pd.DataFrame({'order_id': [1,2,3], 'customer_id': [10,20,10], 'amount': [100,200,150]})
cust_col = pd.DataFrame({'customer_id': [10,20], 'name': ['Alice','Bob']})
result = (
orders_col.set_index('customer_id')
.join(cust_col.set_index('customer_id'), how='left')
.reset_index()
)
print(result)Aligning Series to a DataFrame Index
A Series also has an index, and you can add it as a new column to a DataFrame using assignment — Pandas automatically aligns the Series values to matching index labels. This is a lightweight form of an index-based join that works when you want to add a single column from a Series without calling merge() or join().
df = pd.DataFrame({'sales': [100, 200, 150]}, index=['A', 'B', 'C'])
tax_rate = pd.Series({'A': 0.1, 'B': 0.2, 'C': 0.15})
# Index alignment: Series aligns to DataFrame index automatically
df['tax'] = df['sales'] * tax_rate
print(df)
# sales tax
# A 100 10.0
# B 200 40.0
# C 150 22.5Index Join Performance
Joining on a sorted index is faster than joining on a regular column because Pandas uses binary search on the sorted index. If you repeatedly join on the same key, set that key as the index once at the start of your pipeline. This is especially important in time series workflows where you join on a DatetimeIndex thousands of times across many files.
# Sort index before repeated joins for speed
left = left.sort_index()
right = right.sort_index()
# Both sorted -> Pandas uses merge path with binary search
result = left.join(right, how='inner')
# Check if index is sorted
print('Left sorted:', left.index.is_monotonic_increasing)
print('Right sorted:', right.index.is_monotonic_increasing)Quick Check
Test your understanding of index-based joins from this lesson.
Lesson Recap
In this lesson you learned: DataFrame.join() joins on the index by default with a left join; pd.merge() with left_index=True/right_index=True offers more control; you can join multiple DataFrames at once by passing a list to join(); and using a sorted index improves join performance. Next up we explore reshaping DataFrames with pivot_table.
Frequently asked questions
Is the “Joining on the Index” lesson free?
Yes — the full text of “Joining on the Index” is free to read here on the web, and the Pandas & NumPy Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the Pandas & NumPy Academy course, upgrade to CoddyKit PRO.
What will I learn in “Joining on the Index”?
Use DataFrame.join() and set left_index/right_index=True in merge() to combine DataFrames aligned by their index. You practise Pandas & NumPy Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start Pandas & NumPy Academy?
No prior experience is required. Pandas & NumPy Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Joining on the Index” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this Pandas & NumPy Academy lesson?
Yes. Every Pandas & NumPy Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.