Merging on index in Pandas - Mini Project: Build & Apply

Merging DataFrames on Index with pandas

📖 Scenario: You work in a small company that keeps sales data and customer data in separate tables. You want to combine these tables to see all information together by matching their indexes.

🎯 Goal: Build a pandas DataFrame merge operation that joins two DataFrames using their indexes.

📋 What You'll Learn

Create two pandas DataFrames with specific data and indexes

Define a merge configuration to join on indexes

Use pandas merge function with left_index=True and right_index=True

Complete the merge to produce a combined DataFrame

💡 Why This Matters

🌍 Real World

Combining sales and customer data by matching their unique IDs helps businesses analyze transactions with customer details.

💼 Career

Data analysts and data scientists often merge datasets on indexes to prepare data for reporting and analysis.

1

Create two pandas DataFrames with indexes

Import pandas as pd. Create a DataFrame called sales with columns 'Product' and 'Amount' and index labels 101, 102, 103. The data should be: 'Product': ['Pen', 'Notebook', 'Pencil'] and 'Amount': [10, 20, 15]. Also create a DataFrame called customers with column 'Customer' and the same index labels 101, 102, 103. The data should be 'Customer': ['Alice', 'Bob', 'Charlie'].

Pandas

# Import pandas and create the sales and customers DataFrames with specified data and indexes
# Your code here

Need a hint?

Use pd.DataFrame with a dictionary for columns and specify index as a list of numbers.

2

Set merge configuration to join on indexes

Create a variable called merge_config as a dictionary with keys 'left_index' and 'right_index' both set to True. This will tell pandas to merge on the indexes of both DataFrames.

Pandas

import pandas as pd

sales = pd.DataFrame({
    'Product': ['Pen', 'Notebook', 'Pencil'],
    'Amount': [10, 20, 15]
}, index=[101, 102, 103])

customers = pd.DataFrame({
    'Customer': ['Alice', 'Bob', 'Charlie']
}, index=[101, 102, 103])

# Create merge_config dictionary with left_index and right_index set to True
# Your code here

Need a hint?

Use a dictionary with keys 'left_index' and 'right_index' both set to True.

3

Merge the DataFrames on their indexes

Use pd.merge to merge sales and customers using the merge_config dictionary as keyword arguments. Assign the result to a variable called merged_df.

Pandas

import pandas as pd

sales = pd.DataFrame({
    'Product': ['Pen', 'Notebook', 'Pencil'],
    'Amount': [10, 20, 15]
}, index=[101, 102, 103])

customers = pd.DataFrame({
    'Customer': ['Alice', 'Bob', 'Charlie']
}, index=[101, 102, 103])

merge_config = {'left_index': True, 'right_index': True}

# Merge sales and customers using pd.merge with merge_config
# Your code here

Need a hint?

Use pd.merge(sales, customers, **merge_config) to merge on indexes.

4

Complete by setting the merge type

Update the merge_config dictionary to include the key 'how' with the value 'inner' to specify an inner join. This completes the merge configuration.

Pandas

import pandas as pd

sales = pd.DataFrame({
    'Product': ['Pen', 'Notebook', 'Pencil'],
    'Amount': [10, 20, 15]
}, index=[101, 102, 103])

customers = pd.DataFrame({
    'Customer': ['Alice', 'Bob', 'Charlie']
}, index=[101, 102, 103])

merge_config = {'left_index': True, 'right_index': True}

merged_df = pd.merge(sales, customers, **merge_config)

# Add 'how': 'inner' to merge_config dictionary
# Your code here

Need a hint?

Add the key 'how' with value 'inner' to the merge_config dictionary.