py_notebooks/py_nb_pandas_stocks.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "# Pandas \n",
    "\n",
    "- CityU COM5507 201819A - Unit 1 and part of Unit 3: Python and data science tools \n",
    "- **19 Sep 2018, Week 3: Python in a Notebook & data science tools**\n",
    "\n",
    "\n",
    "- Course Instructor: [Dr. Xinzhi Zhang](www.drxinzhizhang.com)  (JOUR, Hong Kong Baptist University) \n",
    "  - xzzhang2@gmail.com\n",
    "\n",
    "\n",
    "- The codes in this notebook are modified from various sources. All codes are for educational purposes only and released under the CC1.0. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## What is Pandas? \n",
    "\n",
    "Pandas = panel, dataframe, and series. It is a \"flexible and powerful data analysis / manipulation library for Python, providing labeled data structures similar to R data.frame objects, statistical functions, and much more.\" (Pandas GitHub)\n",
    "\n",
    "Pandas has an oject called a **Data Frame** which is **like a table**. \n",
    "\n",
    "![pd dataframe](pd_dataframe.png)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Import Pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# import packages\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "# Extra packages\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt # for plotting\n",
    "import seaborn as sns # for plotting and styling\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Part 1\n",
    "### Simple creation and manipulation of Pandas objects\n",
    "**Key Points:** Pandas has two / three main data types:\n",
    "* Series (similar to numpy arrays, but with index)\n",
    "* DataFrames (table or spreadsheet with Series in the columns) [important!]\n",
    "* Panels (3D version of DataFrame, not as common)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### A Series object in Pandas \n",
    "\n",
    "Like an np.array, but we can combine data types and it has its own index\n",
    "\n",
    "Note: Every column in a DataFrame is a Series\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "s = pd.Series([1,3,5,np.nan,6,8])  # sth. like a list; np.nan = missing data \n",
    "s\n",
    "print(type(s))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "s[2] # got index "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### A data frame object in Pandas\n",
    "\n",
    "We use `pd.DataFrame(**inputs**)` and can insert almost any data type as an argument\n",
    "\n",
    "**Function:** `pd.DataFrame(data=None, index=None, columns=None, dtype=None, copy=False)`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df1 = pd.DataFrame({'label': ['A', 'B', 'C', 'A', 'B', 'C'],\n",
    "                   'value': [1, 2, 3, 4, 5, 6]})\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df1['label']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df1['label'].str.lower()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df1['value'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Apply aggregates across numerical entries:\n",
    "df1.groupby('label').sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# more complex data types \n",
    "\n",
    "s1 = 1\n",
    "s2 = pd.Timestamp('20130102')\n",
    "s3 = pd.Series(1,index=list(range(4)),dtype='float32')\n",
    "s4 = np.arange(0,4) # one range\n",
    "s5 = pd.Categorical([\"test\",\"train\",\"test\",\"train\"])\n",
    "s6 = 'foo'\n",
    "dates = pd.date_range('20130101', periods=4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# something like \"key\" \n",
    "df2 = pd.DataFrame({ 'A' : s1,\n",
    "                    'B' : s2,\n",
    "                    'C' : s3,\n",
    "                    'D' : s4,\n",
    "                    'E' : s5,\n",
    "                    'F' : s6 },\n",
    "                 index=dates )  # the first column\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df2.dtypes # \"df\" is the name of the dataframe; "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df2 = df2.rename(columns = {'F':'hahahah'})\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Part 2: An example: The stock market \n",
    "\n",
    "Data source: https://www.nasdaq.com/symbol/csv/historical \n",
    "\n",
    "![stock pic](pd_stock_pic1.png)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Importing \"CSV\" data "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We can download data from the web by using pd.read_csv\n",
    "# A CSV file is a comma seperated file\n",
    "# We can use this 'pd.read_csv' method with urls that host csv files\n",
    "\n",
    "# source: https://www.nasdaq.com/symbol/csv/historical \n",
    "\n",
    "dfg = pd.read_csv('HistoricalQuotes_2008-2018_googl.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(type(dfg))\n",
    "dfg.head(10) # show first n values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dfg.tail(3) # last three"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dfg.columns # returns columns, can be used to loop over\n",
    "\n",
    "# R names(dfg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dfg.index # return"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Convert the index to pandas datetime object"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "type(dfg['date'][0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dfg.index = pd.to_datetime(dfg['date']) # set index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dfg.drop(['date'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dfg.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "print(type(dfg.index[0]))\n",
    "dfg.index[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dfg['2018-09-16':'2018-09-10']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Attributes & general statitics of a Pandas DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dfg.shape # 2519 business days in the past 10 years, 5 variables "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dfg.size"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dfg.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Some general statistics\n",
    "\n",
    "dfg.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Boolean indexing\n",
    "dfg['open'][dfg['open']>15]  # check what dates the opening > 15 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dfg['open'][dfg['open']>15].head(5)  # check what dates the opening with the firt 5 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Check where Open, High, Low and Close where greater than X\n",
    "# dfg[dfg>X].drop('Volume',axis=1).head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# drop na - not run for now \n",
    "## dfg[dfg>X].drop('Volume',axis=1).dropna()\n",
    "\n",
    "# Comments on dropping and filling NaN values\n",
    "# A view where we drop any rows with value NnN\n",
    "# dfg.dropna(how='any')  # this would be used to drop rows with Nan\n",
    "# dfg.fillna(value=5)    # this would be used to fill NaN values with 5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# If you want the values in an np array\n",
    "npg = dfg.values\n",
    "print(type(npg))\n",
    "print()\n",
    "npg"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Selecting or Viewing Data within a DataFrame\n",
    "Note: While standard Python / Numpy expressions for selecting and setting are intuitive and come in handy for interactive work, for production code, we recommend the optimized pandas data access methods, .at, .iat, .loc, .iloc and .ix. (from 10 min guide to Pandas)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Lets print the five first Close prices for Google\n",
    "# This is a new Series (like a new table)\n",
    "dfg['close'][0:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Lets print the 2 column\n",
    "dfg[['close','open','volume']][0:7]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# A slice: by rows (row numbers)\n",
    "dfg[1:5] # 2nd to 4th element"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "list1 = [0,1,2,3,4]\n",
    "list1[1:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## .loc()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Getting a cross section with .loc - BY VALUES of the index and columns\n",
    "# df.loc[a:b, x:y], by rows and column location\n",
    "\n",
    "# Note: You have to know indices and columns\n",
    "\n",
    "dfg.loc['2017-08-31':'2017-08-21'\n",
    "        ,\n",
    "        'open':'low']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## .iloc()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dfg.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# .iloc slicing at specific location - BY POSITION in the table\n",
    "# Recall:\n",
    "# dfg[a:b] by rows\n",
    "# dfg[[col]] or df[[col1, col2]] by columns\n",
    "# df.loc[a:b, x:y], by index and column values + location\n",
    "# df.iloc[3:5,0:2], numeric position in table\n",
    "\n",
    "dfg.iloc[1:4,3:5] # 2nd to 4th row, 4th to 5th column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Data only from row with index value '3'\n",
    "print (dfg.iloc[3])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# iloc will accept a range with ':', just like numpy\n",
    "dfg.iloc[1:3,:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Can also return specific value\n",
    "print (dfg.iloc[2,1])\n",
    "# same as above but faster for one single scaler value\n",
    "print (dfg.iat[2,1])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## More Basic Statistics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# A quick way to get statistics\n",
    "dfg.describe()\n",
    "# dfg.describe()['A'][1]\n",
    "# dfg.describe()[2:3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dfg.describe().loc[['mean','std'],['high','low']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dfg.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# We can change the index sorting\n",
    "dfg.sort_index(axis=0, ascending=True).head() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# sort by value\n",
    "dfg.sort_values(by='open')[0:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Masks and Boolean Indexing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dfg[0:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# mask 1\n",
    "mg1 = dfg['open'][0:10]>22.40\n",
    "print (mg1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dfg['open'][0:10][dfg['open']>22.40]\n",
    "# shows only rows with opening price greater than 1115"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# mask 2 full data frame\n",
    "mg2 = dfg[0:10]>22.50\n",
    "mg2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dfg[dfg>22.50].head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# we can also drop all NaN values\n",
    "dfg[dfg>22.50].head(10).dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# like Numpy, sometimes you need an actual copy, not a view or slice of the same data\n",
    "dfg_same = dfg\n",
    "dfg_same is dfg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dfg2 = dfg.copy()\n",
    "dfg2 is dfg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "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.6.1"
  },
  "toc": {
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}