merge() for SQL-style joins in Data Analysis Python - Step-by-Step Execution

Concept Flow - merge() for SQL-style joins

Start with two DataFrames

↓

Choose join key(s)

↓

Select join type: inner, left, right, outer

↓

Match rows based on keys

↓

Combine matched rows

↓

Handle unmatched rows per join type

↓

Return merged DataFrame

The merge() function takes two tables, matches rows by keys, combines them based on join type, and returns the joined table.

Execution Sample

Data Analysis Python

import pandas as pd

left = pd.DataFrame({'key': ['A', 'B', 'C'], 'left_val': [1, 2, 3]})
right = pd.DataFrame({'key': ['B', 'C', 'D'], 'right_val': [4, 5, 6]})

result = pd.merge(left, right, on='key', how='inner')
print(result)

This code merges two DataFrames on 'key' using an inner join, keeping only matching keys.

Execution Table

Step	Action	Left DataFrame Rows	Right DataFrame Rows	Matching Keys	Result Rows
1	Start with left and right DataFrames	[A:1, B:2, C:3]	[B:4, C:5, D:6]	-	-
2	Choose join key 'key'	-	-	-	-
3	Select join type 'inner'	-	-	-	-
4	Find matching keys in both: B, C	-	-	[B, C]	-
5	Combine rows with keys B and C	-	-	-	[B:2,4; C:3,5]
6	Return merged DataFrame with 2 rows	-	-	-	[B:2,4; C:3,5]

💡 Inner join keeps only keys present in both DataFrames (B and C).

Variable Tracker

Variable	Start	After Step 4	After Step 5	Final
left	[A:1, B:2, C:3]	[A:1, B:2, C:3]	[A:1, B:2, C:3]	[A:1, B:2, C:3]
right	[B:4, C:5, D:6]	[B:4, C:5, D:6]	[B:4, C:5, D:6]	[B:4, C:5, D:6]
matching_keys	None	[B, C]	[B, C]	[B, C]
result	None	None	[B:2,4; C:3,5]	[B:2,4; C:3,5]

Key Moments - 2 Insights

Why does the merged result only have keys B and C, not A or D?

Because the join type is 'inner', merge() keeps only rows with keys present in both DataFrames, which are B and C (see execution_table step 4).

What happens if we change 'how' to 'left'?

All keys from the left DataFrame are kept, and matching rows from right are added. Unmatched right rows become NaN (not shown in this trace).

Visual Quiz - 3 Questions

Look at the execution_table at step 5, how many rows does the result have?

A 1 row

B 3 rows

C 2 rows

D 4 rows

💡 Hint

Check the 'Result Rows' column at step 5 in execution_table.

At which step do we identify the keys that exist in both DataFrames?

A Step 2

B Step 4

C Step 6

D Step 3

💡 Hint

Look for the step mentioning 'Find matching keys' in execution_table.

If we change 'how' to 'outer', what would happen to keys A and D?

A They would appear with NaN for missing columns

B Only key A would appear

C They would be excluded

D Only key D would appear

💡 Hint

Recall that 'outer' join keeps all keys from both DataFrames, filling missing data with NaN.

Concept Snapshot

merge(left, right, on=key, how=join_type)
- Joins two DataFrames on key(s)
- how options: inner (default), left, right, outer
- inner: keep keys in both
- left: keep all left keys
- right: keep all right keys
- outer: keep all keys from both

Full Transcript

The merge() function in pandas combines two tables by matching rows on specified keys. You pick which keys to join on and the type of join: inner, left, right, or outer. Inner join keeps only rows with keys in both tables. Left join keeps all rows from the left table and matches from the right. Right join keeps all from right and matches from left. Outer join keeps all rows from both tables, filling missing data with NaN. The example merges two small tables on the 'key' column using inner join, resulting in rows only for keys present in both tables. This step-by-step trace shows how keys are matched and rows combined.

Practice

1. What does the how parameter in merge() control when joining two DataFrames?

easy

A. The data types of the columns

B. The columns to join on

C. The order of rows in the result

D. The type of join: inner, left, right, or outer

Solution

1. Step 1: Understand the role of how in merge()

   The how parameter specifies the type of join to perform, such as inner, left, right, or outer join.

2. Step 2: Differentiate from other parameters

   Other parameters like on specify columns to join on, but how controls how rows are matched and combined.

3. Final Answer:

   The type of join: inner, left, right, or outer -> Option D

4. Quick Check:

   how = join type [OK]

Hint: Remember: 'how' means join type like SQL joins [OK]

Common Mistakes:

• Confusing 'how' with 'on' parameter
• Thinking 'how' controls column selection
• Assuming 'how' changes data types

2. Which of the following is the correct syntax to merge two DataFrames df1 and df2 on the column 'id' using an inner join?

easy

A. pd.merge(df1, df2, join='id', how='inner')

B. pd.merge(df1, df2, on='id', how='inner')

C. df1.merge(df2, on='id', type='inner')

D. pd.merge(df1, df2, on='id', join='inner')

Solution

1. Step 1: Identify correct parameter names

   The correct parameters for merging are on for the column and how for join type.

2. Step 2: Check syntax correctness

   pd.merge(df1, df2, on='id', how='inner') uses pd.merge(df1, df2, on='id', how='inner'), which is the correct syntax. Other options use incorrect parameter names like join or type.

3. Final Answer:

   pd.merge(df1, df2, on='id', how='inner') -> Option B

4. Quick Check:

   Correct parameters: on, how [OK]

Hint: Use 'on' for columns and 'how' for join type [OK]

Common Mistakes:

• Using 'join' instead of 'how'
• Using 'type' instead of 'how'
• Mixing parameter names

3. Given the following code, what is the output of result?

import pandas as pd

df1 = pd.DataFrame({'key': ['A', 'B', 'C'], 'val1': [1, 2, 3]})
df2 = pd.DataFrame({'key': ['B', 'C', 'D'], 'val2': [4, 5, 6]})

result = pd.merge(df1, df2, on='key', how='inner')
print(result)

medium

A. key val1 val2
0 B 2 4
1 C 3 5

B. key val1 val2
0 A 1 4
1 B 2 5
2 C 3 6

C. key val1 val2
0 A 1 NaN
1 B 2 4
2 C 3 5

D. key val1 val2
0 B 2 NaN
1 C 3 NaN
2 D NaN 6

Solution

1. Step 1: Understand inner join on 'key'

   Inner join keeps only rows with keys present in both DataFrames: 'B' and 'C'.

2. Step 2: Match rows and combine columns

   For 'B': val1=2, val2=4; for 'C': val1=3, val2=5. Rows with 'A' and 'D' are excluded.

3. Final Answer:

   key val1 val2 0 B 2 4 1 C 3 5 -> Option A

4. Quick Check:

   Inner join = intersection keys [OK]

Hint: Inner join keeps only common keys [OK]

Common Mistakes:

• Including keys not in both DataFrames
• Confusing inner with outer join
• Misaligning values for keys

4. What is wrong with this code snippet?

import pandas as pd

df1 = pd.DataFrame({'id': [1, 2], 'val': ['A', 'B']})
df2 = pd.DataFrame({'key': [1, 3], 'val2': ['X', 'Y']})

result = pd.merge(df1, df2, on='id', how='left')
print(result)

medium

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

B. The 'how' parameter cannot be 'left'

C. The merge function is missing the 'left_on' and 'right_on' parameters

D. Column 'id' does not exist in both DataFrames

Solution

1. Step 1: Check columns used for merging

   The code tries to merge on column 'id', but df2 has 'key' column instead of 'id'.

2. Step 2: Understand merge requirements

   For merging on different column names, left_on and right_on must be used, or columns must have the same name.

3. Final Answer:

   The merge function is missing the 'left_on' and 'right_on' parameters -> Option C

4. Quick Check:

   Merge columns must exist in both DataFrames or be specified [OK]

Hint: Check column names exist in both DataFrames or specify left_on/right_on [OK]

Common Mistakes:

• Assuming merge works with different column names without specifying
• Thinking 'how' parameter is invalid
• Believing row counts must match

5. You have two DataFrames: orders with columns order_id, customer_id, and amount, and customers with columns cust_id and name. How do you merge them to get all orders with customer names, even if some customers are missing in customers DataFrame?

hard

A. pd.merge(orders, customers, left_on='customer_id', right_on='cust_id', how='left')

B. pd.merge(orders, customers, on='customer_id', how='inner')

C. pd.merge(orders, customers, left_on='cust_id', right_on='customer_id', how='right')

D. pd.merge(orders, customers, on='cust_id', how='outer')

Solution

1. Step 1: Identify join keys with different column names

   Orders use 'customer_id', customers use 'cust_id', so use left_on and right_on to specify.

2. Step 2: Choose join type to keep all orders

   Use how='left' to keep all rows from orders, even if no matching customer exists.

3. Final Answer:

   pd.merge(orders, customers, left_on='customer_id', right_on='cust_id', how='left') -> Option A

4. Quick Check:

   Left join keeps all left rows [OK]

Hint: Use left_on/right_on for different keys and 'left' to keep all left rows [OK]

Common Mistakes:

• Using 'on' with different column names
• Using inner join and losing unmatched orders
• Swapping left_on and right_on