{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "# Lab 1: Connecting two tables, one step at a time\n\n**CS203 Data Science Fundamentals | Uses Week 1 lecture material**  \n**Duration:** 2 hours | **Submit:** one executed notebook by 23:59\n\nYou do not need to write a full program today. Read each short code cell, predict what it will do, run it, inspect the output, and answer the question below it.\n\nYou will use two tables:\n\n- students: one row is one student;\n- enrolments: one row is one student registered in one course.\n\nThe central skill is simple: before connecting tables, say what one row means in each table."
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## Your route through the lab\n\n| Part | Time | What you do |\n| --- | ---: | --- |\n| 1. Look | 0:00-0:25 | Run cells and identify rows, columns, and keys |\n| 2. Predict | 0:25-0:40 | Decide what should happen when two tables connect |\n| 3. Connect | 0:40-1:05 | Complete one short merge and check the row count |\n| 4. Summarise | 1:05-1:30 | Complete one group summary and explain its denominator |\n| 5. Challenge | 1:30-1:50 | Change one group variable and interpret carefully |\n| 6. Explain | 1:50-2:00 | Peer check, final explanation, restart and run all |\n\nThere are **10 code cells**. Six are fully provided. You complete four tiny code tasks by replacing one blank or one variable name."
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "from pathlib import Path\nimport pandas as pd\nimport matplotlib.pyplot as plt\n\nRAW_BASE = \"https://raw.githubusercontent.com/Sabokrou/Sabokrou.github.io/main/teaching/data-science-fundamentals/datasets\"\n\ndef load_table(name):\n    local = Path(\"data\") / name\n    if local.exists():\n        return pd.read_csv(local)\n    return pd.read_csv(f\"{RAW_BASE}/{name}\")\n\nstudents = load_table(\"students.csv\")\nenrolments = load_table(\"enrolments.csv\")\n\nprint(\"students:\", students.shape)\nprint(\"enrolments:\", enrolments.shape)"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## Part 1: Look at the first table\n\nBefore running the next cell, predict:\n\n- How many rows do you expect in students?\n- Can the same student ID appear more than once?\n- Which columns describe a student rather than identify a student?"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "students.head()"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "print(\"Is student_id unique in students?\", students[\"student_id\"].is_unique)\nprint(\"Number of students:\", len(students))\nprint(\"Columns:\", list(students.columns))"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "### Explain after running\n\n1. **What does one row in students represent?** _Write one sentence._\n2. **Why is student_id an identifier rather than a useful numerical measurement?** _Write one sentence._\n3. **Choose one column and name its measurement type:** nominal category, number, or identifier. _Write one sentence._"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "student-todo"
    ]
   },
   "outputs": [],
   "source": "# TODO 1 - Replace the blank with the table that has one row per student.\n# Choose exactly one: students   or   enrolments\n\none_student_table = _____\none_student_table[[\"student_id\", \"programme\", \"study_mode\"]].head()"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## Part 2: Look at the second table\n\nRun the next cell. Then compare it with students.\n\n**Predict first:** Should student_id still be unique here? Why or why not?"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "print(enrolments.head())\nprint(\"Is student_id unique in enrolments?\", enrolments[\"student_id\"].is_unique)\nprint(\"Number of enrolment rows:\", len(enrolments))\nprint(\"Number of different students:\", enrolments[\"student_id\"].nunique())\nprint(\"Courses:\", sorted(enrolments[\"course_code\"].unique()))"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "### Explain after running\n\n1. **What does one row in enrolments represent?** _Write one sentence._\n2. **Why can student_id repeat in enrolments without being an error?** _Write one sentence._\n3. **Which table should be on the left if your final table needs one row per course registration?** _Write one sentence._"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## Part 3: Connect the tables\n\nWe want one row per **course registration**, plus the student's programme and study mode.\n\n- enrolments stays on the left because it already has one row per registration.\n- students supplies extra student information.\n- many_to_one means many enrolment rows can match one student row.\n\n**Predict:** The merge should have the same number of rows as enrolments. Why?"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "student-todo"
    ]
   },
   "outputs": [],
   "source": "# TODO 2 - Complete the merge by replacing the blank with one_student_table.\n# Do not change on, how, or validate.\n\njoined = enrolments.merge(\n    _____[[\"student_id\", \"programme\", \"study_mode\"]],\n    on=\"student_id\",\n    how=\"left\",\n    validate=\"many_to_one\",\n)\n\njoined.head()"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "print(\"Rows before merge:\", len(enrolments))\nprint(\"Rows after merge: \", len(joined))\nassert len(joined) == len(enrolments), \"Unexpected row change: stop and ask for help.\"\nassert joined[\"programme\"].notna().all(), \"A student did not match: stop and ask for help.\"\n\nprint(\"Check passed: one row still represents one student-course registration.\")"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "### Explain after connecting\n\n1. **After the merge, what does one row in joined represent?**\n2. **Why does the row count stay the same?**\n3. **What would it mean if the merge created twice as many rows?**\n4. **Why is many_to_one appropriate here?**\n\n> Answer each in one short sentence."
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## Part 4: Make one simple summary\n\nThe next task calculates a **course-registration pass rate** by study mode.\n\nThis denominator is enrolments, not students. A student taking four courses contributes four registration rows.\n\n**Predict:** Is this the same as \u201cthe percentage of students who passed\u201d? Explain before you run the code."
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "student-todo"
    ]
   },
   "outputs": [],
   "source": "# TODO 3 - Replace the blank with the column that divides rows into full-time and part-time groups.\n# Hint: look at one_student_table.head().\n\npass_rate_by_mode = (\n    joined.groupby(_____)[\"passed\"]\n    .agg(n=\"size\", pass_rate=\"mean\")\n    .assign(pass_rate=lambda x: (100 * x[\"pass_rate\"]).round(1))\n    .reset_index()\n)\npass_rate_by_mode"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "ax = pass_rate_by_mode.plot.bar(\n    x=\"study_mode\", y=\"pass_rate\", legend=False,\n    title=\"Course-registration pass rate by study mode\"\n)\nax.set_ylabel(\"Pass rate (%)\")\nax.set_xlabel(\"Study mode\")\nplt.show()"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "### Explain the output\n\n1. Report the two group sizes and pass rates.\n2. State the denominator in plain English.\n3. Complete this sentence:\n\n> \u201cIn this dataset, the course-registration pass rates differ by study mode. This describes ________. It does not prove that ________.\u201d\n\n4. Give one possible reason, other than study mode itself, that could help explain a difference."
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## Part 5: Small challenge\n\nYou have already seen the full pattern. Now change **one word** in the code below.\n\nThe question is: \u201cHow do course-registration pass rates differ by programme?\u201d\n\n- Replace the blank with the correct grouping column.\n- Run the cell.\n- Make a short chart or use the printed table.\n- Explain why the programme with the highest value is not automatically the \u201cbest\u201d programme."
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "student-todo"
    ]
   },
   "outputs": [],
   "source": "# TODO 4 - Replace the blank with the correct column name.\n# Hint: the column describes Biology, Computer Science, Economics, or Psychology.\n\nchallenge_table = (\n    joined.groupby(_____)[\"passed\"]\n    .agg(n=\"size\", pass_rate=\"mean\")\n    .assign(pass_rate=lambda x: (100 * x[\"pass_rate\"]).round(1))\n    .reset_index()\n)\nchallenge_table"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## Final thinking task\n\nA manager says: \u201cThe programme with the highest pass rate is better, so we should copy its teaching everywhere.\u201d\n\nWrite **120-160 words**. Use your challenge table, but answer carefully:\n\n- What does the table show?\n- What does one row represent?\n- What important information is missing?\n- Why does a difference not prove that teaching caused it?\n- What would you check next?\n\n> _Write here._"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## Peer check and submission\n\nAsk a nearby student:\n\n1. What does one row in joined represent?\n2. What is the denominator of pass_rate_by_mode?\n3. What claim is too strong for this data?\n\n- **Reviewer initials:** _replace_\n- **One improvement I made:** _replace_\n\nBefore submitting: **Runtime -> Restart session and run all**. All outputs must remain visible.\n\n| Criterion | Weight |\n| --- | ---: |\n| Rows, tables, and merge explanation | 35% |\n| Completed code and reproducible notebook | 25% |\n| Correct denominator and summary explanation | 20% |\n| Final cautious reasoning task | 15% |\n| Peer check and complete submission | 5% |"
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python",
   "version": "3.x"
  },
  "title": "Lab 1: Connecting two tables, one step at a time"
 },
 "nbformat": 4,
 "nbformat_minor": 5
}