{ "cells": [ { "cell_type": "markdown", "id": "96e2190d", "metadata": {}, "source": [ "# Data Operations" ] }, { "cell_type": "code", "execution_count": 3, "id": "fe813059", "metadata": { "tags": [ "hide-output", "hide-input", "thebe-init", "runnable" ] }, "outputs": [], "source": [ "import sys\n", "sys.path.insert(0, '../../') # adjust path to workspace root\n", "from shared.utils import display_side_by_side" ] }, { "cell_type": "markdown", "id": "b472ee00", "metadata": {}, "source": [ "```{contents}\n", ":local:\n", ":depth: 2\n", "```\n" ] }, { "cell_type": "markdown", "id": "d0961ef2", "metadata": {}, "source": [ "Data analysis often requires combining datasets from multiple sources, aggregating data to extract meaningful insights, and manipulating DataFrames for analysis. This chapter covers three essential categories of Pandas operations:\n", "\n", "**Combining Datasets**\n", "\n", "You'll master when to use each method depending on your data structure and requirements:\n", "- **stack** DataFrames with concatenation (`pd.concat()`), \n", "- **combine** them based on common values using merging (`pd.merge()`), and \n", "- **join** them using indices (`.join()`). \n", "\n", "**Aggregation and GroupBy**\n", "\n", "Master data summarization techniques with two powerful approaches:\n", "- **Simple aggregations** to summarize entire DataFrames with functions like `sum()`, `mean()`, `min()`, and `max()`\n", "- **GroupBy operations** that enable split–apply–combine workflows for conditional aggregations by group using the pattern: `df.groupby('column')['target'].aggregation_function()`\n", "\n", "**DataFrame Operations**\n", "\n", "Learn essential data manipulation techniques for cleaning and organizing your data:\n", "- **Sorting** data by values or index using `sort_values()` and `sort_index()` \n", "- **Renaming** columns and indices with the `rename()` method\n", "- **Handling duplicates** by identifying them with `duplicated()` and removing them with `drop_duplicates()`\n", "\n", "These operations form the foundation for data manipulation and analysis workflows in Pandas, enabling you to efficiently prepare, combine, and analyze datasets for data science projects." ] }, { "cell_type": "code", "execution_count": 4, "id": "4863fae3", "metadata": { "tags": [] }, "outputs": [], "source": [ "import numpy as np\n", "import pandas as pd" ] }, { "cell_type": "markdown", "id": "3478efd2", "metadata": {}, "source": [ "## Combining Datasets\n", "\n", "There are three main ways of combining DataFrame datasets together:\n", "\n", "1. Concatenation (`concat`)\n", "2. Merging (`merge`)\n", "3. Joining (`join`)\n", "\n", "| # | Method | Function | Use Case | Key Parameter | When to Use |\n", "|---|--------|----------|----------|---------------|-------------|\n", "| 1 | **Concatenation** | `pd.concat()` | **Stack** DataFrames along **rows/columns** | `axis=0/1` | Same structure, simple stacking |\n", "| 2 | **Merging** | `pd.merge()` | Combine based on common **values** | `on='column'` | SQL-like joins on column values |\n", "| 3 | **Joining** | `df.join()` | Combine based on **index** | `how='left/right/inner/outer'` | Index-based combinations |\n", "\n", "In this section we will discuss these three methods with examples.\n" ] }, { "cell_type": "markdown", "id": "74d24154", "metadata": {}, "source": [ "Let's build example DataFrames:\n" ] }, { "cell_type": "code", "execution_count": 4, "id": "56d0a4a8", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "
\n", "

df1

\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
RegionQ1Q2
0North100110
1South150160
2East200210
3West175180
\n", "
\n", " \n", "
\n", "

df2

\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
RegionQ3Q4
0North120130
1South170180
2East220230
3West185190
\n", "
\n", " \n", "
\n", "

df3

\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
RegionProduct_AProduct_B
0North5060
1South7585
2East100110
3West8595
\n", "
\n", "
" ], "text/plain": [ "" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import sys\n", "\n", "import pandas\n", "sys.path.insert(0, '../../') # adjust path to workspace root\n", "from shared.utils import display_side_by_side\n", "\n", "import pandas as pd\n", "\n", "# Create simple quarterly sales data for different regions\n", "df1 = pd.DataFrame({\n", " 'Region': ['North', 'South', 'East', 'West'],\n", " 'Q1': [100, 150, 200, 175],\n", " 'Q2': [110, 160, 210, 180]\n", "})\n", "\n", "df2 = pd.DataFrame({\n", " 'Region': ['North', 'South', 'East', 'West'], \n", " 'Q3': [120, 170, 220, 185],\n", " 'Q4': [130, 180, 230, 190]\n", "})\n", "\n", "df3 = pd.DataFrame({\n", " 'Region': ['North', 'South', 'East', 'West'],\n", " 'Product_A': [50, 75, 100, 85],\n", " 'Product_B': [60, 85, 110, 95]\n", "})\n", "\n", "display_side_by_side(df1, df2, df3, names=['df1', 'df2', 'df3'])\n" ] }, { "cell_type": "markdown", "id": "41ca5c28", "metadata": {}, "source": [ "### `.concat()`\n", "\n", "Concatenation basically glues together DataFrames. Keep in mind that dimensions should match along the axis you are concatenating on. You can use **pd.concat** and pass in a list of DataFrames to concatenate together:\n", "\n", "**Syntax:**\n", "```python\n", "pd.concat(objs, axis=0, join='outer', ignore_index=False, keys=None)\n", "```\n", "\n", "**Key Parameters:**\n", "- `objs`: A **`list`** of DataFrames to concatenate\n", "- `axis=0`: Concatenate along rows (default), `axis=1` for columns\n", "- `join='outer'`: How to handle different column names ('inner', 'outer')\n", "- `ignore_index=False`: Whether to reset the index in the result\n" ] }, { "cell_type": "markdown", "id": "fdfbc78c", "metadata": {}, "source": [ "Now let's see what happens when we concatenate all three DataFrames together using `pd.concat()`:\n", "\n", "**Expected Result**: The DataFrames will be stacked vertically (row-wise) creating a single DataFrame with:\n", "- All 12 rows (4 from each DataFrame)\n", "- All unique columns: Region, Q1, Q2, Q3, Q4, Product_A, Product_B\n", "- `NaN` values where DataFrames don't share columns:\n", " - df1 rows: missing Q3, Q4, Product_A, Product_B\n", " - df2 rows: missing Q1, Q2, Product_A, Product_B\n", " - df3 rows: missing Q1, Q2, Q3, Q4" ] }, { "cell_type": "markdown", "id": "a14d2cb3", "metadata": {}, "source": [ "Now let's try concatenating the DataFrames together. \n", "\n", "The `axis` parameter controls the direction of concatenation:\n", "- `axis=0` (default): Concatenates along **rows** (stacks DataFrames vertically)\n", "\n", "- `axis=1`: Concatenates along **columns** (places DataFrames side by side)Let's see both examples:\n", "\n", "\n", "**Remember:** `axis=0` rows run Down, `axis=1` columns run Across" ] }, { "cell_type": "markdown", "id": "68a53ad1", "metadata": {}, "source": [ "#### `axis=0`" ] }, { "cell_type": "code", "execution_count": 6, "id": "6c91c5c1", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
RegionQ1Q2Q3Q4Product_AProduct_B
0North100.0110.0NaNNaNNaNNaN
1South150.0160.0NaNNaNNaNNaN
2East200.0210.0NaNNaNNaNNaN
3West175.0180.0NaNNaNNaNNaN
0NorthNaNNaN120.0130.0NaNNaN
1SouthNaNNaN170.0180.0NaNNaN
2EastNaNNaN220.0230.0NaNNaN
3WestNaNNaN185.0190.0NaNNaN
0NorthNaNNaNNaNNaN50.060.0
1SouthNaNNaNNaNNaN75.085.0
2EastNaNNaNNaNNaN100.0110.0
3WestNaNNaNNaNNaN85.095.0
\n", "
" ], "text/plain": [ " Region Q1 Q2 Q3 Q4 Product_A Product_B\n", "0 North 100.0 110.0 NaN NaN NaN NaN\n", "1 South 150.0 160.0 NaN NaN NaN NaN\n", "2 East 200.0 210.0 NaN NaN NaN NaN\n", "3 West 175.0 180.0 NaN NaN NaN NaN\n", "0 North NaN NaN 120.0 130.0 NaN NaN\n", "1 South NaN NaN 170.0 180.0 NaN NaN\n", "2 East NaN NaN 220.0 230.0 NaN NaN\n", "3 West NaN NaN 185.0 190.0 NaN NaN\n", "0 North NaN NaN NaN NaN 50.0 60.0\n", "1 South NaN NaN NaN NaN 75.0 85.0\n", "2 East NaN NaN NaN NaN 100.0 110.0\n", "3 West NaN NaN NaN NaN 85.0 95.0" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Concatenating along rows (axis=0) - default behavior\n", "pd.concat([df1, df2, df3])" ] }, { "cell_type": "markdown", "id": "0c44cb7b", "metadata": {}, "source": [ "Note how the DataFrames are stacked vertically. Now let's see concatenation along columns:" ] }, { "cell_type": "markdown", "id": "7ec44043", "metadata": {}, "source": [ "#### `axis=1`" ] }, { "cell_type": "code", "execution_count": 7, "id": "8de8956f", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
RegionQ1Q2RegionQ3Q4RegionProduct_AProduct_B
0North100110North120130North5060
1South150160South170180South7585
2East200210East220230East100110
3West175180West185190West8595
\n", "
" ], "text/plain": [ " Region Q1 Q2 Region Q3 Q4 Region Product_A Product_B\n", "0 North 100 110 North 120 130 North 50 60\n", "1 South 150 160 South 170 180 South 75 85\n", "2 East 200 210 East 220 230 East 100 110\n", "3 West 175 180 West 185 190 West 85 95" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Concatenating along columns (axis=1)\n", "# Note: indices do not overlap, so result will have NaNs for missing values\n", "pd.concat([df1, df2, df3], axis=1)" ] }, { "cell_type": "code", "execution_count": 8, "id": "5376dfe9", "metadata": { "tags": [ "thebe-interactive" ] }, "outputs": [], "source": [ "### EXERCISE: Concatenating DataFrames\n", "# 1. print: Create two DataFrames with sales data:\n", "# df1 = pd.DataFrame({'Month': ['Jan', 'Feb'], 'Sales': [100, 150]})\n", "# df2 = pd.DataFrame({'Month': ['Mar', 'Apr'], 'Sales': [200, 180]})\n", "# 2. Concatenate them along rows (default axis=0)\n", "# 3. Concatenate them along columns (axis=1)\n", "# What happens when you concatenate along columns? Explain the NaN values\n", "### Your code starts here:\n", "\n", "\n", "\n", "\n", "### Your code ends here.\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [ "hide-input" ] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " Month Sales\n", "0 Jan 100\n", "1 Feb 150\n", "2 Mar 200\n", "3 Apr 180\n", " Month Sales Month Sales\n", "0 Jan 100 Mar 200\n", "1 Feb 150 Apr 180\n", "Column-wise concatenation aligns rows by index, so unmatched indexes can create NaN values.\n" ] } ], "source": [ "### Solution\n", "\n", "import pandas as pd\n", "\n", "# 1. Create DataFrames\n", "df1 = pd.DataFrame({'Month': ['Jan', 'Feb'], 'Sales': [100, 150]})\n", "df2 = pd.DataFrame({'Month': ['Mar', 'Apr'], 'Sales': [200, 180]})\n", "\n", "print(\"1. DataFrame 1:\")\n", "print(df1)\n", "print()\n", "print(\"DataFrame 2:\")\n", "print(df2)\n", "print()\n", "\n", "# 2. Concatenate along rows\n", "print(\"2. Concatenated along rows (axis=0):\")\n", "result_rows = pd.concat([df1, df2])\n", "print(result_rows)\n", "print()\n", "\n", "# 3. Concatenate along columns\n", "print(\"3. Concatenated along columns (axis=1):\")\n", "result_cols = pd.concat([df1, df2], axis=1)\n", "print(result_cols)\n", "print()\n", "\n", "# 4. Explanation\n", "print(\"Note: When concatenating along columns, indices don't align,\")\n", "print(\"so NaN values appear where data doesn't exist for that index.\")\n" ] }, { "cell_type": "markdown", "id": "e2eed99d", "metadata": {}, "source": [ "### Merging\n", "\n", "The **merge** function allows you to merge DataFrames together using a similar logic as **joining** **SQL** Tables together. " ] }, { "cell_type": "markdown", "id": "4b93b85f", "metadata": {}, "source": [ "#### Syntax\n", "\n", "```python\n", "pd.merge(left, right, how='inner', on=None, left_on=None, right_on=None, \n", " left_index=False, right_index=False, sort=False, \n", " suffixes=('_x', '_y'), copy=True, indicator=False)\n", "```\n", "\n", "**Key Parameters:**\n", "- `left`, `right`: DataFrames to be merged\n", "- `how`: Type of merge to perform (`left`, `right`, `outer`, `inner`, `cross`)\n", "- `on`: Column name(s) to join on (must be present in both DataFrames) \n", "- `left_on`, `right_on`: Column names to join on for left and right DataFrames respectively\n", "- `suffixes`: Tuple of suffixes for overlapping column names (default: ('_x', '_y'))" ] }, { "cell_type": "markdown", "id": "8b5c8e5d", "metadata": {}, "source": [ "The `how` parameter specifies the type of merge:\n", "\n", "| Merge Type | Description | SQL Equivalent |\n", "|------------|-------------|----------------|\n", "| **`inner`** (default) | Returns only **rows** with **matching keys** in both DataFrames | `INNER JOIN` |\n", "| **`outer`** | Returns **all rows** from **both** DataFrames, filling NaN where no match exists | `FULL OUTER JOIN` |\n", "| **`left`** | Returns all rows from the **left** DataFrame, filling NaN for non-matching right rows | `LEFT JOIN` |\n", "| **`right`** | Returns all rows from the **right** DataFrame, filling NaN for non-matching left rows | `RIGHT JOIN` |\n" ] }, { "cell_type": "markdown", "id": "cbc65030", "metadata": {}, "source": [ "Let's create two business DataFrames — **customers** and **orders** — with partial overlap on `customer_id` to demonstrate each merge type:" ] }, { "cell_type": "code", "execution_count": 10, "id": "23a566b7", "metadata": { "tags": [] }, "outputs": [], "source": [ "# customers: C001-C003 (Alice has no order; C004 has an order but no customer record)\n", "left = pd.DataFrame({\n", " 'customer_id': ['C001', 'C002', 'C003'],\n", " 'name': ['Alice', 'Bob', 'Carol'],\n", " 'city': ['New York', 'Chicago', 'Houston']\n", "})\n", "\n", "# orders: C002-C004\n", "right = pd.DataFrame({\n", " 'customer_id': ['C002', 'C003', 'C004'],\n", " 'product': ['Laptop', 'Monitor', 'Keyboard'],\n", " 'revenue': [1200, 400, 80]\n", "})" ] }, { "cell_type": "code", "execution_count": 11, "id": "8b3f2d72", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "
\n", "

customers

\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
customer_idnamecity
0C001AliceNew York
1C002BobChicago
2C003CarolHouston
\n", "
\n", " \n", "
\n", "

orders

\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
customer_idproductrevenue
0C002Laptop1200
1C003Monitor400
2C004Keyboard80
\n", "
\n", "
" ], "text/plain": [ "" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "display_side_by_side(left, right, names=[\"customers\", \"orders\"])" ] }, { "cell_type": "markdown", "id": "d1e1ab6a", "metadata": {}, "source": [ "Now let's see different types of merges:" ] }, { "cell_type": "markdown", "id": "7480d1cb", "metadata": {}, "source": [ "#### Inner Merge\n", "\n", "An **inner merge** returns only the rows where the key values exist in **both** DataFrames. This is the most restrictive type of merge and is the default behavior of `pd.merge()`.\n", "\n", "Only customers **C002** and **C003** appear in both DataFrames, so the result contains just those 2 rows:" ] }, { "cell_type": "code", "execution_count": 12, "id": "9214e1cc", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
customer_idnamecityproductrevenue
0C002BobChicagoLaptop1200
1C003CarolHoustonMonitor400
\n", "
" ], "text/plain": [ " customer_id name city product revenue\n", "0 C002 Bob Chicago Laptop 1200\n", "1 C003 Carol Houston Monitor 400" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Inner merge - only customers who have placed an order\n", "pd.merge(left, right, how='inner', on='customer_id')" ] }, { "cell_type": "markdown", "id": "f9eb4a92", "metadata": {}, "source": [ "#### Outer Merge\n", "\n", "An **outer merge** returns all rows from both DataFrames. Where keys don't match, NaN values are filled in for missing data. This shows the complete picture of all available data." ] }, { "cell_type": "code", "execution_count": 13, "id": "c438a098", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
customer_idnamecityproductrevenue
0C001AliceNew YorkNaNNaN
1C002BobChicagoLaptop1200.0
2C003CarolHoustonMonitor400.0
3C004NaNNaNKeyboard80.0
\n", "
" ], "text/plain": [ " customer_id name city product revenue\n", "0 C001 Alice New York NaN NaN\n", "1 C002 Bob Chicago Laptop 1200.0\n", "2 C003 Carol Houston Monitor 400.0\n", "3 C004 NaN NaN Keyboard 80.0" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Outer merge - all customers and all orders (NaN where no match)\n", "pd.merge(left, right, how='outer', on='customer_id')" ] }, { "cell_type": "markdown", "id": "72c187ee", "metadata": {}, "source": [ "#### Left Merge\n", "\n", "A **left merge** keeps all rows from the left DataFrame and adds matching rows from the right DataFrame. Non-matching keys from the right DataFrame are excluded." ] }, { "cell_type": "code", "execution_count": 14, "id": "277fa1e0", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
customer_idnamecityproductrevenue
0C001AliceNew YorkNaNNaN
1C002BobChicagoLaptop1200.0
2C003CarolHoustonMonitor400.0
\n", "
" ], "text/plain": [ " customer_id name city product revenue\n", "0 C001 Alice New York NaN NaN\n", "1 C002 Bob Chicago Laptop 1200.0\n", "2 C003 Carol Houston Monitor 400.0" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Left merge - all customers, with order data where available\n", "pd.merge(left, right, how='left', on='customer_id')" ] }, { "cell_type": "markdown", "id": "1d89fab0", "metadata": {}, "source": [ "#### Right Merge\n", "\n", "A **right merge** keeps all rows from the right DataFrame and adds matching rows from the left DataFrame. Non-matching keys from the left DataFrame are excluded." ] }, { "cell_type": "code", "execution_count": 15, "id": "a88e43c0", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
customer_idnamecityproductrevenue
0C002BobChicagoLaptop1200
1C003CarolHoustonMonitor400
2C004NaNNaNKeyboard80
\n", "
" ], "text/plain": [ " customer_id name city product revenue\n", "0 C002 Bob Chicago Laptop 1200\n", "1 C003 Carol Houston Monitor 400\n", "2 C004 NaN NaN Keyboard 80" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Right merge - all orders, with customer data where available\n", "pd.merge(left, right, how='right', on='customer_id')" ] }, { "cell_type": "markdown", "id": "f2c095eb", "metadata": {}, "source": [ "#### Merging on Multiple Keys (FYI)\n", "\n", "When you specify multiple columns in the `on` parameter, pandas requires **all key combinations** to match for rows to be included in the result. This creates more restrictive matching conditions.\n", "\n", "For example, with `on=['key1', 'key2']`:\n", "- Row will only be included if **both** key1 AND key2 values match between DataFrames\n", "- This is like an AND condition in SQL: `WHERE df1.key1 = df2.key1 AND df1.key2 = df2.key2`\n", "- Useful when you need to match on composite keys (like combinations of date + product, or region + category)" ] }, { "cell_type": "markdown", "id": "29fe8edb", "metadata": {}, "source": [ "Or to show a more complicated example with multiple keys:\n" ] }, { "cell_type": "code", "execution_count": 16, "id": "80f61369", "metadata": { "tags": [] }, "outputs": [], "source": [ "left = pd.DataFrame({'key1': ['K0', 'K0', 'K1', 'K2'],\n", " 'key2': ['K0', 'K1', 'K0', 'K1'],\n", " 'A': ['A0', 'A1', 'A2', 'A3'],\n", " 'B': ['B0', 'B1', 'B2', 'B3']})\n", "\n", "right = pd.DataFrame({'key1': ['K0', 'K1', 'K1', 'K2'],\n", " 'key2': ['K0', 'K0', 'K0', 'K0'],\n", " 'C': ['C0', 'C1', 'C2', 'C3'],\n", " 'D': ['D0', 'D1', 'D2', 'D3']})" ] }, { "cell_type": "markdown", "id": "d4e9210f", "metadata": {}, "source": [ "Now let's demonstrate different merge types using **both key1 and key2** columns. Notice how pandas only includes rows where **all key combinations match exactly** between the DataFrames.\n", "\n", "The examples below show how each merge type behaves when working with composite keys." ] }, { "cell_type": "markdown", "id": "d187f61f", "metadata": {}, "source": [ "**Inner Merge of Multiple df's**\n", "\n", "inner merge (default): only matching keys" ] }, { "cell_type": "code", "execution_count": 17, "id": "9ba546fc", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
key1key2ABCD
0K0K0A0B0C0D0
1K1K0A2B2C1D1
2K1K0A2B2C2D2
\n", "
" ], "text/plain": [ " key1 key2 A B C D\n", "0 K0 K0 A0 B0 C0 D0\n", "1 K1 K0 A2 B2 C1 D1\n", "2 K1 K0 A2 B2 C2 D2" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "pd.merge(left, right, on=['key1', 'key2'])" ] }, { "cell_type": "markdown", "id": "0365002b", "metadata": {}, "source": [ "**Outer Merge of Multiple df's**\n", "\n", "all rows from both DataFrames" ] }, { "cell_type": "code", "execution_count": 18, "id": "d1c1a957", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
key1key2ABCD
0K0K0A0B0C0D0
1K0K1A1B1NaNNaN
2K1K0A2B2C1D1
3K1K0A2B2C2D2
4K2K0NaNNaNC3D3
5K2K1A3B3NaNNaN
\n", "
" ], "text/plain": [ " key1 key2 A B C D\n", "0 K0 K0 A0 B0 C0 D0\n", "1 K0 K1 A1 B1 NaN NaN\n", "2 K1 K0 A2 B2 C1 D1\n", "3 K1 K0 A2 B2 C2 D2\n", "4 K2 K0 NaN NaN C3 D3\n", "5 K2 K1 A3 B3 NaN NaN" ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "pd.merge(left, right, how='outer', on=['key1', 'key2'])" ] }, { "cell_type": "markdown", "id": "1d9013e1", "metadata": {}, "source": [ "**Right Merge**\n", "\n", "right merge: all rows from right, matching from left" ] }, { "cell_type": "code", "execution_count": 19, "id": "40201e43", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
key1key2ABCD
0K0K0A0B0C0D0
1K1K0A2B2C1D1
2K1K0A2B2C2D2
3K2K0NaNNaNC3D3
\n", "
" ], "text/plain": [ " key1 key2 A B C D\n", "0 K0 K0 A0 B0 C0 D0\n", "1 K1 K0 A2 B2 C1 D1\n", "2 K1 K0 A2 B2 C2 D2\n", "3 K2 K0 NaN NaN C3 D3" ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "pd.merge(left, right, how='right', on=['key1', 'key2'])" ] }, { "cell_type": "markdown", "id": "1143f346", "metadata": {}, "source": [ "**Left Merge**\n", "\n", "left merge: all rows from left, matching from right\n" ] }, { "cell_type": "code", "execution_count": 20, "id": "01e1c151", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
key1key2ABCD
0K0K0A0B0C0D0
1K0K1A1B1NaNNaN
2K1K0A2B2C1D1
3K1K0A2B2C2D2
4K2K1A3B3NaNNaN
\n", "
" ], "text/plain": [ " key1 key2 A B C D\n", "0 K0 K0 A0 B0 C0 D0\n", "1 K0 K1 A1 B1 NaN NaN\n", "2 K1 K0 A2 B2 C1 D1\n", "3 K1 K0 A2 B2 C2 D2\n", "4 K2 K1 A3 B3 NaN NaN" ] }, "execution_count": 20, "metadata": {}, "output_type": "execute_result" } ], "source": [ "pd.merge(left, right, how='left', on=['key1', 'key2'])" ] }, { "cell_type": "code", "execution_count": 21, "id": "63669d1f", "metadata": { "tags": [ "thebe-interactive" ] }, "outputs": [], "source": [ "### EXERCISE: Merging DataFrames\n", "#\n", "# Create two DataFrames:\n", "# products with {'ProdID': [1, 2, 3], 'Name': ['Widget', 'Gadget', 'Doohickey']}\n", "# sales with {'ProdID': [1, 2, 4], 'Units': [100, 150, 75]}\n", "# 1. print both DataFrames\n", "# 2. print: Perform an inner merge on ProdID\n", "# 3. print: Perform a left merge (keep all products)\n", "# 4. print: Perform an outer merge to see all records from both DataFrames\n", "#\n", "### Your code starts here:\n", "\n", "\n", "\n", "\n", "### Your code ends here.\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [ "hide-input" ] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " ProdID Name\n", "0 1 Widget\n", "1 2 Gadget\n", "2 3 Doohickey\n", " ProdID Units\n", "0 1 100\n", "1 2 150\n", "2 4 75\n", " ProdID Name Units\n", "0 1 Widget 100\n", "1 2 Gadget 150\n", " ProdID Name Units\n", "0 1 Widget 100.0\n", "1 2 Gadget 150.0\n", "2 3 Doohickey NaN\n", " ProdID Name Units\n", "0 1 Widget 100.0\n", "1 2 Gadget 150.0\n", "2 3 Doohickey NaN\n", "3 4 NaN 75.0\n" ] } ], "source": [ "### Solution\n", "\n", "import pandas as pd\n", "\n", "# Create DataFrames\n", "products = pd.DataFrame({'ProdID': [1, 2, 3], 'Name': ['Widget', 'Gadget', 'Doohickey']})\n", "sales = pd.DataFrame({'ProdID': [1, 2, 4], 'Units': [100, 150, 75]})\n", "\n", "print(\"Products:\")\n", "print(products)\n", "print()\n", "print(\"Sales:\")\n", "print(sales)\n", "print()\n", "\n", "# 1. Inner merge\n", "print(\"Inner merge (only matching records):\")\n", "inner = pd.merge(products, sales, on='ProdID', how='inner')\n", "print(inner)\n", "print()\n", "\n", "# 2. Left merge\n", "print(\"Left merge (all products):\")\n", "left = pd.merge(products, sales, on='ProdID', how='left')\n", "print(left)\n", "print()\n", "\n", "# 3. Outer merge\n", "print(\"Outer merge (all records):\")\n", "outer = pd.merge(products, sales, on='ProdID', how='outer')\n", "print(outer)\n" ] }, { "cell_type": "markdown", "id": "36818185", "metadata": {}, "source": [ "### `join()`\n", "\n", "Joining is a convenient method for combining the columns of two potentially differently-indexed DataFrames into a single result DataFrame.\n", "\n", "- `join()` is basically just a convenience wrapper around `merge()` under the hood. \n", "- `join()` joins on the **index** by default.\n", "\n", "#### Syntax\n", "\n", "```python\n", "df.join(other, on=None, how='left', lsuffix='', rsuffix='', sort=False)\n", "```\n", "\n", "**Key Parameters:**\n", "- `other`: DataFrame or Series to join with\n", "- `on`: Column name to join on (if None, uses index)\n", "- `how`: Type of join ('left', 'right', 'outer', 'inner', 'cross')\n", "- `lsuffix`, `rsuffix`: Suffixes for overlapping column names from left and right DataFrames\n", "- `sort`: Whether to sort the result DataFrame by the join keys\n", "\n", "**Common Usage:**\n", "```python\n", "df1.join(df2) # Left join on index (default)\n", "df1.join(df2, how='outer') # Outer join on index\n", "df1.join(df2, on='col') # Join using specific column instead of index\n", "```" ] }, { "cell_type": "code", "execution_count": 23, "id": "265c68c0", "metadata": { "tags": [] }, "outputs": [], "source": [ "left = pd.DataFrame({'A': ['A0', 'A1', 'A2'],\n", " 'B': ['B0', 'B1', 'B2']},\n", " index=['K0', 'K1', 'K2'])\n", "\n", "right = pd.DataFrame({'C': ['C0', 'C2', 'C3'],\n", " 'D': ['D0', 'D2', 'D3']},\n", " index=['K0', 'K2', 'K3'])" ] }, { "cell_type": "code", "execution_count": 24, "id": "88025009", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "
\n", "

Left DataFrame

\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
AB
K0A0B0
K1A1B1
K2A2B2
\n", "
\n", " \n", "
\n", "

Right DataFrame

\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
CD
K0C0D0
K2C2D2
K3C3D3
\n", "
\n", "
" ], "text/plain": [ "" ] }, "execution_count": 24, "metadata": {}, "output_type": "execute_result" } ], "source": [ "display_side_by_side(left, right, \"Left DataFrame\", \"Right DataFrame\")" ] }, { "cell_type": "markdown", "id": "252798e0", "metadata": {}, "source": [ "Now let's perform a left join (default behavior):" ] }, { "cell_type": "markdown", "id": "4bcdbce3", "metadata": {}, "source": [ "Try an outer join to include all indices from both DataFrames:" ] }, { "cell_type": "code", "execution_count": 25, "id": "1a219c49", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
ABCD
K0A0B0C0D0
K1A1B1NaNNaN
K2A2B2C2D2
\n", "
" ], "text/plain": [ " A B C D\n", "K0 A0 B0 C0 D0\n", "K1 A1 B1 NaN NaN\n", "K2 A2 B2 C2 D2" ] }, "execution_count": 25, "metadata": {}, "output_type": "execute_result" } ], "source": [ "left.join(right)" ] }, { "cell_type": "code", "execution_count": 26, "id": "6c6f0e0f", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
ABCD
K0A0B0C0D0
K1A1B1NaNNaN
K2A2B2C2D2
K3NaNNaNC3D3
\n", "
" ], "text/plain": [ " A B C D\n", "K0 A0 B0 C0 D0\n", "K1 A1 B1 NaN NaN\n", "K2 A2 B2 C2 D2\n", "K3 NaN NaN C3 D3" ] }, "execution_count": 26, "metadata": {}, "output_type": "execute_result" } ], "source": [ "left.join(right, how='outer')" ] }, { "cell_type": "code", "execution_count": 27, "id": "e9ac11cb", "metadata": { "tags": [ "thebe-interactive" ] }, "outputs": [], "source": [ "### EXERCISE: Joining DataFrames\n", "#\n", "# 1. print (2 dfs): Create two DataFrames with index-based data:\n", "# df_left: with data {'A': [1, 2, 3]} and index=['a', 'b', 'c']\n", "# df_right with data {'B': [4, 5, 6]} and index=['b', 'c', 'd']\n", "# 2. print: Use .join() with default (left join)\n", "# 3. print: Use .join() with how='outer'\n", "# 4. print: Use .join() with how='inner'\n", "#\n", "### Your code starts here:\n", "\n", "\n", "\n", "\n", "### Your code ends here.\n" ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "tags": [ "hide-input" ] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Left DataFrame:\n", " A\n", "a 1\n", "b 2\n", "c 3\n", "\n", "Right DataFrame:\n", " B\n", "b 4\n", "c 5\n", "d 6\n", "\n", "Left join (default):\n", " A B\n", "a 1 NaN\n", "b 2 4.0\n", "c 3 5.0\n", "\n", "Outer join:\n", " A B\n", "a 1.0 NaN\n", "b 2.0 4.0\n", "c 3.0 5.0\n", "d NaN 6.0\n", "\n", "Inner join:\n", " A B\n", "b 2 4\n", "c 3 5\n" ] } ], "source": [ "### Solution\n", "\n", "import pandas as pd\n", "\n", "# Create DataFrames\n", "df_left = pd.DataFrame({'A': [1, 2, 3]}, index=['a', 'b', 'c'])\n", "df_right = pd.DataFrame({'B': [4, 5, 6]}, index=['b', 'c', 'd'])\n", "\n", "print(\"Left DataFrame:\")\n", "print(df_left)\n", "print()\n", "print(\"Right DataFrame:\")\n", "print(df_right)\n", "print()\n", "\n", "# 1. Default left join\n", "print(\"Left join (default):\")\n", "print(df_left.join(df_right))\n", "print()\n", "\n", "# 2. Outer join\n", "print(\"Outer join:\")\n", "print(df_left.join(df_right, how='outer'))\n", "print()\n", "\n", "# 3. Inner join\n", "print(\"Inner join:\")\n", "print(df_left.join(df_right, how='inner'))\n" ] }, { "cell_type": "markdown", "id": "4f019dd4", "metadata": {}, "source": [ "### Summary\n", "\n", "- **`concat()`**: Use when stacking DataFrames with the same columns (row-wise) or same rows (column-wise). Simple \"gluing\" operation.\n", "- **`merge()`**: Use when combining DataFrames based on common column values (like SQL JOIN). Most flexible for relational-style operations.\n", "- **`join()`**: Use when combining DataFrames based on their indices. Convenient shorthand for index-based merging.\n", "\n", "**See also**: [Pandas merge, join, concatenate documentation](https://pandas.pydata.org/docs/user_guide/merging.html)" ] }, { "cell_type": "markdown", "id": "9ca499b6", "metadata": {}, "source": [ "## Aggregation and GroupBy\n", "\n", "Data analysis often involves summarizing large datasets into meaningful insights. **Aggregation** operations help you compute summary statistics (like totals, averages, counts) across your data.\n", "\n", "This section covers two main approaches to data summarization:\n", "\n", "1. **Simple Aggregations**: Calculate **summary** statistics across entire DataFrames or Series\n", "2. **GroupBy Operations**: **Split** data into groups, apply functions to each group, then combine results\n", "\n", "These operations are essential for exploratory data analysis, allowing you to quickly understand patterns and trends in your datasets. We'll start with basic aggregation functions and then explore the powerful **split-apply-combine** workflow using `groupby()`." ] }, { "cell_type": "markdown", "id": "a2d42d05", "metadata": {}, "source": [ "### Simple Aggregations\n", "\n", "Let's start by exploring basic aggregation functions that work on entire Series or DataFrames. These functions calculate summary statistics like `sum()`, `mean()`, `min()`, and `max()`, reducing your data to single meaningful values.\n", "\n", "| Method | Description | Returns |\n", "|--------|-------------|---------|\n", "| `sum()` | Total of all values | Sum of elements |\n", "| `mean()` | Average of all values | Arithmetic mean |\n", "| `median()` | Middle value when sorted | Median value |\n", "| `min()` | Smallest value | Minimum element |\n", "| `max()` | Largest value | Maximum element |\n", "| `std()` | Standard deviation | Measure of spread |\n", "| `var()` | Variance | Squared standard deviation |\n", "| `count()` | Number of non-null values | Count of elements |\n", "| `describe()` | Summary statistics | Complete statistical summary |\n", "\n", "We'll demonstrate with some example data and show how these operations provide quick insights into your datasets." ] }, { "cell_type": "markdown", "id": "8a7fc335", "metadata": {}, "source": [ "Let's create some sample data to demonstrate these aggregation methods. We'll use NumPy's random number generator with a fixed seed to ensure reproducible results." ] }, { "cell_type": "code", "execution_count": 29, "id": "91191c9d", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/plain": [ "0 0\n", "1 7\n", "2 6\n", "3 4\n", "4 4\n", "dtype: int64" ] }, "execution_count": 29, "metadata": {}, "output_type": "execute_result" } ], "source": [ "rng = np.random.default_rng(42)\n", "ser = pd.Series(rng.integers(0, 10, 5))\n", "ser" ] }, { "cell_type": "code", "execution_count": 30, "id": "d6d69e17", "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "21\n" ] } ], "source": [ "### sum\n", "print(ser.sum())" ] }, { "cell_type": "code", "execution_count": 31, "id": "ed87a266", "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "4.2\n" ] } ], "source": [ "### mean\n", "print(ser.mean())" ] }, { "cell_type": "markdown", "id": "a75c13d6", "metadata": {}, "source": [ "Now let's try a few more aggregations on a DataFrame. Let's create a DataFrame with multiple columns to demonstrate how aggregation functions work across different dimensions. We'll create a DataFrame with two columns of random data using the same random number generator for consistency." ] }, { "cell_type": "code", "execution_count": 32, "id": "607c9879", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
AB
00.7739560.975622
10.4388780.761140
20.8585980.786064
30.6973680.128114
40.0941770.450386
\n", "
" ], "text/plain": [ " A B\n", "0 0.773956 0.975622\n", "1 0.438878 0.761140\n", "2 0.858598 0.786064\n", "3 0.697368 0.128114\n", "4 0.094177 0.450386" ] }, "execution_count": 32, "metadata": {}, "output_type": "execute_result" } ], "source": [ "rng = np.random.default_rng(42)\n", "df = pd.DataFrame(\n", " {\n", " 'A': rng.random(5),\n", " 'B': rng.random(5)\n", " }\n", ")\n", "df" ] }, { "cell_type": "code", "execution_count": 33, "id": "d08236e0", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/plain": [ "A 0.572596\n", "B 0.620265\n", "dtype: float64" ] }, "execution_count": 33, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.mean() ### mean for each column" ] }, { "cell_type": "code", "execution_count": 34, "id": "fcf8f9a3", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/plain": [ "A 2.862978\n", "B 3.101326\n", "dtype: float64" ] }, "execution_count": 34, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.sum() ### sum for each column" ] }, { "cell_type": "markdown", "id": "dccee830", "metadata": {}, "source": [ "Now, we'll start with loading the Planets dataset (which is available through the Seaborn visualization package and will be covered later). This dataset has details about more than one thousand extrasolar planets discovered up to 2014." ] }, { "cell_type": "markdown", "id": "1b3f7ef7", "metadata": {}, "source": [ "**Note**: The following example uses the Seaborn library to load sample data. If you don't have Seaborn installed, you can install it using one of these methods:\n", "\n", "**Option 1**: Install via command line with venv activated:\n", "```bash\n", "pip install seaborn\n", "```\n", "\n", "**Option 2**: Install in the notebook by the following line:\n", "```python\n", "%pip install seaborn\n", "```\n", "\n", "After installation, you can comment the line back out to avoid reinstalling in future runs." ] }, { "cell_type": "code", "execution_count": 35, "id": "85be95d0", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/plain": [ "1035" ] }, "execution_count": 35, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import seaborn as sns\n", "planets = sns.load_dataset('planets') ### syntax to load a dataset from seaborn\n", "planets.head()\n", "len(planets)" ] }, { "cell_type": "markdown", "id": "3da4eefc", "metadata": {}, "source": [ "Now let's apply some aggregation functions to this real dataset to see practical examples of data summarization:" ] }, { "cell_type": "code", "execution_count": 36, "id": "255fbcc3", "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Planet Mass Statistics:\n", "Total planets discovered:\t 513\n", "Average planet mass:\t\t 2.64\n", "Median planet mass:\t\t 1.26\n", "Largest planet mass:\t\t 25.00\n", "Smallest planet mass:\t\t 0.00\n" ] } ], "source": [ "# Example 1: Basic descriptive statistics for planet masses\n", "print(\"Planet Mass Statistics:\")\n", "print(f\"Total planets discovered:\\t {planets['mass'].count()}\")\n", "print(f\"Average planet mass:\\t\\t {planets['mass'].mean():.2f}\")\n", "print(f\"Median planet mass:\\t\\t {planets['mass'].median():.2f}\")\n", "print(f\"Largest planet mass:\\t\\t {planets['mass'].max():.2f}\")\n", "print(f\"Smallest planet mass:\\t\\t {planets['mass'].min():.2f}\")" ] }, { "cell_type": "markdown", "id": "a95278ca", "metadata": {}, "source": [ "If you are wondering why the count is 513 rather than more than a thousand, try this:" ] }, { "cell_type": "code", "execution_count": 37, "id": "6394c87d", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/plain": [ "np.int64(513)" ] }, "execution_count": 37, "metadata": {}, "output_type": "execute_result" } ], "source": [ "planets['mass'].notna().sum() ### count of non-missing values in each column" ] }, { "cell_type": "markdown", "id": "b0d823ca", "metadata": {}, "source": [ "\n", "or you can explore missing data as usual to see how messy real life data can be:" ] }, { "cell_type": "code", "execution_count": 38, "id": "3ed7d2b0", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/plain": [ "method 1035\n", "number 1035\n", "orbital_period 992\n", "mass 513\n", "distance 808\n", "year 1035\n", "dtype: int64" ] }, "execution_count": 38, "metadata": {}, "output_type": "execute_result" } ], "source": [ "planets.notnull().sum() ### returns a DataFrame of booleans indicating non-missing values (True) and missing values (False)" ] }, { "cell_type": "code", "execution_count": 39, "id": "5a1776ab", "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Discovery Methods Summary:\n", "Most common discovery method: Radial Velocity\n", "Total discovery methods used: 10\n", "Number of planets discovered by each method:\n", "method\n", "Radial Velocity 553\n", "Transit 397\n", "Imaging 38\n", "Microlensing 23\n", "Eclipse Timing Variations 9\n", "Name: count, dtype: int64\n" ] } ], "source": [ "# Example 2: Summary statistics by discovery method\n", "print(\"Discovery Methods Summary:\")\n", "print(f\"Most common discovery method: {planets['method'].mode()[0]}\")\n", "print(f\"Total discovery methods used: {planets['method'].nunique()}\")\n", "print(f\"Number of planets discovered by each method:\")\n", "print(planets['method'].value_counts().head())" ] }, { "cell_type": "markdown", "id": "986a4783", "metadata": {}, "source": [ "`value_counts()` counts how many times each value appears in a column, from most to least common. Add `normalize=True` to get proportions instead of counts: each count is divided by the total, so the proportions add up to 1." ] }, { "cell_type": "code", "execution_count": null, "id": "c64d9582", "metadata": { "tags": [] }, "outputs": [], "source": [ "planets['method'].value_counts(normalize=True).head() ### proportions instead of counts" ] }, { "cell_type": "markdown", "id": "d9230ec7", "metadata": {}, "source": [ "### GroupBy Operations\n", "\n", "The power of data analysis often lies in understanding patterns within different groups or categories. For example, you might want to find the average sales per region, count the number of planets discovered each year, or calculate the maximum temperature for each month. This is where **GroupBy operations** become essential.\n", "\n", "Pandas' `groupby` operation follows the elegant **split-apply-combine** strategy:\n", "1. **Split** the data into groups based on some criteria\n", "2. **Apply** a function to each group independently\n", "3. **Combine** the results into a single output\n", "\n", "Think of it like organizing students into groups by grade level, calculating each group's average test score, and then presenting all the results together. This concept, borrowed from SQL's `GROUP BY` and formalized by Hadley Wickham, is one of the most powerful tools in pandas for data analysis." ] }, { "cell_type": "markdown", "id": "9692f2de", "metadata": {}, "source": [ "```{figure} ../../figures/groupby.png\n", "---\n", "width: 250px\n", "name: groupby\n", "---\n", "A visual representation of a groupby operation {cite}`Vanderplas_2022`\n", "```" ] }, { "cell_type": "markdown", "id": "25d54994", "metadata": {}, "source": [ "#### GroupBy Workflow: From Split to Results\n", "\n", "Understanding GroupBy operations requires grasping the **split-apply-combine** workflow that happens behind the scenes. When you call `df.groupby('column')`, you're not getting results immediately: you're creating a **GroupBy object** that represents your grouped data structure.\n", "\n", "```python\n", "# Step 1: SPLIT - Create groups (no calculation yet)\n", "grouped = df.groupby('category')\n", "print(type(grouped)) # \n", "\n", "# Step 2: APPLY & COMBINE - Trigger calculation and combine results\n", "result = grouped.sum() # Now pandas computes sum for each group\n", "```\n", "\n", "Why this design matters:\n", "- **Efficiency**: Data is grouped once, then multiple operations can be applied\n", "- **Flexibility**: Same grouped structure can be used for different calculations\n", "- **Readability**: Natural method chaining like `df.groupby('region').sum().sort_values()`" ] }, { "cell_type": "markdown", "id": "d01c2c99", "metadata": {}, "source": [ "#### Essential GroupBy Methods\n", "\n", "**Basic Statistics**\n", "| Method | Returns | Best For | Example |\n", "|--------|---------|----------|---------|\n", "| `.sum()` | Sum of each group | Revenue totals | `df.groupby('region')['sales'].sum()` |\n", "| `.mean()` | Average of each group | Performance averages | `df.groupby('month')['temperature'].mean()` |\n", "| `.median()` | Middle value of each group | Typical values | `df.groupby('education')['income'].median()` |\n", "| `.min()` | Smallest value in each group | Best/worst performers | `df.groupby('student')['test_score'].min()` |\n", "| `.max()` | Largest value in each group | Peak performance | `df.groupby('quarter')['sales'].max()` |\n", "| `.std()` | Standard deviation | Measuring variability | `df.groupby('subject')['scores'].std()` |\n", "\n", "**Counting Operations**\n", "| Method | Returns | Key Difference | When to Use |\n", "|--------|---------|----------------|-------------|\n", "| `.count()` | Non-null values | Excludes NaN | Count valid responses |\n", "| `.size()` | Total rows | Includes NaN | Count all observations |\n", "| `.nunique()` | Unique values | Counts distinct items | Count variety/diversity |\n", "\n", "**Selection Methods**\n", "| Method | Returns | Typical Use Case |\n", "|--------|---------|------------------|\n", "| `.first()` | First value in group | Initial measurements, baseline data |\n", "| `.last()` | Last value in group | Final status, most recent data |\n", "\n", "**Advanced Functions**\n", "| Method | Power Level | Description | Example Use |\n", "|--------|-------------|-------------|-------------|\n", "| `.agg()` | Basic | Apply multiple functions | `grouped.agg(['min', 'max', 'mean'])` |\n", "| `.apply()` | Intermediate | Custom function per group | Complex calculations, custom logic |\n", "| `.transform()` | Advanced | Same-size output | Normalize within groups, percentage of group |\n", "\n", "**Power User Techniques**\n", "\n", "```python\n", "# Multi-function aggregation\n", "sales_summary = df.groupby('region').agg({\n", " 'revenue': ['sum', 'mean', 'count'],\n", " 'customers': 'nunique',\n", " 'orders': 'size'\n", "})\n", "\n", "# Method chaining for complex analysis\n", "top_regions = (df.groupby('region')['revenue']\n", " .sum()\n", " .sort_values(ascending=False)\n", " .head(5))\n", "\n", "# Transform for group-wise calculations\n", "df['revenue_pct_of_region'] = (df['revenue'] / \n", " df.groupby('region')['revenue'].transform('sum') * 100)\n", "```\n", "\n", "**Critical Distinctions to Remember**\n", "\n", "- **`.count()` vs `.size()`**: `.count()` excludes NaN values, `.size()` includes them\n", "- **`.agg()` vs `.apply()`**: `.agg()` for standard functions, `.apply()` for custom logic \n", "- **`.apply()` vs `.transform()`**: `.apply()` can change output size, `.transform()` maintains original DataFrame dimensions\n", "\n", "These methods form the foundation of sophisticated data analysis, enabling you to uncover patterns and insights that would be impossible to see in raw data." ] }, { "cell_type": "code", "execution_count": 80, "id": "5488eb95", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
CompanyPersonSales
0GOOGSam200
1GOOGCharlie120
2MSFTAmy340
3MSFTVanessa124
4FBCarl243
5FBSarah350
\n", "
" ], "text/plain": [ " Company Person Sales\n", "0 GOOG Sam 200\n", "1 GOOG Charlie 120\n", "2 MSFT Amy 340\n", "3 MSFT Vanessa 124\n", "4 FB Carl 243\n", "5 FB Sarah 350" ] }, "execution_count": 80, "metadata": {}, "output_type": "execute_result" } ], "source": [ "data = { ### create a dictionary with data\n", "\n", " 'Company': ['GOOG', 'GOOG', 'MSFT', 'MSFT', 'FB', 'FB'],\n", " 'Person': ['Sam', 'Charlie', 'Amy', 'Vanessa', 'Carl', 'Sarah'],\n", " 'Sales': [200, 120, 340, 124, 243, 350]\n", "}\n", "\n", "df = pd.DataFrame(data) ### create the dataframe\n", "df" ] }, { "cell_type": "markdown", "id": "fc54e789", "metadata": {}, "source": [ "Now you can use the `.groupby()` method to group rows together based off of a column name. For instance let's group based off of Company. This will create a DataFrameGroupBy object:" ] }, { "cell_type": "code", "execution_count": 81, "id": "f87a22cc", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/plain": [ "" ] }, "execution_count": 81, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.groupby('Company')" ] }, { "cell_type": "markdown", "id": "071fe74e", "metadata": {}, "source": [ "You can save this object as a new variable:" ] }, { "cell_type": "code", "execution_count": 82, "id": "4d0207ec", "metadata": { "tags": [] }, "outputs": [], "source": [ "by_comp = df.groupby(\"Company\")" ] }, { "cell_type": "markdown", "id": "34927a14", "metadata": {}, "source": [ "The idea of `groupby` becomes clear if you do a `.describe` on the object. Notice the data index now is grouped by company." ] }, { "cell_type": "code", "execution_count": 98, "id": "7c9886ce", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Sales
countmeanstdmin25%50%75%max
Company
FB2.0296.575.660426243.0269.75296.5323.25350.0
GOOG2.0160.056.568542120.0140.00160.0180.00200.0
MSFT2.0232.0152.735065124.0178.00232.0286.00340.0
\n", "
" ], "text/plain": [ " Sales \n", " count mean std min 25% 50% 75% max\n", "Company \n", "FB 2.0 296.5 75.660426 243.0 269.75 296.5 323.25 350.0\n", "GOOG 2.0 160.0 56.568542 120.0 140.00 160.0 180.00 200.0\n", "MSFT 2.0 232.0 152.735065 124.0 178.00 232.0 286.00 340.0" ] }, "execution_count": 98, "metadata": {}, "output_type": "execute_result" } ], "source": [ "by_comp.describe()" ] }, { "cell_type": "markdown", "id": "43418657", "metadata": {}, "source": [ "And then call aggregate methods off the object:" ] }, { "cell_type": "code", "execution_count": 87, "id": "2292866b", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/plain": [ "Company\n", "FB 296.5\n", "GOOG 160.0\n", "MSFT 232.0\n", "Name: Sales, dtype: float64" ] }, "execution_count": 87, "metadata": {}, "output_type": "execute_result" } ], "source": [ "by_comp['Sales'].mean() ### by_comp.mean()" ] }, { "cell_type": "markdown", "id": "d02d9214", "metadata": {}, "source": [ "Or you may just chain the methods together." ] }, { "cell_type": "code", "execution_count": 100, "id": "b4eaa97f", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/plain": [ "Company\n", "FB 296.5\n", "GOOG 160.0\n", "MSFT 232.0\n", "Name: Sales, dtype: float64" ] }, "execution_count": 100, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.groupby('Company')['Sales'].mean()" ] }, { "cell_type": "markdown", "id": "c40c9b54", "metadata": {}, "source": [ "More examples of aggregate methods:" ] }, { "cell_type": "code", "execution_count": 95, "id": "ec4e4cfc", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/plain": [ "Company\n", "FB 75.660426\n", "GOOG 56.568542\n", "MSFT 152.735065\n", "Name: Sales, dtype: float64" ] }, "execution_count": 95, "metadata": {}, "output_type": "execute_result" } ], "source": [ "by_comp[\"Sales\"].std()" ] }, { "cell_type": "code", "execution_count": 101, "id": "7ff16f5c", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
PersonSales
Company
FBCarl243
GOOGCharlie120
MSFTAmy124
\n", "
" ], "text/plain": [ " Person Sales\n", "Company \n", "FB Carl 243\n", "GOOG Charlie 120\n", "MSFT Amy 124" ] }, "execution_count": 101, "metadata": {}, "output_type": "execute_result" } ], "source": [ "by_comp.min()" ] }, { "cell_type": "code", "execution_count": 48, "id": "8a63112d", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
PersonSales
Company
FBSarah350
GOOGSam200
MSFTVanessa340
\n", "
" ], "text/plain": [ " Person Sales\n", "Company \n", "FB Sarah 350\n", "GOOG Sam 200\n", "MSFT Vanessa 340" ] }, "execution_count": 48, "metadata": {}, "output_type": "execute_result" } ], "source": [ "by_comp.max()" ] }, { "cell_type": "code", "execution_count": 49, "id": "f63b60cf", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
PersonSales
Company
FB22
GOOG22
MSFT22
\n", "
" ], "text/plain": [ " Person Sales\n", "Company \n", "FB 2 2\n", "GOOG 2 2\n", "MSFT 2 2" ] }, "execution_count": 49, "metadata": {}, "output_type": "execute_result" } ], "source": [ "by_comp.count()" ] }, { "cell_type": "code", "execution_count": 51, "id": "15f9c6e9", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
CompanyFBGOOGMSFT
Salescount2.0000002.0000002.000000
mean296.500000160.000000232.000000
std75.66042656.568542152.735065
min243.000000120.000000124.000000
25%269.750000140.000000178.000000
50%296.500000160.000000232.000000
75%323.250000180.000000286.000000
max350.000000200.000000340.000000
\n", "
" ], "text/plain": [ "Company FB GOOG MSFT\n", "Sales count 2.000000 2.000000 2.000000\n", " mean 296.500000 160.000000 232.000000\n", " std 75.660426 56.568542 152.735065\n", " min 243.000000 120.000000 124.000000\n", " 25% 269.750000 140.000000 178.000000\n", " 50% 296.500000 160.000000 232.000000\n", " 75% 323.250000 180.000000 286.000000\n", " max 350.000000 200.000000 340.000000" ] }, "execution_count": 51, "metadata": {}, "output_type": "execute_result" } ], "source": [ "by_comp.describe().transpose()" ] }, { "cell_type": "code", "execution_count": null, "id": "55198c1e", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/plain": [ "Sales count 2.000000\n", " mean 160.000000\n", " std 56.568542\n", " min 120.000000\n", " 25% 140.000000\n", " 50% 160.000000\n", " 75% 180.000000\n", " max 200.000000\n", "Name: GOOG, dtype: float64" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "by_comp.describe().transpose()['GOOG']" ] }, { "cell_type": "code", "execution_count": 52, "id": "e6113ce4", "metadata": { "tags": [ "thebe-interactive" ] }, "outputs": [], "source": [ "### EXERCISE: GroupBy Operations\n", "#\n", "# 1. print: Use the company sales data (\"df\") from the data:\n", "# {'Company': ['GOOG', 'GOOG', 'MSFT', 'MSFT', 'FB', 'FB'],\n", "# 'Person': ['Sam', 'Charlie', 'Amy', 'Vanessa', 'Carl', 'Sarah'],\n", "# 'Sales': [200, 120, 340, 124, 243, 350]}\n", "# 2. print: Group by Company and calculate the total sales for each company\n", "# 3. print: Group by Company and find the maximum sales for each company\n", "# 4. print: Group by Company and count how many salespeople each company has\n", "#\n", "### Your code starts here:\n", "\n", "\n", "\n", "\n", "### Your code ends here.\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [ "hide-input" ] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " Company Person Sales\n", "0 GOOG Sam 200\n", "1 GOOG Charlie 120\n", "2 MSFT Amy 340\n", "3 MSFT Vanessa 124\n", "4 FB Carl 243\n", "5 FB Sarah 350\n", "Company\n", "FB 593\n", "GOOG 320\n", "MSFT 464\n", "Name: Sales, dtype: int64\n", "Company\n", "FB 350\n", "GOOG 200\n", "MSFT 340\n", "Name: Sales, dtype: int64\n", "Company\n", "FB 2\n", "GOOG 2\n", "MSFT 2\n", "Name: Person, dtype: int64\n" ] } ], "source": [ "### Solution\n", "\n", "import pandas as pd\n", "\n", "# Create DataFrame\n", "df = pd.DataFrame({\n", " 'Company': ['GOOG', 'GOOG', 'MSFT', 'MSFT', 'FB', 'FB'],\n", " 'Person': ['Sam', 'Charlie', 'Amy', 'Vanessa', 'Carl', 'Sarah'],\n", " 'Sales': [200, 120, 340, 124, 243, 350]\n", "})\n", "\n", "print(\"Original DataFrame:\")\n", "print(df)\n", "print()\n", "\n", "# 1. Total sales per company\n", "print(\"Total sales per company:\")\n", "print(df.groupby('Company')['Sales'].sum())\n", "print()\n", "\n", "# 2. Maximum sales per company\n", "print(\"Maximum sales per company:\")\n", "print(df.groupby('Company')['Sales'].max())\n", "print()\n", "\n", "# 3. Count of salespeople per company\n", "print(\"Number of salespeople per company:\")\n", "print(df.groupby('Company')['Person'].count())\n" ] }, { "cell_type": "markdown", "id": "9d481d30", "metadata": {}, "source": [ "## Applying Functions: `map()` and `apply()`\n", "\n", "Two of the most frequently used pandas tools for transforming data are `map()` and `apply()`. Both let you run a function on your data without writing an explicit loop, but they work at different levels:\n", "\n", "| Method | Works on | Applies function to | Typical use |\n", "|--------|----------|---------------------|-------------|\n", "| `Series.map()` | Series (one column) | Each **element** | Encode/translate values, simple element-wise transforms |\n", "| `DataFrame.apply()` | DataFrame or Series | Each **column** (default) or each **row** | Column-level calculations, combining columns |\n", "\n", "### `Series.map()`\n", "\n", "`map()` transforms every element in a Series. You can pass:\n", "- a **function** (or lambda) \n", "- a **dictionary** that maps old values to new values\n", "- another Series to look values up in\n" ] }, { "cell_type": "code", "execution_count": 55, "id": "afcffcf3", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
CompanyPersonSalesCompany_Full
0GOOGSam200Google
1GOOGCharlie120Google
2MSFTAmy340Microsoft
3MSFTVanessa124Microsoft
4FBCarl243Meta
5FBSarah350Meta
\n", "
" ], "text/plain": [ " Company Person Sales Company_Full\n", "0 GOOG Sam 200 Google\n", "1 GOOG Charlie 120 Google\n", "2 MSFT Amy 340 Microsoft\n", "3 MSFT Vanessa 124 Microsoft\n", "4 FB Carl 243 Meta\n", "5 FB Sarah 350 Meta" ] }, "execution_count": 55, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Rebuild the company-sales DataFrame used in the GroupBy section\n", "df = pd.DataFrame({\n", " 'Company': ['GOOG', 'GOOG', 'MSFT', 'MSFT', 'FB', 'FB'],\n", " 'Person': ['Sam', 'Charlie', 'Amy', 'Vanessa', 'Carl', 'Sarah'],\n", " 'Sales': [200, 120, 340, 124, 243, 350]\n", "})\n", "\n", "# --- map() with a dictionary ---\n", "# Translate abbreviated company names to full names\n", "name_map = {'GOOG': 'Google', 'MSFT': 'Microsoft', 'FB': 'Meta'}\n", "df['Company_Full'] = df['Company'].map(name_map)\n", "df\n" ] }, { "cell_type": "code", "execution_count": 56, "id": "32d48c83", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
CompanyPersonSalesCompany_FullSales_Tier
0GOOGSam200GoogleHigh
1GOOGCharlie120GoogleLow
2MSFTAmy340MicrosoftHigh
3MSFTVanessa124MicrosoftLow
4FBCarl243MetaHigh
5FBSarah350MetaHigh
\n", "
" ], "text/plain": [ " Company Person Sales Company_Full Sales_Tier\n", "0 GOOG Sam 200 Google High\n", "1 GOOG Charlie 120 Google Low\n", "2 MSFT Amy 340 Microsoft High\n", "3 MSFT Vanessa 124 Microsoft Low\n", "4 FB Carl 243 Meta High\n", "5 FB Sarah 350 Meta High" ] }, "execution_count": 56, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# --- map() with a lambda ---\n", "# Categorize sales as 'High' (>= 200) or 'Low' (< 200) using a lambda\n", "df['Sales_Tier'] = df['Sales'].map(lambda x: 'High' if x >= 200 else 'Low')\n", "df\n" ] }, { "cell_type": "markdown", "id": "0e67ceb3", "metadata": {}, "source": [ "### `df.apply()`\n", "\n", "`apply()` passes each **column** (or row when `axis=1`) to a function and collects the results. This makes it easy to run custom calculations that span multiple cells without writing a loop.\n", "\n", "```python\n", "df.apply(func) # applies func to each column (returns a Series per column)\n", "df.apply(func, axis=1) # applies func to each row\n", "```\n" ] }, { "cell_type": "code", "execution_count": 57, "id": "6bf3ff47", "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " Revenue Cost Units\n", "Ord-001 1200 800 3\n", "Ord-002 450 310 2\n", "Ord-003 3200 2100 8\n", "Ord-004 870 590 3\n", "Ord-005 2100 1400 5\n", "Ord-006 640 420 2\n", "\n", " Revenue Cost Units Profit/Unit\n", "Ord-001 1200 800 3 133.33\n", "Ord-002 450 310 2 70.00\n", "Ord-003 3200 2100 8 137.50\n", "Ord-004 870 590 3 93.33\n", "Ord-005 2100 1400 5 140.00\n", "Ord-006 640 420 2 110.00\n" ] } ], "source": [ "# Retail orders: Revenue, Cost, and Units sold per transaction\n", "orders = pd.DataFrame({\n", " 'Revenue': [1200, 450, 3200, 870, 2100, 640],\n", " 'Cost': [ 800, 310, 2100, 590, 1400, 420],\n", " 'Units': [ 3, 2, 8, 3, 5, 2]\n", "}, index=['Ord-001', 'Ord-002', 'Ord-003', 'Ord-004', 'Ord-005', 'Ord-006'])\n", "\n", "# apply() column-wise (axis=0, default): spread (max − min) of each metric\n", "print(f\"{orders}\\n\")\n", "\n", "orders['Profit/Unit'] = orders.apply(lambda row: round((row[\"Revenue\"] - row[\"Cost\"]) / row[\"Units\"], 2), axis=1)\n", "print(orders)" ] }, { "cell_type": "code", "execution_count": 58, "id": "27aaf99e", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
RevenueCostUnitsProfit/UnitProfitTier
Ord-00112008003133.33400.0Medium
Ord-002450310270.00140.0Small
Ord-003320021008137.501100.0Large
Ord-004870590393.33280.0Small
Ord-005210014005140.00700.0Large
Ord-0066404202110.00220.0Small
\n", "
" ], "text/plain": [ " Revenue Cost Units Profit/Unit Profit Tier\n", "Ord-001 1200 800 3 133.33 400.0 Medium\n", "Ord-002 450 310 2 70.00 140.0 Small\n", "Ord-003 3200 2100 8 137.50 1100.0 Large\n", "Ord-004 870 590 3 93.33 280.0 Small\n", "Ord-005 2100 1400 5 140.00 700.0 Large\n", "Ord-006 640 420 2 110.00 220.0 Small" ] }, "execution_count": 58, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# apply() row-wise (axis=1): calculate profit per order\n", "orders['Profit'] = orders.apply(lambda row: row['Revenue'] - row['Cost'], axis=1)\n", "\n", "# apply() with a named function — classify order size by revenue\n", "def order_tier(row):\n", " if row['Revenue'] >= 2000:\n", " return 'Large'\n", " elif row['Revenue'] >= 1000:\n", " return 'Medium'\n", " else:\n", " return 'Small'\n", "\n", "orders['Tier'] = orders.apply(order_tier, axis=1)\n", "orders" ] }, { "cell_type": "markdown", "id": "997cde82", "metadata": {}, "source": [ "#### `apply()` with `groupby()`\n", "\n", "`apply()` is especially powerful when combined with `groupby()`. It lets you run **any custom function** on each group — not just the built-in aggregations like `sum()` or `mean()`.\n" ] }, { "cell_type": "code", "execution_count": 59, "id": "bc22b6c9", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
CompanyPersonSalesSales_Normalized
0GOOGSam2000.707107
1GOOGCharlie120-0.707107
2MSFTAmy3400.707107
3MSFTVanessa124-0.707107
4FBCarl243-0.707107
5FBSarah3500.707107
\n", "
" ], "text/plain": [ " Company Person Sales Sales_Normalized\n", "0 GOOG Sam 200 0.707107\n", "1 GOOG Charlie 120 -0.707107\n", "2 MSFT Amy 340 0.707107\n", "3 MSFT Vanessa 124 -0.707107\n", "4 FB Carl 243 -0.707107\n", "5 FB Sarah 350 0.707107" ] }, "execution_count": 59, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Rebuild the sales DataFrame (Company_Full and Sales_Tier columns not needed here)\n", "df_sales = pd.DataFrame({\n", " 'Company': ['GOOG', 'GOOG', 'MSFT', 'MSFT', 'FB', 'FB'],\n", " 'Person': ['Sam', 'Charlie', 'Amy', 'Vanessa', 'Carl', 'Sarah'],\n", " 'Sales': [200, 120, 340, 124, 243, 350]\n", "})\n", "\n", "# Custom function: normalize Sales within each company group\n", "# (each value minus group mean, divided by group std)\n", "def normalize(group):\n", " return (group - group.mean()) / group.std()\n", "\n", "df_sales['Sales_Normalized'] = df_sales.groupby('Company')['Sales'].apply(normalize).reset_index(level=0, drop=True)\n", "df_sales\n" ] }, { "cell_type": "code", "execution_count": 60, "id": "29abe511", "metadata": { "tags": [] }, "outputs": [], "source": [ "### EXERCISE: map() and apply()\n", "#\n", "# Given this employee DataFrame:\n", "emp = pd.DataFrame({\n", " 'Name': ['Alice', 'Bob', 'Carol', 'Dave'],\n", " 'Department': ['Eng', 'HR', 'Eng', 'HR'],\n", " 'Salary': [95000, 62000, 105000, 58000]\n", "})\n", "#\n", "# Tasks:\n", "# 1. Use map() with a dictionary to add a 'Dept_Full' column that expands\n", "# 'Eng' → 'Engineering' and 'HR' → 'Human Resources'\n", "# 2. Use apply() (column-wise, axis=0) to print the max salary in each numeric column\n", "# 3. Use apply() (row-wise, axis=1) to add a 'Bonus' column:\n", "# 10 % of Salary for Engineering employees, 8 % for all others\n", "#\n", "### Your code starts here:\n", "\n", "\n", "\n", "\n", "### Your code ends here.\n" ] }, { "cell_type": "code", "execution_count": 61, "id": "db816d5e", "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "1. Dept_Full column added via map():\n", " Name Department Salary Dept_Full\n", "0 Alice Eng 95000 Engineering\n", "1 Bob HR 62000 Human Resources\n", "2 Carol Eng 105000 Engineering\n", "3 Dave HR 58000 Human Resources\n", "\n", "2. Column max via apply():\n", "Salary 105000\n", "dtype: int64\n", "\n", "3. Bonus column added via apply():\n", " Name Department Salary Dept_Full Bonus\n", "0 Alice Eng 95000 Engineering 9500.0\n", "1 Bob HR 62000 Human Resources 4960.0\n", "2 Carol Eng 105000 Engineering 10500.0\n", "3 Dave HR 58000 Human Resources 4640.0\n" ] } ], "source": [ "# Solution\n", "\n", "emp = pd.DataFrame({\n", " 'Name': ['Alice', 'Bob', 'Carol', 'Dave'],\n", " 'Department': ['Eng', 'HR', 'Eng', 'HR'],\n", " 'Salary': [95000, 62000, 105000, 58000]\n", "})\n", "\n", "# 1. map() with a dictionary\n", "dept_map = {'Eng': 'Engineering', 'HR': 'Human Resources'}\n", "emp['Dept_Full'] = emp['Department'].map(dept_map)\n", "print(\"1. Dept_Full column added via map():\")\n", "print(emp)\n", "print()\n", "\n", "# 2. apply() column-wise — max of each numeric column\n", "print(\"2. Column max via apply():\")\n", "print(emp[['Salary']].apply(lambda col: col.max()))\n", "print()\n", "\n", "# 3. apply() row-wise — compute bonus\n", "def calc_bonus(row):\n", " rate = 0.10 if row['Department'] == 'Eng' else 0.08\n", " return row['Salary'] * rate\n", "\n", "emp['Bonus'] = emp.apply(calc_bonus, axis=1)\n", "print(\"3. Bonus column added via apply():\")\n", "print(emp)\n" ] }, { "cell_type": "markdown", "id": "7360b2bc", "metadata": {}, "source": [ "## DataFrame Operations\n", "\n", "Raw data is rarely in the perfect form for analysis. Real-world datasets often need cleaning, organizing, and restructuring before meaningful insights can be extracted. This section explores essential **DataFrame manipulation operations** that help transform messy data into analysis-ready formats.\n", "\n", "We'll cover three fundamental operations that every data analyst should master:\n", "- **Sorting**: Arranging data in meaningful order to reveal patterns and facilitate analysis\n", "- **Handling Duplicates**: Identifying and managing redundant records that can skew results\n", "- **Renaming**: Creating clear, descriptive labels that improve code readability and collaboration\n", "\n", "These operations form the foundation of data preprocessing and are crucial steps in any data analysis workflow." ] }, { "cell_type": "markdown", "id": "6051c9c4", "metadata": {}, "source": [ "### Sorting" ] }, { "cell_type": "code", "execution_count": 62, "id": "0e780bf4", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
WXYZ
2025-09-010.496714-0.1382640.6476891.523030
2025-09-02-0.234153-0.2341371.5792130.767435
2025-09-03-0.4694740.542560-0.463418-0.465730
2025-09-040.241962-1.913280-1.724918-0.562288
2025-09-05-1.0128310.314247-0.908024-1.412304
\n", "
" ], "text/plain": [ " W X Y Z\n", "2025-09-01 0.496714 -0.138264 0.647689 1.523030\n", "2025-09-02 -0.234153 -0.234137 1.579213 0.767435\n", "2025-09-03 -0.469474 0.542560 -0.463418 -0.465730\n", "2025-09-04 0.241962 -1.913280 -1.724918 -0.562288\n", "2025-09-05 -1.012831 0.314247 -0.908024 -1.412304" ] }, "execution_count": 62, "metadata": {}, "output_type": "execute_result" } ], "source": [ "### Recreate DataFrame with random data for sorting examples\n", "np.random.seed(42)\n", "dates = pd.date_range('20250901', periods=5)\n", "df_sort = pd.DataFrame(np.random.randn(5, 4), index=dates, columns=list('WXYZ'))\n", "df_sort" ] }, { "cell_type": "code", "execution_count": 63, "id": "6b660203", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
WXYZ
2025-09-05-1.0128310.314247-0.908024-1.412304
2025-09-03-0.4694740.542560-0.463418-0.465730
2025-09-02-0.234153-0.2341371.5792130.767435
2025-09-040.241962-1.913280-1.724918-0.562288
2025-09-010.496714-0.1382640.6476891.523030
\n", "
" ], "text/plain": [ " W X Y Z\n", "2025-09-05 -1.012831 0.314247 -0.908024 -1.412304\n", "2025-09-03 -0.469474 0.542560 -0.463418 -0.465730\n", "2025-09-02 -0.234153 -0.234137 1.579213 0.767435\n", "2025-09-04 0.241962 -1.913280 -1.724918 -0.562288\n", "2025-09-01 0.496714 -0.138264 0.647689 1.523030" ] }, "execution_count": 63, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Sort by a single column (ascending)\n", "df_sort.sort_values(by='W')" ] }, { "cell_type": "code", "execution_count": 64, "id": "8a81ac10", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
WXYZ
2025-09-05-1.0128310.314247-0.908024-1.412304
2025-09-03-0.4694740.542560-0.463418-0.465730
2025-09-02-0.234153-0.2341371.5792130.767435
2025-09-040.241962-1.913280-1.724918-0.562288
2025-09-010.496714-0.1382640.6476891.523030
\n", "
" ], "text/plain": [ " W X Y Z\n", "2025-09-05 -1.012831 0.314247 -0.908024 -1.412304\n", "2025-09-03 -0.469474 0.542560 -0.463418 -0.465730\n", "2025-09-02 -0.234153 -0.234137 1.579213 0.767435\n", "2025-09-04 0.241962 -1.913280 -1.724918 -0.562288\n", "2025-09-01 0.496714 -0.138264 0.647689 1.523030" ] }, "execution_count": 64, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Sort by multiple columns\n", "df_sort.sort_values(by=['W', 'Z'])" ] }, { "cell_type": "code", "execution_count": 65, "id": "056c9090", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
WXYZ
2025-09-010.496714-0.1382640.6476891.523030
2025-09-02-0.234153-0.2341371.5792130.767435
2025-09-03-0.4694740.542560-0.463418-0.465730
2025-09-040.241962-1.913280-1.724918-0.562288
2025-09-05-1.0128310.314247-0.908024-1.412304
\n", "
" ], "text/plain": [ " W X Y Z\n", "2025-09-01 0.496714 -0.138264 0.647689 1.523030\n", "2025-09-02 -0.234153 -0.234137 1.579213 0.767435\n", "2025-09-03 -0.469474 0.542560 -0.463418 -0.465730\n", "2025-09-04 0.241962 -1.913280 -1.724918 -0.562288\n", "2025-09-05 -1.012831 0.314247 -0.908024 -1.412304" ] }, "execution_count": 65, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Sort by index labels\n", "df_sort.sort_index()" ] }, { "cell_type": "markdown", "id": "a7cd34c4", "metadata": {}, "source": [ "#### `inplace=True`\n", "\n", "By default, `sort_values()` and `sort_index()` **return a new DataFrame** and do **not** modify the original. There are two ways to make the sort permanent:\n", "\n", "**Option 1 — Reassignment (recommended):**\n", "```python\n", "df_sort = df_sort.sort_values(by='W')\n", "```\n", "\n", "**Option 2 — `inplace=True`:**\n", "```python\n", "df_sort.sort_values(by='W', inplace=True)\n", "```\n", "\n", "Both approaches produce the same result. Reassignment is generally preferred in modern pandas because it is more explicit and avoids subtle bugs in chained operations." ] }, { "cell_type": "code", "execution_count": 66, "id": "ca0c1040", "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Before sort:\n", " W X Y Z\n", "2025-09-01 0.496714 -0.138264 0.647689 1.523030\n", "2025-09-02 -0.234153 -0.234137 1.579213 0.767435\n", "2025-09-03 -0.469474 0.542560 -0.463418 -0.465730\n", "2025-09-04 0.241962 -1.913280 -1.724918 -0.562288\n", "2025-09-05 -1.012831 0.314247 -0.908024 -1.412304\n", "\n", "After sort_values (original df_sort unchanged):\n", " W X Y Z\n", "2025-09-01 0.496714 -0.138264 0.647689 1.523030\n", "2025-09-02 -0.234153 -0.234137 1.579213 0.767435\n", "2025-09-03 -0.469474 0.542560 -0.463418 -0.465730\n", "2025-09-04 0.241962 -1.913280 -1.724918 -0.562288\n", "2025-09-05 -1.012831 0.314247 -0.908024 -1.412304\n", "\n", "Option 1 — reassignment (df_sort_v1):\n", " W X Y Z\n", "2025-09-05 -1.012831 0.314247 -0.908024 -1.412304\n", "2025-09-03 -0.469474 0.542560 -0.463418 -0.465730\n", "2025-09-02 -0.234153 -0.234137 1.579213 0.767435\n", "2025-09-04 0.241962 -1.913280 -1.724918 -0.562288\n", "2025-09-01 0.496714 -0.138264 0.647689 1.523030\n", "\n", "Option 2 — inplace=True (df_sort_v2):\n", " W X Y Z\n", "2025-09-05 -1.012831 0.314247 -0.908024 -1.412304\n", "2025-09-03 -0.469474 0.542560 -0.463418 -0.465730\n", "2025-09-02 -0.234153 -0.234137 1.579213 0.767435\n", "2025-09-04 0.241962 -1.913280 -1.724918 -0.562288\n", "2025-09-01 0.496714 -0.138264 0.647689 1.523030\n" ] } ], "source": [ "# Show that sort_values does NOT change the original by default\n", "print(\"Before sort:\")\n", "print(df_sort)\n", "\n", "result = df_sort.sort_values(by='W') # returns a new DataFrame\n", "print(\"\\nAfter sort_values (original df_sort unchanged):\")\n", "print(df_sort)\n", "\n", "# Option 1: reassignment\n", "df_sort_v1 = df_sort.sort_values(by='W')\n", "print(\"\\nOption 1 — reassignment (df_sort_v1):\")\n", "print(df_sort_v1)\n", "\n", "# Option 2: inplace=True\n", "df_sort_v2 = df_sort.copy() # copy so we preserve df_sort for the rest of the notebook\n", "df_sort_v2.sort_values(by='W', inplace=True)\n", "print(\"\\nOption 2 — inplace=True (df_sort_v2):\")\n", "print(df_sort_v2)" ] }, { "cell_type": "code", "execution_count": 67, "id": "3dd7cefb", "metadata": { "tags": [ "thebe-interactive" ] }, "outputs": [], "source": [ "### EXERCISE: Sorting DataFrames\n", "import pandas as pd\n", "# Create a DataFrame:\n", "sort_df = pd.DataFrame({\n", " 'Name': ['Alice', 'Bob', 'Charlie', 'Diana'],\n", " 'Age': [25, 30, 22, 28],\n", " 'Score': [85, 92, 78, 92]\n", "})\n", "#\n", "# Tasks:\n", "# 1. Sort by Age in ascending order\n", "# 2. Sort by Score in descending order\n", "# 3. Sort by Score (descending), then by Age (ascending)\n", "# 4. Sort by index\n", "#\n", "### Your code starts here:\n", "\n", "\n", "\n", "\n", "\n", "### Your code ends here.\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [ "hide-input" ] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " Name Age Score\n", "2 Charlie 22 78\n", "0 Alice 25 85\n", "3 Diana 28 92\n", "1 Bob 30 92\n", " Name Age Score\n", "1 Bob 30 92\n", "3 Diana 28 92\n", "0 Alice 25 85\n", "2 Charlie 22 78\n", " Name Age Score\n", "3 Diana 28 92\n", "1 Bob 30 92\n", "0 Alice 25 85\n", "2 Charlie 22 78\n", " Name Age Score\n", "0 Alice 25 85\n", "1 Bob 30 92\n", "2 Charlie 22 78\n", "3 Diana 28 92\n" ] } ], "source": [ "### Solution\n", "\n", "sort_df = pd.DataFrame({\n", " 'Name': ['Alice', 'Bob', 'Charlie', 'Diana'],\n", " 'Age': [25, 30, 22, 28],\n", " 'Score': [85, 92, 78, 92]\n", "})\n", "\n", "## 1. Sort by Age in ascending order\n", "print(\"1. Sort by Age in ascending order:\")\n", "print(sort_df.sort_values(by='Age'))\n", "# 2. Sort by Score in descending order\n", "print(\"\\n2. Sort by Score in descending order:\")\n", "print(sort_df.sort_values(by='Score', ascending=False))\n", "# 3. Sort by Score (descending), then by Age (ascending)\n", "print(\"\\n3. Sort by Score (descending), then by Age (ascending):\")\n", "print(sort_df.sort_values(by=['Score', 'Age'], ascending=[False, True])) \n", "# 4. Sort by index\n", "print(\"\\n4. Sort by index:\", sort_df.sort_index())\n" ] }, { "cell_type": "markdown", "id": "a883c84d", "metadata": {}, "source": [ "### Handling Duplicates\n", "\n", "DataFrames often contain duplicate rows. Pandas provides methods to identify and remove them." ] }, { "cell_type": "code", "execution_count": 69, "id": "8f403149", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
ABC
0110x
1220y
2220y
3330z
4330w
5440v
\n", "
" ], "text/plain": [ " A B C\n", "0 1 10 x\n", "1 2 20 y\n", "2 2 20 y\n", "3 3 30 z\n", "4 3 30 w\n", "5 4 40 v" ] }, "execution_count": 69, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Create a DataFrame with duplicate rows for demonstration\n", "df_dup = pd.DataFrame({\n", " 'A': [1, 2, 2, 3, 3, 4],\n", " 'B': [10, 20, 20, 30, 30, 40],\n", " 'C': ['x', 'y', 'y', 'z', 'w', 'v']\n", "})\n", "df_dup" ] }, { "cell_type": "code", "execution_count": 70, "id": "920b90f6", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/plain": [ "0 False\n", "1 False\n", "2 True\n", "3 False\n", "4 False\n", "5 False\n", "dtype: bool" ] }, "execution_count": 70, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Check for duplicate rows (returns boolean Series)\n", "df_dup.duplicated()" ] }, { "cell_type": "code", "execution_count": 71, "id": "5d8e4305", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
ABC
0110x
1220y
3330z
5440v
\n", "
" ], "text/plain": [ " A B C\n", "0 1 10 x\n", "1 2 20 y\n", "3 3 30 z\n", "5 4 40 v" ] }, "execution_count": 71, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Drop duplicates based on specific columns\n", "df_dup.drop_duplicates(subset=['A'])" ] }, { "cell_type": "code", "execution_count": 72, "id": "4581d0e2", "metadata": { "tags": [ "thebe-interactive" ] }, "outputs": [], "source": [ "### EXERCISE: Working with Duplicates\n", "#\n", "# Create a DataFrame:\n", "dup_df = pd.DataFrame({\n", " 'A': [1, 2, 2, 3, 3, 4],\n", " 'B': [10, 20, 20, 30, 30, 40],\n", " 'C': ['x', 'y', 'y', 'z', 'w', 'v']\n", "})\n", "#\n", "# Tasks:\n", "# 1. Display the DataFrame\n", "# 2. Check for duplicates (use .duplicated())\n", "# 3. Remove all duplicates (use .drop_duplicates())\n", "# 4. Remove duplicates based on column A only\n", "#\n", "### Your code starts here:\n", "\n", "\n", "\n", "\n", "\n", "### Your code ends here.\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [ "hide-input" ] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " A B C\n", "0 1 10 x\n", "1 2 20 y\n", "2 2 20 y\n", "3 3 30 z\n", "4 3 30 w\n", "5 4 40 v\n", "0 False\n", "1 False\n", "2 True\n", "3 False\n", "4 False\n", "5 False\n", "dtype: bool\n", " A B C\n", "0 1 10 x\n", "1 2 20 y\n", "3 3 30 z\n", "4 3 30 w\n", "5 4 40 v\n", " A B C\n", "0 1 10 x\n", "1 2 20 y\n", "3 3 30 z\n", "5 4 40 v\n" ] } ], "source": [ "### Solution\n", "\n", "dup_df = pd.DataFrame({\n", " 'A': [1, 2, 2, 3, 3, 4],\n", " 'B': [10, 20, 20, 30, 30, 40],\n", " 'C': ['x', 'y', 'y', 'z', 'w', 'v']\n", "})\n", "\n", "# 1. Display the DataFrame\n", "print(\"Original DataFrame:\")\n", "print(dup_df)\n", "print()\n", "\n", "# 2. Check for duplicates\n", "print(\"Duplicate rows:\")\n", "print(dup_df.duplicated())\n", "print()\n", "\n", "# 3. Remove all duplicates\n", "print(\"After removing all duplicates:\")\n", "print(dup_df.drop_duplicates())\n", "print()\n", "\n", "# 4. Remove duplicates based on column A\n", "print(\"After removing duplicates in column A:\")\n", "print(dup_df.drop_duplicates(subset=['A']))\n" ] }, { "cell_type": "markdown", "id": "7451dc6b", "metadata": {}, "source": [ "### Renaming Columns and Index\n", "\n", "Pandas provides the `.rename()` method to rename columns and index labels. This is useful when you need more descriptive names or want to standardize naming conventions." ] }, { "cell_type": "markdown", "id": "2266652c", "metadata": {}, "source": [ "Let's start with our sample DataFrame:" ] }, { "cell_type": "code", "execution_count": 74, "id": "9534c910", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
WXYZ
2025-09-010.496714-0.1382640.6476891.523030
2025-09-02-0.234153-0.2341371.5792130.767435
2025-09-03-0.4694740.542560-0.463418-0.465730
2025-09-040.241962-1.913280-1.724918-0.562288
2025-09-05-1.0128310.314247-0.908024-1.412304
\n", "
" ], "text/plain": [ " W X Y Z\n", "2025-09-01 0.496714 -0.138264 0.647689 1.523030\n", "2025-09-02 -0.234153 -0.234137 1.579213 0.767435\n", "2025-09-03 -0.469474 0.542560 -0.463418 -0.465730\n", "2025-09-04 0.241962 -1.913280 -1.724918 -0.562288\n", "2025-09-05 -1.012831 0.314247 -0.908024 -1.412304" ] }, "execution_count": 74, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_sort" ] }, { "cell_type": "code", "execution_count": 75, "id": "8a50040e", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
WeightX_coordYZ
2025-09-010.496714-0.1382640.6476891.523030
2025-09-02-0.234153-0.2341371.5792130.767435
2025-09-03-0.4694740.542560-0.463418-0.465730
2025-09-040.241962-1.913280-1.724918-0.562288
2025-09-05-1.0128310.314247-0.908024-1.412304
\n", "
" ], "text/plain": [ " Weight X_coord Y Z\n", "2025-09-01 0.496714 -0.138264 0.647689 1.523030\n", "2025-09-02 -0.234153 -0.234137 1.579213 0.767435\n", "2025-09-03 -0.469474 0.542560 -0.463418 -0.465730\n", "2025-09-04 0.241962 -1.913280 -1.724918 -0.562288\n", "2025-09-05 -1.012831 0.314247 -0.908024 -1.412304" ] }, "execution_count": 75, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Rename columns\n", "df_sort.rename(columns={'W': 'Weight', 'X': 'X_coord'})" ] }, { "cell_type": "markdown", "id": "7b0c47cb", "metadata": {}, "source": [ "#### `inplace=True` with `rename()`\n", "\n", "Like `sort_values()`, `rename()` also **returns a new DataFrame** by default — the original is not modified. Apply the same two options:\n", "\n", "**Option 1 — Reassignment (recommended):**\n", "```python\n", "df_sort = df_sort.rename(columns={'W': 'Weight', 'X': 'X_coord'})\n", "```\n", "\n", "**Option 2 — `inplace=True`:**\n", "```python\n", "df_sort.rename(columns={'W': 'Weight', 'X': 'X_coord'}, inplace=True)\n", "```" ] }, { "cell_type": "code", "execution_count": 76, "id": "c554fa1b", "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Before rename:\n", " W X Y Z\n", "2025-09-01 0.496714 -0.138264 0.647689 1.523030\n", "2025-09-02 -0.234153 -0.234137 1.579213 0.767435\n", "2025-09-03 -0.469474 0.542560 -0.463418 -0.465730\n", "2025-09-04 0.241962 -1.913280 -1.724918 -0.562288\n", "2025-09-05 -1.012831 0.314247 -0.908024 -1.412304\n", "\n", "After rename() without assignment (df_sort unchanged):\n", " W X Y Z\n", "2025-09-01 0.496714 -0.138264 0.647689 1.523030\n", "2025-09-02 -0.234153 -0.234137 1.579213 0.767435\n", "2025-09-03 -0.469474 0.542560 -0.463418 -0.465730\n", "2025-09-04 0.241962 -1.913280 -1.724918 -0.562288\n", "2025-09-05 -1.012831 0.314247 -0.908024 -1.412304\n", "\n", "Option 1 — reassignment (df_renamed):\n", " Weight X_coord Y Z\n", "2025-09-01 0.496714 -0.138264 0.647689 1.523030\n", "2025-09-02 -0.234153 -0.234137 1.579213 0.767435\n", "2025-09-03 -0.469474 0.542560 -0.463418 -0.465730\n", "2025-09-04 0.241962 -1.913280 -1.724918 -0.562288\n", "2025-09-05 -1.012831 0.314247 -0.908024 -1.412304\n", "\n", "Option 2 — inplace=True (df_inplace):\n", " Weight X_coord Y Z\n", "2025-09-01 0.496714 -0.138264 0.647689 1.523030\n", "2025-09-02 -0.234153 -0.234137 1.579213 0.767435\n", "2025-09-03 -0.469474 0.542560 -0.463418 -0.465730\n", "2025-09-04 0.241962 -1.913280 -1.724918 -0.562288\n", "2025-09-05 -1.012831 0.314247 -0.908024 -1.412304\n" ] } ], "source": [ "# rename() does NOT change df_sort by default\n", "print(\"Before rename:\")\n", "print(df_sort)\n", "\n", "df_sort.rename(columns={'W': 'Weight', 'X': 'X_coord'}) # result discarded — df_sort unchanged\n", "print(\"\\nAfter rename() without assignment (df_sort unchanged):\")\n", "print(df_sort)\n", "\n", "# Option 1: reassignment\n", "df_renamed = df_sort.rename(columns={'W': 'Weight', 'X': 'X_coord'})\n", "print(\"\\nOption 1 — reassignment (df_renamed):\")\n", "print(df_renamed)\n", "\n", "# Option 2: inplace=True\n", "df_inplace = df_sort.copy()\n", "df_inplace.rename(columns={'W': 'Weight', 'X': 'X_coord'}, inplace=True)\n", "print(\"\\nOption 2 — inplace=True (df_inplace):\")\n", "print(df_inplace)" ] }, { "cell_type": "code", "execution_count": 77, "id": "fe47ef2b", "metadata": { "tags": [ "thebe-interactive" ] }, "outputs": [], "source": [ "### EXERCISE: Renaming DataFrame Elements\n", "#\n", "# Create a DataFrame:\n", "rename_df = pd.DataFrame({\n", " 'a': [1, 2, 3],\n", " 'b': [4, 5, 6],\n", " 'c': [7, 8, 9]\n", "}, index=['row1', 'row2', 'row3'])\n", "#\n", "# Tasks:\n", "# 1. Display the original DataFrame\n", "# 2. Rename column 'a' to 'Alpha' and 'b' to 'Beta'\n", "# 3. Rename the index 'row1' to 'R1' and 'row2' to 'R2'\n", "# 4. Display the renamed DataFrame\n", "#\n", "### Your code starts here:\n", "\n", "\n", "\n", "\n", "\n", "### Your code ends here.\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [ "hide-input" ] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " a b c\n", "row1 1 4 7\n", "row2 2 5 8\n", "row3 3 6 9\n", " Alpha Beta c\n", "R1 1 4 7\n", "R2 2 5 8\n", "row3 3 6 9\n" ] } ], "source": [ "### Solution\n", "\n", "rename_df = pd.DataFrame({\n", " 'a': [1, 2, 3],\n", " 'b': [4, 5, 6],\n", " 'c': [7, 8, 9]\n", "}, index=['row1', 'row2', 'row3'])\n", "\n", "# 1. Display the original DataFrame\n", "print(\"Original DataFrame:\")\n", "print(rename_df)\n", "print()\n", "\n", "# 2. Rename columns 'a' to 'Alpha' and 'b' to 'Beta'\n", "rename_df = rename_df.rename(columns={'a': 'Alpha', 'b': 'Beta'})\n", "\n", "# 3. Rename index 'row1' to 'R1' and 'row2' to 'R2'\n", "rename_df = rename_df.rename(index={'row1': 'R1', 'row2': 'R2'})\n", "\n", "# 4. Display the renamed DataFrame\n", "print(\"Renamed DataFrame:\")\n", "print(rename_df)" ] }, { "cell_type": "markdown", "id": "3548b9db", "metadata": {}, "source": [ "## SQL and Pandas Side by Side\n", "\n", "Many business questions that you would ask a database in SQL have a direct pandas equivalent. If you already know SQL, use this sheet to translate; if you do not, use it as a map of what this chapter covered. Every entry in the pandas column was taught in this chapter or in {doc}`DataFrames <0403-pd-dataframe>`; the examples use a DataFrame named `df`.\n", "\n", "```{figure} ../../figures/sql_vs_pandas.svg\n", ":name: sql-vs-pandas\n", ":width: 100%\n", ":alt: Eleven SQL clauses next to the pandas code that does the same job; the same pairs appear in the text reference below.\n", "\n", "SQL clauses and their pandas equivalents. Redrawn and corrected from the supplied reference image.\n", "```\n", "\n", "
\n", "Text reference: all eleven pairs\n", "\n", "| # | SQL | Pandas | Note | Where taught |\n", "|---:|---|---|---|---|\n", "| 1 | `SELECT * FROM sales` | `df` | `SELECT a, b` is `df[['a', 'b']]` | {doc}`DataFrames <0403-pd-dataframe>`, Selecting Columns |\n", "| 2 | `WHERE price > 100` | `df[df['price'] > 100]` | or `df.query('price > 100')` | {doc}`DataFrames <0403-pd-dataframe>`, Boolean Indexing |\n", "| 3 | `ORDER BY price DESC` | `df.sort_values('price', ascending=False)` | `ascending=True` is the default | this page, Sorting |\n", "| 4 | `LIMIT 5` | `df.head(5)` | first rows only | {doc}`Handling Datasets <0402-pd-handle-datasets>` |\n", "| 5 | `SELECT DISTINCT region` | `df['region'].drop_duplicates()` | or `df['region'].unique()` | this page, Handling Duplicates |\n", "| 6 | `COUNT(*)` / `COUNT(email)` | `len(df)` / `df['email'].count()` | `count()` skips missing values | this page, Simple Aggregations |\n", "| 7 | `GROUP BY region` | `df.groupby('region')['price'].sum()` | split, apply, combine | this page, GroupBy Operations |\n", "| 8 | `HAVING SUM(price) > 1000` | `totals[totals > 1000]` | filter the result of `groupby()`, where `totals` is the row 7 result | this page, GroupBy Operations |\n", "| 9 | `JOIN customers ON id` | `df.merge(customers, on='id')` | `how='inner'` (default), `'left'`, `'outer'` | this page, Merging |\n", "| 10 | `UNION ALL` | `pd.concat([df1, df2])` | `UNION` also drops duplicates: add `.drop_duplicates()` | this page, `.concat()` |\n", "| 11 | `CASE WHEN price > 100 THEN 'high' ELSE 'low' END` | `np.where(df['price'] > 100, 'high', 'low')` | two outcomes; `np.select()` for more | {doc}`Array Computation <../03-numpy/0302-np-ufuncs>`, `np.where()` |\n", "\n", "
" ] }, { "cell_type": "code", "execution_count": 79, "id": "0e560bad", "metadata": { "tags": [] }, "outputs": [], "source": [ "# Flexible display_side_by_side function that handles both calling patterns\n", "from IPython.display import HTML\n", "import pandas as pd\n", "\n", "def display_side_by_side(*args, names=None):\n", " \"\"\"\n", " Display DataFrames side by side using HTML formatting.\n", " Handles both calling patterns:\n", " 1. display_side_by_side(df1, df2, names=[\"Name1\", \"Name2\"])\n", " 2. display_side_by_side(df1, df2, \"Name1\", \"Name2\")\n", " \"\"\"\n", " # Separate DataFrames from potential string names\n", " dfs = []\n", " string_names = []\n", " \n", " for arg in args:\n", " if isinstance(arg, (pd.DataFrame, pd.Series)):\n", " dfs.append(arg)\n", " elif isinstance(arg, str):\n", " string_names.append(arg)\n", " \n", " # Use provided names or string arguments or generate defaults\n", " if names is not None:\n", " final_names = names\n", " elif string_names:\n", " final_names = string_names\n", " else:\n", " final_names = [f'DataFrame {i+1}' for i in range(len(dfs))]\n", " \n", " # Ensure we have enough names\n", " while len(final_names) < len(dfs):\n", " final_names.append(f'DataFrame {len(final_names)+1}')\n", " \n", " html_str = '
'\n", " for df, name in zip(dfs, final_names):\n", " html_str += f'''\n", "
\n", "

{name}

\n", " {df.to_html()}\n", "
\n", " '''\n", " html_str += '
'\n", " return HTML(html_str)" ] } ], "metadata": { "kernelspec": { "display_name": ".venv (3.13.13.final.0)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.13.13" } }, "nbformat": 4, "nbformat_minor": 5 }