Merge, Join, Concat and Reshape DataFrames

1. The big idea: combine by meaning, not by position

Suppose one table contains students and another contains fees. The safe question is not “which rows line up?” but “which key identifies the same student in both tables?”

import pandas as pd

students = pd.DataFrame({
    "StudentId": [101, 102, 103, 104],
    "Name": ["Asha", "Ravi", "Meera", "Kabir"],
    "City": ["Varanasi", "Ranchi", "Varanasi", "Patna"]
})

fees = pd.DataFrame({
    "StudentId": [101, 102, 104, 105],
    "FeePaid": [True, False, True, True]
})

Key concept: a join key should represent identity or a well-defined relationship. Never merge on a convenient column merely because the values happen to look similar.

2. merge(): the main relational operation

result = students.merge(fees, on="StudentId", how="inner")
print(result)

An inner join keeps keys found on both sides. Here StudentId 103 is missing from fees and 105 is missing from students, so neither appears.

3. Left, right and outer joins

left = students.merge(fees, on="StudentId", how="left")
right = students.merge(fees, on="StudentId", how="right")
outer = students.merge(fees, on="StudentId", how="outer")

print(left)
print(right)
print(outer)

After a left join, a student without a fee record receives a missing value in FeePaid. That missing value means “no matching record was found”, not automatically “fee unpaid”.

4. indicator=True: audit your merge

audit = students.merge(
    fees,
    on="StudentId",
    how="outer",
    indicator=True
)

print(audit[["_merge", "StudentId"]])
print(audit["_merge"].value_counts())

_merge shows left_only, right_only or both. This is one of the best tools for checking whether two sources actually align.

5. validate=: protect yourself from accidental many-to-many joins

result = students.merge(
    fees,
    on="StudentId",
    how="left",
    validate="one_to_one"
)

Useful expectations include one_to_one, one_to_many, many_to_one and many_to_many. If the data violates the declared relationship, Pandas raises an error instead of silently multiplying rows.

6. Different key names: left_on and right_on

students = pd.DataFrame({"StudentId": [101, 102], "Name": ["Asha", "Ravi"]})
payments = pd.DataFrame({"StudentCode": [101, 102], "Amount": [1500, 1200]})

result = students.merge(
    payments,
    left_on="StudentId",
    right_on="StudentCode",
    how="left"
)

After merging, decide whether both key columns are useful; if not, remove the redundant one explicitly.

7. Merge on multiple columns

marks = pd.DataFrame({
    "StudentId": [101, 101, 102],
    "Subject": ["Python", "SQL", "Python"],
    "Marks": [88, 79, 72]
})

attendance = pd.DataFrame({
    "StudentId": [101, 101, 102],
    "Subject": ["Python", "SQL", "Python"],
    "Attendance": [94, 91, 83]
})

report = marks.merge(
    attendance,
    on=["StudentId", "Subject"],
    validate="one_to_one"
)

8. Overlapping column names and suffixes

old = pd.DataFrame({"StudentId": [1, 2], "Marks": [70, 80]})
new = pd.DataFrame({"StudentId": [1, 2], "Marks": [75, 84]})

comparison = old.merge(
    new,
    on="StudentId",
    suffixes=("_old", "_new")
)
print(comparison)

9. Data types of join keys must agree

If one file stores StudentId as integer and another stores it as text, normalise before merging.

students["StudentId"] = students["StudentId"].astype("string")
fees["StudentId"] = fees["StudentId"].astype("string")

result = students.merge(fees, on="StudentId")

Also clean spaces and case where appropriate for textual keys—but do not casually “fix” identifiers whose formatting is meaningful.

10. join(): convenient when indexes are the keys

students_i = students.set_index("StudentId")
fees_i = fees.set_index("StudentId")

result = students_i.join(fees_i, how="left")

join() is convenient for index-oriented workflows. merge() is usually clearer when relationships are based on ordinary columns.

11. concat(): stack tables rather than match keys

jan = pd.DataFrame({"Name": ["Asha", "Ravi"], "Marks": [80, 70]})
feb = pd.DataFrame({"Name": ["Meera", "Kabir"], "Marks": [90, 75]})

all_students = pd.concat([jan, feb], ignore_index=True)
print(all_students)

Use concat() when tables represent the same kind of records and you want to stack them. It is not a replacement for a relational merge.

12. concat across columns

names = pd.DataFrame({"Name": ["Asha", "Ravi"]})
scores = pd.DataFrame({"Marks": [88, 72]})

combined = pd.concat([names, scores], axis=1)

This aligns by index. If indexes do not represent the same records, the result can be wrong even though it looks plausible.

13. Wide and long data

Wide: one row may contain several measurement columns such as Python, SQL, AI. Long: one row represents one measurement, often with columns such as Student, Subject, Marks.

Long form would contain four rows: Asha/Python, Asha/SQL, Ravi/Python, Ravi/SQL.

14. melt(): wide to long

wide = pd.DataFrame({
    "Name": ["Asha", "Ravi"],
    "Python": [88, 72],
    "SQL": [79, 81]
})

long = wide.melt(
    id_vars="Name",
    var_name="Subject",
    value_name="Marks"
)
print(long)

Long form is often easier for grouping, charting and tidy analytical workflows.

15. pivot(): long to wide when combinations are unique

wide_again = long.pivot(
    index="Name",
    columns="Subject",
    values="Marks"
)
print(wide_again)

pivot() expects each index/column combination to identify at most one value. Duplicate combinations cause an error because Pandas cannot guess how to combine them.

16. pivot_table(): reshape and aggregate duplicates

tests = pd.DataFrame({
    "Name": ["Asha", "Asha", "Ravi", "Ravi"],
    "Subject": ["Python", "Python", "Python", "Python"],
    "Marks": [80, 90, 70, 74]
})

summary = tests.pivot_table(
    index="Name",
    columns="Subject",
    values="Marks",
    aggfunc="mean"
)

Use pivot_table() when aggregation is part of the question.

17. stack() and unstack(): reshape index levels

summary = pd.DataFrame({
    "Python": [88, 72],
    "SQL": [79, 81]
}, index=["Asha", "Ravi"])

long_series = summary.stack()
wide_again = long_series.unstack()

These are especially useful after grouped analyses that create MultiIndex results.

18. crosstab(): quick frequency tables

students = pd.DataFrame({
    "City": ["Varanasi", "Varanasi", "Ranchi", "Ranchi", "Patna"],
    "Course": ["Python", "AI-ML", "Python", "Python", "AI-ML"]
})

table = pd.crosstab(students["City"], students["Course"])
print(table)

19. A complete relational workflow

students = pd.DataFrame({
    "StudentId": [1, 2, 3],
    "Name": ["Asha", "Ravi", "Meera"]
})

enrolments = pd.DataFrame({
    "StudentId": [1, 1, 2, 3],
    "CourseId": ["P", "S", "P", "S"]
})

courses = pd.DataFrame({
    "CourseId": ["P", "S"],
    "Course": ["Python", "SQL"]
})

report = (
    enrolments
    .merge(students, on="StudentId", validate="many_to_one")
    .merge(courses, on="CourseId", validate="many_to_one")
)

print(report)

The enrolment table is the bridge: one student can have several enrolments, while each StudentId and CourseId should resolve to one master record.

20. Diagnose a row explosion

print(students["StudentId"].duplicated().sum())
print(fees["StudentId"].duplicated().sum())

print(students["StudentId"].value_counts().head())
print(fees["StudentId"].value_counts().head())

Always distinguish an intended one-to-many relationship from an accidental duplicate-key problem.

21. Practice exercises

1. Left-join students to payments and identify students without payment records.
2. Use an outer join with indicator=True to list orphan records on both sides.
3. Prove that a master table has unique IDs before merging.
4. Merge marks and attendance on both StudentId and Subject.
5. Concatenate three monthly sales DataFrames.
6. Convert a wide marks table to long format using melt().
7. Create a subject-by-student table using pivot().
8. Use pivot_table() to calculate average marks where repeated tests exist.
9. Build a city-course frequency report with crosstab().
10. Explain why concat(axis=1) can be dangerous when indexes do not match.

22. Mini project: Learning Centre Master Report

Create four tables: students, courses, enrolments and payments. Produce one report containing student name, course name, amount paid and payment status. Validate every relationship, audit unmatched records, then create a pivot table showing total payments by city and course.

Open the complete runnable Level 6 demo