Combining & Reshaping DataFrames in Pandas

Four operations cover virtually every real-world need for combining and reshaping DataFrames: merge(), concat(), melt(), and pivot(). Knowing when to reach for each — and what pitfalls to avoid — saves hours of debugging.

All examples use a consistent student scores dataset:

import pandas as pd
import numpy as np

students = pd.DataFrame({
    "StudentID": [1, 2, 3, 4],
    "Name": ["Alice", "Bob", "Charlie", "David"],
    "Age": [20, 22, 19, 21]
})

scores = pd.DataFrame({
    "StudentID": [1, 2, 3, 5],
    "Subject": ["Math", "Science", "Math", "History"],
    "Score": [85, 90, 78, 92]
})

StudentID=4 exists only in students, and StudentID=5 only in scores — perfect for demonstrating join type differences.

1. merge() — SQL-Style Joins

pd.merge() aligns two DataFrames on a key column, exactly like SQL JOINs. The how parameter controls which rows survive:

Inner Join

merged_inner = pd.merge(students, scores, on="StudentID", how="inner")

StudentID=4 (David, no score) and StudentID=5 (no student record) are both dropped.

Left Join

merged_left = pd.merge(students, scores, on="StudentID", how="left")

David is kept — with NaN for Subject and Score since he has no score record.

Outer Join

merged_outer = pd.merge(students, scores, on="StudentID", how="outer")

All 5 unique StudentIDs appear. NaN fills wherever data is absent on either side.

Common Pitfall

# Raises KeyError — "Name" and "Subject" don't match in the other DataFrame
pd.merge(students, scores, left_on="Name", right_on="Subject")

Use left_on / right_on when key column names differ between DataFrames, not when the values don’t match.

2. concat() — Stacking DataFrames

pd.concat() stacks DataFrames without any key alignment — it simply appends by position:

• axis=0 — vertically (more rows)
• axis=1 — horizontally (more columns)

Vertical Stack

# Append students to itself — 8 rows total
concat_vertical = pd.concat([students, students], axis=0, ignore_index=True)

ignore_index=True resets the index to 0, 1, 2, ... instead of repeating 0, 1, 2, 3, 0, 1, 2, 3.

Horizontal Stack

concat_horizontal = pd.concat(
    {"Students": students.head(3), "Scores": scores.head(3)},
    axis=1
)

Passing a dict adds a MultiIndex header, making it clear which columns came from where.

merge() vs concat() — The Critical Distinction

df_a = pd.DataFrame({"A": [1, 2]}, index=[0, 1])
df_b = pd.DataFrame({"B": [10, 20]}, index=[1, 2])  # different index!

pd.concat([df_a, df_b], axis=1)
# Row 0: A=1, B=NaN
# Row 1: A=2, B=10
# Row 2: A=NaN, B=20

concat() aligns on index, not on key values. Mismatched indexes silently produce NaN. Use merge() whenever you need key-based alignment.

3. melt() — Wide → Long Format

melt() unpivots a DataFrame from wide format (each variable in its own column) to long format (one row per observation). This is what seaborn, plotly, and most ML pipelines expect.

wide_df = pd.DataFrame({
    "StudentID": [1, 2, 3],
    "Math": [85, 90, 78],
    "Science": [88, 92, 80],
    "History": [75, 85, 70]
})

melted = pd.melt(
    wide_df,
    id_vars=["StudentID"],      # columns to keep as identifiers
    value_vars=["Math", "Science", "History"],  # columns to unpivot
    var_name="Subject",         # name for the new 'variable' column
    value_name="Score"          # name for the new 'value' column
)

Result: 9 rows (3 students × 3 subjects), 3 columns (StudentID, Subject, Score).

Common Pitfall

# KeyError if value_vars column doesn't exist
pd.melt(wide_df, id_vars=["StudentID"], value_vars=["English"])
# KeyError: "['English'] not in index"

4. pivot() and pivot_table() — Long → Wide Format

pivot() is the inverse of melt() — it reshapes long format back to wide.

long_df = pd.DataFrame({
    "StudentID": [1, 1, 2, 2, 3, 3],
    "Subject": ["Math", "Science", "Math", "Science", "Math", "Science"],
    "Score": [85, 88, 90, 92, 78, 80]
})

pivoted = long_df.pivot(index="StudentID", columns="Subject", values="Score")

Result: StudentID as rows, Math/Science as columns.

When pivot() Fails — Use pivot_table()

pivot() requires unique index + columns combinations. If StudentID=1 has two Math scores, it raises a ValueError:

# Raises: ValueError: Index contains duplicate entries, cannot reshape
long_df_dups.pivot(index="StudentID", columns="Subject", values="Score")

pivot_table() handles duplicates by aggregating:

pd.pivot_table(
    long_df_dups,
    index="StudentID",
    columns="Subject",
    values="Score",
    aggfunc="mean",   # or "sum", "max", "min", list of funcs
    fill_value=0      # replace NaN with 0
)

Quick Reference

Rule of thumb:

• Reach for merge() when you need key-based alignment
• Reach for concat() when you’re just stacking without a join key
• melt() ↔ pivot() are inverses — use them to switch between wide and long