Inner join in Data Analysis Python - Step-by-Step Execution

Concept Flow - Inner join

Start with Table A

↓

Find matching keys in Table B

↓

Combine rows where keys match

↓

Output rows with matched data only

↓

End

Inner join takes two tables and combines rows where the key values match in both tables, leaving out rows without matches.

Execution Sample

Data Analysis Python

import pandas as pd

df1 = pd.DataFrame({'id': [1, 2, 3], 'name': ['Alice', 'Bob', 'Charlie']})
df2 = pd.DataFrame({'id': [2, 3, 4], 'age': [25, 30, 40]})

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

This code merges two dataframes on the 'id' column, keeping only rows with matching 'id's in both.

Execution Table

Step	Action	df1 Rows Considered	df2 Rows Considered	Matching Keys Found	Result Rows Added
1	Start with df1 and df2	[{1, Alice}, {2, Bob}, {3, Charlie}]	[{2, 25}, {3, 30}, {4, 40}]	N/A	N/A
2	Check id=1 in df1 against df2	{1, Alice}	[{2, 25}, {3, 30}, {4, 40}]	No match	No row added
3	Check id=2 in df1 against df2	{2, Bob}	[{2, 25}, {3, 30}, {4, 40}]	Match found	Add {2, Bob, 25}
4	Check id=3 in df1 against df2	{3, Charlie}	[{2, 25}, {3, 30}, {4, 40}]	Match found	Add {3, Charlie, 30}
5	No more rows in df1	N/A	N/A	N/A	End with 2 rows in result

💡 All rows in df1 checked; only rows with matching ids in df2 included in result.

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
result	empty	empty	[{2, Bob, 25}]	[{2, Bob, 25}, {3, Charlie, 30}]	[{2, Bob, 25}, {3, Charlie, 30}]

Key Moments - 2 Insights

Why is the row with id=1 from df1 not in the result?

Because in execution_table row 2, id=1 has no matching id in df2, so it is excluded from the inner join result.

Does the row with id=4 from df2 appear in the result?

No, because inner join only keeps rows with matching keys in both tables; id=4 is not in df1, so it is excluded (see execution_table rows 3-5).

Visual Quiz - 3 Questions

Look at the execution_table, what rows are added to the result after Step 3?

A [{2, Bob, 25}]

B [{1, Alice, 25}]

C [{3, Charlie, 30}]

D No rows added

💡 Hint

Check the 'Result Rows Added' column at Step 3 in execution_table.

At which step does the condition for id=1 fail to find a match?

A Step 1

B Step 3

C Step 2

D Step 4

💡 Hint

Look at the 'Matching Keys Found' column for id=1 in execution_table.

If df2 had an extra row with id=3 and age=35, how would the result change?

A Result would be unchanged

B Result would have duplicate rows for id=3

C Result would exclude id=3 rows

D Result would include id=4 row

💡 Hint

Consider how inner join matches multiple rows with the same key in variable_tracker.

Concept Snapshot

Inner join combines two tables by matching rows on a key.
Only rows with keys present in both tables appear in the result.
Syntax in pandas: pd.merge(df1, df2, on='key', how='inner')
Useful to find common data between tables.
Rows without matches are excluded.

Full Transcript

Inner join is a way to combine two tables by matching rows that share the same key value. We start with two tables, look for keys that appear in both, and then combine those rows into one result table. Rows that don't have a matching key in the other table are left out. For example, if we have a table of people with ids and names, and another table with ids and ages, an inner join on the id will give us only the people who have both a name and an age recorded. The code example uses pandas merge with how='inner' to do this. Step by step, we check each id in the first table against the second, add matching rows to the result, and skip those without matches. This helps us focus on data that is common to both tables.

Practice

1. What does an inner join do when combining two tables?

easy

A. Returns only rows with matching keys in both tables

B. Returns all rows from the first table, matching or not

C. Returns all rows from the second table, matching or not

D. Returns all rows from both tables, matching or not

Solution

1. Step 1: Understand join types

   Inner join returns rows where keys match in both tables only.

2. Step 2: Compare options with definition

   The option 'Returns only rows with matching keys in both tables' describes this behavior correctly.

3. Final Answer:

   Returns only rows with matching keys in both tables -> Option A

4. Quick Check:

   Inner join = matching rows only [OK]

Hint: Inner join keeps only matching rows from both tables [OK]

Common Mistakes:

• Confusing inner join with left or right join
• Thinking inner join returns unmatched rows
• Mixing up join types and their results

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

easy

A. df1.join(df2, on='id', how='outer')

B. pd.concat([df1, df2], axis=1, join='inner')

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

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

Solution

1. Step 1: Identify pandas inner join syntax

   The correct function is pd.merge() with how='inner' and on='id'.

2. Step 2: Check each option

   pd.merge(df1, df2, on='id', how='inner') matches this exactly; others use wrong methods or join types.

3. Final Answer:

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

4. Quick Check:

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

Hint: Use pd.merge with how='inner' for inner joins [OK]

Common Mistakes:

• Using join() instead of merge() for inner join
• Using wrong join type like 'outer' or 'left'
• Using concat which does not perform join

3. Given two 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], 'val1': ['B', 'C'], 'val2': ['X', 'Y']}

B. {'id': [1, 4], 'val1': ['A', None], 'val2': [None, 'Z']}

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

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

Solution

1. Step 1: Identify matching keys for inner join

   Keys in both df1 and df2 are 2 and 3 only.

2. Step 2: Merge rows with matching keys

   Rows with id 2 and 3 combine val1 and val2 columns.

3. Final Answer:

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

4. Quick Check:

   Inner join keeps ids 2 and 3 only [OK]

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

Common Mistakes:

• Including keys not present in both tables
• Confusing inner join with outer join
• Assuming all rows are kept

4. You wrote this code to inner join two DataFrames:

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

But it raises an error. What is the likely cause?

medium

A. Using 'how' parameter incorrectly

B. Missing the 'on' parameter to specify join key

C. DataFrames must be converted to lists first

D. Inner join is not supported by pandas

Solution

1. Step 1: Check required parameters for pd.merge

   pd.merge requires 'on' or 'left_on'/'right_on' to specify join keys.

2. Step 2: Identify missing 'on' parameter

   Code misses 'on' parameter, causing error.

3. Final Answer:

   Missing the 'on' parameter to specify join key -> Option B

4. Quick Check:

   pd.merge needs 'on' key [OK]

Hint: Always specify 'on' column for merge keys [OK]

Common Mistakes:

• Forgetting to specify join keys
• Assuming default join key exists
• Trying to join incompatible data types

5. You have two DataFrames:

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

How many rows will result contain after the inner join?

hard

A. 6

B. 4

C. 3

D. 5

Solution

1. Step 1: Identify matching keys and duplicates

   Keys 2 and 3 appear multiple times in both DataFrames.

2. Step 2: Calculate Cartesian product for duplicates

   For id=2: df1 has 2 rows, df2 has 2 rows -> 2*2=4 rows.
   For id=3: df1 has 1 row, df2 has 1 row -> 1*1=1 row.
   Total rows = 4 + 1 = 5 rows.

3. Step 3: Recount carefully

   Recount confirms 5 rows total.

4. Final Answer:

   5 -> Option D

5. Quick Check:

   Duplicates multiply rows in inner join [OK]

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

Common Mistakes:

• Ignoring duplicate keys causing more rows
• Counting unique keys only
• Assuming one-to-one join always