{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "# Week 2 pandas and Financial Market Data\n\nPostgraduate Financial Econometrics Python Bootcamp 2026 27\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "NumPy arrays organise numerical values by position. pandas adds meaningful row and column labels. This notebook develops one workflow: create and select labelled data, download market prices, inspect the result, prepare a price table, work with missing observations, summarise and compare price paths, and save the prepared data.\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## Creating a Series\n\nA pandas **Series** is a one-dimensional collection of values with an index. The values below are labelled by date and the Series is named `AAPL`.\n"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "import pandas as pd\n\ndates = pd.to_datetime([\n    \"2025-01-02\",\n    \"2025-01-03\",\n    \"2025-01-06\",\n])\n\naapl = pd.Series(\n    [185.64, 187.15, 184.86],\n    index=dates,\n    name=\"AAPL\",\n)\n\naapl\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "A Series retains its index, name, data type, and shape.\n"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "aapl.index, aapl.name, aapl.dtype, aapl.shape\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "Use `.loc` for a label and `.iloc` for a numerical position.\n"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "aapl.loc[\"2025-01-03\"], aapl.iloc[1]\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## Creating a DataFrame\n\nA pandas **DataFrame** is a two-dimensional table with row and column labels. The `price_data` list supplies the rows used in the script.\n"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "price_data = [\n    [185.64, 370.87, 172.27],\n    [187.15, 374.23, 173.08],\n    [184.86, 371.30, 171.58],\n]\n\nsample_prices = pd.DataFrame(\n    price_data,\n    index=dates,\n    columns=[\"AAPL\", \"MSFT\", \"JPM\"],\n)\nsample_prices.index.name = \"Date\"\n\nsample_prices\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "Inspect a new table before changing it. `shape` gives rows and columns; `index` and `columns` show the labels; `dtypes` shows the type stored in each column; and `head()` displays the first rows.\n"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "sample_prices.shape, sample_prices.index, sample_prices.columns, sample_prices.dtypes\n"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "sample_prices.head()\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## Selecting rows and columns\n\nSelecting one column returns a Series. Selecting a list of column names returns a DataFrame, even when the list contains only one name.\n"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "aapl_prices = sample_prices[\"AAPL\"]\ntwo_assets = sample_prices[[\"AAPL\", \"MSFT\"]]\n\ntype(aapl_prices), type(two_assets)\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "Use `.loc` when selecting by a meaningful label and `.iloc` when selecting by numerical position. In both cases, write rows first and columns second.\n"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "labelled_value = sample_prices.loc[\"2025-01-03\", \"MSFT\"]\npositioned_value = sample_prices.iloc[1, 1]\nfirst_two_rows = sample_prices.iloc[:2, :]\n\nlabelled_value, positioned_value, first_two_rows\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "A Boolean Series can filter rows. Here the comparison creates one `True` or `False` value per date, and `.loc` keeps the dates for which it is true.\n"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "aapl_above_186 = sample_prices.loc[sample_prices[\"AAPL\"] > 186]\naapl_above_186\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## Exercise 9 Selecting labelled data\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "Using the `sample_prices` DataFrame:\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "1. select the `MSFT` column as a Series;\n2. select `AAPL` and `JPM` as a DataFrame;\n3. select the first row by position;\n4. select the row labelled `2025-01-03`;\n5. select the AAPL price on `2025-01-06` as one value.\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "### Solution\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "**(1) One column as a Series**\n"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "sample_prices[\"MSFT\"]\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "**(2) Two columns as a DataFrame**\n"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "sample_prices[[\"AAPL\", \"JPM\"]]\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "**(3) The first row by position**\n"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "sample_prices.iloc[0]\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "**(4) One row by date label**\n"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "sample_prices.loc[\"2025-01-03\"]\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "**(5) One value by row and column labels**\n"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "sample_prices.loc[\"2025-01-06\", \"AAPL\"]\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## Downloading market data\n\n`yfinance` provides a convenient teaching interface to Yahoo Finance data. The fixed sample below ends at the start of 2026 so that every class requests the same period. The end date is excluded.\n"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "import yfinance as yf\n\ntickers = [\"AAPL\", \"MSFT\", \"JPM\", \"SPY\", \"TLT\", \"GLD\"]\nstart_date = \"2015-01-01\"\nend_date = \"2026-01-01\"\n"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "raw = yf.download(\n    tickers,\n    start=start_date,\n    end=end_date,\n    interval=\"1d\",\n    auto_adjust=False,\n    actions=False,\n    group_by=\"column\",\n    progress=False,\n    multi_level_index=True,\n)\n\nraw.head()\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "For several tickers, the downloaded columns have two levels: the market-data field and the ticker. Before selecting anything, inspect the shape, date range, and column labels. A cached copy should be available if the download service is unavailable.\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## Exercise 10 Inspect and prepare the downloaded prices\n\nUsing `raw`:\n\n**(a)** Display the first five rows.\n\n**(b)** Display its shape and identify the number of rows and columns.\n\n**(c)** Display the earliest and latest dates.\n\n**(d)** Display the column labels. Then select the `Adj Close` field as a separate DataFrame called `prices` and display its first five rows.\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "### Solution\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "#### (a) View the first rows\n"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "raw.head()\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "#### (b) Check the table dimensions\n"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "raw.shape\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "The first number is the number of rows; the second is the number of columns.\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "#### (c) Check the date range\n"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "raw.index.min()\n"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "raw.index.max()\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "#### (d) Inspect the columns and select adjusted prices\n"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "raw.columns\n"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "prices = raw[\"Adj Close\"].copy()\nprices.head()\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## Finishing the adjusted-price table\n\nPut the columns in the requested ticker order and give the date index a clear name. The result has one row per trading date and one column per asset.\n"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "prices = prices.reindex(columns=tickers)\nprices.index.name = \"Date\"\n\nprices.head()\n"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "prices.shape, prices.index.min(), prices.index.max(), prices.columns\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## Selecting dates and observations\n\nWith a date index, `.loc` accepts a year, a month, or a date range. `.iloc` still selects by position.\n"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "march_2020 = prices.loc[\"2020-03\", [\"AAPL\", \"SPY\"]]\nlast_ten = prices.iloc[-10:, :]\n\nmarch_2020.head(), last_ten.shape\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## Exercise 11 Select useful samples\n\nUsing `prices`:\n\n**(a)** Select the SPY column for the calendar year 2020.\n\n**(b)** Select AAPL, JPM, and SPY for March 2020.\n\n**(c)** Select the final ten observations for all assets.\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "### Solution\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "#### (a) SPY during 2020\n"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "spy_2020 = prices.loc[\"2020\", \"SPY\"]\nspy_2020.head()\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "#### (b) Three assets during March 2020\n"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "march_2020 = prices.loc[\"2020-03\", [\"AAPL\", \"JPM\", \"SPY\"]]\nmarch_2020.head()\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "#### (c) Final ten observations\n"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "last_ten = prices.iloc[-10:, :]\nlast_ten\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## Missing observations\n\n`isna()` identifies missing values. Summing its Boolean results counts missing observations; taking the mean gives the missing proportion. At this stage, report missing data rather than automatically filling or deleting it.\n"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "missing_report = pd.DataFrame(index=prices.columns)\nmissing_report[\"missing_count\"] = prices.isna().sum()\nmissing_report[\"missing_percent\"] = prices.isna().mean() * 100\n\nmissing_report\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "Some calculations require every selected asset to be observed on the same date. `dropna()` creates that complete-case sample. Compare its shape with the original before deciding whether the lost rows are acceptable.\n"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "complete_prices = prices.dropna()\n\nprices.shape, complete_prices.shape\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## Summarising columns\n\n`describe()` provides a standard numerical summary. `agg()` is useful when only a small set of statistics is required. Both operate down the rows and return one result for each asset column.\n"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "prices.describe()\n"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "prices.agg([\"count\", \"mean\", \"std\", \"min\", \"max\"])\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## Plotting one price series\n\nPlotting one column provides a visual check of its ordering and broad behaviour. The notebook deliberately stops at price levels; constructing and interpreting returns belongs to the financial econometrics material that follows.\n"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "import matplotlib.pyplot as plt\n\nfig, ax = plt.subplots(figsize=(10, 5))\nprices[\"SPY\"].plot(ax=ax, color=\"navy\")\nax.set_title(\"SPY adjusted closing price\")\nax.set_xlabel(\"Date\")\nax.set_ylabel(\"Adjusted price level (USD)\")\nax.grid(alpha=0.3)\nfig.tight_layout()\nplt.show()\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## Comparing several price paths\n\nDifferent assets have different price levels, so plotting their raw prices on one axis can be misleading. Dividing each column by its first observation and multiplying by 100 rebases every series to the same starting value. This is a price index, not a return series.\n"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "comparison_sample = complete_prices.loc[\"2020\":\"2025\"]\nrebased_prices = comparison_sample / comparison_sample.iloc[0] * 100\n\nrebased_prices.head()\n"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "fig, ax = plt.subplots(figsize=(10, 5))\nrebased_prices.plot(ax=ax)\nax.set_title(\"Adjusted prices rebased to 100\")\nax.set_xlabel(\"Date\")\nax.set_ylabel(\"Price index\")\nax.grid(alpha=0.3)\nfig.tight_layout()\nplt.show()\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## Saving and reloading a prepared table\n\nSaving the prepared data separates data collection from later analysis. When reloading the CSV, restore the date column as a parsed date index.\n"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "from pathlib import Path\n\nPath(\"data\").mkdir(exist_ok=True)\noutput_path = Path(\"data/adjusted_close.csv\")\nprices.to_csv(output_path)\n"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "prices_reloaded = pd.read_csv(\n    output_path,\n    index_col=\"Date\",\n    parse_dates=[\"Date\"],\n)\n\nprices_reloaded.head()\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## Exercise 12 Prepare a comparison sample\n\nUse the prepared `prices` DataFrame.\n\n**(a)** Select AAPL, SPY, and TLT from 2020 through 2025 and store the result as `analysis_prices`.\n\n**(b)** Count the missing observations in each column. Then remove dates with any missing values and store the result as `complete_analysis_prices`. Compare the shapes before and after.\n\n**(c)** Rebase the complete price series to 100 at their first common date and store the result as `analysis_index`. Display its first five rows.\n\n**(d)** Plot the three rebased series with a title and labelled axes.\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "### Solution\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "#### (a) Select the assets and dates\n"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "analysis_prices = prices.loc[\n    \"2020\":\"2025\",\n    [\"AAPL\", \"SPY\", \"TLT\"],\n]\n\nanalysis_prices.head()\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "#### (b) Check and remove missing observations\n"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "analysis_prices.isna().sum()\n"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "complete_analysis_prices = analysis_prices.dropna()\n\nanalysis_prices.shape, complete_analysis_prices.shape\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "#### (c) Rebase the series\n"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "analysis_index = (\n    complete_analysis_prices\n    / complete_analysis_prices.iloc[0]\n    * 100\n)\n\nanalysis_index.head()\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "#### (d) Plot the comparison\n"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "fig, ax = plt.subplots(figsize=(10, 5))\nanalysis_index.plot(ax=ax)\nax.set_title(\"AAPL, SPY, and TLT: adjusted prices rebased to 100\")\nax.set_xlabel(\"Date\")\nax.set_ylabel(\"Price index\")\nax.grid(alpha=0.3)\nfig.tight_layout()\nplt.show()\n"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## Week 2 takeaways\n\nYou can create and inspect a labelled DataFrame, distinguish Series from DataFrame results, select by label or position, inspect and prepare downloaded market data, handle a complete-case sample, summarise columns, compare rebased price paths, and save and reload prepared data.\n"
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python",
   "version": "3"
  },
  "financial_econometrics": {
   "academic_year": "2026/27",
   "source": "postgraduate_financial_econometrics.tex",
   "week": 2,
   "solutions_included": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}