{
 "nbformat": 4,
 "nbformat_minor": 5,
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python",
   "version": "3.11"
  },
  "colab": {
   "provenance": []
  }
 },
 "cells": [
  {
   "cell_type": "markdown",
   "id": "cell-0000",
   "metadata": {},
   "source": [
    "# Filtering, Sorting & Grouping\n",
    "\n",
    "Ask real questions of your data — boolean masks, combined conditions, isin and between, loc vs iloc, sort_values, groupby aggregation, and pivot tables.\n",
    "\n",
    "*Part of the free [Python for Data Science](https://ramadnsyh.dev/courses/python-for-data-science) course by [Muhammad Ramadiansyah](https://ramadnsyh.dev). This notebook is generated from the interactive lesson — [read it online](https://ramadnsyh.dev/courses/python-for-data-science/pandas-filtering).*"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-0001",
   "metadata": {},
   "source": [
    "A DataFrame you can only look at is a spreadsheet. A DataFrame you can\n",
    "**query** is a superpower. This lesson covers the operations that answer real\n",
    "questions: \"which orders were over $50?\", \"what's the average rating per\n",
    "genre?\", \"who are our top five customers?\" — all without a single loop.\n",
    "\n",
    "We'll use one small cereal-style dataset throughout, so each cell is\n",
    "self-contained."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-0002",
   "metadata": {},
   "source": [
    "## Boolean masks: filtering rows\n",
    "\n",
    "The pattern is the one you met with NumPy: build a Series of `True`/`False`,\n",
    "then use it to index the DataFrame:"
   ]
  },
  {
   "cell_type": "code",
   "id": "cell-0003",
   "metadata": {},
   "execution_count": null,
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.DataFrame({\n",
    "    \"name\":    [\"Corn Flakes\", \"Choco Blast\", \"Fiber One\", \"Honey Pops\",\n",
    "                \"Bran Crunch\", \"Sugar Bombs\", \"Oat Rings\", \"Wheat Bites\"],\n",
    "    \"mfr\":     [\"K\", \"N\", \"K\", \"G\", \"P\", \"G\", \"K\", \"N\"],\n",
    "    \"calories\":[100, 130, 60, 110, 90, 150, 105, 95],\n",
    "    \"sugars\":  [2, 12, 0, 11, 5, 15, 6, 3],\n",
    "    \"rating\":  [45.9, 22.4, 68.2, 31.1, 53.3, 18.0, 40.4, 59.6],\n",
    "})\n",
    "\n",
    "high_rated = df[df[\"rating\"] > 50]\n",
    "print(high_rated)\n",
    "print()\n",
    "print(df[df[\"mfr\"] == \"K\"])       # equality works too"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-0004",
   "metadata": {},
   "source": [
    "Read `df[df[\"rating\"] > 50]` inside-out: the inner expression makes a boolean\n",
    "mask, the outer brackets keep only the `True` rows."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-0005",
   "metadata": {},
   "source": [
    "## Combining conditions: the parentheses gotcha\n",
    "\n",
    "Multiple conditions use `&` (and), `|` (or), and `~` (not) — **not** Python's\n",
    "`and`/`or`/`not`. And each condition **must** be wrapped in parentheses,\n",
    "because `&` binds tighter than `>`:"
   ]
  },
  {
   "cell_type": "code",
   "id": "cell-0006",
   "metadata": {},
   "execution_count": null,
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.DataFrame({\n",
    "    \"name\":    [\"Corn Flakes\", \"Choco Blast\", \"Fiber One\", \"Honey Pops\",\n",
    "                \"Bran Crunch\", \"Sugar Bombs\", \"Oat Rings\", \"Wheat Bites\"],\n",
    "    \"mfr\":     [\"K\", \"N\", \"K\", \"G\", \"P\", \"G\", \"K\", \"N\"],\n",
    "    \"calories\":[100, 130, 60, 110, 90, 150, 105, 95],\n",
    "    \"sugars\":  [2, 12, 0, 11, 5, 15, 6, 3],\n",
    "    \"rating\":  [45.9, 22.4, 68.2, 31.1, 53.3, 18.0, 40.4, 59.6],\n",
    "})\n",
    "\n",
    "# healthy AND tasty: low sugar and high rating\n",
    "print(df[(df[\"sugars\"] < 5) & (df[\"rating\"] > 40)])\n",
    "print()\n",
    "# either very low calorie OR top-rated\n",
    "print(df[(df[\"calories\"] < 90) | (df[\"rating\"] > 55)])\n",
    "print()\n",
    "# NOT made by Kellogg's (\"K\")\n",
    "print(df[~(df[\"mfr\"] == \"K\")])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-0007",
   "metadata": {},
   "source": [
    "> **The classic error**\n",
    "> \n",
    "> `df[df[\"sugars\"] < 5 & df[\"rating\"] > 40]` — without parentheses — raises a\n",
    "> confusing `ValueError` about \"truth value of a Series is ambiguous\", because\n",
    "> Python evaluates `5 & df[\"rating\"]` first. When you see that error, check your\n",
    "> parentheses (and that you used `&`/`|`, not `and`/`or`)."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-0008",
   "metadata": {},
   "source": [
    "## isin, between, and string filters\n",
    "\n",
    "Three shortcuts save you from long chains of `|`:"
   ]
  },
  {
   "cell_type": "code",
   "id": "cell-0009",
   "metadata": {},
   "execution_count": null,
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.DataFrame({\n",
    "    \"name\":    [\"Corn Flakes\", \"Choco Blast\", \"Fiber One\", \"Honey Pops\",\n",
    "                \"Bran Crunch\", \"Sugar Bombs\", \"Oat Rings\", \"Wheat Bites\"],\n",
    "    \"mfr\":     [\"K\", \"N\", \"K\", \"G\", \"P\", \"G\", \"K\", \"N\"],\n",
    "    \"calories\":[100, 130, 60, 110, 90, 150, 105, 95],\n",
    "    \"sugars\":  [2, 12, 0, 11, 5, 15, 6, 3],\n",
    "    \"rating\":  [45.9, 22.4, 68.2, 31.1, 53.3, 18.0, 40.4, 59.6],\n",
    "})\n",
    "\n",
    "# membership: mfr is K or N (instead of two == chained with |)\n",
    "print(df[df[\"mfr\"].isin([\"K\", \"N\"])])\n",
    "print()\n",
    "# range: calories from 90 to 110 inclusive\n",
    "print(df[df[\"calories\"].between(90, 110)])\n",
    "print()\n",
    "# string matching via the .str accessor\n",
    "print(df[df[\"name\"].str.contains(\"Cr\")])\n",
    "print(df[df[\"name\"].str.startswith(\"C\")])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-0010",
   "metadata": {},
   "source": [
    "The `.str` accessor exposes most Python string methods (`contains`, `startswith`,\n",
    "`lower`, `split`, …) applied to every value at once — vectorized, like everything\n",
    "else."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-0011",
   "metadata": {},
   "source": [
    "## loc vs iloc\n",
    "\n",
    "Both select rows and columns, but by different coordinates:\n",
    "\n",
    "- **`.loc[rows, cols]`** — by **label** (index values, column names); slices are *inclusive*\n",
    "- **`.iloc[rows, cols]`** — by **integer position**; slices *exclude* the end, like Python lists"
   ]
  },
  {
   "cell_type": "code",
   "id": "cell-0012",
   "metadata": {},
   "execution_count": null,
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.DataFrame({\n",
    "    \"mfr\":     [\"K\", \"N\", \"K\", \"G\"],\n",
    "    \"calories\":[100, 130, 60, 110],\n",
    "    \"rating\":  [45.9, 22.4, 68.2, 31.1],\n",
    "}, index=[\"Corn Flakes\", \"Choco Blast\", \"Fiber One\", \"Honey Pops\"])\n",
    "\n",
    "# by label — note the slice INCLUDES \"Fiber One\"\n",
    "print(df.loc[\"Choco Blast\":\"Fiber One\", [\"calories\", \"rating\"]])\n",
    "print()\n",
    "# by position — rows 0-1, columns 0-1 (end excluded)\n",
    "print(df.iloc[0:2, 0:2])\n",
    "print()\n",
    "# loc also accepts a boolean mask plus a column selection:\n",
    "print(df.loc[df[\"rating\"] > 40, \"calories\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-0013",
   "metadata": {},
   "source": [
    "That last pattern — `df.loc[mask, column]` — is also the safe way to **modify**\n",
    "a filtered subset (plain chained indexing like `df[mask][col] = ...` triggers\n",
    "the infamous `SettingWithCopyWarning`)."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-0014",
   "metadata": {},
   "source": [
    "## Sorting\n",
    "\n",
    "`sort_values` orders rows by one or more columns:"
   ]
  },
  {
   "cell_type": "code",
   "id": "cell-0015",
   "metadata": {},
   "execution_count": null,
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.DataFrame({\n",
    "    \"name\":    [\"Corn Flakes\", \"Choco Blast\", \"Fiber One\", \"Honey Pops\",\n",
    "                \"Bran Crunch\", \"Sugar Bombs\", \"Oat Rings\", \"Wheat Bites\"],\n",
    "    \"mfr\":     [\"K\", \"N\", \"K\", \"G\", \"P\", \"G\", \"K\", \"N\"],\n",
    "    \"calories\":[100, 130, 60, 110, 90, 150, 105, 95],\n",
    "    \"rating\":  [45.9, 22.4, 68.2, 31.1, 53.3, 18.0, 40.4, 59.6],\n",
    "})\n",
    "\n",
    "# top 3 by rating\n",
    "print(df.sort_values(\"rating\", ascending=False).head(3))\n",
    "print()\n",
    "# two keys: by manufacturer, then rating within each\n",
    "print(df.sort_values([\"mfr\", \"rating\"], ascending=[True, False]))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-0016",
   "metadata": {},
   "source": [
    "## groupby: split, apply, combine\n",
    "\n",
    "`groupby` is the single most important pandas operation. It **splits** the\n",
    "table into groups, **applies** an aggregation to each, and **combines** the\n",
    "results into a new table:"
   ]
  },
  {
   "cell_type": "code",
   "id": "cell-0017",
   "metadata": {},
   "execution_count": null,
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.DataFrame({\n",
    "    \"mfr\":     [\"K\", \"N\", \"K\", \"G\", \"P\", \"G\", \"K\", \"N\"],\n",
    "    \"type\":    [\"cold\", \"cold\", \"cold\", \"cold\", \"hot\", \"cold\", \"hot\", \"cold\"],\n",
    "    \"calories\":[100, 130, 60, 110, 90, 150, 105, 95],\n",
    "    \"sugars\":  [2, 12, 0, 11, 5, 15, 6, 3],\n",
    "    \"rating\":  [45.9, 22.4, 68.2, 31.1, 53.3, 18.0, 40.4, 59.6],\n",
    "})\n",
    "\n",
    "# average rating per manufacturer\n",
    "print(df.groupby(\"mfr\")[\"rating\"].mean().round(1))\n",
    "print()\n",
    "# several statistics at once with .agg\n",
    "print(df.groupby(\"mfr\").agg(\n",
    "    n=(\"rating\", \"size\"),\n",
    "    avg_rating=(\"rating\", \"mean\"),\n",
    "    max_sugar=(\"sugars\", \"max\"),\n",
    ").round(1))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-0018",
   "metadata": {},
   "source": [
    "The named-aggregation form — `new_name=(\"column\", \"function\")` — keeps the\n",
    "output tidy and self-documenting."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-0019",
   "metadata": {},
   "source": [
    "## pivot_table: groupby in two dimensions\n",
    "\n",
    "When you want groups along **both** axes (rows *and* columns), reach for\n",
    "`pivot_table`:"
   ]
  },
  {
   "cell_type": "code",
   "id": "cell-0020",
   "metadata": {},
   "execution_count": null,
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.DataFrame({\n",
    "    \"mfr\":     [\"K\", \"N\", \"K\", \"G\", \"P\", \"G\", \"K\", \"N\"],\n",
    "    \"type\":    [\"cold\", \"cold\", \"cold\", \"cold\", \"hot\", \"cold\", \"hot\", \"cold\"],\n",
    "    \"calories\":[100, 130, 60, 110, 90, 150, 105, 95],\n",
    "    \"rating\":  [45.9, 22.4, 68.2, 31.1, 53.3, 18.0, 40.4, 59.6],\n",
    "})\n",
    "\n",
    "table = df.pivot_table(\n",
    "    values=\"rating\",\n",
    "    index=\"mfr\",        # rows\n",
    "    columns=\"type\",     # columns\n",
    "    aggfunc=\"mean\",\n",
    ")\n",
    "print(table.round(1))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-0021",
   "metadata": {},
   "source": [
    "Each cell is the mean rating for one (manufacturer, type) combination — the\n",
    "same result as `groupby([\"mfr\", \"type\"])`, just reshaped into a grid that's\n",
    "much easier to scan (and to feed into a heatmap, as you'll see in the seaborn\n",
    "lesson)."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-0022",
   "metadata": {},
   "source": [
    "### 🏋️ Exercise — Query an orders table\n",
    "\n",
    "100])\n",
    "\n",
    "# 2. card orders over 100\n",
    "print(orders[(orders[\"payment\"] == \"card\") & (orders[\"amount\"] > 100)])\n",
    "\n",
    "# 3. per-city summary\n",
    "print(orders.groupby(\"city\").agg(\n",
    "    n_orders=(\"amount\", \"size\"),\n",
    "    total=(\"amount\", \"sum\"),\n",
    "    avg=(\"amount\", \"mean\"),\n",
    ").round(1))\n",
    "\n",
    "# 4. city x payment grid\n",
    "print(orders.pivot_table(values=\"amount\", index=\"city\",\n",
    "                         columns=\"payment\", aggfunc=\"sum\"))\n",
    "`}\n",
    ">\n",
    "Build an orders DataFrame with columns `city` (3 cities, 10 rows), `payment`\n",
    "(`\"card\"`, `\"cash\"`, `\"e-wallet\"`), and `amount`. Then answer: (1) which orders\n",
    "exceed 100? (2) which are **card** orders over 100 (two conditions)? (3) for\n",
    "each city, how many orders, total, and average amount (one `groupby().agg()`)?\n",
    "(4) build a `pivot_table` of total amount by city × payment method."
   ]
  },
  {
   "cell_type": "code",
   "id": "cell-0023",
   "metadata": {},
   "execution_count": null,
   "outputs": [],
   "source": [
    "# Your solution here"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-0024",
   "metadata": {},
   "source": [
    "<details><summary>✅ Show solution</summary>\n",
    "\n",
    "```python\n",
    "import pandas as pd\n",
    "\n",
    "orders = pd.DataFrame({\n",
    "    \"city\":    [\"Jakarta\", \"Bandung\", \"Jakarta\", \"Surabaya\", \"Bandung\",\n",
    "                \"Jakarta\", \"Surabaya\", \"Jakarta\", \"Bandung\", \"Surabaya\"],\n",
    "    \"payment\": [\"card\", \"cash\", \"e-wallet\", \"card\", \"card\",\n",
    "                \"cash\", \"e-wallet\", \"card\", \"e-wallet\", \"card\"],\n",
    "    \"amount\":  [120.0, 35.5, 80.0, 210.0, 55.0, 15.0, 95.5, 300.0, 42.0, 130.0],\n",
    "})\n",
    "\n",
    "# 1. orders over 100\n",
    "print(orders[orders[\"amount\"] > 100])\n",
    "\n",
    "# 2. card orders over 100\n",
    "print(orders[(orders[\"payment\"] == \"card\") & (orders[\"amount\"] > 100)])\n",
    "\n",
    "# 3. per-city summary\n",
    "print(orders.groupby(\"city\").agg(\n",
    "    n_orders=(\"amount\", \"size\"),\n",
    "    total=(\"amount\", \"sum\"),\n",
    "    avg=(\"amount\", \"mean\"),\n",
    ").round(1))\n",
    "\n",
    "# 4. city x payment grid\n",
    "print(orders.pivot_table(values=\"amount\", index=\"city\",\n",
    "                         columns=\"payment\", aggfunc=\"sum\"))\n",
    "```\n",
    "\n",
    "</details>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-0025",
   "metadata": {},
   "source": [
    "You can now slice, dice, and summarize tables — next we make the numbers\n",
    "visible with Matplotlib."
   ]
  }
 ]
}