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

Concept Flow - Right join behavior

Start with two tables

↓

Identify keys to join on

↓

Match keys from right table to left table

↓

Keep all rows from right table

↓

Add matching rows from left table

↓

Fill missing left data with NaN

↓

Output combined table

Right join keeps all rows from the right table and matches rows from the left table where keys match, filling missing left data with NaN.

Execution Sample

Pandas

import pandas as pd
left = pd.DataFrame({'key': ['A', 'B'], 'val': [1, 2]})
right = pd.DataFrame({'key': ['B', 'C'], 'val': [3, 4]})
result = pd.merge(left, right, on='key', how='right', suffixes=('_left', '_right'))
print(result)

This code performs a right join on two small dataframes by the 'key' column.

Execution Table

Step	Action	Left Table Row	Right Table Row	Match?	Result Row
1	Check right row key 'B'	Left row with key 'B' exists	Right row key 'B'	Yes	{key: 'B', val_left: 2, val_right: 3}
2	Check right row key 'C'	Left row with key 'C' does not exist	Right row key 'C'	No	{key: 'C', val_left: NaN, val_right: 4}
3	No more right rows			Exit	Final result table formed

💡 All right table rows processed; right join includes all right rows with matched or NaN left data.

Variable Tracker

Variable	Start	After Step 1	After Step 2	Final
result_rows	[]	[{key: 'B', val_left: 2, val_right: 3}]	[{key: 'B', val_left: 2, val_right: 3}, {key: 'C', val_left: NaN, val_right: 4}]	[{key: 'B', val_left: 2, val_right: 3}, {key: 'C', val_left: NaN, val_right: 4}]

Key Moments - 2 Insights

Why does the row with key 'C' have NaN in the left table columns?

Because right join keeps all rows from the right table even if there is no matching key in the left table, so missing left data is filled with NaN as shown in execution_table row 2.

Does the right join include rows from the left table that have no matching key in the right table?

No, right join only guarantees all rows from the right table are included; unmatched left rows are excluded, as seen in execution_table where left row with key 'A' is missing.

Visual Quiz - 3 Questions

Look at the execution table, what is the result row for the right table key 'B' at step 1?

A {key: 'B', val_left: 1, val_right: 3}

B {key: 'B', val_left: NaN, val_right: 3}

C {key: 'B', val_left: 2, val_right: 3}

D {key: 'B', val_left: 2, val_right: NaN}

💡 Hint

Check execution_table row 1 under 'Result Row'

At which step does the join add a row with NaN for left table columns?

A Step 1

B Step 2

C Step 3

D No step adds NaN

💡 Hint

Look at execution_table row 2 for NaN in left columns

If the join type changed to 'left', which key would definitely appear in the result?

A 'A'

B 'B'

C 'C'

D 'D'

💡 Hint

Variable_tracker shows 'A' is missing in right join but would appear in left join

Concept Snapshot

Right join in pandas merges two tables keeping all rows from the right table.
Matching rows from the left table are added; unmatched left rows are excluded.
Missing left data for unmatched keys is filled with NaN.
Use pd.merge(left, right, on='key', how='right') to perform right join.

Full Transcript

Right join behavior in pandas means combining two tables by a key, keeping all rows from the right table. For each row in the right table, pandas looks for matching rows in the left table. If a match is found, it combines the data. If not, it fills the left table's columns with NaN. Rows in the left table without matching keys in the right table are not included. This is shown step-by-step in the execution table where the right table's keys 'B' and 'C' are processed. The key 'B' matches a left row, so values from both tables appear. The key 'C' has no left match, so left columns are NaN. This behavior helps keep all data from the right table while adding left data when available.

Practice

1. What does a right join do in pandas merge operation?

easy

A. Keeps only rows that match in both dataframes.

B. Keeps all rows from the left dataframe and matches from the right.

C. Keeps all rows from the right dataframe and matches from the left.

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

Solution

1. Step 1: Understand the join direction

   A right join keeps all rows from the right dataframe regardless of matches.

2. Step 2: Understand matching behavior

   It adds matching rows from the left dataframe where possible; unmatched left rows become NaN.

3. Final Answer:

   Keeps all rows from the right dataframe and matches from the left. -> Option C

4. Quick Check:

   Right join = all right rows kept [OK]

Hint: Right join keeps all right rows, fills left missing with NaN [OK]

Common Mistakes:

• Confusing right join with left join
• Thinking right join keeps all left rows
• Assuming only matching rows are kept

2. Which of the following is the correct syntax to perform a right join on two pandas DataFrames df1 and df2 using column 'key'?

easy

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

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

C. df1.join(df2, how='right', on='key')

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

Solution

1. Step 1: Identify correct merge function and parameters

   Use pd.merge() with how='right' and on='key' to join on the key column.

2. Step 2: Check other options for correctness

   pd.merge(df1, df2, how='left', on='key') uses left join, df1.join(df2, how='right', on='key') uses join method incorrectly with on parameter, pd.merge(df1, df2, how='inner', on='key') uses inner join.

3. Final Answer:

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

4. Quick Check:

   Right join syntax = pd.merge(..., how='right', on=...) [OK]

Hint: Use pd.merge with how='right' and on='key' for right join [OK]

Common Mistakes:

• Using left join instead of right join
• Using DataFrame.join with on parameter (not supported)
• Forgetting to specify 'how' parameter

3. Given the following DataFrames:

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

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

medium

A. [{'key': 'B', 'val1': 2, 'val2': 4}, {'key': 'C', 'val1': 3, 'val2': 5}, {'key': 'D', 'val1': NaN, 'val2': 6}]

B. [{'key': 'A', 'val1': 1, 'val2': NaN}, {'key': 'B', 'val1': 2, 'val2': 4}, {'key': 'C', 'val1': 3, 'val2': 5}]

C. [{'key': 'A', 'val1': 1, 'val2': NaN}, {'key': 'B', 'val1': 2, 'val2': 4}, {'key': 'C', 'val1': 3, 'val2': 5}, {'key': 'D', 'val1': NaN, 'val2': 6}]

D. [{'key': 'B', 'val1': 2, 'val2': 4}, {'key': 'C', 'val1': 3, 'val2': 5}]

Solution

1. Step 1: Identify rows kept by right join

   Right join keeps all rows from df2 (keys B, C, D).

2. Step 2: Match df1 rows on key and fill missing

   Keys B and C match with df1, key D has no match so val1 is NaN.

3. Final Answer:

   [{'key': 'B', 'val1': 2, 'val2': 4}, {'key': 'C', 'val1': 3, 'val2': 5}, {'key': 'D', 'val1': NaN, 'val2': 6}] -> Option A

4. Quick Check:

   Right join keeps all right keys, unmatched left = NaN [OK]

Hint: Right join keeps all right keys; unmatched left columns show NaN [OK]

Common Mistakes:

• Expecting all left keys to appear
• Forgetting NaN for missing left matches
• Confusing inner join output with right join

4. You run this code:

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

But get a KeyError: 'id'. What is the likely cause?

medium

A. df1 does not have a column named 'id'.

B. df2 does not have a column named 'key'.

C. You cannot use different column names in left_on and right_on.

D. The 'how' parameter cannot be 'right' with left_on and right_on.

Solution

1. Step 1: Understand KeyError meaning

   KeyError means the specified column is missing in the dataframe.

2. Step 2: Check which dataframe is missing the column

   Since error is 'id', and left_on='id', df1 likely lacks 'id' column.

3. Final Answer:

   df1 does not have a column named 'id'. -> Option A

4. Quick Check:

   KeyError 'id' means missing column in df1 [OK]

Hint: KeyError means missing column in specified dataframe [OK]

Common Mistakes:

• Assuming right_on column missing causes KeyError for left_on
• Thinking different column names can't be used
• Believing 'right' join disallows left_on/right_on

5. You have two DataFrames:

df1 = pd.DataFrame({'key': ['X', 'Y', 'Z'], 'val1': [10, 20, 30]})
df2 = pd.DataFrame({'key': ['Y', 'Z', 'W'], 'val2': [100, 200, 300]})

You want to keep all rows from df2 and add matching val1 from df1. Then fill missing val1 with 0 instead of NaN. Which code achieves this using a right join?

hard

A. pd.merge(df2, df1, how='right', on='key').fillna({'val1': 0})

B. pd.merge(df1, df2, how='right', on='key').fillna({'val1': 0})

C. pd.merge(df1, df2, how='left', on='key').fillna({'val1': 0})

D. pd.merge(df2, df1, how='left', on='key').fillna({'val1': 0})

Solution

1. Step 1: Determine which dataframe is right

   To keep all rows from df2, df2 must be the right dataframe in merge.

2. Step 2: Use right join and fill missing val1

   Use how='right' with on='key' and then fill NaN in val1 with 0.

3. Final Answer:

   pd.merge(df1, df2, how='right', on='key').fillna({'val1': 0}) -> Option B

4. Quick Check:

   Right join with df2 right, fillna val1=0 [OK]

Hint: Right join with df2 right, then fillna val1 with 0 [OK]

Common Mistakes:

• Swapping left and right dataframes incorrectly
• Using left join instead of right join
• Filling NaN before merge