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

Inner Join Behavior with pandas

📖 Scenario: You work in a small company that keeps two separate lists: one for employees and one for their departments. You want to combine these lists to see which employees belong to which departments.

🎯 Goal: Build a pandas DataFrame that shows employees matched with their departments using an inner join.

📋 What You'll Learn

Create two pandas DataFrames named employees and departments with exact data.

Create a variable key_column with the column name to join on.

Use pandas merge function with how='inner' to join the DataFrames on key_column.

Store the result in a DataFrame named employee_department.

💡 Why This Matters

🌍 Real World

Companies often keep separate lists for employees and departments. Joining these lists helps to see which employee works in which department.

💼 Career

Understanding inner joins is essential for data analysts and database professionals to combine related data from multiple tables or sources.

1

Create the employees DataFrame

Create a pandas DataFrame called employees with these exact rows and columns: EmployeeID and Name. The rows are: 1, 'Alice', 2, 'Bob', 3, 'Charlie', 4, 'David'.

Pandas

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

Need a hint?

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

2

Create the departments DataFrame and key column

Create a pandas DataFrame called departments with columns EmployeeID and Department. The rows are: 1, 'HR', 2, 'Engineering', 4, 'Marketing', 5, 'Sales'. Then create a variable called key_column and set it to the string 'EmployeeID'.

Pandas

import pandas as pd
employees = pd.DataFrame({
    'EmployeeID': [1, 2, 3, 4],
    'Name': ['Alice', 'Bob', 'Charlie', 'David']
})
# Create the departments DataFrame with EmployeeID and Department columns
# Create the key_column variable with value 'EmployeeID'
# Your code here

Need a hint?

Use the same method as Step 1 to create departments. Then assign the string 'EmployeeID' to key_column.

3

Perform the inner join

Use the pandas merge function to join employees and departments on the column stored in key_column. Use how='inner' to keep only matching rows. Store the result in a DataFrame called employee_department.

Pandas

import pandas as pd
employees = pd.DataFrame({
    'EmployeeID': [1, 2, 3, 4],
    'Name': ['Alice', 'Bob', 'Charlie', 'David']
})
departments = pd.DataFrame({
    'EmployeeID': [1, 2, 4, 5],
    'Department': ['HR', 'Engineering', 'Marketing', 'Sales']
})
key_column = 'EmployeeID'
# Use pd.merge with how='inner' on key_column to join employees and departments
# Store the result in employee_department
# Your code here

Need a hint?

Use pd.merge(employees, departments, on=key_column, how='inner') to join.

4

Complete the DataFrame with correct columns

Ensure the employee_department DataFrame contains exactly these columns in this order: EmployeeID, Name, and Department. If needed, reorder the columns explicitly.

Pandas

import pandas as pd
employees = pd.DataFrame({
    'EmployeeID': [1, 2, 3, 4],
    'Name': ['Alice', 'Bob', 'Charlie', 'David']
})
departments = pd.DataFrame({
    'EmployeeID': [1, 2, 4, 5],
    'Department': ['HR', 'Engineering', 'Marketing', 'Sales']
})
key_column = 'EmployeeID'
employee_department = pd.merge(employees, departments, on=key_column, how='inner')
# Reorder columns of employee_department to ['EmployeeID', 'Name', 'Department']
# Your code here

Need a hint?

Use bracket notation to reorder columns: employee_department = employee_department[['EmployeeID', 'Name', 'Department']].