python_and_pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "1f68e5b6",
   "metadata": {},
   "source": [
    "# Jupyter Notebook Basics"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ed3e8e41",
   "metadata": {},
   "source": [
    "**Cell modes**: There are different cell modes, the most important ones are:\n",
    "\n",
    "* Code\n",
    "* Markdown\n",
    "\n",
    "This cell is written with Markdown. Double-click it to see the source.\n",
    "\n",
    "**Edit vs. Command Mode**\n",
    "\n",
    "* Edit mode: click into a cell or hit `Enter`\n",
    "* Command mode: hit ESC\n",
    "\n",
    "**Keyboard shortcuts (command mode)**\n",
    "\n",
    "* `Shift+Enter`: Run the cell\n",
    "* `↑ (up-arrow)` and `↓ (down-arrow)`: Move cell selector up or down\n",
    "* `b` and `a`: Insert a new cell *below* or *above*\n",
    "* `dd`: Delete the current cell\n",
    "* `m`: Switch cell type to Markdown\n",
    "* `y`: Switch cell type to code"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "afa6fbfe",
   "metadata": {},
   "source": [
    "# Python Basics"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "74d23665",
   "metadata": {},
   "source": [
    "### Get access to additional functionality by *importing* packages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "77aa607a",
   "metadata": {},
   "outputs": [],
   "source": [
    "import math\n",
    "math.sqrt(49)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2ee1fea0",
   "metadata": {},
   "source": [
    "### Everything is an object. Objects have attributes and methods."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "715fb7ad",
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime as dt\n",
    "timestamp = dt.datetime(2020, 1, 31, 14, 30) # timestamp is a datetime object\n",
    "timestamp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d8af4297",
   "metadata": {},
   "outputs": [],
   "source": [
    "timestamp.year  # attribute (no brackets)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "624032b8",
   "metadata": {},
   "outputs": [],
   "source": [
    "timestamp.strftime('%Y-%m-%d')  # method (brackets) \"string format time\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "df5fd650",
   "metadata": {},
   "source": [
    "### Lists and Dictionaries are the most important data structures"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f60a2b49",
   "metadata": {},
   "outputs": [],
   "source": [
    "# List\n",
    "file_names = [\"one.xlsx\", \"two.xlsx\", \"three.xlsx\", \"four.xlsx\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fb37d33f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Dictionary\n",
    "exchange_rates = {\"EURUSD\": 1.1152, \"GBPUSD\": 1.2454, \"AUDUSD\": 0.6161}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "be8797cc",
   "metadata": {},
   "source": [
    "### Indexing and Slicing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "194a8e90",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Python is zero-based (unlike Excel)\n",
    "file_names[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8999be41",
   "metadata": {},
   "outputs": [],
   "source": [
    "file_names[1:3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1cc9ac0e",
   "metadata": {},
   "outputs": [],
   "source": [
    "file_names[1:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "00c6194c",
   "metadata": {},
   "outputs": [],
   "source": [
    "file_names[:]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e4cc5c44",
   "metadata": {},
   "source": [
    "### Accessing Dictionary elements"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b3cddb15",
   "metadata": {},
   "outputs": [],
   "source": [
    "exchange_rates['EURUSD']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c001e0c7",
   "metadata": {},
   "source": [
    "### For loops"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bd5fc666",
   "metadata": {},
   "outputs": [],
   "source": [
    "for file_name in file_names:\n",
    "    print(file_name)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a589b9df",
   "metadata": {},
   "source": [
    "### Functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5c4985e6",
   "metadata": {},
   "outputs": [],
   "source": [
    "def fahrenheit_to_celsius(degrees):\n",
    "    return (degrees-32) * (5/9)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8732ecb7",
   "metadata": {},
   "outputs": [],
   "source": [
    "fahrenheit_to_celsius(100)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ae57235a",
   "metadata": {},
   "source": [
    "# Pandas Basics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2674280f",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "pd.options.plotting.backend = \"plotly\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ae291bf0",
   "metadata": {},
   "source": [
    "### A DataFrame has an Index, Columns, and Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3fca7df1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Index ~ observations, Columns ~ variables\n",
    "df = pd.DataFrame(data={\"NYC\": [300.1, 100.2, 123.45],\n",
    "                        \"Chicago\": [400.3, 300.4, 334.2],\n",
    "                        \"San Francisco\": [1000.5, 1100.6, 993.4]})"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7927ac97",
   "metadata": {},
   "source": [
    "### GOTCHA: methods return copies!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bad5bcfe",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.sort_values('NYC')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "11f39cce",
   "metadata": {},
   "outputs": [],
   "source": [
    "# df is unchanged\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1f2413c0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# If you want to change the original DataFrame, do this:\n",
    "df = df.sort_values('NYC')\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f1e3c8ec",
   "metadata": {},
   "source": [
    "### Data Selection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fa987314",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Label-based data selection\n",
    "df.loc[:, ['Chicago']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "51e7f74a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[[0, 1], ['Chicago', 'NYC']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bad06eb1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Integer-based data selection\n",
    "df.iloc[0, 0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "69074dea",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.iloc[:2, :2]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4a2dbf2e",
   "metadata": {},
   "source": [
    "### GOTCHA: Series (1d) vs DataFrames (2d)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "65ee9f73",
   "metadata": {},
   "outputs": [],
   "source": [
    "# This is a pandas Series (1d)\n",
    "# Series have many things in common with 2d DataFrames, but\n",
    "# they don't do data alignment (we'll see further down what that is)\n",
    "df.loc[:, 'Chicago']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6d07fb89",
   "metadata": {},
   "source": [
    "### Column selection shortcut"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2b1ca087",
   "metadata": {},
   "outputs": [],
   "source": [
    "# df.loc[:, 'Chicago'] is the same as:\n",
    "df['Chicago']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "059af87f",
   "metadata": {},
   "source": [
    "### Vectorization and Data Alignment"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2d66459e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# elementwise without loops (\"vectorization\")\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "69dc73d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "df * 10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8ddc98d0",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['NYC'] + df['Chicago']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c555da75",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = pd.DataFrame(data={\"San Francisco\": [10.1, 10.2, 12.4],\n",
    "                        \"NYC\": [11.1, 10.2, 12.45],\n",
    "                        \"Chicago\": [40.3, 30.4, 33.2]})\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "25a8c019",
   "metadata": {},
   "outputs": [],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d3b8b51d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df + df2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c104ec94",
   "metadata": {},
   "source": [
    "# Time Series Analysis with pandas and Plotly"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2f87d17b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read a sheet of an Excel file\n",
    "msft = pd.read_excel(\"xlsx/MSFT.xlsx\")\n",
    "msft"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "13cf3acf",
   "metadata": {},
   "outputs": [],
   "source": [
    "msft.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bf3e125a",
   "metadata": {},
   "outputs": [],
   "source": [
    "msft.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "44d1e3d3",
   "metadata": {},
   "outputs": [],
   "source": [
    "msft = msft.sort_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bc9604c2",
   "metadata": {},
   "outputs": [],
   "source": [
    "msft = msft.set_index('Date')\n",
    "msft"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8996872a",
   "metadata": {},
   "source": [
    "### Powerful selection based on date strings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4a5334c6",
   "metadata": {},
   "outputs": [],
   "source": [
    "msft.loc[\"2019\", \"Adj Close\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7674d121",
   "metadata": {},
   "outputs": [],
   "source": [
    "msft.loc[\"2019-06\":\"2020-05\", \"Adj Close\"].plot()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aa789d2e",
   "metadata": {},
   "source": [
    "## Shifting and Percentage Changes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "37695b93",
   "metadata": {},
   "outputs": [],
   "source": [
    "msft_close = msft.loc[:, [\"Adj Close\"]].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "097f8660",
   "metadata": {},
   "outputs": [],
   "source": [
    "msft_close.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8888edd9",
   "metadata": {},
   "outputs": [],
   "source": [
    "msft_close.shift(1).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "707715fd",
   "metadata": {},
   "outputs": [],
   "source": [
    "returns = np.log(msft_close / msft_close.shift(1))\n",
    "returns = returns.rename(columns={\"Adj Close\": \"returns\"})\n",
    "returns.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6e009c79",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Plot a histogram with the daily log returns\n",
    "returns.plot.hist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "61f737f3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Simple Returns\n",
    "simple_rets = msft_close.pct_change()\n",
    "simple_rets = simple_rets.rename(columns={\"Adj Close\": \"simple rets\"})\n",
    "simple_rets.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "11ad932e",
   "metadata": {},
   "source": [
    "# Rebasing and Correlation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1aaa6931",
   "metadata": {},
   "outputs": [],
   "source": [
    "parts = []  # List to collect individual DataFrames\n",
    "for ticker in [\"AAPL\", \"AMZN\", \"GOOGL\", \"MSFT\"]:\n",
    "    # \"usecols\" allows us to only read in the Date and Adj Close\n",
    "    adj_close = pd.read_excel(f\"xlsx/{ticker}.xlsx\",\n",
    "                            index_col=\"Date\", usecols=[\"Date\", \"Adj Close\"])\n",
    "    # Rename the column into the ticker symbol\n",
    "    adj_close = adj_close.rename(columns={\"Adj Close\": ticker})\n",
    "    # Append the stock's DataFrame to the parts list\n",
    "    parts.append(adj_close)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fef6352f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Data alignment at work: Combine the 4 DataFrames into a single DataFrame\n",
    "adj_close = pd.concat(parts, axis=1)\n",
    "adj_close"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8ac0f7c2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Only use rows where we have data for all stocks\n",
    "adj_close = adj_close.dropna()\n",
    "adj_close.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "634fc3be",
   "metadata": {},
   "source": [
    "### Let's write a DataFrame to a new Excel file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a6859a8f",
   "metadata": {},
   "outputs": [],
   "source": [
    "adj_close.to_excel('adj_close.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "22f9667a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use a sample from June 2019 - May 2020\n",
    "adj_close_sample = adj_close.loc[\"2019-06\":\"2020-05\", :]\n",
    "rebased_prices = adj_close_sample / adj_close_sample.iloc[0, :] * 100\n",
    "rebased_prices.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "701d21b2",
   "metadata": {},
   "outputs": [],
   "source": [
    "rebased_prices.plot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b71e9516",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Correlation of daily log returns\n",
    "returns = np.log(adj_close / adj_close.shift(1))\n",
    "returns.corr()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f632bbb4",
   "metadata": {},
   "outputs": [],
   "source": [
    "import plotly.express as px"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5c9265cd",
   "metadata": {},
   "outputs": [],
   "source": [
    "fig = px.imshow(returns.corr(),\n",
    "                x=adj_close.columns,\n",
    "                y=adj_close.columns,\n",
    "                color_continuous_scale=list(\n",
    "                    reversed(px.colors.sequential.RdBu)),\n",
    "                zmin=-1, zmax=1)\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dcd400bb",
   "metadata": {},
   "source": [
    "## Resampling"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8df4b175",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Downsampling\n",
    "end_of_month = adj_close.resample(\"M\").last()\n",
    "end_of_month.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4ec81ce0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Upsampling\n",
    "end_of_month.resample(\"D\").asfreq().head()  # No transformation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5f22746b",
   "metadata": {},
   "outputs": [],
   "source": [
    "end_of_month.resample(\"W-FRI\").ffill().head()  # Forward fill"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "84e7d531",
   "metadata": {},
   "source": [
    "## Rolling Windows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cd85ef4e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Plot the moving average for MSFT with data from 2019\n",
    "msft19 = msft.loc[\"2019\", [\"Adj Close\"]].copy()\n",
    "# Add the 25 day moving average as a new column to the DataFrame\n",
    "msft19.loc[:, \"25day average\"] = msft19[\"Adj Close\"].rolling(25).mean()\n",
    "msft19.plot()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "abe34367",
   "metadata": {},
   "source": [
    "## If you can, avoid Excel files as data source\n",
    "\n",
    "`%%time` is a *cell magic* that prints the time spent in the cell  \n",
    "Use `%%timeit` to get the average of multiple runs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "49fd21e2",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "parts = []  # List to collect individual DataFrames\n",
    "for ticker in [\"AAPL\", \"AMZN\", \"GOOGL\", \"MSFT\"]:\n",
    "    # \"usecols\" allows us to only read in the Date and Adj Close\n",
    "    adj_close = pd.read_excel(f\"xlsx/{ticker}.xlsx\",\n",
    "                            index_col=\"Date\", parse_dates=[\"Date\"],\n",
    "                            usecols=[\"Date\", \"Adj Close\"])\n",
    "    # Rename the column into the ticker symbol\n",
    "    adj_close = adj_close.rename(columns={\"Adj Close\": ticker})\n",
    "    # Append the stock's DataFrame to the parts list\n",
    "    parts.append(adj_close)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "75bbfe34",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "parts = []  # List to collect individual DataFrames\n",
    "for ticker in [\"AAPL\", \"AMZN\", \"GOOGL\", \"MSFT\"]:\n",
    "    # \"usecols\" allows us to only read in the Date and Adj Close\n",
    "    adj_close = pd.read_csv(f\"csv/{ticker}.csv\",\n",
    "                            index_col=\"Date\", parse_dates=[\"Date\"],\n",
    "                            usecols=[\"Date\", \"Adj Close\"])\n",
    "    # Rename the column into the ticker symbol\n",
    "    adj_close = adj_close.rename(columns={\"Adj Close\": ticker})\n",
    "    # Append the stock's DataFrame to the parts list\n",
    "    parts.append(adj_close)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}