Merging Datasets

Welcome! Today, we'll dive into merging datasets. Imagine you have two sets of information about the same group of people or things, and you want to combine them into one set. This is important in data analysis because it helps you enrich your data and discover more insights.

By the end of this lesson, you'll understand:

1. How to merge datasets using the pd.merge() function.
2. Different ways to merge datasets: left, right, inner, and outer joins.
3. Practical examples to connect the theory with real-life scenarios.

Merging datasets is like combining two puzzles. In Python, we use the pd.merge() function from Pandas to do this. Let's create two simple datasets:

import pandas as pd

# Dataset 1: Basic information about some students
students = pd.DataFrame({
    'student_id': [1, 2, 3],
    'name': ['Alice', 'Bob', 'Charlie'],
    'age': [15, 16, 17]
})

# Dataset 2: Additional performance information about the same students
performance = pd.DataFrame({
    'student_id': [1, 3, 4],
    'grade': ['A', 'B', 'A'],
    'attendance': [95, 85, 100]
})

Now that we have two datasets, let's combine them using pd.merge(). We will merge these datasets based on the common column student_id.

# Merging the datasets on 'student_id'
students_merged = pd.merge(students, performance, on='student_id', how='left')
print(students_merged)
#    student_id     name  age grade  attendance
# 0           1    Alice   15     A        95.0
# 1           2      Bob   16   NaN         NaN
# 2           3  Charlie   17     B        85.0

Explanation:

• students: Contains basic information about students.
• performance: Contains additional info like grades and attendance.
• pd.merge(students, performance, on='student_id', how='left'): Merges the two DataFrames based on student_id. The how='left' parameter keeps all rows from the left DataFrame (students).

Merging datasets can be done in various ways, depending on how much and what kind of data you want to include. Here are the most common types:

1. Left Join: All rows from the left DataFrame, matched rows from the right DataFrame.
2. Right Join: All rows from the right DataFrame, matched rows from the left DataFrame.

Let's see examples of each type:

# Left Join
left_join = pd.merge(students, performance, on='student_id', how='left')
print(left_join)
#    student_id     name  age grade  attendance
# 0           1    Alice   15     A        95.0
# 1           2      Bob   16   NaN         NaN
# 2           3  Charlie   17     B        85.0

# Right Join
right_join = pd.merge(students, performance, on='student_id', how='right')
print(right_join)
#    student_id     name   age grade  attendance
# 0           1    Alice  15.0     A          95
# 1           3  Charlie  17.0     B          85
# 2           4      NaN   NaN     A         100