Merging on multiple keys in Data Analysis Python - Mini Project: Build & Apply

Merging on multiple keys

📖 Scenario: You work in a store's data team. You have two tables: one with sales data and another with product details. Both tables share two columns: store_id and product_id. You want to combine these tables to see sales along with product info.

🎯 Goal: Build a Python program that merges two data tables on the two keys store_id and product_id using pandas. The final output should show sales data with product names and categories.

📋 What You'll Learn

Create two pandas DataFrames named sales and products with exact data given.

Create a list variable named keys containing the two keys 'store_id' and 'product_id'.

Use pandas merge function with on=keys to join sales and products.

Print the merged DataFrame.

💡 Why This Matters

🌍 Real World

Merging data on multiple keys is common in business when combining sales, inventory, or customer data from different sources.

💼 Career

Data analysts and data scientists often merge datasets on multiple columns to prepare data for analysis or reporting.

1

Create the sales and products DataFrames

Create a pandas DataFrame called sales with these rows: {'store_id': 1, 'product_id': 101, 'units_sold': 5}, {'store_id': 1, 'product_id': 102, 'units_sold': 3}, {'store_id': 2, 'product_id': 101, 'units_sold': 2}. Also create a pandas DataFrame called products with these rows: {'store_id': 1, 'product_id': 101, 'product_name': 'Pen', 'category': 'Stationery'}, {'store_id': 1, 'product_id': 102, 'product_name': 'Notebook', 'category': 'Stationery'}, {'store_id': 2, 'product_id': 101, 'product_name': 'Pen', 'category': 'Stationery'}.

Data Analysis Python

import pandas as pd
# Create the sales DataFrame
# Create the products DataFrame

Hint

Use pd.DataFrame with a list of dictionaries to create each DataFrame.

2

Create the list of keys for merging

Create a list variable called keys that contains the strings 'store_id' and 'product_id' in that order.

Data Analysis Python

import pandas as pd
sales = pd.DataFrame([
    {'store_id': 1, 'product_id': 101, 'units_sold': 5},
    {'store_id': 1, 'product_id': 102, 'units_sold': 3},
    {'store_id': 2, 'product_id': 101, 'units_sold': 2}
])
products = pd.DataFrame([
    {'store_id': 1, 'product_id': 101, 'product_name': 'Pen', 'category': 'Stationery'},
    {'store_id': 1, 'product_id': 102, 'product_name': 'Notebook', 'category': 'Stationery'},
    {'store_id': 2, 'product_id': 101, 'product_name': 'Pen', 'category': 'Stationery'}
])
# Create the list keys with 'store_id' and 'product_id'

Hint

Use a Python list with the two key strings in order.

3

Merge the sales and products DataFrames on multiple keys

Create a new DataFrame called merged by merging sales and products using pd.merge with the on=keys argument.

Data Analysis Python

import pandas as pd
sales = pd.DataFrame([
    {'store_id': 1, 'product_id': 101, 'units_sold': 5},
    {'store_id': 1, 'product_id': 102, 'units_sold': 3},
    {'store_id': 2, 'product_id': 101, 'units_sold': 2}
])
products = pd.DataFrame([
    {'store_id': 1, 'product_id': 101, 'product_name': 'Pen', 'category': 'Stationery'},
    {'store_id': 1, 'product_id': 102, 'product_name': 'Notebook', 'category': 'Stationery'},
    {'store_id': 2, 'product_id': 101, 'product_name': 'Pen', 'category': 'Stationery'}
])
keys = ['store_id', 'product_id']
# Merge sales and products on keys into merged

Hint

Use pd.merge(sales, products, on=keys) to join on multiple columns.

4

Print the merged DataFrame

Write a print statement to display the merged DataFrame.

Data Analysis Python

import pandas as pd
sales = pd.DataFrame([
    {'store_id': 1, 'product_id': 101, 'units_sold': 5},
    {'store_id': 1, 'product_id': 102, 'units_sold': 3},
    {'store_id': 2, 'product_id': 101, 'units_sold': 2}
])
products = pd.DataFrame([
    {'store_id': 1, 'product_id': 101, 'product_name': 'Pen', 'category': 'Stationery'},
    {'store_id': 1, 'product_id': 102, 'product_name': 'Notebook', 'category': 'Stationery'},
    {'store_id': 2, 'product_id': 101, 'product_name': 'Pen', 'category': 'Stationery'}
])
keys = ['store_id', 'product_id']
merged = pd.merge(sales, products, on=keys)
# Print the merged DataFrame

Hint

Use print(merged) to show the combined table.