Inner join behavior in Pandas - Step-by-Step Execution

Concept Flow - Inner join behavior

Start with two tables

↓

Identify common columns

↓

Match rows where keys are equal

↓

Combine matched rows into new table

↓

Discard rows without matches

↓

Result: Inner join table

Inner join takes two tables and keeps only rows where the join keys match in both tables.

Execution Sample

Pandas

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')

This code merges two tables on the 'key' column, keeping only rows with keys in both tables.

Execution Table

Step	Action	Left Table Rows	Right Table Rows	Matched Keys	Result Rows
1	Start with left and right tables	[{'A',1}, {'B',2}, {'C',3}]	[{'B',4}, {'C',5}, {'D',6}]	None yet	Empty
2	Identify common keys	Keys: A, B, C	Keys: B, C, D	B, C	Empty
3	Match rows on keys B and C	Rows with B and C	Rows with B and C	B, C	Empty
4	Combine matched rows	Row B: (B,2)	Row B: (B,4)	B	Row: (B,2,4)
5	Combine matched rows	Row C: (C,3)	Row C: (C,5)	C	Row: (C,3,5)
6	Discard unmatched rows (A, D)	Row A discarded	Row D discarded	B, C	Rows: (B,2,4), (C,3,5)
7	Final result	N/A	N/A	B, C	2 rows: (B,2,4), (C,3,5)

💡 No more matching keys; inner join returns only rows with keys B and C.

Variable Tracker

Variable	Start	After Step 2	After Step 4	After Step 6	Final
left	[{'A',1}, {'B',2}, {'C',3}]	Same	Same	Same	Same
right	[{'B',4}, {'C',5}, {'D',6}]	Same	Same	Same	Same
matched_keys	None	['B', 'C']	['B', 'C']	['B', 'C']	['B', 'C']
result	Empty	Empty	[{'B',2,4}]	[{'B',2,4}, {'C',3,5}]	[{'B',2,4}, {'C',3,5}]

Key Moments - 2 Insights

Why are rows with keys 'A' and 'D' missing in the result?

Because inner join keeps only rows where keys exist in both tables. Rows with 'A' only in left and 'D' only in right have no match, so they are excluded (see execution_table rows 6 and 7).

How does pandas know which rows to combine?

Pandas matches rows by comparing the values in the join column 'key'. Only rows with the same key value in both tables are combined (see execution_table rows 3 and 4).

Visual Quiz - 3 Questions

Look at the execution table, what keys are matched during the inner join?

A B and C

B A and D

C A, B, C, and D

D Only A

💡 Hint

Check the 'Matched Keys' column in execution_table rows 2 and 3.

At which step are unmatched rows discarded?

A Step 3

B Step 6

C Step 1

D Step 7

💡 Hint

Look at the 'Action' column in execution_table row 6.

If the right table had key 'A' instead of 'D', how many rows would the result have?

A 2 rows

B 1 row

C 3 rows

D 0 rows

💡 Hint

Think about which keys would match in both tables if right had 'A' instead of 'D'.

Concept Snapshot

Inner join merges two tables on matching keys.
Only rows with keys in both tables appear in the result.
Unmatched rows are dropped.
Use pd.merge(left, right, on='key', how='inner') in pandas.
Result columns combine data from both tables.

Full Transcript

Inner join in pandas combines two tables by matching rows where the join key is the same in both tables. It discards rows that do not have a matching key in the other table. For example, if the left table has keys A, B, C and the right table has keys B, C, D, the inner join keeps only rows with keys B and C. The result table contains combined columns from both tables for these matched keys. Rows with keys A and D are excluded because they do not have matches. This behavior is shown step-by-step in the execution table, where matched keys are identified, rows combined, and unmatched rows discarded. The variable tracker shows how the result builds up after each step. Understanding this helps avoid confusion about why some rows disappear after an inner join.

Practice

1. What does an inner join do when merging two pandas DataFrames?

easy

A. Keeps only rows with matching keys in both DataFrames

B. Keeps all rows from the left DataFrame and fills missing with NaN

C. Keeps all rows from the right DataFrame and fills missing with NaN

D. Keeps all rows from both DataFrames, filling missing with NaN

Solution

1. Step 1: Understand join types in pandas

   Inner join keeps only rows where the key exists in both DataFrames.

2. Step 2: Compare with other join types

   Left join keeps all left rows, right join keeps all right rows, outer join keeps all rows from both.

3. Final Answer:

   Keeps only rows with matching keys in both DataFrames -> Option A

4. Quick Check:

   Inner join = matching keys only [OK]

Hint: Inner join keeps only common keys in both tables [OK]

Common Mistakes:

• Confusing inner join with left or outer join
• Thinking inner join keeps all rows
• Assuming inner join fills missing with NaN

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

easy

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

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

C. df1.join(df2, on='id')

D. df1.merge(df2, left_on='id', right_on='id', how='left')

Solution

1. Step 1: Identify correct function and parameters

   Use pd.merge() with on='id' and how='inner' for inner join.

2. Step 2: Check other options for errors

   df1.join(df2, on='id') defaults to left join, pd.merge(df1, df2, on='id', how='outer') uses outer join, df1.merge(df2, left_on='id', right_on='id', how='left') uses left join.

3. Final Answer:

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

4. Quick Check:

   pd.merge + how='inner' + on='id' = correct syntax [OK]

Hint: Use pd.merge with how='inner' and on='column' [OK]

Common Mistakes:

• Using wrong join type like 'outer' or 'left'
• Using df.join() without specifying correct parameters
• Mixing left_on and right_on incorrectly

3. Given the following DataFrames:

df1 = pd.DataFrame({'id': [1, 2, 3], 'val1': ['A', 'B', 'C']})
df2 = pd.DataFrame({'id': [2, 3, 4], 'val2': ['X', 'Y', 'Z']})
result = pd.merge(df1, df2, on='id', how='inner')

What is the content of result?

medium

A. {'id': [2, 3, 4], 'val1': ['B', 'C', 'NaN'], 'val2': ['X', 'Y', 'Z']}

B. {'id': [1, 2, 3], 'val1': ['A', 'B', 'C'], 'val2': ['NaN', 'X', 'Y']}

C. {'id': [2, 3], 'val1': ['B', 'C'], 'val2': ['X', 'Y']}

D. {'id': [1, 2, 3, 4], 'val1': ['A', 'B', 'C', 'NaN'], 'val2': ['NaN', 'X', 'Y', 'Z']}

Solution

1. Step 1: Identify matching keys for inner join

   Inner join keeps rows where 'id' is in both df1 and df2: ids 2 and 3.

2. Step 2: Combine matching rows

   Rows with id 2 and 3 are merged with their respective val1 and val2 values.

3. Final Answer:

   {'id': [2, 3], 'val1': ['B', 'C'], 'val2': ['X', 'Y']} -> Option C

4. Quick Check:

   Inner join = only common ids 2 and 3 [OK]

Hint: Inner join keeps only keys present in both DataFrames [OK]

Common Mistakes:

• Including rows with id 1 or 4 which are not common
• Confusing inner join with outer join results
• Expecting NaN values in inner join output

4. You run this code:

df1 = pd.DataFrame({'key': [1, 2], 'val': ['A', 'B']})
df2 = pd.DataFrame({'key': [2, 3], 'val': ['X', 'Y']})
result = pd.merge(df1, df2, on='key', how='inner')

But you get an error: ValueError: columns overlap but no suffix specified. What is the fix?

medium

A. Remove the 'on' parameter from pd.merge()

B. Change how='inner' to how='outer'

C. Rename columns before merge to avoid overlap

D. Add suffixes parameter like suffixes=('_left', '_right') in pd.merge()

Solution

1. Step 1: Understand cause of error

   Both DataFrames have a 'val' column causing overlap without suffixes.

2. Step 2: Apply fix by adding suffixes

   Use suffixes=('_left', '_right') in pd.merge() to differentiate overlapping columns.

3. Final Answer:

   Add suffixes parameter like suffixes=('_left', '_right') in pd.merge() -> Option D

4. Quick Check:

   Overlapping columns need suffixes to avoid error [OK]

Hint: Add suffixes to handle overlapping columns in merge [OK]

Common Mistakes:

• Changing join type does not fix column overlap
• Removing 'on' causes wrong join keys
• Ignoring need for suffixes with overlapping columns

5. You have two DataFrames:

df1 = pd.DataFrame({'id': [1, 2, 2, 3], 'score': [10, 20, 30, 40]})
df2 = pd.DataFrame({'id': [2, 2, 3], 'grade': ['A', 'B', 'C']})
result = pd.merge(df1, df2, on='id', how='inner')

How many rows will result have after the inner join?

hard

A. 4

B. 5

C. 6

D. 7

Solution

1. Step 1: Identify matching keys and duplicates

   Keys 2 and 3 are common. df1 has two rows with id=2, df2 has two rows with id=2.

2. Step 2: Calculate Cartesian product for duplicates

   For id=2: 2 rows in df1 x 2 rows in df2 = 4 rows; for id=3: 1 row x 1 row = 1 row; total = 5 rows.

3. Step 3: Count total rows

   Total rows = 4 (id=2) + 1 (id=3) = 5 rows.

4. Final Answer:

   5 -> Option B

5. Quick Check:

   Duplicates multiply rows in inner join [OK]

Hint: Duplicates multiply rows in inner join result [OK]

Common Mistakes:

• Counting unique keys instead of row combinations
• Ignoring duplicate rows in join keys
• Assuming row count equals unique keys count