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.