Merging on multiple keys in Data Analysis Python

Introduction

Merging on multiple keys helps you combine data from two tables using more than one matching column. This gives more accurate matches when one column alone is not enough.

You have sales data split by store and date in two tables and want to combine them.

You want to join customer info and orders using both customer ID and region.

You need to merge employee records from two systems using employee ID and department.

You want to combine product details and inventory using product ID and warehouse location.

Syntax

Data Analysis Python

merged_df = df1.merge(df2, on=['key1', 'key2'], how='inner')

The on parameter takes a list of column names to match on.

The how parameter controls the type of merge: 'inner', 'left', 'right', or 'outer'.

Examples

Merge two dataframes on columns 'city' and 'date' using inner join by default.

Data Analysis Python

merged = df1.merge(df2, on=['city', 'date'])

Keep all rows from df1 and add matching rows from df2 based on 'user_id' and 'product_id'.

Data Analysis Python

merged = df1.merge(df2, on=['user_id', 'product_id'], how='left')

Combine all rows from both dataframes, matching on 'employee' and 'department'.

Data Analysis Python

merged = df1.merge(df2, on=['employee', 'department'], how='outer')

Sample Program

This code merges two sales dataframes on both 'store' and 'date' columns. It keeps only rows where both store and date match in both tables.

Data Analysis Python

import pandas as pd

# Create first dataframe
sales1 = pd.DataFrame({
    'store': ['A', 'A', 'B', 'B'],
    'date': ['2024-01-01', '2024-01-02', '2024-01-01', '2024-01-02'],
    'sales': [100, 150, 200, 250]
})

# Create second dataframe
sales2 = pd.DataFrame({
    'store': ['A', 'B', 'B', 'C'],
    'date': ['2024-01-01', '2024-01-01', '2024-01-03', '2024-01-02'],
    'discount': [5, 10, 15, 20]
})

# Merge on both 'store' and 'date'
merged_sales = sales1.merge(sales2, on=['store', 'date'], how='inner')

print(merged_sales)

Important Notes

If the column names differ in the two dataframes, use left_on and right_on to specify keys.

Using multiple keys reduces wrong matches when one key alone is not unique.

Summary

Merging on multiple keys combines data using more than one column for better matching.

Use a list of column names in the on parameter.

Choose the merge type with the how parameter.

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