Merging on multiple keys in Data Analysis Python - Step-by-Step Execution

Concept Flow - Merging on multiple keys

Start with two DataFrames

↓

Identify multiple keys to join on

↓

Match rows where all keys are equal

↓

Combine matched rows into one

↓

Result: merged DataFrame with combined info

↓

End

We take two tables, find rows where all key columns match, and join their data into one combined table.

Execution Sample

Data Analysis Python

import pandas as pd

df1 = pd.DataFrame({'City': ['NY', 'LA', 'NY'], 'Year': [2020, 2020, 2021], 'Pop': [8, 4, 8.3]})
df2 = pd.DataFrame({'City': ['NY', 'LA', 'NY'], 'Year': [2020, 2020, 2022], 'GDP': [1500, 1000, 1600]})

merged = pd.merge(df1, df2, on=['City', 'Year'])
print(merged)

This code merges two tables on both 'City' and 'Year' columns, combining rows where both match.

Execution Table

Step	Action	df1 Row	df2 Row	Keys Matched?	Merged Row
1	Check df1 row 0 and df2 row 0	{City: NY, Year: 2020, Pop: 8}	{City: NY, Year: 2020, GDP: 1500}	Yes	{City: NY, Year: 2020, Pop: 8, GDP: 1500}
2	Check df1 row 0 and df2 row 1	{City: NY, Year: 2020, Pop: 8}	{City: LA, Year: 2020, GDP: 1000}	No	No merge
3	Check df1 row 0 and df2 row 2	{City: NY, Year: 2020, Pop: 8}	{City: NY, Year: 2022, GDP: 1600}	No	No merge
4	Check df1 row 1 and df2 row 0	{City: LA, Year: 2020, Pop: 4}	{City: NY, Year: 2020, GDP: 1500}	No	No merge
5	Check df1 row 1 and df2 row 1	{City: LA, Year: 2020, Pop: 4}	{City: LA, Year: 2020, GDP: 1000}	Yes	{City: LA, Year: 2020, Pop: 4, GDP: 1000}
6	Check df1 row 1 and df2 row 2	{City: LA, Year: 2020, Pop: 4}	{City: NY, Year: 2022, GDP: 1600}	No	No merge
7	Check df1 row 2 and df2 row 0	{City: NY, Year: 2021, Pop: 8.3}	{City: NY, Year: 2020, GDP: 1500}	No	No merge
8	Check df1 row 2 and df2 row 1	{City: NY, Year: 2021, Pop: 8.3}	{City: LA, Year: 2020, GDP: 1000}	No	No merge
9	Check df1 row 2 and df2 row 2	{City: NY, Year: 2021, Pop: 8.3}	{City: NY, Year: 2022, GDP: 1600}	No	No merge
10	Merge complete	-	-	-	2 rows merged

💡 No more rows to check; merge finished with 2 matching rows on both keys.

Variable Tracker

Variable	Start	After Step 1	After Step 5	Final
merged_rows	[]	[{City: NY, Year: 2020, Pop: 8, GDP: 1500}]	[{City: NY, Year: 2020, Pop: 8, GDP: 1500}, {City: LA, Year: 2020, Pop: 4, GDP: 1000}]	[{City: NY, Year: 2020, Pop: 8, GDP: 1500}, {City: LA, Year: 2020, Pop: 4, GDP: 1000}]

Key Moments - 2 Insights

Why do some rows from df1 not appear in the merged result?

Only rows where all keys ('City' and 'Year') match in both tables are merged. Rows without matching keys are excluded, as shown in execution_table steps 7-9.

What happens if only one key matches but not the other?

Rows are not merged unless all keys match. For example, in step 2, 'City' matches but 'Year' does not, so no merge occurs.

Visual Quiz - 3 Questions

Look at the execution table, what is the merged row at step 5?

A No merge

B {City: NY, Year: 2020, Pop: 8, GDP: 1500}

C {City: LA, Year: 2020, Pop: 4, GDP: 1000}

D {City: NY, Year: 2022, Pop: 8.3, GDP: 1600}

💡 Hint

Check the 'Merged Row' column at step 5 in the execution_table.

At which step does the condition 'Keys Matched?' become false for df1 row 0?

A Step 2

B Step 1

C Step 5

D Step 10

💡 Hint

Look at steps 1-3 for df1 row 0 in the execution_table under 'Keys Matched?'.

If we add a row to df2 with City='NY' and Year=2021, what would happen in the merged result?

A No change, because keys must match exactly

B A new merged row with Pop=8.3 and GDP from new df2 row appears

C All rows from df1 will merge with this new row

D Merge will fail with error

💡 Hint

Refer to variable_tracker and how matching keys create merged rows.

Concept Snapshot

pd.merge(df1, df2, on=[key1, key2])
- Joins two DataFrames on multiple columns
- Only rows with all keys matching are merged
- Result contains combined columns from both
- Useful for detailed matching like city and year
- Non-matching rows are excluded by default

Full Transcript

Merging on multiple keys means combining two tables by matching rows where all specified columns are equal. We start with two DataFrames, identify the keys to join on, then check each row pair to see if all keys match. If they do, we combine their data into one row in the result. Rows without matching keys are left out. This process helps us combine related data from different sources precisely. The example code merges on 'City' and 'Year', producing a table with population and GDP where both match. The execution table shows step-by-step how each row pair is checked and merged or skipped. Variables track the growing list of merged rows. Key moments clarify why some rows don't merge and the importance of all keys matching. The quiz tests understanding of these steps and outcomes.

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