{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# DataFrames" ] }, { "cell_type": "code", "execution_count": 5, "metadata": { "tags": [ "hide-input", "thebe-init", "runnable" ] }, "outputs": [], "source": [ "import numpy as np\n", "import pandas as pd" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```{contents}\n", ":local:\n", ":depth: 2\n", "```\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Pandas DataFrames are the core data structure in Pandas, modeled after similar structures in the **R** programming language. A DataFrame is a **two-dimensional, labeled data structure** that can be thought of as:\n", "\n", "- A **collection** of **Series** objects combined into a single table, all sharing the same index\n", "- A **spreadsheet** or SQL table with rows and columns\n", "- A **dictionary** of **Series**, where each column is a Series with a shared index\n", "\n", "**Key Features of DataFrames**\n", "\n", "- **Labeled axes**: Both rows (index) and columns have labels, making data access intuitive and explicit\n", "- **Heterogeneous data**: Different columns can contain different data types (integers, floats, strings, dates, etc.)\n", "- **Size-mutable**: You can insert and delete columns\n", "- Powerful operations: Built-in methods for filtering, grouping, merging, reshaping, and statistical analysis\n", "- Alignment of data based on labels\n", "- **Missing data** handling (see [0404-missing-data.ipynb](0404-missing-data.ipynb) for details)\n", "- Rich I/O tools for reading/writing various formats (CSV, Excel, SQL, JSON, etc.)\n", "\n", "**Why DataFrames?**\n", "\n", "While NumPy arrays are excellent for numerical computation, DataFrames offer several advantages:\n", "\n", "- **Column names** instead of just numeric indices\n", "- **Mixed data types** in different columns\n", "- Accessing and selecting data\n", "- Modifying DataFrames (adding/removing columns and rows)\n", "- Sorting and filtering\n", "- Working with indices, including MultiIndex\n", "\n", "**What You'll Learn**\n", "\n", "In this notebook, we'll explore:\n", "\n", "1. Creating DataFrames from various sources\n", "2. DataFrame attributes and methods\n", "3. Indexing and selecting data\n", "4. Sorting and filtering\n", "5. Working with indices, including MultiIndex\n", "\n", "**Note:** \n", "- Additional Resources:\n", " - [Official Pandas Documentation](https://pandas.pydata.org/docs/)\n", " - [DataFrame API Reference](https://pandas.pydata.org/docs/reference/frame.html)\n", " - [10 Minutes to Pandas](https://pandas.pydata.org/docs/user_guide/10min.html)\n" ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "### header cell\n", "### load libraries\n", "\n", "import numpy as np\n", "import pandas as pd" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Creating DataFrames\n", "\n", "There are several common ways to create a DataFrame in pandas:\n", "\n", "| Method | Description |\n", "|---|---|\n", "| `pd.read_csv()` | Load data from a CSV file — the most common approach in practice |\n", "| `pd.DataFrame(dict)` | Build a DataFrame from a Python dictionary (keys become column names) |\n", "| `pd.DataFrame(list of lists)` | Build from a 2D list, optionally specifying column names |\n", "| `pd.DataFrame(np array)` | Build from a NumPy array, useful for numeric/randomly generated data |\n", "\n", "In this section we'll cover the two most common: reading from a file and constructing one from scratch." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### `pd.read_csv( )`\n", "\n", "- The most common way to read a file is to use `read_csv()` function." ] }, { "cell_type": "code", "execution_count": 4, "metadata": { "scrolled": true, "tags": [] }, "outputs": [], "source": [ "# Read CSV file - both forward slashes (/) work on Windows, macOS, and Linux\n", "\n", "df_sample = pd.read_csv(\"../../data/data.csv\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### DataFrame Output and Preview\n", "\n", "To preview a dataframe, we usually use the `.head()` and `.tail()` methods. \n", "\n", "There are several ways to display a DataFrame, each with different formatting and truncation behavior:\n", "\n", "| Method | Description |\n", "|--------|-------------|\n", "| `df_sample` | Displays DataFrame in table format with first/last 5 rows by default |\n", "| `print(df_sample)` | Prints text representation showing header(5) and tail(5) rows |\n", "| `df_sample.to_string()` | Converts entire DataFrame to string without truncation |\n", "| `pd.options.display.max_rows` | Controls how many rows are displayed before truncation |" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "To display a DataFrame, simply type its name:" ] }, { "cell_type": "code", "execution_count": 5, "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", "
DurationPulseMaxpulseCalories
060110130409.1
160117145479.0
260103135340.0
345109175282.4
445117148406.0
...............
1603085120250.4
1614590130260.4
1624595130270.0
16345100140280.9
16460105140290.8
\n", "

165 rows × 4 columns

\n", "
" ], "text/plain": [ " Duration Pulse Maxpulse Calories\n", "0 60 110 130 409.1\n", "1 60 117 145 479.0\n", "2 60 103 135 340.0\n", "3 45 109 175 282.4\n", "4 45 117 148 406.0\n", ".. ... ... ... ...\n", "160 30 85 120 250.4\n", "161 45 90 130 260.4\n", "162 45 95 130 270.0\n", "163 45 100 140 280.9\n", "164 60 105 140 290.8\n", "\n", "[165 rows x 4 columns]" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_sample # Default view is table-like\n", " # Pandas will show first 5 and last 5 rows (10 total) by default\n", " # The shape information is displayed at the bottom" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Using `print()` displays the DataFrame in plain text format:" ] }, { "cell_type": "code", "execution_count": 6, "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " Duration Pulse Maxpulse Calories\n", "0 60 110 130 409.1\n", "1 60 117 145 479.0\n", "2 60 103 135 340.0\n", "3 45 109 175 282.4\n", "4 45 117 148 406.0\n", ".. ... ... ... ...\n", "160 30 85 120 250.4\n", "161 45 90 130 260.4\n", "162 45 95 130 270.0\n", "163 45 100 140 280.9\n", "164 60 105 140 290.8\n", "\n", "[165 rows x 4 columns]\n" ] } ], "source": [ "print(df_sample) # print() shows same information but in plain text format " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "**`.to_string()`**\n", "- `.to_string()` converts the entire DataFrame to a string without truncation. \n", "- useful when you need the complete string representation\n", "- the sample below is commented out because it will show the whole 339 rows of data." ] }, { "cell_type": "code", "execution_count": 7, "metadata": { "tags": [] }, "outputs": [], "source": [ "### The to_string() method is used to display the entire \n", "### DataFrame as a string, without truncation.\n", "### commented out to avoid overwhelming output in case of large DataFrame\n", "\n", "# print(df_sample.to_string())" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Pandas has a `display.max_rows` setting that is typically 60 line by default (system-dependent) and that's the max number of lines you can print. When printing more than the max_rows, you get the default 10 (first 5 + last 5 ) rows. You need to change the pd.options.display.max_rows setting if you need to print more lines." ] }, { "cell_type": "code", "execution_count": 8, "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "60 \n", "\n", " Duration Pulse Maxpulse Calories\n", "0 60 110 130 409.1\n", "1 60 117 145 479.0\n", "2 60 103 135 340.0\n", "3 45 109 175 282.4\n", "4 45 117 148 406.0\n", "5 60 102 127 300.5\n", "6 60 110 136 374.0\n", "7 45 104 134 253.3\n", "8 30 109 133 195.1\n", "9 60 98 124 269.0\n", "10 60 103 147 329.3 \n", "\n" ] } ], "source": [ "### uncomment the following line to change the max_rows setting to 5\n", "# pd.options.display.max_rows = 5\n", "print(pd.options.display.max_rows, \"\\n\") ### current max_rows setting (default is 60)\n", "\n", "print(df_sample.head(11), \"\\n\") ### 11 is less than max_rows, will print \n", "# print(df_sample.head(100), \"\\n\") ### 100 is more than max_rows, show first 5 + last 5 rows" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "When working with large datasets, you often want to see just a sample of the data rather than the entire DataFrame." ] }, { "cell_type": "code", "execution_count": 11, "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", "
DurationPulseMaxpulseCalories
060110130409.1
160117145479.0
260103135340.0
345109175282.4
445117148406.0
\n", "
" ], "text/plain": [ " Duration Pulse Maxpulse Calories\n", "0 60 110 130 409.1\n", "1 60 117 145 479.0\n", "2 60 103 135 340.0\n", "3 45 109 175 282.4\n", "4 45 117 148 406.0" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# View first 5 rows (default)\n", "df_sample.head()" ] }, { "cell_type": "code", "execution_count": 12, "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", "
DurationPulseMaxpulseCalories
1624595130270.0
16345100140280.9
16460105140290.8
\n", "
" ], "text/plain": [ " Duration Pulse Maxpulse Calories\n", "162 45 95 130 270.0\n", "163 45 100 140 280.9\n", "164 60 105 140 290.8" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# View last 3 rows\n", "df_sample.tail(3)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "For this exercise, either \n", "- {download}`download baby.csv <../../data/baby.csv>` and place it in your project `data` folder, or \n", "- just use `pd.read_csv(\"../../data/baby.csv\")` in the Live Code cell to access the `baby.csv` dataset." ] }, { "cell_type": "code", "execution_count": 13, "metadata": { "tags": [ "thebe-interactive" ] }, "outputs": [], "source": [ "### EXERCISE: Reading CSV Files\n", "# Using the data/baby.csv file:\n", "# 1. Read the CSV file into a DataFrame called df_baby\n", "# 2. Display the first 3 rows (use head() with appropriate argument)\n", "# 3. Display the last 2 rows (use tail() with appropriate argument)\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": [ " Birth Weight Gestational Days ... Maternal Pregnancy Weight Maternal Smoker\n", "0 120 284 ... 100 False\n", "1 113 282 ... 135 False\n", "2 128 279 ... 115 True\n", "\n", "[3 rows x 6 columns]\n", " Birth Weight ... Maternal Smoker\n", "1172 125 ... False\n", "1173 117 ... False\n", "\n", "[2 rows x 6 columns]\n" ] } ], "source": [ "### Solution\n", "from pathlib import Path\n", "import pandas as pd\n", "\n", "project_root = next(parent for parent in [Path.cwd(), *Path.cwd().parents] if (parent / '_config.yml').exists())\n", "df_baby = pd.read_csv(project_root / 'data' / 'baby.csv')\n", "\n", "print(df_baby.head(3))\n", "print(df_baby.tail(2))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Creating a DataFrame from NumPy Arrays\n", "\n", "So far we've been working with `df_sample`, which was loaded from a CSV file. Now let's create a brand-new DataFrame from scratch using randomly generated data and a **date-based index**.\n", "\n", "We use `pd.date_range()` to generate a sequence of consecutive dates, and `np.random.default_rng(42)` — the modern NumPy random number Generator — to fill the DataFrame with random integers. Passing a seed (`42`) ensures reproducibility so the same values are produced every time the code is run.\n", "\n", "You can also add indices by using the `index=` parameter. " ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "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", "
fruitquantity
0apple1
1banana2
2cherry3
\n", "
" ], "text/plain": [ " fruit quantity\n", "0 apple 1\n", "1 banana 2\n", "2 cherry 3" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "#### creating a DataFrame from numpy array\n", "#### np array + labels (columns) to create a DataFrame\n", "\n", "fruits = np.array([[\"apple\", 1], [\"banana\", 2], [\"cherry\", 3]])\n", "\n", "columns=[\"fruit\", \"quantity\"] ### column labels for the DataFrame\n", "\n", "df_fruits = pd.DataFrame(fruits, columns = columns)\n", "df_fruits" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "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", "
fruitquantity
firstapple1
secondbanana2
thirdcherry3
\n", "
" ], "text/plain": [ " fruit quantity\n", "first apple 1\n", "second banana 2\n", "third cherry 3" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "### indices are 0, 1, 2 by default. You can specify your own indices using the index parameter.\n", "\n", "df_fruits_with_custom_index = pd.DataFrame(fruits, columns = columns, index = [\"first\", \"second\", \"third\"])\n", "\n", "df_fruits_with_custom_index" ] }, { "cell_type": "code", "execution_count": null, "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", "
ABCDEF
2020-01-01187968494887
2020-01-02177228185797
2020-01-03767874805621
2020-01-04855055432693
\n", "
" ], "text/plain": [ " A B C D E F\n", "2020-01-01 18 79 68 49 48 87\n", "2020-01-02 17 72 28 18 57 97\n", "2020-01-03 76 78 74 80 56 21\n", "2020-01-04 85 50 55 43 26 93" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# np.random.seed(42) ### old way to set random seed, still works but will show a warning in newer versions of NumPy\n", "rng = np.random.default_rng(42)\n", "\n", "dates = pd.date_range(\"2020-01-01\", periods=4) ### date_range() generates a sequence of dates starting from \"2020-01-01\" for 4 periods (4 days)\n", "\n", "df_dates = pd.DataFrame(rng.integers(10, 100, size=(4, 6 )), index=dates, columns=list(\"ABCDEF\"))\n", "df_dates\n", "\n", "### note the list(\"ABCDEF\") creates a list of column labels ['A', 'B', 'C', 'D', 'E', 'F'] for the DataFrame" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Creating a DataFrame from a Dictionary\n", "\n", "The most common way to create a DataFrame from scratch is to pass a dictionary\n", "where each **key** becomes a column name and its **value** is a list of column values. You can also pass a custom index to label the rows.\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "### creating a DataFrame from a dictionary\n", "\n", "df = pd.DataFrame({\n", " \"fruit\": [\"apple\", \"banana\", \"cherry\"],\n", " \"price\": [100, 200, 300]\n", "})\n", "\n", "df" ] }, { "cell_type": "code", "execution_count": 16, "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", "
NameAgeCityScore
S1Alice20NYC85
S2Bob22LA92
S3Charlie21Chicago67
S4Diana23Boston78
\n", "
" ], "text/plain": [ " Name Age City Score\n", "S1 Alice 20 NYC 85\n", "S2 Bob 22 LA 92\n", "S3 Charlie 21 Chicago 67\n", "S4 Diana 23 Boston 78" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_students = pd.DataFrame({\n", " 'Name': ['Alice', 'Bob', 'Charlie', 'Diana'],\n", " 'Age': [20, 22, 21, 23],\n", " 'City': ['NYC', 'LA', 'Chicago', 'Boston'],\n", " 'Score': [85, 92, 67, 78]\n", "}, index=['S1', 'S2', 'S3', 'S4'])\n", "\n", "df_students" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "You can add a new column using a **boolean condition**." ] }, { "cell_type": "code", "execution_count": 17, "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", "
NameAgeCityScorePass
S1Alice20NYC85True
S2Bob22LA92True
S3Charlie21Chicago67False
S4Diana23Boston78True
\n", "
" ], "text/plain": [ " Name Age City Score Pass\n", "S1 Alice 20 NYC 85 True\n", "S2 Bob 22 LA 92 True\n", "S3 Charlie 21 Chicago 67 False\n", "S4 Diana 23 Boston 78 True" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_students['Pass'] = df_students['Score'] >= 70\n", "df_students\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Create DataFrame with employee salary data (in thousands)\n" ] }, { "cell_type": "code", "execution_count": 42, "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", "
SalaryBonusYearsRating
Alice758.534.2
Bob8212.354.7
Charlie685.223.8
Diana9115.874.9
Eve553.113.5
\n", "
" ], "text/plain": [ " Salary Bonus Years Rating\n", "Alice 75 8.5 3 4.2\n", "Bob 82 12.3 5 4.7\n", "Charlie 68 5.2 2 3.8\n", "Diana 91 15.8 7 4.9\n", "Eve 55 3.1 1 3.5" ] }, "execution_count": 42, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_sal = pd.DataFrame({\n", " 'Salary': [75, 82, 68, 91, 55],\n", " 'Bonus': [8.5, 12.3, 5.2, 15.8, 3.1],\n", " 'Years': [3, 5, 2, 7, 1],\n", " 'Rating': [4.2, 4.7, 3.8, 4.9, 3.5]\n", "}, index=['Alice', 'Bob', 'Charlie', 'Diana', 'Eve'])\n", "\n", "df_sal" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## DataFrame Attributes & Methods\n", "\n", "DataFrames have many useful attributes and methods that help us understand the structure and content of our data. Let's explore the most commonly used ones." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### The `dtypes` Attribute\n", "\n", "Note that Python \n", "The `.dtypes` attribute shows the data type of each column. Pandas infers types automatically when creating or loading a DataFrame. The most common types are:\n", "\n", "| dtype | Description | Example values |\n", "|---|---|---|\n", "| `int64` | Integer numbers | `1`, `42`, `-7` |\n", "| `float64` | Decimal numbers | `3.14`, `NaN` |\n", "| `object` | Strings (or mixed types) | `'Alice'`, `'NYC'` |\n", "| `bool` | Boolean values | `True`, `False` |\n", "| `datetime64` | Dates and times | `2020-01-01` |\n", "\n", "Getting the dtypes right matters — numeric operations like `.mean()` won't work on `object` columns, and storage is more efficient with proper types.\n", "\n", "| Type Concept | Comes From | Used For | Returns / Represents |\n", "| --------------------- | ---------- | ------------------------------------------------- | ---------------------------------------------------- |\n", "| `int`, `float`, `str` | Python | Built-in scalar data types | A Python **object** (e.g., `5`, `3.14`, `\"hello\"`) |\n", "| `int64`, `float64` | NumPy | Fixed-width numeric storage in arrays | A NumPy **scalar** type (e.g., `np.int64`, `np.float64`) |\n", "| `dtype` | NumPy | Describing the data type of a single array/Series | A NumPy `dtype` object (e.g., `dtype('int64')`) |\n", "| `dtypes` | pandas | Viewing all column data types in a DataFrame | A pandas **Series** of NumPy `dtype` objects |\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "To get the dtypes of the salary dataframe, you issue `df.dtypes` and pandas returns a Series object of **column dtypes**." ] }, { "cell_type": "code", "execution_count": 19, "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", "
SalaryBonusYearsRating
Alice758.534.2
Bob8212.354.7
Charlie685.223.8
Diana9115.874.9
Eve553.113.5
\n", "
" ], "text/plain": [ " Salary Bonus Years Rating\n", "Alice 75 8.5 3 4.2\n", "Bob 82 12.3 5 4.7\n", "Charlie 68 5.2 2 3.8\n", "Diana 91 15.8 7 4.9\n", "Eve 55 3.1 1 3.5" ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_sal" ] }, { "cell_type": "code", "execution_count": 20, "metadata": { "tags": [] }, "outputs": [ { "data": { "text/plain": [ "Salary int64\n", "Bonus float64\n", "Years int64\n", "Rating float64\n", "dtype: object" ] }, "execution_count": 20, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_sal.dtypes" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "When you select a column (`Series`) from a dataframe, the dtype of the dataframe will be shown at the end of the displayed Series. Or you can use the `.dtype` property to access the dtype. " ] }, { "cell_type": "code", "execution_count": 21, "metadata": { "tags": [] }, "outputs": [ { "data": { "text/plain": [ "Alice 75\n", "Bob 82\n", "Charlie 68\n", "Diana 91\n", "Eve 55\n", "Name: Salary, dtype: int64" ] }, "execution_count": 21, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_sal['Salary'] ### ### Use bracket notation to select a single column" ] }, { "cell_type": "code", "execution_count": 22, "metadata": { "tags": [] }, "outputs": [ { "data": { "text/plain": [ "dtype('int64')" ] }, "execution_count": 22, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_sal['Salary'].dtype ### Get the data type of the 'Salary' column (SERIES)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Shape and Size\n", "\n", "Understanding the dimensions of a DataFrame is essential when working with real-world datasets. The `.shape`, `.size`, and `len()` properties give you a quick overview of how much data you're working with.\n", "\n", "| Item | Type | Parentheses? | Returns | Meaning |\n", "| --------- | -------------------- | ------------ | ------------------- | ------------------------------------------- |\n", "| `.shape` | df property (attribute) | No | **Tuple** `(rows, columns)` | Dimensions of the DataFrame |\n", "| `.size` | df property (attribute) | No | Integer | Total number of elements (`rows × columns`) |\n", "| `len(df)` | Python built-in function | Yes | Integer | Number of rows |\n", "| `.head()` | df method | Yes | DataFrame | First 5 rows (by default) |\n", "\n" ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "tags": [] }, "outputs": [ { "ename": "NameError", "evalue": "name 'df_sample' is not defined", "output_type": "error", "traceback": [ "\u001b[31m---------------------------------------------------------------------------\u001b[39m", "\u001b[31mNameError\u001b[39m Traceback (most recent call last)", "\u001b[36mCell\u001b[39m\u001b[36m \u001b[39m\u001b[32mIn[1]\u001b[39m\u001b[32m, line 2\u001b[39m\n\u001b[32m 1\u001b[39m \u001b[38;5;66;03m# Get the dimensions of the DataFrame (rows, columns)\u001b[39;00m\n\u001b[32m----> \u001b[39m\u001b[32m2\u001b[39m \u001b[43mdf_sample\u001b[49m.shape\n\u001b[32m 3\u001b[39m \u001b[38;5;28mprint\u001b[39m(\u001b[33mf\u001b[39m\u001b[33m\"\u001b[39m\u001b[33mshape(): Dimensions of the DataFrame:\u001b[39m\u001b[38;5;130;01m\\t\u001b[39;00m\u001b[38;5;130;01m\\t\u001b[39;00m\u001b[38;5;130;01m\\t\u001b[39;00m\u001b[33m \u001b[39m\u001b[38;5;132;01m{\u001b[39;00mdf_sample.shape\u001b[38;5;132;01m}\u001b[39;00m\u001b[33m\"\u001b[39m)\n\u001b[32m 5\u001b[39m \u001b[38;5;66;03m# Get the total number of elements (rows * columns)\u001b[39;00m\n", "\u001b[31mNameError\u001b[39m: name 'df_sample' is not defined" ] } ], "source": [ "# Get the dimensions of the DataFrame (rows, columns)\n", "df_sample.shape\n", "print(f\"shape(): Dimensions of the DataFrame:\\t\\t\\t {df_sample.shape}\")\n", "\n", "# Get the total number of elements (rows * columns)\n", "df_sample.size\n", "print(f\"size(): Total number of elements in the DataFrame:\\t {df_sample.size}\")\n", "\n", "# Get the number of rows\n", "len(df_sample)\n", "print(f\"len(): Number of rows in the DataFrame:\\t\\t\\t {len(df_sample)}\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Columns and Index\n", "\n", "Every DataFrame has two key structural attributes: `.columns`, which holds the column labels, and `.index`, which labels the rows. Knowing how to access and work with these is fundamental to selecting and manipulating data." ] }, { "cell_type": "code", "execution_count": 32, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " Duration Pulse Maxpulse Calories\n", "0 60 110 130 409.1\n", "1 60 117 145 479.0\n", "2 60 103 135 340.0\n", "3 45 109 175 282.4\n", "4 45 117 148 406.0\n", ".. ... ... ... ...\n", "160 30 85 120 250.4\n", "161 45 90 130 260.4\n", "162 45 95 130 270.0\n", "163 45 100 140 280.9\n", "164 60 105 140 290.8\n", "\n", "[165 rows x 4 columns]\n" ] } ], "source": [ "print(df_sample)" ] }, { "cell_type": "code", "execution_count": 33, "metadata": { "tags": [] }, "outputs": [ { "data": { "text/plain": [ "Index(['Duration', 'Pulse', 'Maxpulse', 'Calories'], dtype='object')" ] }, "execution_count": 33, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Get all column names\n", "\n", "df_sample.columns" ] }, { "cell_type": "code", "execution_count": 34, "metadata": { "tags": [] }, "outputs": [ { "data": { "text/plain": [ "RangeIndex(start=0, stop=165, step=1)" ] }, "execution_count": 34, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Get the index (row labels)\n", "df_sample.index" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Statistical Summary\n", "\n", "The `.info()` method gives you a concise overview of a DataFrame's structure — including column names, data types, and non-null counts. The `.describe()` method then computes summary statistics (count, mean, min, max, etc.) for each column. Together, these are usually the first two things you run when exploring a new dataset." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### The `info()` Method" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Now that we have the DataFrame object, let's use `df.info()` to get the:\n", "1. data type\n", "2. number of values in the **column**\n", "3. Null values" ] }, { "cell_type": "code", "execution_count": 36, "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", "RangeIndex: 165 entries, 0 to 164\n", "Data columns (total 4 columns):\n", " # Column Non-Null Count Dtype \n", "--- ------ -------------- ----- \n", " 0 Duration 165 non-null int64 \n", " 1 Pulse 165 non-null int64 \n", " 2 Maxpulse 165 non-null int64 \n", " 3 Calories 160 non-null float64\n", "dtypes: float64(1), int64(3)\n", "memory usage: 5.3 KB\n" ] } ], "source": [ "# df_sample.drop(df_sample.index[df_sample.index > 169], inplace=True)\n", "df_sample.info()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### The `describe()` Method\n", "\n", "`.describe()` generates summary statistics for all **numeric columns** in one shot. It returns:\n", "\n", "| Statistic | Description |\n", "|---|---|\n", "| `count` | Number of non-null values |\n", "| `mean` | Average value |\n", "| `std` | Standard deviation (spread of values) |\n", "| `min` / `max` | Smallest and largest values |\n", "| `25%`, `50%`, `75%` | Quartiles — the value below which 25%, 50%, and 75% of the data falls |\n", "\n", "The 50% row is the **median**. If `mean` and `median` differ significantly, the distribution is likely skewed." ] }, { "cell_type": "code", "execution_count": 37, "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", "
DurationPulseMaxpulseCalories
count165.000000165.000000165.000000160.000000
mean63.757576107.218182133.721212377.311250
std42.79277214.57406316.508899269.526378
min15.00000080.000000100.00000050.300000
25%45.000000100.000000124.000000250.625000
50%60.000000105.000000131.000000318.600000
75%60.000000111.000000140.000000388.200000
max300.000000159.000000184.0000001860.400000
\n", "
" ], "text/plain": [ " Duration Pulse Maxpulse Calories\n", "count 165.000000 165.000000 165.000000 160.000000\n", "mean 63.757576 107.218182 133.721212 377.311250\n", "std 42.792772 14.574063 16.508899 269.526378\n", "min 15.000000 80.000000 100.000000 50.300000\n", "25% 45.000000 100.000000 124.000000 250.625000\n", "50% 60.000000 105.000000 131.000000 318.600000\n", "75% 60.000000 111.000000 140.000000 388.200000\n", "max 300.000000 159.000000 184.000000 1860.400000" ] }, "execution_count": 37, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_sample.describe() # Get summary statistics for numeric columns" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "For this exercise, either \n", "- {download}`download baby.csv <../../data/actors.csv>` and place it in your project `data` folder, or \n", "- just use `pd.read_csv(\"../../data/actors.csv\")` in the Live Code cell to access the `baby.csv` dataset." ] }, { "cell_type": "code", "execution_count": 28, "metadata": { "tags": [ "thebe-interactive" ] }, "outputs": [], "source": [ "### EXERCISE: Exploring DataFrame Attributes\n", "#\n", "# Using the actors.csv file from the data directory:\n", "# 1. Read the CSV file into a DataFrame called df_actors\n", "# 2. Find the number of rows and columns (shape)\n", "# 3. Check the data types of each column\n", "# 4. Get a statistical summary of the DataFrame\n", "# 5. Display the index values\n", "# 6. Calculate the total number of elements\n", "#\n", "### Your code starts here:\n", "\n", "\n", "\n", "\n", "\n", "### Your code ends here.\n" ] }, { "cell_type": "code", "execution_count": 41, "metadata": { "tags": [ "hide-input" ] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Shape (rows, columns): (50, 6)\n", "\n", "Data types:\n", "Actor object\n", "Total Gross float64\n", "Number of Movies int64\n", "Average per Movie float64\n", "#1 Movie object\n", "Gross float64\n", "dtype: object\n", "\n", "Statistical summary:\n", " Total Gross Number of Movies Average per Movie Gross\n", "count 50.000000 50.000000 50.000000 50.000000\n", "mean 3034.088000 37.880000 93.518000 428.690000\n", "std 591.528271 14.122843 58.509175 184.344412\n", "min 2416.500000 7.000000 39.000000 169.700000\n", "25% 2612.425000 27.500000 67.550000 279.300000\n", "50% 2824.500000 37.000000 79.050000 419.150000\n", "75% 3185.800000 43.750000 100.125000 519.800000\n", "max 4871.700000 79.000000 451.800000 936.700000\n", "\n", "Index: [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49]\n", "\n", "Total elements: 300\n" ] } ], "source": [ "### Solution\n", "\n", "# 1. Read the CSV file\n", "df_actors = pd.read_csv('../../data/actors.csv')\n", "\n", "# 2. Number of rows and columns\n", "print(\"Shape (rows, columns):\", df_actors.shape)\n", "print()\n", "\n", "# 3. Data types\n", "print(\"Data types:\")\n", "print(df_actors.dtypes)\n", "print()\n", "\n", "# 4. Statistical summary\n", "print(\"Statistical summary:\")\n", "print(df_actors.describe())\n", "print()\n", "\n", "# 5. Index values\n", "print(\"Index:\", df_actors.index.tolist())\n", "print()\n", "\n", "# 6. Total elements\n", "print(\"Total elements:\", df_actors.size)\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## DataFrame Selection and Indexing\n", "\n", "Selection and indexing are fundamental operations in pandas that allow you to extract specific portions of your data. Understanding these operations is crucial for effective data analysis and manipulation.\n", "\n", "\n", "**Selection** refers to extracting data from a DataFrame—whether that's entire columns, specific rows, or individual cells. Pandas provides several methods for selection, each with its own use cases:\n", "\n", "1. **Bracket notation `[]`**: The simplest method for selecting columns\n", " - `df['column']` → selects a single column (returns a Series)\n", " - `df[['col1', 'col2']]` → selects multiple columns (returns a DataFrame)\n", " - Note: Use double brackets `[[]]` for multiple columns to get a DataFrame\n", "\n", "2. **`.loc[]`**: Label-based indexing\n", " - Uses row and column **labels** (names)\n", " - Syntax: `df.loc[row_label, column_label]`\n", " - Supports slicing: `df.loc['A':'C', 'col1':'col3']` (inclusive on both ends)\n", " - Ideal when you know the index names\n", "\n", "3. **`.iloc[]`**: Integer position-based indexing\n", " - Uses row and column **positions** (integers, starting from 0)\n", " - Syntax: `df.iloc[row_position, column_position]`\n", " - Supports slicing: `df.iloc[0:3, 0:2]` (exclusive on the end, like Python lists)\n", " - Ideal when you need to select by position\n", "\n", "4. **`.at[]` / `.iat[]`**: Fast access to a **single value**\n", " - `.at[row_label, col_label]` — label-based, faster than `.loc[]`\n", " - `.iat[row_pos, col_pos]` — position-based, faster than `.iloc[]`\n", " - Best used when reading or writing one cell, especially inside loops\n", "\n", "\n", "A more complete summary list of indexing and selection methods:\n", "\n", "| Method | Syntax | Selects | Example |\n", "|---|---|---|---|\n", "| Column (single) | `df['col']` or `df.col` | One column as a Series | `df['age']` |\n", "| Column (multiple) | `df[['col1','col2']]` | Multiple columns as a DataFrame | `df[['name','age']]` |\n", "| Row slicing (position) | `df[start:stop]` | Rows by position (slice) | `df[0:5]` |\n", "| Boolean mask | `df[condition]` | Rows matching a condition | `df[df['age'] > 30]` |\n", "| `.loc[]` (label-based) | `df.loc[row_label, col_label]` | Rows/cols by label | `df.loc[3, 'age']` |\n", "| `.loc[]` row slice | `df.loc[start:stop]` | Rows by label (inclusive of stop) | `df.loc[2:5]` |\n", "| `.loc[]` with condition | `df.loc[condition, 'col']` | Filtered rows, specific column(s) | `df.loc[df['age']>30, 'name']` |\n", "| `.iloc[]` (position-based) | `df.iloc[row_pos, col_pos]` | Rows/cols by integer position | `df.iloc[0, 1]` |\n", "| `.iloc[]` slice | `df.iloc[start:stop, start:stop]` | Rows/cols by position (exclusive of stop) | `df.iloc[0:3, 0:2]` |\n", "| `.at[]` | `df.at[row_label, col_label]` | Single scalar value by label (fast) | `df.at[3, 'age']` |\n", "| `.iat[]` | `df.iat[row_pos, col_pos]` | Single scalar value by position (fast) | `df.iat[0, 1]` |\n", "| `.query()` | `df.query('expression')` | Rows matching a string expression | `df.query('age > 30')` |\n", "| `.isin()` | `df[df['col'].isin([...])]` | Rows where value is in a list | `df[df['city'].isin(['NYC','LA'])]` |\n", "| `.filter()` | `df.filter(items=[...])` or `like=` or `regex=` | Columns (or rows with axis=0) by name pattern | `df.filter(like='date')` |\n", "| Multiple conditions | `df[(cond1) & (cond2)]` | Rows matching combined boolean conditions | `df[(df['age']>30) & (df['city']=='NYC')]` |\n", "| `.xs()` | `df.xs(key, level=..., axis=...)` | Cross-section from MultiIndex | `df.xs('2023', level='year')` |\n", "| `.head()` / `.tail()` | `df.head(n)` / `df.tail(n)` | First/last n rows | `df.head(10)` |\n", "| `.sample()` | `df.sample(n)` | Random n rows | `df.sample(5)` |" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "When to use each of the **core** methods:\n", "\n", "- Use **bracket notation** for quick column selection\n", "- Use **`.loc[]`** when working with labeled indices (most common in data analysis)\n", "- Use **`.iloc[]`** when you need position-based selection (e.g., \"first 5 rows\")\n", "- Use **`.at[]`** / **`.iat[]`** when accessing a single cell — especially inside loops where performance matters" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Common Patterns\n", "\n", "```python\n", "# Select a single column\n", "df['Name']\n", "\n", "# Select multiple columns\n", "df[['Name', 'Age', 'City']] ### note the list \n", "\n", "# Select specific rows and columns by label\n", "df.loc['Alice', 'Age']\n", "df.loc[['Alice', 'Bob'], ['Name', 'Age']]\n", "\n", "# Select by position\n", "df.iloc[0, 1] # First row, second column\n", "df.iloc[0:3, :] # First 3 rows, all columns\n", "df.iloc[:, 0:2] # All rows, first 2 columns\n", "\n", "# Boolean indexing\n", "df[df['Age'] > 25] # Filter rows based on condition\n", "```\n", "\n", "Let's practice these concepts with some exercises." ] }, { "cell_type": "code", "execution_count": 43, "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", "
SalaryBonusYearsRating
Alice758.534.2
Bob8212.354.7
Charlie685.223.8
Diana9115.874.9
Eve553.113.5
\n", "
" ], "text/plain": [ " Salary Bonus Years Rating\n", "Alice 75 8.5 3 4.2\n", "Bob 82 12.3 5 4.7\n", "Charlie 68 5.2 2 3.8\n", "Diana 91 15.8 7 4.9\n", "Eve 55 3.1 1 3.5" ] }, "execution_count": 43, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_sal" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Selecting Columns\n", "Pass a list of column names to select multiple columns. The result is a DataFrame." ] }, { "cell_type": "code", "execution_count": 44, "metadata": { "tags": [] }, "outputs": [ { "data": { "text/plain": [ "Alice 75\n", "Bob 82\n", "Charlie 68\n", "Diana 91\n", "Eve 55\n", "Name: Salary, dtype: int64" ] }, "execution_count": 44, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_sal['Salary']" ] }, { "cell_type": "code", "execution_count": 45, "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", "
SalaryRating
Alice754.2
Bob824.7
Charlie683.8
Diana914.9
Eve553.5
\n", "
" ], "text/plain": [ " Salary Rating\n", "Alice 75 4.2\n", "Bob 82 4.7\n", "Charlie 68 3.8\n", "Diana 91 4.9\n", "Eve 55 3.5" ] }, "execution_count": 45, "metadata": {}, "output_type": "execute_result" } ], "source": [ "### Pass a list of column names\n", "\n", "df_sal[['Salary','Rating']]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Using .loc[ ] - Label-based indexing\n", "\n", "Selecting columns by `.loc[]` and return `pd.Series`." ] }, { "cell_type": "code", "execution_count": 33, "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " \n", "\n" ] }, { "data": { "text/plain": [ "Salary 75.0\n", "Bonus 8.5\n", "Years 3.0\n", "Rating 4.2\n", "Name: Alice, dtype: float64" ] }, "execution_count": 33, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ser = df_sal.loc['Alice']\n", "print(type(ser), \"\\n\")\n", "ser\n" ] }, { "cell_type": "code", "execution_count": null, "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", "
SalaryBonusYearsRating
Alice758.534.2
Bob8212.354.7
\n", "
" ], "text/plain": [ " Salary Bonus Years Rating\n", "Alice 75 8.5 3 4.2\n", "Bob 82 12.3 5 4.7" ] }, "execution_count": 46, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_sal.loc[['Alice', 'Bob']] ### Pass a list of row labels to select multiple rows\n", "\n", "### looks like a DataFrame, but it's actually a DataFrame, not a Series. " ] }, { "cell_type": "code", "execution_count": 48, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " \n", "\n" ] } ], "source": [ "### The type() function confirms this.\n", "\n", "two_labels = df_sal.loc[['Alice', 'Bob']]\n", "print(type(two_labels), \"\\n\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false, "jupyter": { "outputs_hidden": false }, "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", "
SalaryRating
Alice754.2
Bob824.7
\n", "
" ], "text/plain": [ " Salary Rating\n", "Alice 75 4.2\n", "Bob 82 4.7" ] }, "execution_count": 35, "metadata": {}, "output_type": "execute_result" } ], "source": [ "### Get Salary and Rating for Alice and Bob\n", "### pass a list of row labels and a list of column names to select specific rows and columns\n", "\n", "df_sal.loc[['Alice','Bob'],['Salary','Rating']]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Using .iloc[ ] - Position-based indexing\n", "\n", "Instead of using labels, you use index position numbers. " ] }, { "cell_type": "code", "execution_count": 49, "metadata": { "collapsed": false, "jupyter": { "outputs_hidden": false }, "tags": [] }, "outputs": [ { "data": { "text/plain": [ "Salary 68.0\n", "Bonus 5.2\n", "Years 2.0\n", "Rating 3.8\n", "Name: Charlie, dtype: float64" ] }, "execution_count": 49, "metadata": {}, "output_type": "execute_result" } ], "source": [ "### Get the third row (index position 2) - Charlie's data\n", "\n", "df_sal.iloc[2]" ] }, { "cell_type": "code", "execution_count": 50, "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", "
SalaryBonusYearsRating
Alice758.534.2
Bob8212.354.7
\n", "
" ], "text/plain": [ " Salary Bonus Years Rating\n", "Alice 75 8.5 3 4.2\n", "Bob 82 12.3 5 4.7" ] }, "execution_count": 50, "metadata": {}, "output_type": "execute_result" } ], "source": [ "### Get first two rows (positions 0 and 1)\n", "\n", "df_sal.iloc[0:2]" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [ { "data": { "text/plain": [ "np.int64(75)" ] }, "execution_count": 51, "metadata": {}, "output_type": "execute_result" } ], "source": [ "### Get value at row position 0, column position 0 (Alice's Salary)\n", "\n", "df_sal.iloc[0, 0]\n", "\n", "### output display is a little different than print() " ] }, { "cell_type": "code", "execution_count": 52, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "75\n" ] } ], "source": [ "print(df_sal.iloc[0, 0])" ] }, { "cell_type": "code", "execution_count": 53, "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", "
SalaryBonus
Alice758.5
Bob8212.3
\n", "
" ], "text/plain": [ " Salary Bonus\n", "Alice 75 8.5\n", "Bob 82 12.3" ] }, "execution_count": 53, "metadata": {}, "output_type": "execute_result" } ], "source": [ "### Get first two rows and first two columns\n", "\n", "df_sal.iloc[0:2, 0:2]" ] }, { "cell_type": "code", "execution_count": 40, "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", "
SalaryYears
Alice753
Bob825
Charlie682
Diana917
Eve551
\n", "
" ], "text/plain": [ " Salary Years\n", "Alice 75 3\n", "Bob 82 5\n", "Charlie 68 2\n", "Diana 91 7\n", "Eve 55 1" ] }, "execution_count": 40, "metadata": {}, "output_type": "execute_result" } ], "source": [ "### Get all rows, but only columns at positions 0 and 2 (Salary and Rating)\n", "df_sal.iloc[:, [0, 2]]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### `.at[]` and `.iat[]` — Fast Single-Value Access\n", "\n", "When you only need to read or write a **single cell**, `.at[]` and `.iat[]` are faster than `.loc[]` and `.iloc[]` because they are optimized for scalar access.\n", "\n", "| Accessor | Based on | Example |\n", "|---|---|---|\n", "| `.at[label, col]` | Row/column **labels** | `df.at['Alice', 'Salary']` |\n", "| `.iat[row, col]` | Integer **positions** | `df.iat[0, 0]` |\n", "\n", "Use these when performance matters in a loop or when you just need one value." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "tags": [] }, "outputs": [ { "data": { "text/plain": [ "np.int64(75)" ] }, "execution_count": 54, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Using .at[] for fast label-based access to a single value\n", "\n", "df_sal.at['Alice', 'Salary'] ### at labels (row label, column label)" ] }, { "cell_type": "code", "execution_count": 56, "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Using .loc[]: 4.7\n", "Using .at[]: 4.7\n" ] } ], "source": [ "# Performance comparison: .at[] is faster than .loc[] for single values\n", "# .loc[] and .at[] both use label-based indexing\n", "\n", "print(\"Using .loc[]:\", df_sal.loc['Bob', 'Rating'])\n", "print(\"Using .at[]:\", df_sal.at['Bob', 'Rating'])" ] }, { "cell_type": "code", "execution_count": 57, "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Using .iloc[]: 4.7\n", "Using .iat[]: 4.7\n" ] } ], "source": [ "# .iat[] uses integer positions (row index, column index) — both are 0-based\n", "\n", "print(\"Using .iloc[]:\", df_sal.iloc[1, 3]) # row 1, col 3\n", "print(\"Using .iat[]:\", df_sal.iat[1, 3]) # same cell, faster for single value" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Boolean Indexing\n", "\n", "Boolean indexing lets you filter rows based on a condition. You pass a boolean expression inside `[]`, and pandas returns only the rows where the condition is `True`.\n", "\n", "- A single condition: `df[df['Salary'] > 70]`\n", "- Multiple conditions: combine with `&` (and) or `|` (or), and wrap each condition in parentheses\n", "- Best practice: use `.loc[]` when you also want to select specific columns alongside the filter\n", "- Use **`.query()`** for a cleaner string-based syntax: `df.query('Salary > 70 and Years > 2')`" ] }, { "cell_type": "code", "execution_count": 44, "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", "
SalaryBonusYearsRating
Alice758.534.2
Bob8212.354.7
Charlie685.223.8
Diana9115.874.9
Eve553.113.5
\n", "
" ], "text/plain": [ " Salary Bonus Years Rating\n", "Alice 75 8.5 3 4.2\n", "Bob 82 12.3 5 4.7\n", "Charlie 68 5.2 2 3.8\n", "Diana 91 15.8 7 4.9\n", "Eve 55 3.1 1 3.5" ] }, "execution_count": 44, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_sal" ] }, { "cell_type": "code", "execution_count": 45, "metadata": { "collapsed": false, "jupyter": { "outputs_hidden": false }, "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", "
SalaryBonusYearsRating
Alice758.534.2
Bob8212.354.7
Diana9115.874.9
\n", "
" ], "text/plain": [ " Salary Bonus Years Rating\n", "Alice 75 8.5 3 4.2\n", "Bob 82 12.3 5 4.7\n", "Diana 91 15.8 7 4.9" ] }, "execution_count": 45, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_sal[df_sal[\"Salary\"]>70]" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false, "jupyter": { "outputs_hidden": false }, "tags": [] }, "outputs": [ { "data": { "text/plain": [ "Alice 3\n", "Bob 5\n", "Diana 7\n", "Name: Years, dtype: int64" ] }, "execution_count": 58, "metadata": {}, "output_type": "execute_result" } ], "source": [ "### and from the above, we can get the Years column for employees with Salary > 70\n", "### Get Years for employees with Salary > 70\n", "\n", "df_sal[df_sal['Salary']>70]['Years']" ] }, { "cell_type": "code", "execution_count": 47, "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", "
YearsBonus
Alice38.5
Bob512.3
Diana715.8
\n", "
" ], "text/plain": [ " Years Bonus\n", "Alice 3 8.5\n", "Bob 5 12.3\n", "Diana 7 15.8" ] }, "execution_count": 47, "metadata": {}, "output_type": "execute_result" } ], "source": [ "### BETTER PRACTICE: Use .loc[] for row and column selection together\n", "df_sal.loc[df_sal['Salary']>70, ['Years','Bonus']]" ] }, { "cell_type": "code", "execution_count": 48, "metadata": { "collapsed": false, "jupyter": { "outputs_hidden": false }, "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", "
SalaryBonusYearsRating
Alice758.534.2
Bob8212.354.7
Diana9115.874.9
\n", "
" ], "text/plain": [ " Salary Bonus Years Rating\n", "Alice 75 8.5 3 4.2\n", "Bob 82 12.3 5 4.7\n", "Diana 91 15.8 7 4.9" ] }, "execution_count": 48, "metadata": {}, "output_type": "execute_result" } ], "source": [ "### Combine conditions with & (and) or | (or)\n", "### Each condition must be in parentheses\n", "\n", "df_sal.loc[(df_sal['Salary']>70) & (df_sal['Years'] > 2)]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "**`.query()`** is an alternative to boolean indexing that uses a cleaner string syntax — no need for `df[...]` wrappers or parentheses around each condition. It's especially readable for multi-condition filters." ] }, { "cell_type": "code", "execution_count": 49, "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", "
SalaryBonusYearsRating
Alice758.534.2
Bob8212.354.7
Diana9115.874.9
\n", "
" ], "text/plain": [ " Salary Bonus Years Rating\n", "Alice 75 8.5 3 4.2\n", "Bob 82 12.3 5 4.7\n", "Diana 91 15.8 7 4.9" ] }, "execution_count": 49, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Same result as above, but more readable\n", "df_sal.query('Salary > 70 and Years > 2')" ] }, { "cell_type": "code", "execution_count": 50, "metadata": { "tags": [ "thebe-interactive" ] }, "outputs": [], "source": [ "### EXERCISE: DataFrame Selection and Indexing\n", "#\n", "# Using df_sal (the employee salary DataFrame defined earlier):\n", "# Index: Alice, Bob, Charlie, Diana, Eve\n", "# Columns: Salary, Bonus, Years, Rating\n", "#\n", "# Tasks:\n", "# 1. Select only the 'Salary' and 'Years' columns (use double brackets)\n", "# 2. Use .loc[] to get the 'Bonus' and 'Rating' for 'Bob' and 'Diana'\n", "# 3. Use .iloc[] to get the first 3 rows and first 2 columns\n", "# 4. Use boolean indexing to filter employees with Rating >= 4.5\n", "# 5. Use .query() to find employees with Years >= 3 and Bonus > 8\n", "#\n", "### Your code starts here:\n", "\n", "\n", "\n", "\n", "### Your code ends here.\n" ] }, { "cell_type": "code", "execution_count": 59, "metadata": { "tags": [ "hide-input" ] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Salary and Years:\n", " Salary Years\n", "Alice 75 3\n", "Bob 82 5\n", "Charlie 68 2\n", "Diana 91 7\n", "Eve 55 1\n", "\n", "Bonus and Rating for Bob and Diana:\n", " Bonus Rating\n", "Bob 12.3 4.7\n", "Diana 15.8 4.9\n", "\n", "First 3 rows, first 2 columns:\n", " Salary Bonus\n", "Alice 75 8.5\n", "Bob 82 12.3\n", "Charlie 68 5.2\n", "\n", "Employees with Rating >= 4.5:\n", " Salary Bonus Years Rating\n", "Bob 82 12.3 5 4.7\n", "Diana 91 15.8 7 4.9\n", "\n", "Employees with Years >= 3 and Bonus > 8:\n", " Salary Bonus Years Rating\n", "Alice 75 8.5 3 4.2\n", "Bob 82 12.3 5 4.7\n", "Diana 91 15.8 7 4.9\n" ] } ], "source": [ "### Solution\n", "\n", "# 1. Select Salary and Years columns\n", "print(\"Salary and Years:\")\n", "print(df_sal[['Salary', 'Years']])\n", "print()\n", "\n", "# 2. Bonus and Rating for Bob and Diana\n", "print(\"Bonus and Rating for Bob and Diana:\")\n", "print(df_sal.loc[['Bob', 'Diana'], ['Bonus', 'Rating']])\n", "print()\n", "\n", "# 3. First 3 rows and first 2 columns using .iloc[]\n", "print(\"First 3 rows, first 2 columns:\")\n", "print(df_sal.iloc[0:3, 0:2])\n", "print()\n", "\n", "# 4. Use boolean indexing to filter employees with Rating >= 4.5\n", "print(\"Employees with Rating >= 4.5:\")\n", "print(df_sal[df_sal['Rating'] >= 4.5])\n", "print()\n", "\n", "# 5. Use .query() to find employees with Years >= 3 and Bonus > 8\n", "print(\"Employees with Years >= 3 and Bonus > 8:\")\n", "print(df_sal.query('Years >= 3 and Bonus > 8'))\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Modifying Rows/Columns" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Adding/Dropping columns\n", "\n", "Using the drop function. Note that you have to specify **axis=1** to drop the column label.\n", "\n", "**Understanding axis parameter:**\n", "- `axis=0` → operates on **rows** (index) - \"go down the rows\"\n", "- `axis=1` → operates on **columns** - \"go across the columns\"\n", "\n", "**Mnemonic**: Think of axis as the direction you're moving. axis=0 goes down (rows), axis=1 goes across (columns)." ] }, { "cell_type": "code", "execution_count": 61, "metadata": { "tags": [] }, "outputs": [], "source": [ "### Create a new column: Total Compensation in df_sal\n", "\n", "df_sal['TotalComp'] = df_sal['Salary'] + df_sal['Bonus']" ] }, { "cell_type": "code", "execution_count": 60, "metadata": { "collapsed": false, "jupyter": { "outputs_hidden": false }, "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", "
SalaryBonusYearsRating
Alice758.534.2
Bob8212.354.7
Charlie685.223.8
Diana9115.874.9
Eve553.113.5
\n", "
" ], "text/plain": [ " Salary Bonus Years Rating\n", "Alice 75 8.5 3 4.2\n", "Bob 82 12.3 5 4.7\n", "Charlie 68 5.2 2 3.8\n", "Diana 91 15.8 7 4.9\n", "Eve 55 3.1 1 3.5" ] }, "execution_count": 60, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Modified in place! \n", "\n", "df_sal" ] }, { "cell_type": "code", "execution_count": 54, "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", "
SalaryBonusYearsRating
Alice758.534.2
Bob8212.354.7
Charlie685.223.8
Diana9115.874.9
Eve553.113.5
\n", "
" ], "text/plain": [ " Salary Bonus Years Rating\n", "Alice 75 8.5 3 4.2\n", "Bob 82 12.3 5 4.7\n", "Charlie 68 5.2 2 3.8\n", "Diana 91 15.8 7 4.9\n", "Eve 55 3.1 1 3.5" ] }, "execution_count": 54, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_sal.drop('TotalComp',axis=1,inplace=True)\n", "\n", "df_sal" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Dropping rows\n", "\n", "To drop rows, use the `.drop()` method with **axis=0** (or no axis parameter since axis=0 is the default).\n", "\n", "You can drop rows by specifying their index labels." ] }, { "cell_type": "code", "execution_count": 55, "metadata": { "collapsed": false, "jupyter": { "outputs_hidden": false }, "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", "
SalaryBonusYearsRating
Alice758.534.2
Bob8212.354.7
Diana9115.874.9
Eve553.113.5
\n", "
" ], "text/plain": [ " Salary Bonus Years Rating\n", "Alice 75 8.5 3 4.2\n", "Bob 82 12.3 5 4.7\n", "Diana 91 15.8 7 4.9\n", "Eve 55 3.1 1 3.5" ] }, "execution_count": 55, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Drop a single row by index label (returns a new DataFrame)\n", "df_sal.drop('Charlie')" ] }, { "cell_type": "code", "execution_count": 67, "metadata": { "collapsed": false, "jupyter": { "outputs_hidden": false }, "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", "
SalaryBonusYearsRatingTotalComp
Bob8212.354.794.3
Charlie685.223.873.2
Diana9115.874.9106.8
\n", "
" ], "text/plain": [ " Salary Bonus Years Rating TotalComp\n", "Bob 82 12.3 5 4.7 94.3\n", "Charlie 68 5.2 2 3.8 73.2\n", "Diana 91 15.8 7 4.9 106.8" ] }, "execution_count": 67, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Drop multiple rows (pass a list of index labels)\n", "df_sal.drop(['Alice', 'Eve'], axis=0)" ] }, { "cell_type": "code", "execution_count": 77, "metadata": {}, "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", "
SalaryBonusYearsPerformanceTotalComp
Alice758.534.283.5
Bob8212.354.794.3
Charlie685.223.873.2
Diana9115.874.9106.8
Eve553.113.558.1
\n", "
" ], "text/plain": [ " Salary Bonus Years Performance TotalComp\n", "Alice 75 8.5 3 4.2 83.5\n", "Bob 82 12.3 5 4.7 94.3\n", "Charlie 68 5.2 2 3.8 73.2\n", "Diana 91 15.8 7 4.9 106.8\n", "Eve 55 3.1 1 3.5 58.1" ] }, "execution_count": 77, "metadata": {}, "output_type": "execute_result" } ], "source": [ "### methods that return a transformed copy \n", "\n", "df_sal.drop('Bonus', axis=1) # returns a NEW DataFrame; df_sal itself is unchanged\n", "df_sal.sort_values('Salary') # same — returns a copy\n", "df_sal.fillna(0) # same\n", "df_sal.rename(columns={'Rating': 'Performance'}) # same" ] }, { "cell_type": "code", "execution_count": 76, "metadata": {}, "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", "
SalaryBonusYearsRatingTotalComp
Alice758.534.283.5
Bob8212.354.794.3
Charlie685.223.873.2
Diana9115.874.9106.8
Eve553.113.558.1
\n", "
" ], "text/plain": [ " Salary Bonus Years Rating TotalComp\n", "Alice 75 8.5 3 4.2 83.5\n", "Bob 82 12.3 5 4.7 94.3\n", "Charlie 68 5.2 2 3.8 73.2\n", "Diana 91 15.8 7 4.9 106.8\n", "Eve 55 3.1 1 3.5 58.1" ] }, "execution_count": 76, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_sal" ] }, { "cell_type": "code", "execution_count": 57, "metadata": { "tags": [] }, "outputs": [], "source": [ "# Use inplace=True to modify the original DataFrame\n", "# df_sal.drop('Bob', axis=0, inplace=True)\n", "\n", "# Note: Commented out to preserve df_sal for other examples" ] }, { "cell_type": "code", "execution_count": 79, "metadata": { "tags": [ "thebe-interactive" ] }, "outputs": [], "source": [ "### EXERCISE: DataFrame Indexing Practice\n", "#\n", "# Given the following sales data:\n", "sales_data = pd.DataFrame({\n", " 'Product': ['Laptop', 'Mouse', 'Keyboard', 'Monitor', 'Webcam'],\n", " 'Q1': [50, 200, 150, 75, 90],\n", " 'Q2': [65, 210, 160, 80, 95],\n", " 'Q3': [70, 190, 155, 85, 100],\n", " 'Q4': [80, 220, 170, 90, 110]\n", "}, index=['P1', 'P2', 'P3', 'P4', 'P5'])\n", "#\n", "# Tasks:\n", "# 1. Select the Q2 and Q4 columns\n", "# 2. Select rows P2 and P4\n", "# 3. Select Q3 sales for rows P1, P3, and P5 (should return a Series)\n", "# 4. Add a new column 'Total' that sums Q1 through Q4 (Tip: use .sum(axis=1))\n", "# 5. Drop the Q1 column (without modifying the original DataFrame)\n", "#\n", "# Hint: Use double brackets [[]] for selecting multiple columns\n", "#\n", "### Your code starts here:\n", "\n", "\n", "\n", "\n", "\n", "### Your code ends here.\n" ] }, { "cell_type": "code", "execution_count": 81, "metadata": { "tags": [ "hide-input" ] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Q2 and Q4 columns:\n", " Q2 Q4\n", "P1 65 80\n", "P2 210 220\n", "P3 160 170\n", "P4 80 90\n", "P5 95 110\n", "\n", "Rows P2 and P4:\n", " Product Q1 Q2 Q3 Q4\n", "P2 Mouse 200 210 190 220\n", "P4 Monitor 75 80 85 90\n", "\n", "Q3 sales for P1, P3, P5:\n", "P1 70\n", "P3 155\n", "P5 100\n", "Name: Q3, dtype: int64\n", "\n", "With Total column:\n", " Product Q1 Q2 Q3 Q4 Total\n", "P1 Laptop 50 65 70 80 265\n", "P2 Mouse 200 210 190 220 820\n", "P3 Keyboard 150 160 155 170 635\n", "P4 Monitor 75 80 85 90 330\n", "P5 Webcam 90 95 100 110 395\n", "\n", "Without Q1 column:\n", " Product Q2 Q3 Q4\n", "P1 Laptop 65 70 80\n", "P2 Mouse 210 190 220\n", "P3 Keyboard 160 155 170\n", "P4 Monitor 80 85 90\n", "P5 Webcam 95 100 110\n" ] } ], "source": [ "### Solution\n", "\n", "sales_data = pd.DataFrame({\n", " 'Product': ['Laptop', 'Mouse', 'Keyboard', 'Monitor', 'Webcam'],\n", " 'Q1': [50, 200, 150, 75, 90],\n", " 'Q2': [65, 210, 160, 80, 95],\n", " 'Q3': [70, 190, 155, 85, 100],\n", " 'Q4': [80, 220, 170, 90, 110]\n", "}, index=['P1', 'P2', 'P3', 'P4', 'P5'])\n", "\n", "# 1. Select the Q2 and Q4 columns\n", "print(\"Q2 and Q4 columns:\")\n", "print(sales_data[['Q2', 'Q4']])\n", "print()\n", "\n", "# 2. Select rows P2 and P4\n", "print(\"Rows P2 and P4:\")\n", "print(sales_data.loc[['P2', 'P4']])\n", "print()\n", "\n", "# 3. Select Q3 sales for rows P1, P3, and P5\n", "print(\"Q3 sales for P1, P3, P5:\")\n", "print(sales_data.loc[['P1', 'P3', 'P5'], 'Q3'])\n", "print()\n", "\n", "# 4. Add a new column 'Total' that sums Q1 through Q4\n", "sales_data_with_total = sales_data.copy()\n", "sales_data_with_total['Total'] = sales_data_with_total[['Q1', 'Q2', 'Q3', 'Q4']].sum(axis=1)\n", "print(\"With Total column:\")\n", "print(sales_data_with_total)\n", "print()\n", "\n", "# 5. Drop the Q1 column\n", "print(\"Without Q1 column:\")\n", "print(sales_data.drop('Q1', axis=1))\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Applying Functions with `.apply()`\n", "\n", "The `.apply()` method is a powerful tool that allows you to apply a function along an axis of the DataFrame (rows or columns). This is useful for transformations, calculations, and custom operations.\n", "\n", "The syntax for `.apply()` is:\n", "\n", "```python\n", "DataFrame.apply(func, axis=0, raw=False, result_type=None, args=(), **kwargs)\n", "```\n", "\n", "**Parameters:**\n", "- `func`: Function to apply to each column or row\n", "- `axis`: {0 or 'index', 1 or 'columns'}, default 0\n", " - 0 or 'index': apply function to each column\n", " - 1 or 'columns': apply function to each row\n", "- `raw`: bool, default False. If False, passes each row/column as a Series to the function. If True, passes ndarray\n", "- `result_type`: {'expand', 'reduce', 'broadcast', None}, default None\n", "- `args`: tuple, positional arguments to pass to func\n", "- `**kwargs`: additional keyword arguments to pass to func\n", "\n", "**Returns:** Series or DataFrame\n", "\n", "**Performance Note:** For simple operations like arithmetic or comparisons, vectorized operations are usually faster than `.apply()`. Use `.apply()` when you need to apply a complex function that can't be vectorized.\n", "\n", "```python\n", "# Vectorized (faster): df['A'] * 2\n", "# Using apply (slower): df['A'].apply(lambda x: x * 2)\n", "```" ] }, { "cell_type": "code", "execution_count": 60, "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", "
ABCDEF
2020-01-01187968494887
2020-01-02177228185797
2020-01-03767874805621
2020-01-04855055432693
\n", "
" ], "text/plain": [ " A B C D E F\n", "2020-01-01 18 79 68 49 48 87\n", "2020-01-02 17 72 28 18 57 97\n", "2020-01-03 76 78 74 80 56 21\n", "2020-01-04 85 50 55 43 26 93" ] }, "execution_count": 60, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_dates" ] }, { "cell_type": "code", "execution_count": 61, "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "A 196\n", "B 279\n", "C 225\n", "D 190\n", "E 187\n", "F 298\n", "dtype: int64\n", "\n", "\n" ] } ], "source": [ "# Apply a function to each column (axis=0, default)\n", "\n", "print(df_dates.apply(np.sum))\n", "print()\n", "print(type(df_dates.apply(np.sum)))" ] }, { "cell_type": "code", "execution_count": 62, "metadata": { "tags": [] }, "outputs": [ { "data": { "text/plain": [ "2020-01-01 349\n", "2020-01-02 289\n", "2020-01-03 385\n", "2020-01-04 352\n", "Freq: D, dtype: int64" ] }, "execution_count": 62, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_dates.apply(np.sum, axis=1) ### axis = 1" ] }, { "cell_type": "code", "execution_count": 63, "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", "
ABCDEF
2020-01-01361581369896174
2020-01-02341445636114194
2020-01-0315215614816011242
2020-01-041701001108652186
\n", "
" ], "text/plain": [ " A B C D E F\n", "2020-01-01 36 158 136 98 96 174\n", "2020-01-02 34 144 56 36 114 194\n", "2020-01-03 152 156 148 160 112 42\n", "2020-01-04 170 100 110 86 52 186" ] }, "execution_count": 63, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Apply a custom function (double each value)\n", "df_dates.apply(lambda x: x * 2)" ] }, { "cell_type": "code", "execution_count": 64, "metadata": { "tags": [ "thebe-interactive" ] }, "outputs": [], "source": [ "### EXERCISE: Applying Functions\n", "#\n", "# Create a DataFrame:\n", "apply_df = pd.DataFrame({\n", " 'A': [1, 2, 3, 4],\n", " 'B': [10, 20, 30, 40],\n", " 'C': [100, 200, 300, 400]\n", "})\n", "#\n", "# Tasks:\n", "# 1. Apply a function to get the sum of each column\n", "# 2. Apply a function to get the maximum value in each row\n", "# 3. Apply a lambda function to double all values in column A\n", "# 4. Apply a custom function that adds 5 to all values\n", "#\n", "### Your code starts here:\n", "\n", "\n", "\n", "\n", "\n", "### Your code ends here.\n" ] }, { "cell_type": "code", "execution_count": 82, "metadata": { "tags": [ "hide-input" ] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Sum of each column:\n" ] }, { "ename": "NameError", "evalue": "name 'apply_df' is not defined", "output_type": "error", "traceback": [ "\u001b[31m---------------------------------------------------------------------------\u001b[39m", "\u001b[31mNameError\u001b[39m Traceback (most recent call last)", "\u001b[36mCell\u001b[39m\u001b[36m \u001b[39m\u001b[32mIn[82]\u001b[39m\u001b[32m, line 5\u001b[39m\n\u001b[32m 1\u001b[39m \u001b[38;5;66;03m### Solution\u001b[39;00m\n\u001b[32m 2\u001b[39m \n\u001b[32m 3\u001b[39m \u001b[38;5;66;03m# 1. Apply a function to get the sum of each column\u001b[39;00m\n\u001b[32m 4\u001b[39m \u001b[38;5;28mprint\u001b[39m(\u001b[33m\"\u001b[39m\u001b[33mSum of each column:\u001b[39m\u001b[33m\"\u001b[39m)\n\u001b[32m----> \u001b[39m\u001b[32m5\u001b[39m \u001b[38;5;28mprint\u001b[39m(\u001b[43mapply_df\u001b[49m.apply(np.sum))\n\u001b[32m 6\u001b[39m \u001b[38;5;28mprint\u001b[39m()\n\u001b[32m 8\u001b[39m \u001b[38;5;66;03m# 2. Apply a function to get the maximum value in each row\u001b[39;00m\n", "\u001b[31mNameError\u001b[39m: name 'apply_df' is not defined" ] } ], "source": [ "### Solution\n", "\n", "# 1. Apply a function to get the sum of each column\n", "print(\"Sum of each column:\")\n", "print(apply_df.apply(np.sum))\n", "print()\n", "\n", "# 2. Apply a function to get the maximum value in each row\n", "print(\"Maximum value in each row:\")\n", "print(apply_df.apply(np.max, axis=1))\n", "print()\n", "\n", "# 3. Apply a lambda function to double all values in column A\n", "print(\"Column A doubled:\")\n", "print(apply_df['A'].apply(lambda x: x * 2))\n", "print()\n", "\n", "# 4. Apply a custom function that adds 5 to all values\n", "def add_five(x):\n", " return x + 5\n", "\n", "print(\"All values plus 5:\")\n", "print(apply_df.apply(add_five))\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Advanced Indexing\n", "\n", "Indices are a powerful feature in Pandas that distinguish it from simple arrays. This section covers advanced index operations including basic manipulation and hierarchical indexing." ] }, { "cell_type": "code", "execution_count": 66, "metadata": { "collapsed": false, "jupyter": { "outputs_hidden": false }, "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", "
ABCDEF
2020-01-01187968494887
2020-01-02177228185797
2020-01-03767874805621
2020-01-04855055432693
\n", "
" ], "text/plain": [ " A B C D E F\n", "2020-01-01 18 79 68 49 48 87\n", "2020-01-02 17 72 28 18 57 97\n", "2020-01-03 76 78 74 80 56 21\n", "2020-01-04 85 50 55 43 26 93" ] }, "execution_count": 66, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_dates" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Changing Index\n", "\n", "You can directly assign a new index to a DataFrame by setting the `.index` attribute. The new index must have the same length as the number of rows." ] }, { "cell_type": "code", "execution_count": 67, "metadata": { "tags": [] }, "outputs": [ { "data": { "text/plain": [ "['MO', 'MI', 'MS', 'MN']" ] }, "execution_count": 67, "metadata": {}, "output_type": "execute_result" } ], "source": [ "newind = 'MO MI MS MN'.split()\n", "newind" ] }, { "cell_type": "code", "execution_count": 68, "metadata": { "collapsed": false, "jupyter": { "outputs_hidden": false }, "tags": [] }, "outputs": [], "source": [ "df_dates.index = newind" ] }, { "cell_type": "code", "execution_count": 69, "metadata": { "collapsed": false, "jupyter": { "outputs_hidden": false }, "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", "
ABCDEF
MO187968494887
MI177228185797
MS767874805621
MN855055432693
\n", "
" ], "text/plain": [ " A B C D E F\n", "MO 18 79 68 49 48 87\n", "MI 17 72 28 18 57 97\n", "MS 76 78 74 80 56 21\n", "MN 85 50 55 43 26 93" ] }, "execution_count": 69, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_dates" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Resetting and Setting Index\n", "\n", "Besides direct assignment, Pandas provides two powerful methods for manipulating DataFrame indices:\n", "\n", "**`.reset_index()`**: Converts the current index back to a regular column and creates a new default integer index (0, 1, 2, ...).\n", "\n", "- `drop=False` (default): Keeps the old index as a new column\n", "- `drop=True`: Discards the old index completely\n", "- `inplace=False` (default): Returns a new DataFrame\n", "- `inplace=True`: Modifies the DataFrame in place\n", "\n", "**`.set_index()`**: Converts a column into the index.\n", "\n", "- Takes a column name (or list of column names for MultiIndex)\n", "- The specified column(s) become the new index\n", "- `drop=True` (default): Removes the column from DataFrame after setting it as index\n", "- `inplace=False` (default): Returns a new DataFrame\n", "\n", "These methods are particularly useful for:\n", "- Preparing data for merging operations\n", "- Converting between wide and long formats\n", "- Creating hierarchical indices\n", "- Organizing data for time series analysis" ] }, { "cell_type": "code", "execution_count": 70, "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Original DataFrame:\n", " Name Score Grade\n", "100 Alice 85 B\n", "200 Bob 92 A\n", "300 Charlie 78 C\n", "\n" ] } ], "source": [ "# Example: Create a DataFrame with custom index\n", "sample_df = pd.DataFrame({\n", " 'Name': ['Alice', 'Bob', 'Charlie'],\n", " 'Score': [85, 92, 78],\n", " 'Grade': ['B', 'A', 'C']\n", "}, index=[100, 200, 300])\n", "\n", "print(\"Original DataFrame:\")\n", "print(sample_df)\n", "print()" ] }, { "cell_type": "code", "execution_count": 71, "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "After reset_index() with drop=False:\n", " index Name Score Grade\n", "0 100 Alice 85 B\n", "1 200 Bob 92 A\n", "2 300 Charlie 78 C\n", "\n" ] } ], "source": [ "# reset_index() with drop=False: old index becomes a column named 'index'\n", "reset_df = sample_df.reset_index()\n", "print(\"After reset_index() with drop=False:\")\n", "print(reset_df)\n", "print()" ] }, { "cell_type": "code", "execution_count": 72, "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "After reset_index() with drop=True:\n", " Name Score Grade\n", "0 Alice 85 B\n", "1 Bob 92 A\n", "2 Charlie 78 C\n", "\n" ] } ], "source": [ "# reset_index() with drop=True: old index is discarded\n", "reset_df_dropped = sample_df.reset_index(drop=True)\n", "print(\"After reset_index() with drop=True:\")\n", "print(reset_df_dropped)\n", "print()" ] }, { "cell_type": "code", "execution_count": 73, "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "After set_index('Name'):\n", " Score Grade\n", "Name \n", "Alice 85 B\n", "Bob 92 A\n", "Charlie 78 C\n", "\n", "Notice 'Name' is now the index, not a column\n" ] } ], "source": [ "# set_index(): Make 'Name' column the new index\n", "name_index_df = sample_df.set_index('Name')\n", "print(\"After set_index('Name'):\")\n", "print(name_index_df)\n", "print()\n", "print(\"Notice 'Name' is now the index, not a column\")" ] }, { "cell_type": "code", "execution_count": 74, "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "After chaining reset_index() and set_index('Grade'):\n", " Name Score\n", "Grade \n", "B Alice 85\n", "A Bob 92\n", "C Charlie 78\n" ] } ], "source": [ "# Chain reset_index() and set_index() together\n", "# First reset to default index, then set 'Grade' as new index\n", "chained_df = sample_df.reset_index(drop=True).set_index('Grade')\n", "print(\"After chaining reset_index() and set_index('Grade'):\")\n", "print(chained_df)" ] }, { "cell_type": "code", "execution_count": 75, "metadata": { "tags": [ "thebe-interactive" ] }, "outputs": [], "source": [ "### EXERCISE: Index Manipulation\n", "#\n", "# Create a DataFrame:\n", "idx_df = pd.DataFrame({\n", " 'Value': [10, 20, 30, 40],\n", " 'Category': ['A', 'B', 'A', 'C']\n", "}, index=[100, 200, 300, 400])\n", "#\n", "# Tasks:\n", "# 1. Display the original DataFrame\n", "# 2. Reset the index (drop the old index)\n", "# 3. Set 'Category' as the new index\n", "# 4. Display the final 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": [ " Value Category\n", "100 10 A\n", "200 20 B\n", "300 30 A\n", "400 40 C\n", " Value Category\n", "0 10 A\n", "1 20 B\n", "2 30 A\n", "3 40 C\n", " Value\n", "Category \n", "A 10\n", "B 20\n", "A 30\n", "C 40\n" ] } ], "source": [ "### Solution\n", "\n", "idx_df = pd.DataFrame({\n", " 'Value': [10, 20, 30, 40],\n", " 'Category': ['A', 'B', 'A', 'C']\n", "}, index=[100, 200, 300, 400])\n", "\n", "# 1. Display the DataFrame\n", "print(\"Original DataFrame:\")\n", "print(idx_df)\n", "print()\n", "\n", "# 2. Reset index\n", "idx_df = idx_df.reset_index(drop=True)\n", "print(\"After reset_index:\")\n", "print(idx_df)\n", "print()\n", "\n", "# 3. Set Category as index\n", "idx_df = idx_df.set_index('Category')\n", "\n", "# 4. Display final DataFrame\n", "print(\"With Category as index:\")\n", "print(idx_df)\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Multi-level index\n", "\n", "- `pd.MultiIndex.from_arrays()`" ] }, { "cell_type": "code", "execution_count": 77, "metadata": { "tags": [] }, "outputs": [ { "data": { "text/plain": [ "MultiIndex([('G1', 1),\n", " ('G1', 2),\n", " ('G1', 3),\n", " ('G2', 1),\n", " ('G2', 2),\n", " ('G2', 3)],\n", " names=['Group', 'Num'])" ] }, "execution_count": 77, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Create a hierarchical index using MultiIndex.from_tuples()\n", "# Each tuple represents (outer_level, inner_level)\n", "outside = ['G1', 'G1', 'G1', 'G2', 'G2', 'G2']\n", "inside = [1, 2, 3, 1, 2, 3]\n", "hier_index = pd.MultiIndex.from_arrays([outside, inside], names=['Group', 'Num'])\n", "hier_index" ] }, { "cell_type": "code", "execution_count": 78, "metadata": { "collapsed": false, "jupyter": { "outputs_hidden": false }, "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", "
AB
GroupNum
G110.6738090.631114
2-1.576465-1.918862
3-0.841993-0.360288
G21-1.4916500.069742
2-0.0689390.778954
3-1.829980-0.842671
\n", "
" ], "text/plain": [ " A B\n", "Group Num \n", "G1 1 0.673809 0.631114\n", " 2 -1.576465 -1.918862\n", " 3 -0.841993 -0.360288\n", "G2 1 -1.491650 0.069742\n", " 2 -0.068939 0.778954\n", " 3 -1.829980 -0.842671" ] }, "execution_count": 78, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_multi = pd.DataFrame(data = np.random.randn(6,2), index=hier_index, columns=['A','B'])\n", "df_multi" ] }, { "cell_type": "code", "execution_count": 79, "metadata": { "collapsed": false, "jupyter": { "outputs_hidden": false }, "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", "
AB
Num
10.6738090.631114
2-1.576465-1.918862
3-0.841993-0.360288
\n", "
" ], "text/plain": [ " A B\n", "Num \n", "1 0.673809 0.631114\n", "2 -1.576465 -1.918862\n", "3 -0.841993 -0.360288" ] }, "execution_count": 79, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_multi.loc['G1']" ] }, { "cell_type": "code", "execution_count": 80, "metadata": { "tags": [] }, "outputs": [ { "data": { "text/plain": [ "np.float64(0.7789536552404416)" ] }, "execution_count": 80, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_multi.loc['G2'].loc[2]['B'] ### this 2 is really 2" ] }, { "cell_type": "code", "execution_count": 81, "metadata": { "collapsed": false, "jupyter": { "outputs_hidden": false }, "tags": [] }, "outputs": [ { "data": { "text/plain": [ "FrozenList(['Group', 'Num'])" ] }, "execution_count": 81, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_multi.index.names" ] }, { "cell_type": "code", "execution_count": 82, "metadata": { "collapsed": false, "jupyter": { "outputs_hidden": false }, "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", "
AB
GroupNum
G110.6738090.631114
2-1.576465-1.918862
3-0.841993-0.360288
G21-1.4916500.069742
2-0.0689390.778954
3-1.829980-0.842671
\n", "
" ], "text/plain": [ " A B\n", "Group Num \n", "G1 1 0.673809 0.631114\n", " 2 -1.576465 -1.918862\n", " 3 -0.841993 -0.360288\n", "G2 1 -1.491650 0.069742\n", " 2 -0.068939 0.778954\n", " 3 -1.829980 -0.842671" ] }, "execution_count": 82, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_multi" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The `.xs()` method (short for **cross-section**) is a convenient way to select rows or columns from a Pandas DataFrame, especially when working with **MultiIndex** objects. It works like a shortcut for slicing." ] }, { "cell_type": "code", "execution_count": 83, "metadata": { "collapsed": false, "jupyter": { "outputs_hidden": false }, "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", "
AB
Group
G10.6738090.631114
G2-1.4916500.069742
\n", "
" ], "text/plain": [ " A B\n", "Group \n", "G1 0.673809 0.631114\n", "G2 -1.491650 0.069742" ] }, "execution_count": 83, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_multi.xs(1, level='Num')" ] }, { "cell_type": "code", "execution_count": 84, "metadata": { "collapsed": false, "jupyter": { "outputs_hidden": false }, "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " A B\n", "Group \n", "G1 0.673809 0.631114\n", "G2 -1.491650 0.069742\n" ] } ], "source": [ "# Select all rows where Num = 1 (across all groups)\n", "print(df_multi.xs(1, level='Num'))" ] }, { "cell_type": "code", "execution_count": 85, "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " A B\n", "Group \n", "G1 -1.576465 -1.918862\n", "G2 -0.068939 0.778954\n" ] } ], "source": [ "print(df_multi.xs(2, level='Num'))" ] }, { "cell_type": "code", "execution_count": 86, "metadata": { "collapsed": false, "jupyter": { "outputs_hidden": false }, "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", "
AB
Num
10.6738090.631114
2-1.576465-1.918862
3-0.841993-0.360288
\n", "
" ], "text/plain": [ " A B\n", "Num \n", "1 0.673809 0.631114\n", "2 -1.576465 -1.918862\n", "3 -0.841993 -0.360288" ] }, "execution_count": 86, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Select all rows where Group = 'G1'\n", "df_multi.xs('G1', level='Group')" ] }, { "cell_type": "code", "execution_count": 87, "metadata": { "collapsed": false, "jupyter": { "outputs_hidden": false }, "tags": [] }, "outputs": [ { "data": { "text/plain": [ "A 0.673809\n", "B 0.631114\n", "Name: (G1, 1), dtype: float64" ] }, "execution_count": 87, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Select a specific (Group, Num) combination\n", "df_multi.xs(('G1', 1))" ] }, { "cell_type": "code", "execution_count": 88, "metadata": { "tags": [] }, "outputs": [ { "data": { "text/plain": [ "Group\n", "G1 0.673809\n", "G2 -1.491650\n", "Name: A, dtype: float64" ] }, "execution_count": 88, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Get Num=1 rows, only column A\n", "df_multi.xs(1, level='Num')['A']" ] }, { "cell_type": "code", "execution_count": 89, "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", "
AB
Group
G10.6738090.631114
G2-1.4916500.069742
\n", "
" ], "text/plain": [ " A B\n", "Group \n", "G1 0.673809 0.631114\n", "G2 -1.491650 0.069742" ] }, "execution_count": 89, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Get Num=1 rows, both A and B\n", "df_multi.xs(1, level='Num')[['A', 'B']]" ] }, { "cell_type": "code", "execution_count": 90, "metadata": { "tags": [ "thebe-interactive" ] }, "outputs": [], "source": [ "### EXERCISE: Working with MultiIndex\n", "#\n", "# Create a MultiIndex DataFrame:\n", "regions = ['North', 'North', 'North', 'South', 'South', 'South']\n", "quarters = ['Q1', 'Q2', 'Q3', 'Q1', 'Q2', 'Q3']\n", "multi_idx = pd.MultiIndex.from_arrays([regions, quarters], names=['Region', 'Quarter'])\n", "sales_multi = pd.DataFrame({\n", " 'Revenue': [100, 150, 120, 80, 90, 110],\n", " 'Costs': [60, 70, 65, 50, 55, 60]\n", "}, index=multi_idx)\n", "#\n", "# Tasks:\n", "# 1. Display the DataFrame\n", "# 2. Select all data for the 'North' region using .loc[]\n", "# 3. Select all Q1 data across both regions using .xs()\n", "# 4. Get the Revenue for South region, Q2\n", "#\n", "### Your code starts here:\n", "\n", "\n", "\n", "\n", "\n", "### Your code ends here.\n" ] }, { "cell_type": "code", "execution_count": 83, "metadata": { "tags": [ "hide-input" ] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "MultiIndex DataFrame:\n" ] }, { "ename": "NameError", "evalue": "name 'sales_multi' is not defined", "output_type": "error", "traceback": [ "\u001b[31m---------------------------------------------------------------------------\u001b[39m", "\u001b[31mNameError\u001b[39m Traceback (most recent call last)", "\u001b[36mCell\u001b[39m\u001b[36m \u001b[39m\u001b[32mIn[83]\u001b[39m\u001b[32m, line 5\u001b[39m\n\u001b[32m 1\u001b[39m \u001b[38;5;66;03m### Solution\u001b[39;00m\n\u001b[32m 2\u001b[39m \n\u001b[32m 3\u001b[39m \u001b[38;5;66;03m# 1. Display the DataFrame\u001b[39;00m\n\u001b[32m 4\u001b[39m \u001b[38;5;28mprint\u001b[39m(\u001b[33m\"\u001b[39m\u001b[33mMultiIndex DataFrame:\u001b[39m\u001b[33m\"\u001b[39m)\n\u001b[32m----> \u001b[39m\u001b[32m5\u001b[39m \u001b[38;5;28mprint\u001b[39m(\u001b[43msales_multi\u001b[49m)\n\u001b[32m 6\u001b[39m \u001b[38;5;28mprint\u001b[39m() \n\u001b[32m 7\u001b[39m \u001b[38;5;66;03m# 2. Select all data for the 'North' region using .loc[]\u001b[39;00m\n", "\u001b[31mNameError\u001b[39m: name 'sales_multi' is not defined" ] } ], "source": [ "### Solution\n", "\n", "# 1. Display the DataFrame\n", "print(\"MultiIndex DataFrame:\")\n", "print(sales_multi)\n", "print() \n", "# 2. Select all data for the 'North' region using .loc[]\n", "print(\"Data for North region:\")\n", "print(sales_multi.loc['North'])\n", "print()\n", "# 3. Select all Q1 data across both regions using .xs()\n", "print(\"Q1 data across all regions:\")\n", "print(sales_multi.xs('Q1', level='Quarter'))\n", "print()\n", "# 4. Get the Revenue for South region, Q2\n", "print(\"Revenue for South region, Q2:\")\n", "print(sales_multi.loc[('South', 'Q2'), 'Revenue'])\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] } ], "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": 4 }