# Let us import pandas to start working with DataFrames
import pandas as pd

# Let us create our first DataFrame with students and their classes
students = pd.DataFrame({
    'student_id': [1, 2, 3],
    'name': ['Sam', 'Alex', 'Jordan'],
    'class': ['Math', 'English', 'Art']
})

students

# Here is another DataFrame showing student test scores
scores = pd.DataFrame({
    'student_id': [1, 2, 4],
    'test_score': [88, 92, 77]
})

scores

# Let us merge the students and scores DataFrames on student_id
merged = pd.merge(students, scores, on='student_id')

merged

# Notice that student_id 4 did not appear in the merged result.
# That is because it only combines rows where the IDs are present in both tables.

# Use how='outer' to keep all rows from both DataFrames
merged_outer = pd.merge(students, scores, on='student_id', how='outer')

merged_outer

# how='left' keeps all rows from the left table (students)
merged_left = pd.merge(students, scores, on='student_id', how='left')

merged_left

# how='right' keeps all rows from the right table (scores)
merged_right = pd.merge(students, scores, on='student_id', how='right')

merged_right

# What if the columns have different names? Let us see an example.
info = pd.DataFrame({
    'id': [1, 2, 3],
    'hobby': ['Reading', 'Hiking', 'Drawing']
})

pd.merge(students, info, left_on='student_id', right_on='id')

# Here is how suffixes work if both DataFrames have a 'class' column
classes = pd.DataFrame({
    'student_id': [2, 3],
    'class': ['Science', 'Drama']
})

pd.merge(students, classes, on='student_id', how='inner')

# Use the 'suffixes' argument to customize the suffixes
pd.merge(students, classes, on='student_id', how='inner', suffixes=('_orig', '_new'))

# time for you to try: type two column names to merge on, separated by a comma
columns = input('Type two columns to merge on, separated by a comma: ').split(',')
columns = [col.strip() for col in columns]
result = pd.merge(students, scores, left_on=columns[0], right_on=columns[0])
result

# Mini-project: Combine purchase and customer info to see who bought what
purchases = pd.DataFrame({
    'purchase_id': [101, 102, 103],
    'customer_id': [1, 1, 2],
    'item': ['Book', 'Pen', 'Notebook']
})
customers = pd.DataFrame({
    'customer_id': [1, 2, 3],
    'customer_name': ['Lee', 'Dana', 'Morgan']
})

purchase_info = pd.merge(purchases, customers, on='customer_id')
purchase_info

# Part 2: Let us see what happens with an outer join
all_purchases = pd.merge(purchases, customers, on='customer_id', how='outer')
all_purchases

# What if you get an error? For example, columns are missing or mis-typed.
try:
    pd.merge(purchases, customers, on='wrong_column')
except Exception as e:
    print('Error:', e)
    

# Best practice: Always look at your data before merging
print(students.head())
print(scores.head())

# Common mistake: Forgetting to reset the index after merging
merged_with_index = pd.merge(students, scores, on='student_id')
merged_reset = merged_with_index.reset_index(drop=True)
merged_reset

# Extra tip: You can merge on multiple columns too
# Let us add a new DataFrame for this example
grades = pd.DataFrame({
    'student_id': [1, 2, 2],
    'class': ['Math', 'English', 'Math'],
    'grade': ['A', 'B', 'A']
})

multi_merge = pd.merge(students, grades, on=['student_id', 'class'], how='inner')
multi_merge