Right join behavior in Pandas - Mini Project: Build & Apply

Understanding Right Join Behavior with pandas

📖 Scenario: You are working with two small tables representing employees and their departments in a company. You want to combine these tables to see all departments and the employees assigned to them, including departments that currently have no employees.

🎯 Goal: Build a pandas DataFrame merge using a right join to combine employee and department data, showing all departments and matching employees.

📋 What You'll Learn

Create a pandas DataFrame called employees with columns EmployeeID, Name, and DeptID using the exact data provided.

Create a pandas DataFrame called departments with columns DeptID and DeptName using the exact data provided.

Create a variable called how_join and set it to the string 'right' to specify the join type.

Use the pandas merge function to join employees and departments on the DeptID column using the how_join variable.

Store the result in a variable called right_joined.

💡 Why This Matters

🌍 Real World

Right joins are useful when you want to keep all records from a reference table (like departments) and add matching data from another table (like employees), even if some departments have no employees yet.

💼 Career

Data analysts and data scientists often use right joins to combine datasets for reporting and analysis, ensuring no important reference data is lost.

1

Create the employees DataFrame

Create a pandas DataFrame called employees with these exact rows and columns: EmployeeID with values 1, 2, 3; Name with values 'Alice', 'Bob', 'Charlie'; and DeptID with values 10, 20, 30.

Pandas

import pandas as pd
# Create the employees DataFrame with EmployeeID, Name, and DeptID columns
# Your code here

Hint

Use pd.DataFrame with a dictionary where keys are column names and values are lists of column data.

2

Create the departments DataFrame

Create a pandas DataFrame called departments with these exact rows and columns: DeptID with values 10, 20, 30, 40; and DeptName with values 'HR', 'Finance', 'IT', 'Marketing'.

Pandas

import pandas as pd
employees = pd.DataFrame({
    'EmployeeID': [1, 2, 3],
    'Name': ['Alice', 'Bob', 'Charlie'],
    'DeptID': [10, 20, 30]
})
# Create the departments DataFrame with DeptID and DeptName columns
# Your code here

Hint

Use pd.DataFrame with a dictionary for the departments data.

3

Set the join type variable how_join

Create a variable called how_join and set it to the string 'right' to specify the type of join for merging.

Pandas

import pandas as pd
employees = pd.DataFrame({
    'EmployeeID': [1, 2, 3],
    'Name': ['Alice', 'Bob', 'Charlie'],
    'DeptID': [10, 20, 30]
})
departments = pd.DataFrame({
    'DeptID': [10, 20, 30, 40],
    'DeptName': ['HR', 'Finance', 'IT', 'Marketing']
})
# Create a variable how_join and set it to 'right'
# Your code here

Hint

Assign the string 'right' to the variable how_join.

4

Perform the right join merge

Use the pandas merge function to join employees and departments on the DeptID column using the how_join variable. Store the result in a variable called right_joined.

Pandas

import pandas as pd
employees = pd.DataFrame({
    'EmployeeID': [1, 2, 3],
    'Name': ['Alice', 'Bob', 'Charlie'],
    'DeptID': [10, 20, 30]
})
departments = pd.DataFrame({
    'DeptID': [10, 20, 30, 40],
    'DeptName': ['HR', 'Finance', 'IT', 'Marketing']
})
how_join = 'right'
# Merge employees and departments on DeptID using how_join and store in right_joined
# Your code here

Hint

Use pd.merge() with on='DeptID' and how=how_join.

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