{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Combining & Reshaping DataFrames in Pandas\n",
    "\n",
    "This notebook covers four essential operations for combining and reshaping pandas DataFrames:\n",
    "\n",
    "1. **`merge()`** — SQL-style joins (inner, left, right, outer)\n",
    "2. **`concat()`** — Stacking DataFrames vertically or horizontally\n",
    "3. **`melt()`** — Wide → Long format transformation\n",
    "4. **`pivot()` / `pivot_table()`** — Long → Wide format transformation\n",
    "\n",
    "We use a student scores dataset throughout for consistency."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Sample DataFrames\n",
    "\n",
    "We have two DataFrames:\n",
    "- `students` — student info (ID, Name, Age)\n",
    "- `scores` — subject scores (StudentID, Subject, Score)\n",
    "\n",
    "Note: StudentID=4 exists only in `students`, and StudentID=5 only in `scores` — useful for demonstrating join types."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "students = pd.DataFrame({\n",
    "    \"StudentID\": [1, 2, 3, 4],\n",
    "    \"Name\": [\"Alice\", \"Bob\", \"Charlie\", \"David\"],\n",
    "    \"Age\": [20, 22, 19, 21]\n",
    "})\n",
    "\n",
    "scores = pd.DataFrame({\n",
    "    \"StudentID\": [1, 2, 3, 5],\n",
    "    \"Subject\": [\"Math\", \"Science\", \"Math\", \"History\"],\n",
    "    \"Score\": [85, 90, 78, 92]\n",
    "})\n",
    "\n",
    "print(\"Students DataFrame:\")\n",
    "display(students)\n",
    "\n",
    "print(\"\\nScores DataFrame:\")\n",
    "display(scores)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "## 1. `merge()` — Joining DataFrames\n",
    "\n",
    "`pd.merge()` is the pandas equivalent of SQL JOINs. The `how` parameter controls which rows are kept:\n",
    "\n",
    "| `how` | Behaviour |\n",
    "|---|---|\n",
    "| `inner` | Only rows with matching keys in **both** DataFrames |\n",
    "| `left` | All rows from left, matching from right (NaN if no match) |\n",
    "| `right` | All rows from right, matching from left |\n",
    "| `outer` | All rows from both, NaN where no match |"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Inner Join: only StudentIDs present in BOTH DataFrames (1, 2, 3)\n",
    "merged_inner = pd.merge(students, scores, on=\"StudentID\", how=\"inner\")\n",
    "print(\"Inner Join (StudentID 4 and 5 dropped):\")\n",
    "display(merged_inner)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Left Join: keep all students, NaN for StudentID=4 who has no score\n",
    "merged_left = pd.merge(students, scores, on=\"StudentID\", how=\"left\")\n",
    "print(\"Left Join (David has NaN score — no matching score record):\")\n",
    "display(merged_left)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Outer Join: keep all rows from both, NaN where no match\n",
    "merged_outer = pd.merge(students, scores, on=\"StudentID\", how=\"outer\")\n",
    "print(\"Outer Join (all rows, NaN for mismatches):\")\n",
    "display(merged_outer)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Common pitfall: merging on mismatched key columns raises KeyError\n",
    "try:\n",
    "    pd.merge(students, scores, left_on=\"Name\", right_on=\"Subject\")\n",
    "except KeyError as e:\n",
    "    print(f\"KeyError: {e}\")\n",
    "    print(\"Tip: Use left_on / right_on when key column names differ between DataFrames.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "## 2. `concat()` — Stacking DataFrames\n",
    "\n",
    "`pd.concat()` stacks DataFrames either:\n",
    "- **Vertically** (`axis=0`) — add more rows (same columns)\n",
    "- **Horizontally** (`axis=1`) — add more columns (same rows)\n",
    "\n",
    "Unlike `merge()`, `concat()` does **not** align on key columns — it stacks by position."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Vertical concat: stack two identical DataFrames\n",
    "# ignore_index=True resets the index to 0,1,2...\n",
    "concat_vertical = pd.concat([students, students], axis=0, ignore_index=True)\n",
    "print(\"Vertical Concat (8 rows, reset index):\")\n",
    "display(concat_vertical)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Horizontal concat with keys creates a MultiIndex column header\n",
    "students_short = students.head(3)\n",
    "scores_short = scores.head(3)\n",
    "\n",
    "concat_horizontal = pd.concat(\n",
    "    {\"Students\": students_short, \"Scores\": scores_short},\n",
    "    axis=1\n",
    ")\n",
    "print(\"Horizontal Concat (side-by-side, MultiIndex columns):\")\n",
    "display(concat_horizontal)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge() vs concat() — key distinction\n",
    "# concat does NOT align by key — it aligns by index position\n",
    "# This can silently produce wrong results if indexes don't match\n",
    "df_a = pd.DataFrame({\"A\": [1, 2]}, index=[0, 1])\n",
    "df_b = pd.DataFrame({\"B\": [10, 20]}, index=[1, 2])  # different index!\n",
    "\n",
    "print(\"concat with mismatched index → NaN filling:\")\n",
    "display(pd.concat([df_a, df_b], axis=1))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "## 3. `melt()` — Wide → Long Format\n",
    "\n",
    "`melt()` unpivots a DataFrame from **wide format** (each subject in its own column) to **long format** (one row per observation). This is the format required by seaborn, plotly, and most ML libraries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Wide format: each subject is a separate column\n",
    "wide_df = pd.DataFrame({\n",
    "    \"StudentID\": [1, 2, 3],\n",
    "    \"Math\": [85, 90, 78],\n",
    "    \"Science\": [88, 92, 80],\n",
    "    \"History\": [75, 85, 70]\n",
    "})\n",
    "print(\"Wide Format:\")\n",
    "display(wide_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# melt() converts to long format\n",
    "# id_vars: columns to keep as-is (identifier columns)\n",
    "# value_vars: columns to unpivot (defaults to all remaining)\n",
    "melted = pd.melt(\n",
    "    wide_df,\n",
    "    id_vars=[\"StudentID\"],\n",
    "    value_vars=[\"Math\", \"Science\", \"History\"],\n",
    "    var_name=\"Subject\",\n",
    "    value_name=\"Score\"\n",
    ")\n",
    "print(\"Long Format after melt():\")\n",
    "display(melted.sort_values(\"StudentID\").reset_index(drop=True))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Common pitfall: specifying a value_vars column that doesn't exist\n",
    "try:\n",
    "    pd.melt(wide_df, id_vars=[\"StudentID\"], value_vars=[\"English\"])\n",
    "except KeyError as e:\n",
    "    print(f\"KeyError: {e}\")\n",
    "    print(\"Tip: All columns listed in value_vars must exist in the DataFrame.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "## 4. `pivot()` and `pivot_table()` — Long → Wide Format\n",
    "\n",
    "`pivot()` is the inverse of `melt()` — it reshapes long format back to wide. `pivot_table()` is its more powerful cousin that handles **duplicate entries** using an aggregation function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Long format DataFrame\n",
    "long_df = pd.DataFrame({\n",
    "    \"StudentID\": [1, 1, 2, 2, 3, 3],\n",
    "    \"Subject\": [\"Math\", \"Science\", \"Math\", \"Science\", \"Math\", \"Science\"],\n",
    "    \"Score\": [85, 88, 90, 92, 78, 80]\n",
    "})\n",
    "print(\"Long Format:\")\n",
    "display(long_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# pivot(): strict — requires unique index/column combinations\n",
    "pivoted = long_df.pivot(index=\"StudentID\", columns=\"Subject\", values=\"Score\")\n",
    "pivoted.columns.name = None  # clean up column header label\n",
    "print(\"pivot() result (wide format):\")\n",
    "display(pivoted)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# pivot_table(): handles duplicates with aggregation\n",
    "# Useful when the same StudentID + Subject combination appears multiple times\n",
    "pivot_table = pd.pivot_table(\n",
    "    long_df,\n",
    "    index=\"StudentID\",\n",
    "    columns=\"Subject\",\n",
    "    values=\"Score\",\n",
    "    aggfunc=\"mean\",\n",
    "    fill_value=0\n",
    ")\n",
    "pivot_table.columns.name = None\n",
    "print(\"pivot_table() with mean aggregation:\")\n",
    "display(pivot_table)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# pivot() raises ValueError on duplicate entries — use pivot_table() instead\n",
    "long_df_dups = pd.DataFrame({\n",
    "    \"StudentID\": [1, 1, 1],\n",
    "    \"Subject\": [\"Math\", \"Math\", \"Science\"],  # Math appears twice for StudentID=1\n",
    "    \"Score\": [85, 87, 88]\n",
    "})\n",
    "\n",
    "try:\n",
    "    long_df_dups.pivot(index=\"StudentID\", columns=\"Subject\", values=\"Score\")\n",
    "except ValueError as e:\n",
    "    print(f\"ValueError: {e}\")\n",
    "    print(\"\\nFix: Use pivot_table() with aggfunc to handle duplicates:\")\n",
    "    display(pd.pivot_table(long_df_dups, index=\"StudentID\", columns=\"Subject\",\n",
    "                           values=\"Score\", aggfunc=\"mean\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "## Quick Reference\n",
    "\n",
    "| Operation | Function | Use Case |\n",
    "|---|---|---|\n",
    "| Join on key column | `pd.merge()` | Combining tables like SQL JOINs |\n",
    "| Stack rows/columns | `pd.concat()` | Appending more data, no key alignment |\n",
    "| Wide → Long | `pd.melt()` | Preparing data for plotting/ML |\n",
    "| Long → Wide (unique) | `df.pivot()` | Reshaping when no duplicates |\n",
    "| Long → Wide (with agg) | `pd.pivot_table()` | Reshaping with summarization |\n",
    "\n",
    "**Key rule of thumb:**\n",
    "- Use `merge()` when you need to **align on a key**\n",
    "- Use `concat()` when you need to **stack** without key alignment\n",
    "- Use `melt()` ↔ `pivot()` to switch between **wide** and **long** format"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python",
   "version": "3.11.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}