{ "cells": [ { "cell_type": "markdown", "id": "5ed89a22-a129-49ac-9e9d-add54889edd7", "metadata": {}, "source": [ "# Handling Datasets\n", "\n", "After learning Python data structures (e.g., list, dictionary, tuple) and NumPy arrays, we are now ready to deal with data using Pandas. There are different file types used for datasets and we are going to learn how to import and export them in this section as part of data management using Pandas. " ] }, { "cell_type": "code", "execution_count": 2, "id": "1dda9599", "metadata": { "execution": { "iopub.execute_input": "2026-08-31T06:00:41.129361Z", "iopub.status.busy": "2026-08-31T06:00:41.129183Z", "iopub.status.idle": "2026-08-31T06:00:41.548921Z", "shell.execute_reply": "2026-08-31T06:00:41.548626Z" }, "tags": [ "hide-input", "thebe-init", "runnable" ] }, "outputs": [], "source": [ "# if you have not installed pandas through the command line\n", "# do so by opening a new command line tab/window, then\n", "# activate the virtual environment, and issue \"pip install pandas\"\n", "# or, you may do \"%pip install pandas\" in a Jupyter notebook.\n", "# do remember to comment out the installation line afterwards\n", "# so that it would not run every time you run the cell\n", "\n", "# we also need NumPy sometimes for processing data as it's the foundation of Pandas\n", "\n", "# %pip install pandas\n", "# %pip install numpy\n", "import numpy as np\n", "import pandas as pd" ] }, { "cell_type": "markdown", "id": "7092859e", "metadata": {}, "source": [ "```{contents}\n", ":local:\n", ":depth: 2\n", "```\n" ] }, { "cell_type": "markdown", "id": "2d5801d8", "metadata": {}, "source": [ "## Pandas I/O Functions\n", "\n", "Once you've set up your file paths, the next step is to actually read data into Pandas DataFrames. Pandas provides a rich collection of **input/output (I/O) functions** that can handle virtually any data format you'll encounter in data science work. These functions are designed to be intuitive and flexible, allowing you to read data from files, databases, web pages, and more with just a single line of code.\n", "\n", "The most commonly used function is `pd.read_csv()` for reading comma-separated values (CSV) files, which is the standard format for tabular data. However, Pandas also supports Excel files, JSON, HTML tables, SQL databases, and many other formats. Each `read_` function returns a DataFrame object that you can immediately work with using all of Pandas' powerful data manipulation capabilities.\n" ] }, { "cell_type": "markdown", "id": "a4ea3d41", "metadata": {}, "source": [ "### Common Pandas I/O Functions\n", "\n", "Pandas provides a comprehensive set of functions for reading and writing data in various formats. Below is a summary of the most commonly used functions:\n", "\n", "| Function | Description | Common Parameters | Returns |\n", "|----------|-------------|-------------------|---------|\n", "| `pd.read_csv()` | Read comma-separated values (CSV) file into DataFrame | `filepath`, `sep`, `header`, `index_col`, `usecols`, `dtype` | DataFrame |\n", "| `pd.read_excel()` | Read Excel file into DataFrame | `filepath`, `sheet_name`, `header`, `index_col`, `usecols` | DataFrame |\n", "| `pd.read_html()` | Read HTML tables into a list of DataFrames | `url`, `match`, `header`, `index_col` | List of DataFrames |\n", "| `pd.read_json()` | Read JSON string or file into DataFrame | `filepath`, `orient`, `typ`, `dtype` | DataFrame or Series |\n", "| `pd.read_sql()` | Read SQL query or database table into DataFrame | `sql`, `con`, `index_col`, `columns` | DataFrame |\n", "| `pd.read_sql_table()` | Read SQL database table into DataFrame | `table_name`, `con`, `schema`, `index_col` | DataFrame |\n", "| `pd.read_sql_query()` | Read SQL query into DataFrame | `sql`, `con`, `index_col`, `params` | DataFrame |\n", "\n", "**Output Functions:**\n", "\n", "| Function | Description | Common Parameters |\n", "|----------|-------------|-------------------|\n", "| `df.to_csv()` | Write DataFrame to CSV file | `filepath`, `sep`, `index`, `header`, `mode` |\n", "| `df.to_excel()` | Write DataFrame to Excel file | `filepath`, `sheet_name`, `index`, `header` |\n", "| `df.to_json()` | Write DataFrame to JSON file | `filepath`, `orient`, `indent` |\n", "| `df.to_sql()` | Write DataFrame to SQL database | `name`, `con`, `if_exists`, `index` |\n", "\n", "**Key Parameters:**\n", "- `filepath`: Path to the file (string or file-like object)\n", "- `sep`: Delimiter to use (CSV, default is comma)\n", "- `header`: Row number(s) to use as column names\n", "- `index_col`: Column(s) to use as row labels\n", "- `usecols`: Subset of columns to read\n", "- `dtype`: Data type for data or columns\n", "- `sheet_name`: Excel sheet name or index\n", "- `con`: Database connection object\n", "- `index`: Whether to write row names (index)\n" ] }, { "cell_type": "markdown", "id": "9c08444f", "metadata": {}, "source": [ "## Accessing Files\n", "\n", "You can also pass the path (full or relative) and file names of your data as arguments to the Pandas `read_` functions to directly read the file. However, it is recommended to use a path variable for your data directory, which will make your life easier in the long run. " ] }, { "cell_type": "markdown", "id": "d9d68916", "metadata": {}, "source": [ "To access your data folder in Jupyter Notebook environment, you can either use a full path or a relative path.\n", "\n", "1. **Full path**:\n", " - **macOS** (and Linux): A full/absolute path starts with \"`/`\" (the \"root\" directory); e.g., `data_path = \"/Users/`/`workspace/dsm/data/\"`.\n", "\n", " - **Windows**: A full path includes a drive letter and backslashes (`\\`) for path separator; e.g. `data_path = \"C:\\Users\\\\workspace\\dsm\\data\\`\") although forward slashes usually work as well.\n", "\n", "2. **Relative path**: Usually it's just easier to use a relative path to access the data folders nearby (e.g., `data_path = ../../data/`).\n", "\n", "3. **User home directory**: Instead of starting from the system root level, you can usually use your user home directory as the starting point to reach your data file directory. Python `os.path.expanduser('~')` function resolves the directory passed to it (e.g., `~`, representing the home directory) to a full path. " ] }, { "cell_type": "markdown", "id": "4b4b080a", "metadata": {}, "source": [ "### Building Path Variable" ] }, { "cell_type": "markdown", "id": "cc84908b", "metadata": {}, "source": [ "To access your dataset files using the Pandas `read_` functions, the path variable and the `path.join` function can be used together to pass the path/file as arguments:" ] }, { "cell_type": "code", "execution_count": 3, "id": "86d30269", "metadata": { "execution": { "iopub.execute_input": "2026-08-31T06:00:41.550453Z", "iopub.status.busy": "2026-08-31T06:00:41.550346Z", "iopub.status.idle": "2026-08-31T06:00:41.562285Z", "shell.execute_reply": "2026-08-31T06:00:41.562072Z" }, "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "../../data/\n" ] }, { "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
060110130409.1
160117145479.0
260103135340.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" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "### works on macOS, Linux, and Windows\n", "import os\n", "import pandas as pd\n", "\n", "### works in your local computer (home directory)\n", "# home_dir = os.path.expanduser('~') # get the user's home directory \n", "# data_path = os.path.expanduser(home_dir + '/workspace/dsm/data/') ### concatenate project data path\n", "\n", "### works in the online book (server path)\n", "data_path = '../../data/' ### use relative path to the data folder in the project directory\n", "\n", "print(data_path) ### output will vary based on your system and project data path\n", "\n", "df = pd.read_csv(data_path + \"data.csv\") ### read the CSV file into a DataFrame\n", "df.head(3) ### display the first 3 rows of the DataFrame ==> later" ] }, { "cell_type": "markdown", "id": "2d199684", "metadata": {}, "source": [ "\n", "### Accessing Dataset Files\n", "\n", "After you have your path variable (e.g. `data_path`) to your data directory, you join the path variable with the paths/filenames using `os.path.join()` to access the dataset file. It can take multiple arguments like `os.path.join(\"folder1\", \"folder2\", \"file.txt\")`. This way you don't have to worry about the path separator (\"`/`\" or \"`\\`\").\n", "\n", "**In most cases, just use relative path (see below).\n" ] }, { "cell_type": "code", "execution_count": 35, "id": "69297765", "metadata": { "execution": { "iopub.execute_input": "2026-08-31T06:00:41.563342Z", "iopub.status.busy": "2026-08-31T06:00:41.563280Z", "iopub.status.idle": "2026-08-31T06:00:41.566434Z", "shell.execute_reply": "2026-08-31T06:00:41.566264Z" }, "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "/Users/tcn85/workspace/dsm/data/data.csv\n" ] }, { "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
060110130409.1
160117145479.0
260103135340.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" ] }, "execution_count": 35, "metadata": {}, "output_type": "execute_result" } ], "source": [ "### or using os.path.join(path variable, filename)\n", "\n", "data_file = os.path.join(data_path, \"data.csv\")\n", "print(data_file) ### output will vary based on your system and project data path\n", "\n", "df = pd.read_csv(data_file)\n", "df.head(3)" ] }, { "cell_type": "code", "execution_count": null, "id": "ec01366a", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "'/Users/tcn85/workspace/dsm/data'" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "### just to show the path variables we used in the book\n", "### which is not locally available because the username is from the web server\n", "### when you run this code in your local computer, you need to change the path to your own project data path\n", "### and the username in the path will be different\n", "\n", "data_path = os.path.expanduser('~/workspace/dsm/data')\n", "data_path" ] }, { "cell_type": "code", "execution_count": 5, "id": "94e9c76b", "metadata": { "execution": { "iopub.execute_input": "2026-08-31T06:00:41.567328Z", "iopub.status.busy": "2026-08-31T06:00:41.567272Z", "iopub.status.idle": "2026-08-31T06:00:41.568701Z", "shell.execute_reply": "2026-08-31T06:00:41.568512Z" }, "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "data file once: ../../data/data.csv\n" ] } ], "source": [ "### join the path and the filename\n", "data_file = os.path.join(data_path, \"data.csv\")\n", "print(\"data file once: \", data_file) # print the full path to the data file" ] }, { "cell_type": "code", "execution_count": 6, "id": "d3f4a2b1", "metadata": { "execution": { "iopub.execute_input": "2026-08-31T06:00:41.569771Z", "iopub.status.busy": "2026-08-31T06:00:41.569712Z", "iopub.status.idle": "2026-08-31T06:00:41.571590Z", "shell.execute_reply": "2026-08-31T06:00:41.571267Z" }, "tags": [] }, "outputs": [ { "data": { "text/plain": [ "'../../data/data.csv'" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "data_file = os.path.join(data_path, 'data.csv' )\n", "data_file" ] }, { "cell_type": "markdown", "id": "93468f95", "metadata": {}, "source": [ "### Using Relative Paths\n", "\n", "To use a relative path to access a dataset file in another folder (e.g., \"data\"), you need to know the relative location of the data folder and usually we use the special path character **\\** (**/** for macOS) and **`..`** to build the path. **..** means **move up one level in the directory tree**. For example, to access the **data** directory (see figure below): \n", "\n", "- If you are working on the `YOU-ARE-HERE.ipynb` notebook , you need to go up one level (**`..`**) above the notebooks directory, then go down into the `data` directory:\n", "\n", " `data_path = pd.read_csv('../data/')`\n", "\n", "- If you are working on the `YOU-MAY-BE-HERE.ipynb` notebook, you need to move up two levels (`folder` and `notebooks`, or **../..**), then go down into the `data` directory:\n", " `data_path = pd.read_csv('../../data/')`" ] }, { "cell_type": "markdown", "id": "c47543f5", "metadata": {}, "source": [ "To visualize your project folder, you may use the `tree` command in the command line.\n", "\n", "````{tab-set}\n", "```{tab-item} Windows\n", "\n", ":::{code-block} text\n", "(.venv) PS C:\\Users\\[user]\\workspace\\dsm> tree\n", "Folder PATH listing\n", "Volume serial number is 420C-3FF4\n", "C:.\n", "├───.ipynb_checkpoints\n", "├───.venv\n", "├── data\n", "│ ├── data.csv\n", "│ ├── example\n", "│ └── wine.csv\n", "├── notebooks\n", "│ ├── YOU-ARE-HERE.ipynb\n", "│ └── folder\n", "│ └── YOU-MAY-BE-HERE.ipynb\n", ":::\n", "```\n", "\n", "```{tab-item} macOS\n", "\n", ":::{code-block} text\n", "[user]@[host]:~/workspace/dsm$ tree\n", ".\n", "├── data\n", "│ ├── data.csv\n", "│ ├── example\n", "│ └── wine.csv\n", "├── notebooks\n", "│ ├── YOU-ARE-HERE.ipynb\n", "│ └── folder\n", "│ └── YOU-MAY-BE-HERE.ipynb\n", ":::\n", "```\n", "````\n" ] }, { "cell_type": "markdown", "id": "c49fa8b0", "metadata": {}, "source": [ "You can see that a relative path can be easier to setup for accessing data if you know the relative position of the data directory to your current working directory (CWD). Often time we even use it in an ad hoc manner without building a folder path variable but accessing the datasets directly:" ] }, { "cell_type": "code", "execution_count": 7, "id": "0527e441", "metadata": { "execution": { "iopub.execute_input": "2026-08-31T06:00:41.572960Z", "iopub.status.busy": "2026-08-31T06:00:41.572905Z", "iopub.status.idle": "2026-08-31T06:00:41.575231Z", "shell.execute_reply": "2026-08-31T06:00:41.575009Z" }, "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": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "### You need to adjust the path depth (i.e., how many \"..\" you need depending on where you \n", "### run the code. If you run the code in the same directory as the data file, then \n", "### you can just use \"data.csv\" as the path.If you run the code in a sub-subdirectory, then you need to \n", "### use \"../../data/data.csv\" to go up two levels and then into the data directory, and so on.) \n", "### ./data/data.csv\n", "### ../data/data.csv\n", "### ../../data/data.csv\n", "### ../../../data/data.csv\n", "\n", "# data_file = (\"../../data/data.csv\")\n", "df = pd.read_csv(\"../../data/data.csv\") ### df = dataframe ==> later\n", "df" ] }, { "cell_type": "markdown", "id": "1d30f4e8", "metadata": {}, "source": [ "## Data Input and Output\n", "\n", "This section serves as the reference code for obtaining input and output with Pandas. Pandas can read a variety of file types using its **pd.read_** methods. Here we cover the most common data types:\n", "\n", "- CSV\n", "- Excel\n", "- HTML\n", "- SQL" ] }, { "cell_type": "markdown", "id": "6a401364", "metadata": {}, "source": [ "### CSV\n", "\n", "#### CSV Input" ] }, { "cell_type": "markdown", "id": "a995ca86", "metadata": {}, "source": [ "### `pd.read_csv()`\n", "\n", "Now let us read the dataset file (`data_file`) using Pandas `pd.read_csv()` function. You may read the data file directly or open it as a file and save it to a variable so you can manipulate the data.\n" ] }, { "cell_type": "code", "execution_count": null, "id": "61b7f09d", "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", "
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" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "# home = os.path.expanduser('~')\n", "# data_path = os.path.join(home, 'workspace', 'dsm', 'data'). ###, or\n", "# data_path = os.path.join(home, '/workspace/dsm/data/') ### cross-platform\n", "# data_file = os.path.join(data_path, 'data.csv')\n", "\n", "### quotation marks are used to denote a string (file path here). \n", "df = pd.read_csv(\"../../data/data.csv\") ### read the csv file into a DataFrame\n", "df.head() ### display the first 5 rows of the DataFrame\n", "\n", "### note that you may need to change the path below to point to the correct location of the example file\n", "\n", "\n", "### open a file and read its content in Python\n", "# with open(\"../../data/data.csv\") as file:\n", "# content = file.read()\n", "# print(content)\n" ] }, { "cell_type": "markdown", "id": "4a76d7ff", "metadata": {}, "source": [ "#### CSV Output\n", "\n", "You can use Pandas I/O functions such as `df.to_csv()` to output file to `.csv` format. You will need to specify the path, otherwise the file will be in the same folder as the environment the code is run, i.g., where this notebook is. " ] }, { "cell_type": "code", "execution_count": 34, "id": "48d13442", "metadata": { "execution": { "iopub.execute_input": "2026-08-31T06:00:41.591715Z", "iopub.status.busy": "2026-08-31T06:00:41.591651Z", "iopub.status.idle": "2026-08-31T06:00:41.596747Z", "shell.execute_reply": "2026-08-31T06:00:41.596485Z" }, "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "/var/folders/g4/v24tl8t172g5d7rzsd63y51w0000gp/T/out-data.csv\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Bank NameCityStateCertAcquiring InstitutionClosing DateFund Sort ascending
0Tioga-Franklin Savings BankPhiladelphiaPennsylvania33802Second Federal Savings and Loan Association of...August 21, 202610554
1Small Business BankLenexaKansas25744The Farmers State Bank of Oakley, KansasJuly 17, 202610553
2Kentland Federal Savings and Loan AssociationKentlandIndiana28722Kentland BankJuly 10, 202610552
3Community Bank and Trust - West GeorgiaLaGrangeGeorgia25796Anchor BankMay 1, 202610551
4Metropolitan Capital Bank & TrustChicagoIllinois57488First Independence BankJanuary 30, 202610550
5The Santa Anna National BankSanta AnnaTexas5520Coleman County State BankJune 27, 202510549
6Pulaski Savings BankChicagoIllinois28611Millennium BankJanuary 17, 202510548
7The First National Bank of LindsayLindsayOklahoma4134First Bank & Trust Co.October 18, 202410547
8Republic First Bank dba Republic BankPhiladelphiaPennsylvania27332Fulton Bank, National AssociationApril 26, 202410546
9Citizens BankSac CityIowa8758Iowa Trust & Savings BankNovember 3, 202310545
10Heartland Tri-State BankElkhartKansas25851Dream First Bank, N.A.July 28, 202310544
11First Republic BankSan FranciscoCalifornia59017JPMorgan Chase Bank, N.A.May 1, 202310543
12Signature BankNew YorkNew York57053Flagstar Bank, N.A.March 12, 202310540
13Silicon Valley BankSanta ClaraCalifornia24735First–Citizens Bank & Trust CompanyMarch 10, 202310539
14Almena State BankAlmenaKansas15426Equity BankOctober 23, 202010538
15First City Bank of FloridaFort Walton BeachFlorida16748United Fidelity Bank, fsbOctober 16, 202010537
16The First State BankBarboursvilleWest Virginia14361MVB Bank, Inc.April 3, 202010536
17Ericson State BankEricsonNebraska18265Farmers and Merchants BankFebruary 14, 202010535
18City National Bank of New JerseyNewarkNew Jersey21111Industrial BankNovember 1, 201910534
19Resolute BankMaumeeOhio58317Buckeye State BankOctober 25, 201910533
20Louisa Community BankLouisaKentucky58112Kentucky Farmers Bank CorporationOctober 25, 201910532
21The Enloe State BankCooperTexas10716Legend Bank, N. A.May 31, 201910531
22Washington Federal Bank for SavingsChicagoIllinois30570Royal Savings BankDecember 15, 201710530
23The Farmers and Merchants State Bank of ArgoniaArgoniaKansas17719Conway BankOctober 13, 201710529
24Fayette County BankSaint ElmoIllinois1802United Fidelity Bank, fsbMay 26, 201710528
\n", "
" ], "text/plain": [ " Bank Name City \\\n", "0 Tioga-Franklin Savings Bank Philadelphia \n", "1 Small Business Bank Lenexa \n", "2 Kentland Federal Savings and Loan Association Kentland \n", "3 Community Bank and Trust - West Georgia LaGrange \n", "4 Metropolitan Capital Bank & Trust Chicago \n", "5 The Santa Anna National Bank Santa Anna \n", "6 Pulaski Savings Bank Chicago \n", "7 The First National Bank of Lindsay Lindsay \n", "8 Republic First Bank dba Republic Bank Philadelphia \n", "9 Citizens Bank Sac City \n", "10 Heartland Tri-State Bank Elkhart \n", "11 First Republic Bank San Francisco \n", "12 Signature Bank New York \n", "13 Silicon Valley Bank Santa Clara \n", "14 Almena State Bank Almena \n", "15 First City Bank of Florida Fort Walton Beach \n", "16 The First State Bank Barboursville \n", "17 Ericson State Bank Ericson \n", "18 City National Bank of New Jersey Newark \n", "19 Resolute Bank Maumee \n", "20 Louisa Community Bank Louisa \n", "21 The Enloe State Bank Cooper \n", "22 Washington Federal Bank for Savings Chicago \n", "23 The Farmers and Merchants State Bank of Argonia Argonia \n", "24 Fayette County Bank Saint Elmo \n", "\n", " State Cert Acquiring Institution \\\n", "0 Pennsylvania 33802 Second Federal Savings and Loan Association of... \n", "1 Kansas 25744 The Farmers State Bank of Oakley, Kansas \n", "2 Indiana 28722 Kentland Bank \n", "3 Georgia 25796 Anchor Bank \n", "4 Illinois 57488 First Independence Bank \n", "5 Texas 5520 Coleman County State Bank \n", "6 Illinois 28611 Millennium Bank \n", "7 Oklahoma 4134 First Bank & Trust Co. \n", "8 Pennsylvania 27332 Fulton Bank, National Association \n", "9 Iowa 8758 Iowa Trust & Savings Bank \n", "10 Kansas 25851 Dream First Bank, N.A. \n", "11 California 59017 JPMorgan Chase Bank, N.A. \n", "12 New York 57053 Flagstar Bank, N.A. \n", "13 California 24735 First–Citizens Bank & Trust Company \n", "14 Kansas 15426 Equity Bank \n", "15 Florida 16748 United Fidelity Bank, fsb \n", "16 West Virginia 14361 MVB Bank, Inc. \n", "17 Nebraska 18265 Farmers and Merchants Bank \n", "18 New Jersey 21111 Industrial Bank \n", "19 Ohio 58317 Buckeye State Bank \n", "20 Kentucky 58112 Kentucky Farmers Bank Corporation \n", "21 Texas 10716 Legend Bank, N. A. \n", "22 Illinois 30570 Royal Savings Bank \n", "23 Kansas 17719 Conway Bank \n", "24 Illinois 1802 United Fidelity Bank, fsb \n", "\n", " Closing Date Fund Sort ascending \n", "0 August 21, 2026 10554 \n", "1 July 17, 2026 10553 \n", "2 July 10, 2026 10552 \n", "3 May 1, 2026 10551 \n", "4 January 30, 2026 10550 \n", "5 June 27, 2025 10549 \n", "6 January 17, 2025 10548 \n", "7 October 18, 2024 10547 \n", "8 April 26, 2024 10546 \n", "9 November 3, 2023 10545 \n", "10 July 28, 2023 10544 \n", "11 May 1, 2023 10543 \n", "12 March 12, 2023 10540 \n", "13 March 10, 2023 10539 \n", "14 October 23, 2020 10538 \n", "15 October 16, 2020 10537 \n", "16 April 3, 2020 10536 \n", "17 February 14, 2020 10535 \n", "18 November 1, 2019 10534 \n", "19 October 25, 2019 10533 \n", "20 October 25, 2019 10532 \n", "21 May 31, 2019 10531 \n", "22 December 15, 2017 10530 \n", "23 October 13, 2017 10529 \n", "24 May 26, 2017 10528 " ] }, "execution_count": 34, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import tempfile\n", "from pathlib import Path\n", "\n", "output_file = Path(tempfile.gettempdir()) / 'out-data.csv'\n", "df.to_csv(output_file, index=False)\n", "print(output_file)\n", "df" ] }, { "cell_type": "markdown", "id": "c06c50c1", "metadata": {}, "source": [ "### Excel\n", "Pandas can read and write excel files, keep in mind, this only imports data. Not formulas or images, having images or macros may cause this read_excel method to crash. " ] }, { "cell_type": "markdown", "id": "2a92f89f", "metadata": {}, "source": [ "#### Excel Input\n", "\n", "- If you see the error message:\n", "\n", " ImportError: Missing optional dependency 'openpyxl'. Use pip or conda to install openpyxl.\n", "\n", " Install the dependency by issuing `%pip install openpyxl`" ] }, { "cell_type": "code", "execution_count": 12, "id": "47775dbf", "metadata": { "execution": { "iopub.execute_input": "2026-08-31T06:00:41.597926Z", "iopub.status.busy": "2026-08-31T06:00:41.597862Z", "iopub.status.idle": "2026-08-31T06:00:41.599234Z", "shell.execute_reply": "2026-08-31T06:00:41.599022Z" }, "tags": [] }, "outputs": [], "source": [ "### install dependencies if needed\n", "# %pip install openpyxl" ] }, { "cell_type": "code", "execution_count": 13, "id": "9edae929", "metadata": { "execution": { "iopub.execute_input": "2026-08-31T06:00:41.600359Z", "iopub.status.busy": "2026-08-31T06:00:41.600283Z", "iopub.status.idle": "2026-08-31T06:00:41.680599Z", "shell.execute_reply": "2026-08-31T06:00:41.680376Z" }, "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Unnamed: 0.14Unnamed: 0.13Unnamed: 0.12Unnamed: 0.11Unnamed: 0.10Unnamed: 0.9Unnamed: 0.8Unnamed: 0.7Unnamed: 0.6Unnamed: 0.5Unnamed: 0.4Unnamed: 0.3Unnamed: 0.2Unnamed: 0.1Unnamed: 0abcd
00000000000000000123
11111111111111114567
2222222222222222891011
333333333333333312131415
\n", "
" ], "text/plain": [ " Unnamed: 0.14 Unnamed: 0.13 Unnamed: 0.12 Unnamed: 0.11 Unnamed: 0.10 \\\n", "0 0 0 0 0 0 \n", "1 1 1 1 1 1 \n", "2 2 2 2 2 2 \n", "3 3 3 3 3 3 \n", "\n", " Unnamed: 0.9 Unnamed: 0.8 Unnamed: 0.7 Unnamed: 0.6 Unnamed: 0.5 \\\n", "0 0 0 0 0 0 \n", "1 1 1 1 1 1 \n", "2 2 2 2 2 2 \n", "3 3 3 3 3 3 \n", "\n", " Unnamed: 0.4 Unnamed: 0.3 Unnamed: 0.2 Unnamed: 0.1 Unnamed: 0 a b \\\n", "0 0 0 0 0 0 0 1 \n", "1 1 1 1 1 1 4 5 \n", "2 2 2 2 2 2 8 9 \n", "3 3 3 3 3 3 12 13 \n", "\n", " c d \n", "0 2 3 \n", "1 6 7 \n", "2 10 11 \n", "3 14 15 " ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "### read Excel file\n", "df = pd.read_excel('../../data/Excel_Sample.xlsx', sheet_name='Sheet1')\n", "df" ] }, { "cell_type": "code", "execution_count": 14, "id": "0516b4ba", "metadata": { "execution": { "iopub.execute_input": "2026-08-31T06:00:41.681668Z", "iopub.status.busy": "2026-08-31T06:00:41.681574Z", "iopub.status.idle": "2026-08-31T06:00:41.683272Z", "shell.execute_reply": "2026-08-31T06:00:41.683085Z" }, "tags": [] }, "outputs": [ { "data": { "text/plain": [ "pandas.core.frame.DataFrame" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "type(df)" ] }, { "cell_type": "markdown", "id": "274a421c", "metadata": {}, "source": [ "#### Excel Output" ] }, { "cell_type": "code", "execution_count": 15, "id": "73fc4e09", "metadata": { "execution": { "iopub.execute_input": "2026-08-31T06:00:41.684218Z", "iopub.status.busy": "2026-08-31T06:00:41.684161Z", "iopub.status.idle": "2026-08-31T06:00:41.692177Z", "shell.execute_reply": "2026-08-31T06:00:41.691947Z" }, "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "/var/folders/g4/v24tl8t172g5d7rzsd63y51w0000gp/T/dsm-ch04-Excel_Sample.xlsx\n" ] } ], "source": [ "import tempfile\n", "from pathlib import Path\n", "\n", "output_file = Path(tempfile.gettempdir()) / 'dsm-ch04-Excel_Sample.xlsx'\n", "df.to_excel(output_file, sheet_name='Sheet1')\n", "print(output_file)" ] }, { "cell_type": "markdown", "id": "8059ae26", "metadata": {}, "source": [ "### HTML\n", "\n", "You may need to install htmllib5,lxml, and BeautifulSoup4. In your terminal/command prompt run:\n", "\n", " conda install lxml\n", " conda install html5lib\n", " conda install BeautifulSoup4\n", "\n", "Then restart Jupyter Notebook.\n", "(or use pip install if you aren't using the Anaconda Distribution)\n", "\n", "Pandas can read table tabs off of html. For example:" ] }, { "cell_type": "markdown", "id": "700d31a6", "metadata": {}, "source": [ "#### HTML Input\n", "\n", "- If you see the error message:\n", "\n", " `ImportError: Missing optional dependency 'lxml'. Use pip or conda to install lxml.`\n", "\n", " Install the dependency: `%pip install lxml`\n", "\n", "- **macOS users** do this to get rid of a certificate error (4 steps in the (.venv) lines below; [source](https://stackoverflow.com/a/70495761/2914652)): \n", " \n", "```bash\n", "(.venv) [user]@[host]ː~/workspace/dsm$ cd /Applications/Python 3.12/\n", "(.venv) [user]@[host]ː/Applications/Python 3.12$ ls\n", " Icon? Install Certificates.command Python Documentation.html ReadMe.rtf\n", " IDLE.app License.rtf Python Launcher.app Update Shell Profile.command\n", "(.venv) [user]@[host]ː/Applications/Python 3.12$ ./Install\\ Certificates.command \n", " -- pip install --upgrade certifi\n", " Collecting certifi\n", " Using cached certifi-2025.8.3-py3-none-any.whl.metadata (2.4 kB)\n", " Using cached certifi-2025.8.3-py3-none-any.whl (161 kB)\n", " Installing collected packages: certifi\n", " Successfully installed certifi-2025.8.3\n", "(.venv) [user]@[host]ː/Applications/Python 3.12$ cd ~/workspace/dsm\n", "```\n", "\n", "Pandas `read_html` function will read tables off of a webpage and return a list of DataFrame objects:" ] }, { "cell_type": "code", "execution_count": 16, "id": "16612b63", "metadata": { "execution": { "iopub.execute_input": "2026-08-31T06:00:41.693240Z", "iopub.status.busy": "2026-08-31T06:00:41.693173Z", "iopub.status.idle": "2026-08-31T06:00:41.694373Z", "shell.execute_reply": "2026-08-31T06:00:41.694149Z" }, "tags": [] }, "outputs": [], "source": [ "##### install dependencies if needed\n", "# %pip install lxml\n", "# %pip install pandas" ] }, { "cell_type": "code", "execution_count": 17, "id": "958e1c52", "metadata": { "execution": { "iopub.execute_input": "2026-08-31T06:00:41.695206Z", "iopub.status.busy": "2026-08-31T06:00:41.695150Z", "iopub.status.idle": "2026-08-31T06:00:42.440607Z", "shell.execute_reply": "2026-08-31T06:00:42.439995Z" }, "tags": [ "skip-execution" ] }, "outputs": [], "source": [ "### read a table straight from a web page (needs an internet connection)\n", "import pandas as pd\n", "df = pd.read_html('https://www.fdic.gov/bank-failures/failed-bank-list')" ] }, { "cell_type": "markdown", "id": "e7654a53", "metadata": {}, "source": [ "`pd.read_html()` returns a **list** of DataFrames, one for each `` on the page. Web pages change and go offline, so for the rest of this section we read a copy of the FDIC page saved in the `data` folder. `read_html()` works the same way on a local file:" ] }, { "cell_type": "code", "execution_count": null, "id": "3452d569", "metadata": {}, "outputs": [], "source": [ "### read the saved copy of the page\n", "df = pd.read_html('../../data/fdic_failed_banks.html')\n", "len(df) ### number of tables found on the page" ] }, { "cell_type": "code", "execution_count": 18, "id": "2aa79c0a", "metadata": { "execution": { "iopub.execute_input": "2026-08-31T06:00:42.443423Z", "iopub.status.busy": "2026-08-31T06:00:42.443223Z", "iopub.status.idle": "2026-08-31T06:00:42.450760Z", "shell.execute_reply": "2026-08-31T06:00:42.450312Z" }, "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", "
Bank NameCityStateCertAcquiring InstitutionClosing DateFund Sort ascending
0Tioga-Franklin Savings BankPhiladelphiaPennsylvania33802Second Federal Savings and Loan Association of...August 21, 202610554
1Small Business BankLenexaKansas25744The Farmers State Bank of Oakley, KansasJuly 17, 202610553
2Kentland Federal Savings and Loan AssociationKentlandIndiana28722Kentland BankJuly 10, 202610552
3Community Bank and Trust - West GeorgiaLaGrangeGeorgia25796Anchor BankMay 1, 202610551
4Metropolitan Capital Bank & TrustChicagoIllinois57488First Independence BankJanuary 30, 202610550
\n", "" ], "text/plain": [ " Bank Name City State \\\n", "0 Tioga-Franklin Savings Bank Philadelphia Pennsylvania \n", "1 Small Business Bank Lenexa Kansas \n", "2 Kentland Federal Savings and Loan Association Kentland Indiana \n", "3 Community Bank and Trust - West Georgia LaGrange Georgia \n", "4 Metropolitan Capital Bank & Trust Chicago Illinois \n", "\n", " Cert Acquiring Institution Closing Date \\\n", "0 33802 Second Federal Savings and Loan Association of... August 21, 2026 \n", "1 25744 The Farmers State Bank of Oakley, Kansas July 17, 2026 \n", "2 28722 Kentland Bank July 10, 2026 \n", "3 25796 Anchor Bank May 1, 2026 \n", "4 57488 First Independence Bank January 30, 2026 \n", "\n", " Fund Sort ascending \n", "0 10554 \n", "1 10553 \n", "2 10552 \n", "3 10551 \n", "4 10550 " ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df[0].head()" ] }, { "cell_type": "markdown", "id": "0982f95e", "metadata": {}, "source": [ "### SQL " ] }, { "cell_type": "markdown", "id": "549a5a79", "metadata": {}, "source": [ "([source](https://www.reddit.com/r/learnpython/comments/hgrekl/need_help_use_sql_on_a_dataframe_created_through/)) The pandas.io.sql module provides a collection of query wrappers to both facilitate data retrieval and to reduce dependency on DB-specific API. Database abstraction is provided by SQLAlchemy if installed. In addition you will need a driver library for your database. Examples of such drivers are psycopg2 for PostgreSQL or pymysql for MySQL. For SQLite this is included in Python’s standard library by default. You can find an overview of supported drivers for each SQL dialect in the SQLAlchemy docs.\n", "\n", "\n", "If SQLAlchemy is not installed, a fallback is only provided for sqlite (and for mysql for backwards compatibility, but this is deprecated and will be removed in a future version). This mode requires a Python database adapter which respect the Python DB-API.\n", "\n", "See also some cookbook examples for some advanced strategies.\n", "\n", "The key functions are:\n", "\n", "* read_sql_table(table_name, con[, schema, ...])\t\n", " * Read SQL database table into a DataFrame.\n", "* read_sql_query(sql, con[, index_col, ...])\t\n", " * Read SQL query into a DataFrame.\n", "* read_sql(sql, con[, index_col, ...])\t\n", " * Read SQL query or database table into a DataFrame.\n", "* DataFrame.to_sql(name, con[, flavor, ...])\t\n", " * Write records stored in a DataFrame to a SQL database." ] }, { "cell_type": "code", "execution_count": 19, "id": "ebcf4e1f", "metadata": { "execution": { "iopub.execute_input": "2026-08-31T06:00:42.452660Z", "iopub.status.busy": "2026-08-31T06:00:42.452508Z", "iopub.status.idle": "2026-08-31T06:00:42.559708Z", "shell.execute_reply": "2026-08-31T06:00:42.559390Z" }, "tags": [] }, "outputs": [], "source": [ "# %pip install SQLAlchemy\n", "from sqlalchemy import create_engine" ] }, { "cell_type": "code", "execution_count": 20, "id": "d6fba0db", "metadata": { "execution": { "iopub.execute_input": "2026-08-31T06:00:42.560891Z", "iopub.status.busy": "2026-08-31T06:00:42.560818Z", "iopub.status.idle": "2026-08-31T06:00:42.566650Z", "shell.execute_reply": "2026-08-31T06:00:42.566452Z" }, "tags": [] }, "outputs": [], "source": [ "engine = create_engine('sqlite:///:memory:')" ] }, { "cell_type": "code", "execution_count": 21, "id": "c826a24b", "metadata": { "execution": { "iopub.execute_input": "2026-08-31T06:00:42.567595Z", "iopub.status.busy": "2026-08-31T06:00:42.567535Z", "iopub.status.idle": "2026-08-31T06:00:42.568899Z", "shell.execute_reply": "2026-08-31T06:00:42.568734Z" }, "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n" ] } ], "source": [ "print(type(df))\n", "df = pd.DataFrame(df[0])" ] }, { "cell_type": "code", "execution_count": 22, "id": "90af4a3f", "metadata": { "execution": { "iopub.execute_input": "2026-08-31T06:00:42.569746Z", "iopub.status.busy": "2026-08-31T06:00:42.569695Z", "iopub.status.idle": "2026-08-31T06:00:42.573543Z", "shell.execute_reply": "2026-08-31T06:00:42.573377Z" }, "tags": [] }, "outputs": [ { "data": { "text/plain": [ "25" ] }, "execution_count": 22, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.to_sql('data', engine)" ] }, { "cell_type": "code", "execution_count": 23, "id": "26712b24", "metadata": { "execution": { "iopub.execute_input": "2026-08-31T06:00:42.574418Z", "iopub.status.busy": "2026-08-31T06:00:42.574360Z", "iopub.status.idle": "2026-08-31T06:00:42.578553Z", "shell.execute_reply": "2026-08-31T06:00:42.578362Z" }, "tags": [] }, "outputs": [], "source": [ "sql_df = pd.read_sql('data',con=engine)" ] }, { "cell_type": "code", "execution_count": 24, "id": "e5021f3b", "metadata": { "execution": { "iopub.execute_input": "2026-08-31T06:00:42.579562Z", "iopub.status.busy": "2026-08-31T06:00:42.579509Z", "iopub.status.idle": "2026-08-31T06:00:42.582147Z", "shell.execute_reply": "2026-08-31T06:00:42.581980Z" }, "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", "
indexBank NameCityStateCertAcquiring InstitutionClosing DateFund Sort ascending
00Tioga-Franklin Savings BankPhiladelphiaPennsylvania33802Second Federal Savings and Loan Association of...August 21, 202610554
11Small Business BankLenexaKansas25744The Farmers State Bank of Oakley, KansasJuly 17, 202610553
22Kentland Federal Savings and Loan AssociationKentlandIndiana28722Kentland BankJuly 10, 202610552
33Community Bank and Trust - West GeorgiaLaGrangeGeorgia25796Anchor BankMay 1, 202610551
44Metropolitan Capital Bank & TrustChicagoIllinois57488First Independence BankJanuary 30, 202610550
\n", "
" ], "text/plain": [ " index Bank Name City \\\n", "0 0 Tioga-Franklin Savings Bank Philadelphia \n", "1 1 Small Business Bank Lenexa \n", "2 2 Kentland Federal Savings and Loan Association Kentland \n", "3 3 Community Bank and Trust - West Georgia LaGrange \n", "4 4 Metropolitan Capital Bank & Trust Chicago \n", "\n", " State Cert Acquiring Institution \\\n", "0 Pennsylvania 33802 Second Federal Savings and Loan Association of... \n", "1 Kansas 25744 The Farmers State Bank of Oakley, Kansas \n", "2 Indiana 28722 Kentland Bank \n", "3 Georgia 25796 Anchor Bank \n", "4 Illinois 57488 First Independence Bank \n", "\n", " Closing Date Fund Sort ascending \n", "0 August 21, 2026 10554 \n", "1 July 17, 2026 10553 \n", "2 July 10, 2026 10552 \n", "3 May 1, 2026 10551 \n", "4 January 30, 2026 10550 " ] }, "execution_count": 24, "metadata": {}, "output_type": "execute_result" } ], "source": [ "sql_df.head()" ] }, { "cell_type": "markdown", "id": "996866f3", "metadata": {}, "source": [ "#### Reading from SQLite Databases\n", "\n", "Beyond CSV files, Pandas can read from many data sources. Let's explore loading data from a SQLite database that we've used in previous SQL activities. You'll appreciate how convenient Pandas makes this!\n", "\n", "To connect to a SQLite database, we:\n", "1. Import the sqlite3 library (part of Python's standard library)\n", "2. Connect to the database using `sqlite3.connect()`\n", "3. Use the Pandas function `read_sql_query()` to execute your query\n", "4. Close the connection using the `close()` function (or use `with` statement)" ] }, { "cell_type": "code", "execution_count": 37, "id": "ab41750e", "metadata": { "execution": { "iopub.execute_input": "2026-08-31T06:00:42.583036Z", "iopub.status.busy": "2026-08-31T06:00:42.582981Z", "iopub.status.idle": "2026-08-31T06:00:42.585995Z", "shell.execute_reply": "2026-08-31T06:00:42.585856Z" }, "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", "
name
0Album
1Artist
2Customer
3Employee
4Genre
5Invoice
6InvoiceLine
7MediaType
8Playlist
9PlaylistTrack
10Track
\n", "
" ], "text/plain": [ " name\n", "0 Album\n", "1 Artist\n", "2 Customer\n", "3 Employee\n", "4 Genre\n", "5 Invoice\n", "6 InvoiceLine\n", "7 MediaType\n", "8 Playlist\n", "9 PlaylistTrack\n", "10 Track" ] }, "execution_count": 37, "metadata": {}, "output_type": "execute_result" } ], "source": [ "### List all tables in the SQLite database (similar to \"SHOW TABLES;\" in MySQL)\n", "\n", "import sqlite3\n", "conn = sqlite3.connect('../../data/chinook.db')\n", "\n", "query = \"SELECT name FROM sqlite_master WHERE type='table';\"\n", "\n", "pd.read_sql_query(query, conn)" ] }, { "cell_type": "code", "execution_count": 26, "id": "273f2bcb", "metadata": { "execution": { "iopub.execute_input": "2026-08-31T06:00:42.586846Z", "iopub.status.busy": "2026-08-31T06:00:42.586798Z", "iopub.status.idle": "2026-08-31T06:00:42.590342Z", "shell.execute_reply": "2026-08-31T06:00:42.590185Z" }, "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
CustomerIdFirstNameLastNameCompanyAddressCityStateCountryPostalCodePhoneFaxEmailSupportRepId
01LuísGonçalvesEmbraer - Empresa Brasileira de Aeronáutica S.A.Av. Brigadeiro Faria Lima, 2170São José dos CamposSPBrazil12227-000+55 (12) 3923-5555+55 (12) 3923-5566luisg@embraer.com.br3
12LeonieKöhlerNoneTheodor-Heuss-Straße 34StuttgartNoneGermany70174+49 0711 2842222Noneleonekohler@surfeu.de5
23FrançoisTremblayNone1498 rue BélangerMontréalQCCanadaH2G 1A7+1 (514) 721-4711Noneftremblay@gmail.com3
34BjørnHansenNoneUllevålsveien 14OsloNoneNorway0171+47 22 44 22 22Nonebjorn.hansen@yahoo.no4
45FrantišekWichterlováJetBrains s.r.o.Klanova 9/506PragueNoneCzech Republic14700+420 2 4172 5555+420 2 4172 5555frantisekw@jetbrains.com4
\n", "
" ], "text/plain": [ " CustomerId FirstName LastName \\\n", "0 1 Luís Gonçalves \n", "1 2 Leonie Köhler \n", "2 3 François Tremblay \n", "3 4 Bjørn Hansen \n", "4 5 František Wichterlová \n", "\n", " Company \\\n", "0 Embraer - Empresa Brasileira de Aeronáutica S.A. \n", "1 None \n", "2 None \n", "3 None \n", "4 JetBrains s.r.o. \n", "\n", " Address City State Country \\\n", "0 Av. Brigadeiro Faria Lima, 2170 São José dos Campos SP Brazil \n", "1 Theodor-Heuss-Straße 34 Stuttgart None Germany \n", "2 1498 rue Bélanger Montréal QC Canada \n", "3 Ullevålsveien 14 Oslo None Norway \n", "4 Klanova 9/506 Prague None Czech Republic \n", "\n", " PostalCode Phone Fax \\\n", "0 12227-000 +55 (12) 3923-5555 +55 (12) 3923-5566 \n", "1 70174 +49 0711 2842222 None \n", "2 H2G 1A7 +1 (514) 721-4711 None \n", "3 0171 +47 22 44 22 22 None \n", "4 14700 +420 2 4172 5555 +420 2 4172 5555 \n", "\n", " Email SupportRepId \n", "0 luisg@embraer.com.br 3 \n", "1 leonekohler@surfeu.de 5 \n", "2 ftremblay@gmail.com 3 \n", "3 bjorn.hansen@yahoo.no 4 \n", "4 frantisekw@jetbrains.com 4 " ] }, "execution_count": 26, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import sqlite3\n", "conn = sqlite3.connect('../../data/chinook.db')\n", "\n", "df_customer = pd.read_sql_query(\"SELECT * FROM customer;\", conn)\n", "df_customer.head()" ] }, { "cell_type": "code", "execution_count": 27, "id": "3501c7d7", "metadata": { "execution": { "iopub.execute_input": "2026-08-31T06:00:42.591162Z", "iopub.status.busy": "2026-08-31T06:00:42.591116Z", "iopub.status.idle": "2026-08-31T06:00:42.593429Z", "shell.execute_reply": "2026-08-31T06:00:42.593274Z" }, "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", "
ArtistIdName
01AC/DC
12Accept
23Aerosmith
34Alanis Morissette
45Alice In Chains
56Antônio Carlos Jobim
67Apocalyptica
78Audioslave
89BackBeat
910Billy Cobham
\n", "
" ], "text/plain": [ " ArtistId Name\n", "0 1 AC/DC\n", "1 2 Accept\n", "2 3 Aerosmith\n", "3 4 Alanis Morissette\n", "4 5 Alice In Chains\n", "5 6 Antônio Carlos Jobim\n", "6 7 Apocalyptica\n", "7 8 Audioslave\n", "8 9 BackBeat\n", "9 10 Billy Cobham" ] }, "execution_count": 27, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# BETTER PRACTICE: Use 'with' statement to automatically close connection\n", "# The 'with' statement ensures the connection is closed even if an error occurs\n", "\n", "sql = 'SELECT * FROM Artist LIMIT 10'\n", "with sqlite3.connect(\"../../data/chinook.db\") as conn:\n", " df_artist = pd.read_sql_query(sql, conn)\n", "\n", "df_artist" ] }, { "cell_type": "markdown", "id": "cell-d77a038d", "metadata": {}, "source": [ "\n", "## Section Practice\n", "\n", "Use this short exercise to check the main workflow from this section.\n" ] }, { "cell_type": "code", "execution_count": 28, "id": "cell-3f5b660d", "metadata": { "execution": { "iopub.execute_input": "2026-08-31T06:00:42.594357Z", "iopub.status.busy": "2026-08-31T06:00:42.594307Z", "iopub.status.idle": "2026-08-31T06:00:42.595501Z", "shell.execute_reply": "2026-08-31T06:00:42.595308Z" }, "tags": [ "thebe-interactive" ] }, "outputs": [], "source": [ "### Exercise: Practice Handling Datasets\n", "# Create a list named monthly_sales with the values 120, 135, 150, and 145.\n", "# Calculate the average monthly sales and print it rounded to one decimal place.\n", "### Your code starts here\n", "\n", "\n", "\n", "\n", "### Your code ends here\n" ] }, { "cell_type": "code", "execution_count": 29, "id": "cell-72f00843", "metadata": { "execution": { "iopub.execute_input": "2026-08-31T06:00:42.596342Z", "iopub.status.busy": "2026-08-31T06:00:42.596292Z", "iopub.status.idle": "2026-08-31T06:00:42.597640Z", "shell.execute_reply": "2026-08-31T06:00:42.597479Z" }, "tags": [ "hide-input" ] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "137.5\n" ] } ], "source": [ "### Solution\n", "monthly_sales = [120, 135, 150, 145]\n", "average_sales = sum(monthly_sales) / len(monthly_sales)\n", "print(round(average_sales, 1))\n" ] } ], "metadata": { "kernelspec": { "display_name": ".venv (3.13.13)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.13.13" } }, "nbformat": 4, "nbformat_minor": 5 }