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.

Practice

1.

What does merging on multiple keys in pandas allow you to do?

Choose the best explanation.

easy

A. Join dataframes by adding new columns without matching rows.

B. Merge dataframes only by index without considering columns.

C. Sort dataframes based on multiple columns before merging.

D. Combine two dataframes using more than one column to match rows.

Solution

1. Step 1: Understand merging concept

   Merging combines rows from two dataframes based on matching column values.

2. Step 2: Identify multiple keys usage

   Using multiple keys means matching rows where all specified columns have the same values.

3. Final Answer:

   Combine two dataframes using more than one column to match rows. -> Option D

4. Quick Check:

   Merging on multiple keys = combine on several columns [OK]

Hint: Think of matching rows by multiple columns at once [OK]

Common Mistakes:

• Confusing merge with concatenation
• Using only one column when multiple are needed
• Merging without specifying keys
• Assuming merge sorts data automatically

2.

Which of the following is the correct syntax to merge two pandas DataFrames df1 and df2 on columns 'city' and 'year'?

import pandas as pd

# Assume df1 and df2 are defined
merged_df = 

easy

A. pd.merge(df1, df2, on=['city', 'year'])

B. pd.merge(df1, df2, keys=['city', 'year'])

C. df1.merge(df2, by=['city', 'year'])

D. df1.join(df2, on=['city', 'year'])

Solution

1. Step 1: Recall pandas merge syntax

   The correct parameter to specify columns for merging is on with a list of column names.

2. Step 2: Check each option

   pd.merge(df1, df2, on=['city', 'year']) uses on=['city', 'year'], which is correct. Others use wrong parameters like keys or by.

3. Final Answer:

   pd.merge(df1, df2, on=['city', 'year']) -> Option A

4. Quick Check:

   Use on for columns list in merge [OK]

Hint: Use 'on' parameter with list of columns for multiple keys [OK]

Common Mistakes:

• Using 'keys' instead of 'on'
• Using 'by' instead of 'on'
• Using join() instead of merge() for multiple keys
• Passing columns as a string instead of list

3.

Given the following dataframes:

import pandas as pd

df1 = pd.DataFrame({
    'city': ['NY', 'LA', 'NY', 'SF'],
    'year': [2020, 2020, 2021, 2020],
    'value1': [10, 20, 30, 40]
})

df2 = pd.DataFrame({
    'city': ['NY', 'LA', 'NY', 'SF'],
    'year': [2020, 2021, 2021, 2020],
    'value2': [100, 200, 300, 400]
})

merged = pd.merge(df1, df2, on=['city', 'year'], how='inner')
print(merged)

What will be the output?

medium

A. All rows from df1 with matching rows from df2 or NaN if no match

B. All rows from df2 with matching rows from df1 or NaN if no match

C. Rows where city and year match in both dfs: NY-2020, NY-2021, SF-2020

D. Concatenation of df1 and df2 ignoring keys

Solution

1. Step 1: Understand inner merge on multiple keys

   Inner merge returns rows where all keys match in both dataframes.

2. Step 2: Identify matching rows

   Matching pairs are: ('NY', 2020), ('NY', 2021), and ('SF', 2020). 'LA' with year 2020 or 2021 does not match in both.

3. Final Answer:

   Rows where city and year match in both dfs: NY-2020, NY-2021, SF-2020 -> Option C

4. Quick Check:

   Inner merge = only matching keys rows [OK]

Hint: Inner merge keeps only rows matching all keys [OK]

Common Mistakes:

• Confusing inner with outer merge
• Expecting all rows from one dataframe
• Ignoring that both keys must match
• Assuming concatenation instead of merge

4.

Consider this code snippet:

import pandas as pd

df1 = pd.DataFrame({'A': [1, 2], 'B': [3, 4]})
df2 = pd.DataFrame({'A': [1, 2], 'B': [3, 5]})

merged = pd.merge(df1, df2, on=['A', 'B'])
print(merged)

What is the issue if the output is an empty dataframe?

medium

A. The values in column 'B' do not match exactly in both dataframes for all rows.

B. The merge syntax is incorrect; 'on' should be 'by'.

C. DataFrames must have the same number of rows to merge.

D. You cannot merge on multiple columns with the same names.

Solution

1. Step 1: Check merge keys values

   Rows merge only if all key columns match exactly. Here, df1 has B=4 in second row, df2 has B=5.

2. Step 2: Understand empty result cause

   Because second rows differ in 'B', only first rows match. First rows have A=1 and B=3 in both, so one row should merge. If output is empty, likely no exact matches.

3. Final Answer:

   The values in column 'B' do not match exactly in both dataframes for all rows. -> Option A

4. Quick Check:

   Exact key match needed for merge rows [OK]

Hint: Check all key columns have matching values for merge [OK]

Common Mistakes:

• Thinking merge needs same row count
• Using wrong parameter name 'by'
• Assuming merge works without exact key matches
• Believing multiple keys with same names cause error

5.

You have two dataframes:

df_sales = pd.DataFrame({
    'store': ['A', 'B', 'A', 'B'],
    'month': [1, 1, 2, 2],
    'sales': [100, 200, 150, 250]
})

df_targets = pd.DataFrame({
    'store': ['A', 'B'],
    'month': [1, 2],
    'target': [120, 230]
})

You want to merge these to compare sales with targets for matching store and month. Which merge type should you use to keep all sales data and add targets where available?

hard

A. Use how='outer' to keep all rows from both dataframes.

B. Use how='left' to keep all sales rows and add matching targets.

C. Use how='right' to keep all targets and add matching sales.

D. Use how='inner' to keep only rows with matching store and month in both.

Solution

1. Step 1: Identify main dataframe to keep

   You want to keep all sales data, so df_sales is the left/main dataframe.

2. Step 2: Choose merge type

   Left merge keeps all rows from left dataframe and adds matching rows from right. Missing matches get NaN.

3. Final Answer:

   Use how='left' to keep all sales rows and add matching targets. -> Option B

4. Quick Check:

   Left merge = keep left all, add right matches [OK]

Hint: Left merge keeps all left rows, adds matching right rows [OK]

Common Mistakes:

• Using inner merge and losing unmatched sales
• Using right merge and losing unmatched sales
• Using outer merge and getting extra unmatched rows
• Confusing which dataframe is left or right