Left and right joins in Data Analysis Python - Step-by-Step Execution

Concept Flow - Left and right joins

Start with two tables

↓

Choose join type

↓

Left Join

↓

Match keys in left table

↓

Keep all rows from left

↓

Fill missing with NaN

↓

Resulting joined table

Left join keeps all rows from the left table and matches from right; right join keeps all rows from right and matches from left.

Execution Sample

Data Analysis Python

import pandas as pd

left = pd.DataFrame({'key': [1, 2, 3], 'A': ['a1', 'a2', 'a3']})
right = pd.DataFrame({'key': [2, 3, 4], 'B': ['b2', 'b3', 'b4']})

left_join = pd.merge(left, right, on='key', how='left')
right_join = pd.merge(left, right, on='key', how='right')

This code creates two small tables and performs left and right joins on the 'key' column.

Execution Table

Step	Action	Left Table Row	Right Table Row	Join Type	Result Row	Notes
1	Start with left row key=1	key=1, A=a1	No match in right	Left	key=1, A=a1, B=NaN	Left join keeps left row, fills missing right with NaN
2	Start with left row key=2	key=2, A=a2	key=2, B=b2	Left	key=2, A=a2, B=b2	Keys match, merge values
3	Start with left row key=3	key=3, A=a3	key=3, B=b3	Left	key=3, A=a3, B=b3	Keys match, merge values
4	Start with right row key=2	key=2, A=a2	key=2, B=b2	Right	key=2, A=a2, B=b2	Keys match, merge values
5	Start with right row key=3	key=3, A=a3	key=3, B=b3	Right	key=3, A=a3, B=b3	Keys match, merge values
6	Start with right row key=4	No match in left	key=4, B=b4	Right	key=4, A=NaN, B=b4	Right join keeps right row, fills missing left with NaN
7	No more rows	-	-	-	-	Join complete

💡 All rows from left (left join) or right (right join) processed; unmatched rows filled with NaN.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 5	After Step 6	Final
left_join	empty	[key=1,A=a1,B=NaN]	[key=1,A=a1,B=NaN; key=2,A=a2,B=b2]	[key=1,A=a1,B=NaN; key=2,A=a2,B=b2; key=3,A=a3,B=b3]	same	same	same	final left join table
right_join	empty	empty	empty	empty	[key=2,A=a2,B=b2]	[key=2,A=a2,B=b2; key=3,A=a3,B=b3]	[key=2,A=a2,B=b2; key=3,A=a3,B=b3; key=4,A=NaN,B=b4]	final right join table

Key Moments - 3 Insights

Why does the left join keep the row with key=1 even though it has no match in the right table?

Because left join keeps all rows from the left table regardless of matches, filling missing right columns with NaN as shown in execution_table row 1.

Why does the right join include the row with key=4 even though it has no match in the left table?

Right join keeps all rows from the right table, filling missing left columns with NaN, as seen in execution_table row 6.

What happens to rows with matching keys in both tables during the join?

Rows with matching keys are merged combining columns from both tables, shown in execution_table rows 2,3 (left join) and 4,5 (right join).

Visual Quiz - 3 Questions

Look at the execution_table, what is the value of B in the left_join result for key=1?

A b1

B b2

C NaN

D Empty string

💡 Hint

Check execution_table row 1 where left key=1 has no matching right row, so B is NaN.

At which step does the right join add a row with NaN in column A?

A Step 6

B Step 5

C Step 4

D Step 2

💡 Hint

Look at execution_table row 6 where right key=4 has no left match, so A is NaN.

If the join type changes from 'left' to 'inner', what would happen to the row with key=1?

A It would be kept with NaN values

B It would be dropped from the result

C It would be duplicated

D It would appear with values from right table

💡 Hint

Inner join keeps only matching keys; see how left join keeps unmatched rows in execution_table row 1.

Concept Snapshot

Left join: keeps all rows from left table, adds matching right rows, fills missing with NaN.
Right join: keeps all rows from right table, adds matching left rows, fills missing with NaN.
Use pd.merge(left, right, on='key', how='left' or 'right') in pandas.
Unmatched rows get NaN in missing columns.
Useful to preserve data from one side while adding info from the other.

Full Transcript

This visual execution shows how left and right joins work in data analysis using pandas. We start with two tables having a key column. Left join keeps all rows from the left table and adds matching rows from the right table, filling missing right columns with NaN. Right join keeps all rows from the right table and adds matching rows from the left table, filling missing left columns with NaN. The execution table traces each row processed, showing how matches merge and unmatched rows fill with NaN. Variable tracking shows how the joined tables build up step by step. Key moments clarify why unmatched rows appear and how matching rows merge. The quiz tests understanding of NaN placement and join behavior. The snapshot summarizes the key points and syntax for quick reference.

Practice

1. What does a left join do when combining two tables in data analysis?

easy

A. Keeps all rows from both tables, matching or not.

B. Keeps all rows from the left table and adds matching rows from the right table.

C. Keeps only rows that match in both tables.

D. Keeps all rows from the right table and adds matching rows from the left table.

Solution

1. Step 1: Understand the role of the left table in a left join

   A left join keeps every row from the left table regardless of matches in the right table.

2. Step 2: Understand how matching rows from the right table are added

   Only rows from the right table that match the left table's keys are added; unmatched right rows are ignored.

3. Final Answer:

   Keeps all rows from the left table and adds matching rows from the right table. -> Option B

4. Quick Check:

   Left join = all left rows + matching right rows [OK]

Hint: Left join keeps all left rows, adds right matches [OK]

Common Mistakes:

• Confusing left join with right join
• Thinking left join keeps unmatched right rows
• Believing left join only keeps matching rows

2. Which of the following is the correct syntax for a left join using pandas in Python?

easy

A. pd.merge(df1, df2, how='left', on='key')

B. pd.merge(df1, df2, how='right', on='key')

C. pd.join(df1, df2, how='left')

D. df1.left_join(df2, on='key')

Solution

1. Step 1: Recall pandas merge syntax for joins

   In pandas, the function to join tables is pd.merge(), specifying the join type with the 'how' parameter.

2. Step 2: Identify the correct parameters for a left join

   Using how='left' and on='key' correctly performs a left join on the specified key column.

3. Final Answer:

   pd.merge(df1, df2, how='left', on='key') -> Option A

4. Quick Check:

   pd.merge + how='left' = left join [OK]

Hint: Use pd.merge with how='left' for left join [OK]

Common Mistakes:

• Using pd.join instead of pd.merge
• Wrong 'how' parameter like 'right' or missing
• Calling non-existent left_join method

3. Given these two DataFrames:

df1 = pd.DataFrame({'id': [1, 2, 3], 'value': ['A', 'B', 'C']})
df2 = pd.DataFrame({'id': [2, 3, 4], 'score': [90, 80, 70]})

What is the result of pd.merge(df1, df2, how='left', on='id')?

medium

A. [{'id':1,'value':'A','score':NaN},{'id':2,'value':'B','score':90},{'id':3,'value':'C','score':80}]

B. [{'id':2,'value':'B','score':90},{'id':3,'value':'C','score':80},{'id':4,'value':NaN,'score':70}]

C. [{'id':1,'value':'A','score':NaN},{'id':2,'value':'B','score':90},{'id':3,'value':'C','score':80},{'id':4,'value':NaN,'score':70}]

D. [{'id':1,'value':'A','score':NaN},{'id':2,'value':'B','score':NaN},{'id':3,'value':'C','score':NaN}]

Solution

1. Step 1: Identify the left and right tables

   df1 is the left table with ids 1, 2, 3; df2 is the right table with ids 2, 3, 4.

2. Step 2: Perform left join on 'id'

   All rows from df1 appear. For id=1, no match in df2, so score is NaN. For ids 2 and 3, scores 90 and 80 are added. id=4 from df2 is ignored.

3. Final Answer:

   [{'id':1,'value':'A','score':NaN},{'id':2,'value':'B','score':90},{'id':3,'value':'C','score':80}] -> Option A

4. Quick Check:

   Left join keeps all df1 rows + matching df2 rows [OK]

Hint: Left join keeps all left rows, unmatched right rows ignored [OK]

Common Mistakes:

• Including unmatched right rows in output
• Replacing unmatched left values with NaN incorrectly
• Confusing left and right join results

4. You run this code but get an error:

pd.merge(df1, df2, how='right', key='id')

What is the error and how to fix it?

medium

A. Error: DataFrames must have same columns. Fix by renaming columns.

B. Error: 'how' must be 'left' or 'inner'. Fix by changing 'right' to 'left'.

C. Error: 'key' is not a valid argument. Fix by using 'on='id'' instead.

D. No error, code runs fine.

Solution

1. Step 1: Identify the incorrect parameter name

   The parameter to specify join keys in pd.merge is 'on', not 'key'.

2. Step 2: Correct the parameter name

   Replace 'key='id'' with 'on='id'' to fix the error.

3. Final Answer:

   Error: 'key' is not a valid argument. Fix by using 'on='id'' instead. -> Option C

4. Quick Check:

   Use 'on' for join keys in pd.merge [OK]

Hint: Use 'on' not 'key' for join columns in pd.merge [OK]

Common Mistakes:

• Using 'key' instead of 'on' parameter
• Changing 'how' incorrectly
• Assuming no error occurs

5. You have two DataFrames:

df_customers = pd.DataFrame({'customer_id': [1, 2, 3], 'name': ['Ann', 'Bob', 'Cara']})
df_orders = pd.DataFrame({'order_id': [101, 102], 'customer_id': [2, 4], 'amount': [250, 300]})

You want to list all orders with customer names, including orders without matching customers. Which join should you use and why?

hard

A. Full outer join to keep all customers and all orders.

B. Left join on 'customer_id' to keep all customers and add matching orders.

C. Inner join on 'customer_id' to keep only matching customers and orders.

D. Right join on 'customer_id' to keep all orders and add matching customer names.

Solution

1. Step 1: Identify which table to keep all rows from

   You want all orders listed, even if no matching customer exists, so keep all rows from df_orders.

2. Step 2: Choose join type to keep all right table rows

   df_orders is the right table in the join. Using a right join keeps all rows from df_orders and adds matching customer names from df_customers.

3. Final Answer:

   Right join on 'customer_id' to keep all orders and add matching customer names. -> Option D

4. Quick Check:

   Right join keeps all right table rows [OK]

Hint: Use right join to keep all orders, add matching customers [OK]

Common Mistakes:

• Using left join keeps all customers, not all orders
• Using inner join drops unmatched orders
• Using full join adds unmatched customers unnecessarily