pandas_basics.ipynb

{
 "metadata": {
  "name": "pandas_basics"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import pandas as pd\n",
      "\n",
      "from pandas import Series, DataFrame, Panel"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Pandas\n",
      "======\n",
      "\n",
      "*Material by Chang She*\n",
      "\n",
      "http://pandas.pydata.org/\n",
      "\n",
      "https://github.com/pydata/pandas\n",
      "\n",
      "Python for Data Analysis ( http://shop.oreilly.com/product/0636920023784.do )\n",
      "\n",
      "Important topics:\n",
      "\n",
      "- Creating and accessing data structures\n",
      "  - Series, DataFrame, Panel\n",
      "- Creating labels (Index, MultiIndex)\n",
      "- Indexing/slicing data structures\n",
      "- Data IO\n",
      "- Numerical computations\n",
      "- Data alignment\n",
      "- Missing data handling\n",
      "- Grouping"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "##Pandas has data structures for 1-D, 2-D, and 3-D data\n",
      "- Series (1-D vector data)\n",
      "- DataFrame (2-D tabular data)\n",
      "- Panel (3-D data cube)"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "####Series"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "values = [1, 2, 3]\n",
      "labels = ['Cashews', 'Almonds', 'Peanuts']"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "s = Series(values, labels)\n",
      "s"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "s.index"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "s.values"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "type(s.values)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Accessing Series elements"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "s['Peanuts']"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "s['Almonds']"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "s['Cashews']"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Series from dicts"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "d = dict(zip(labels, values))\n",
      "Series(d)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "How come the order changed?"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "####DataFrame"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df = DataFrame([[1, 2, 3], \n",
      "                [4, 5, 6],\n",
      "                [7, 8, 9]], \n",
      "               index=['a', 'b', 'c'], \n",
      "               columns=labels)\n",
      "df"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.index"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.columns"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.values"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Accessing DataFrame components"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df['Cashews']"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "type(df['Cashews'])"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Columns can be accessed as attributes"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.Cashews"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.ix['a', :]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "##There are many ways to access the data in pandas objects"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "s"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "positional"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "s[0]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "s[-1]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "s[:2]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "s[:-1]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "list of labels"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "s[['Cashews', 'Peanuts', 'Almonds']]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "s[0] = 5"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "#### Accessing DataFrame via the indexer (.ix)"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Single row using position"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.ix[0]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Using label"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.ix['a', :]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Single column"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.ix[:, 'Cashews']"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Single element access"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.ix[0, 1]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "lists, tuples, slices, arrays, oh my!"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.ix[[0, 1], :-1]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "boolean indexing"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.ix[df.Almonds > 4]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "boolean indexing with a DataFrame"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df[df > 3] # new in 0.9.1"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Mutation can also happen via the indexer as well"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.ix[:, 0] = 1"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.ix['b', :] = Series([6, 1, 5], df.columns)\n",
      "df"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "### Practical 1:\n",
      "\n",
      "Create a 4-by-2 DataFrame where\n",
      "- one column is ['A', 'B', 'A', 'B'] \n",
      "- the other is 4 random numbers\n",
      "\n",
      "Get all entries where the string column is 'A'\n",
      "\n",
      "Get the entry at position (2, 1)\n",
      "\n",
      "Get all entries from the numerical column where the string column is not 'A'"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "##Real data comes from files and databases"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "path = 'https://dl.dropbox.com/u/22164876/data.csv'\n",
      "df = pd.read_csv(path)\n",
      "\n",
      "df"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.shape"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.index"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.head()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "The default index isn't very useful"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df = pd.read_csv(path, index_col=['Date', 'Time'])\n",
      "df"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.index[:5]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.index[0]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "We want pandas to interpret dates and times automatically"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "ticks = pd.read_csv(path, parse_dates={'ts': ['Date', 'Time']}, index_col='ts')\n",
      "ticks"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "ticks.index"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "ticks.index[0]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "isinstance(ticks.index[0], datetime.datetime)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Why did we make a subclass of datetime?"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "ticks.index[0].nanosecond"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Let's do some simple operations"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "sq_price = ticks.Price**2\n",
      "np.sqrt(sq_price.mean())"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "sq_vol = ticks.Volume**2\n",
      "np.sqrt(sq_vol.mean())"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "ticks['Price'].std()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "mean = ticks.Price.mean()\n",
      "std = ticks.Price.std(ddof=0)\n",
      "uncentered = np.sqrt(mean**2 + std**2)\n",
      "uncentered"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "std = ticks.Price.std()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "ticks['Price'].std()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "ticks['Price'].std()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "read_csv and to_csv are good friends"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "ticks.to_csv('tmp.csv')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pd.read_csv('tmp.csv', index_col='ts')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "####Databases"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import pandas.io.sql as sql\n",
      "import sqlite3"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "con = sqlite3.connect(':memory:')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "sql.write_frame(ticks, 'ticks', con)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "sql.read_frame('select * from ticks', con)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "####HDF"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "store = pd.HDFStore('ticks.h5')\n",
      "store['ticks'] = ticks"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "store['ticks']"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "store.close()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "### Basic Computations"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df = ticks.ix[:1000, ['Price', 'Volume']]\n",
      "df"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.index[0]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Summary statistics about this DataFrame"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.describe()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Each of the summary stats can be computed separately"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.count()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.mean()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.std()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.min()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.max()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.quantile(0.50)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.median()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "#### Compute across a different axes"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": true,
     "input": [
      "df.mean(axis=1)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.ix[:10, :].std(axis=1)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "### Arbitrary functions\n",
      "\n",
      "- `apply` for columnwise operations\n",
      "- `applymap` for elementwise operations"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.sum()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.apply(lambda x: x.sum())"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.applymap(lambda x: x.sum()).head() # no effect"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.head()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.head().applymap(lambda x: x**2)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "#### One of the three R's of education"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.ix[:5]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Scalar operations are done element-wise"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.ix[:5] * 10"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.ix[:5] + 100"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "DataFrame with DataFrame (or Series with Series) is element-by-element"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.ix[:5] - df.ix[:5]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "### Broadcasting"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "means = df.mean()\n",
      "means"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.ix[:5] - means"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "DataFrame with Series means each Series element is applied to each DataFrame column"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "result = df - means\n",
      "\n",
      "result.mean()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "The term broadcasting describes how arrays with different shapes are manipulated together in computations. \n",
      "Start with the last dimension, two dimensions are compatible for broadcasting if either they are equal or one of them has 1 element"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "## Basic stats computations"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.cov()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.dot(df.T) # oops, wrong orientation"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.T.dot(df)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.corr()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.ix[:5].abs()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.kurt()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "ticks.ix[:, :3].head()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "### Practical 2:\n",
      "\n",
      "Reimplement DataFrame.cov"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.cov()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "demeaned = df - df.mean()\n",
      "numer = demeaned.T.dot(demeaned)\n",
      "denom = demeaned.count()\n",
      "numer / denom"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Sample cov!"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "numer / (denom - 1)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Now package it up as a function and test against DataFrame.cov"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "def cov(df):\n",
      "    demeaned = df - df.mean()\n",
      "    numer = demeaned.T.dot(demeaned)\n",
      "    denom = demeaned.count()\n",
      "    return numer / (denom - 1)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "#### Extra credit: what about NA handling?"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df2 = df.copy()\n",
      "df2.ix[:10, 0] = np.nan\n",
      "df2.ix[-10:, 1] = np.nan"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "cov(df2)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "oops, we want to limit the data here"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "cov(df2.dropna())"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df2.cov()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Take home: how to implement this NA handling generically for arbitrary sized DataFrame?"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "### Data alignment"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.ix[:5]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.ix[6:11]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.ix[:5] + df.ix[6:11]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "#### What is df.ix[:5] + df.ix[6:11]?"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "### Creating lagged/lead data with shift"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.head()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.head().shift()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.Price.pct_change()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "### reindex"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "every_other = df.ix[[0, 1, 2, 5, 7]]\n",
      "every_other"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "missing = every_other.reindex(df.ix[:20].index)\n",
      "missing"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df = ticks.ix[:, ['Price', 'Volume']]\n",
      "df"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df = ticks.reindex(columns=['Price', 'Volume'])\n",
      "df"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "## Missing Data"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "missing"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "#### Pandas NA handling is intrinsic"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "missing.mean()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "missing.values"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "missing.values.mean(axis=0)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "#### We can fill NAs"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "missing.Price"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "missing.Price.fillna(method='ffill')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "missing.Price.fillna(method='bfill')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "missing.Price.fillna(method='ffill', limit=3)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Does the filling method matter? How? Which is appropriate for this data? for your data?"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "We can fill using constant values"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "missing.Price.fillna(missing.Price.mean())"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "We can interpolate"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "missing.Price.interpolate()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "missing.apply(Series.interpolate)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "We can ignore NAs"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "missing.dropna()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "## Groupby"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Let's read in the data again without combining columns"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "ticks = pd.read_csv(path, parse_dates=['Date'])\n",
      "ticks"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df = ticks.ix[:, ['Price', 'Volume']]\n",
      "df"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df['Returns'] = df.Price.pct_change()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "grouped = df.groupby(ticks.Date)\n",
      "grouped"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "grouped.Returns.mean()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "grouped.Volume.sum()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "grouped.Price.std()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "### Custom aggregations"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Compounded returns"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "grouped.Returns.agg(lambda x: (1 + x).prod() - 1)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Daily volume weighted average price"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "grouped.agg(lambda x: (x.Price * x.Volume).sum() / x.Volume.sum())"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Daily percent change in price"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "grouped.agg(lambda x: x.irow(-1) / x.irow(0) - 1)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "We could also have used first and last"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "grouped.last() / grouped.first() - 1"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Difference here is that first/last gets the first/last non-na element"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "### Non-aggregating Manipulations"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "rs = grouped.transform(lambda x: (x - x.mean()) / x.std())\n",
      "rs"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "rs.min()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "rs.max()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "rs.mean()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "rs.std()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "ticks"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df = ticks.ix[:, ['Date', 'Time', 'Price', 'Volume']]\n",
      "df"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df = df.set_index(['Date', 'Time'])\n",
      "df"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "rets = df.Price / df.Price.shift(5) - 1\n",
      "rets"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "grouped = df.groupby(level=0)\n",
      "rs = grouped.transform(lambda x: (x / x.shift(5) - 1).cumsum())\n",
      "returns = rs.Price\n",
      "returns"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "### Practical 3\n",
      "\n",
      "Implement function that:\n",
      "\n",
      "- takes in a DataFrame with intraday data and a mapping for dates\n",
      "- compute the raw 2nd moment for each day"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "def raw_var(df, dates):\n",
      "    return df.groupby(dates).agg(lambda x: (x**2).sum() / (x.count() - 1))"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "raw_var(df, ticks.Date)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Are student solutions robust to NAs?"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    }
   ],
   "metadata": {}
  }
 ]
}