0003 Data Alignment with Python pandas DataFrame.ipynb

{
 "metadata": {
  "name": "",
  "signature": "sha256:3d1b0a2a5640a9723a8e75d088e6d92821119f598d4a7380e07529286c3144fb"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "## Setup"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import pandas as pd\n",
      "import numpy as np\n",
      "\n",
      "# turn of data table rendering\n",
      "pd.set_option('display.notebook_repr_html', False) \n",
      "\n",
      "# This function is to show two data structures side by side\n",
      "# Used in Web McKinney's presentations: http://www.youtube.com/watch?v=w26x-z-BdWQ\n",
      "def side_by_side(*objs, **kwds):\n",
      "    from pandas.core.common import adjoin\n",
      "    space = kwds.get('space', 4)\n",
      "    reprs = [repr(obj).split('\\n') for obj in objs]\n",
      "    print adjoin(space, *reprs)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 1
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "## Reading from CSV file"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Reading stock data from csv. Used in a pandas tutorial by Wes McKinney. \n",
      "df = pd.read_csv('data\\stock_data.csv', index_col=0, parse_dates=True)\n",
      "\n",
      "# Take a look at the last 5 rows\n",
      "df.tail()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 2,
       "text": [
        "               AA    AAPL     GE     IBM    JNJ   MSFT    PEP      SPX    XOM\n",
        "2011-10-10  10.09  388.81  16.14  186.62  64.43  26.94  61.87  1194.89  76.28\n",
        "2011-10-11  10.30  400.29  16.14  185.00  63.96  27.00  60.95  1195.54  76.27\n",
        "2011-10-12  10.05  402.19  16.40  186.12  64.33  26.96  62.70  1207.25  77.16\n",
        "2011-10-13  10.10  408.43  16.22  186.82  64.23  27.18  62.36  1203.66  76.37\n",
        "2011-10-14  10.26  422.00  16.60  190.53  64.72  27.27  62.24  1224.58  78.11"
       ]
      }
     ],
     "prompt_number": 2
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Query the number of rows\n",
      "len(df)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 3,
       "text": [
        "1000"
       ]
      }
     ],
     "prompt_number": 3
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Slices of the DataFrame of IBM stock\n",
      "s1 = df.IBM[10:20]\n",
      "s2 = df.IBM[5:15]\n",
      "print s1.head()\n",
      "print s2.head()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "2007-11-12    94.70\n",
        "2007-11-13    98.27\n",
        "2007-11-14    96.56\n",
        "2007-11-15    96.71\n",
        "2007-11-16    97.82\n",
        "Name: IBM, dtype: float64\n",
        "2007-11-05    105.48\n",
        "2007-11-06    105.27\n",
        "2007-11-07    103.69\n",
        "2007-11-08     99.05\n",
        "2007-11-09     93.58\n",
        "Name: IBM, dtype: float64\n"
       ]
      }
     ],
     "prompt_number": 4
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Union (of the index) and sum where both values are not NaN\n",
      "s1 + s2"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 5,
       "text": [
        "2007-11-05       NaN\n",
        "2007-11-06       NaN\n",
        "2007-11-07       NaN\n",
        "2007-11-08       NaN\n",
        "2007-11-09       NaN\n",
        "2007-11-12    189.40\n",
        "2007-11-13    196.54\n",
        "2007-11-14    193.12\n",
        "2007-11-15    193.42\n",
        "2007-11-16    195.64\n",
        "2007-11-19       NaN\n",
        "2007-11-20       NaN\n",
        "2007-11-21       NaN\n",
        "2007-11-23       NaN\n",
        "2007-11-26       NaN\n",
        "Name: IBM, dtype: float64"
       ]
      }
     ],
     "prompt_number": 5
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Same thing but now stripped of the NaN values\n",
      "(s1 + s2).dropna()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 6,
       "text": [
        "2007-11-12    189.40\n",
        "2007-11-13    196.54\n",
        "2007-11-14    193.12\n",
        "2007-11-15    193.42\n",
        "2007-11-16    195.64\n",
        "Name: IBM, dtype: float64"
       ]
      }
     ],
     "prompt_number": 6
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "## Align"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Align s1 and s2 with an outer join = default\n",
      "a, b = s1.align(s2, join='outer')\n",
      "side_by_side(a, b)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "2007-11-05      NaN          2007-11-05    105.48     \n",
        "2007-11-06      NaN          2007-11-06    105.27     \n",
        "2007-11-07      NaN          2007-11-07    103.69     \n",
        "2007-11-08      NaN          2007-11-08     99.05     \n",
        "2007-11-09      NaN          2007-11-09     93.58     \n",
        "2007-11-12    94.70          2007-11-12     94.70     \n",
        "2007-11-13    98.27          2007-11-13     98.27     \n",
        "2007-11-14    96.56          2007-11-14     96.56     \n",
        "2007-11-15    96.71          2007-11-15     96.71     \n",
        "2007-11-16    97.82          2007-11-16     97.82     \n",
        "2007-11-19    95.42          2007-11-19       NaN     \n",
        "2007-11-20    96.54          2007-11-20       NaN     \n",
        "2007-11-21    95.42          2007-11-21       NaN     \n",
        "2007-11-23    97.13          2007-11-23       NaN     \n",
        "2007-11-26    95.19          2007-11-26       NaN     \n",
        "Name: IBM, dtype: float64    Name: IBM, dtype: float64\n"
       ]
      }
     ],
     "prompt_number": 7
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Align s1 and s2 with an inner join\n",
      "a, b = s1.align(s2, join='inner')\n",
      "side_by_side(a, b)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "2007-11-12    94.70          2007-11-12    94.70      \n",
        "2007-11-13    98.27          2007-11-13    98.27      \n",
        "2007-11-14    96.56          2007-11-14    96.56      \n",
        "2007-11-15    96.71          2007-11-15    96.71      \n",
        "2007-11-16    97.82          2007-11-16    97.82      \n",
        "Name: IBM, dtype: float64    Name: IBM, dtype: float64\n"
       ]
      }
     ],
     "prompt_number": 8
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Align s1 and s2 with a left join\n",
      "a, b = s1.align(s2, join='left')\n",
      "side_by_side(a, b)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "2007-11-12    94.70          2007-11-12    94.70      \n",
        "2007-11-13    98.27          2007-11-13    98.27      \n",
        "2007-11-14    96.56          2007-11-14    96.56      \n",
        "2007-11-15    96.71          2007-11-15    96.71      \n",
        "2007-11-16    97.82          2007-11-16    97.82      \n",
        "2007-11-19    95.42          2007-11-19      NaN      \n",
        "2007-11-20    96.54          2007-11-20      NaN      \n",
        "2007-11-21    95.42          2007-11-21      NaN      \n",
        "2007-11-23    97.13          2007-11-23      NaN      \n",
        "2007-11-26    95.19          2007-11-26      NaN      \n",
        "Name: IBM, dtype: float64    Name: IBM, dtype: float64\n"
       ]
      }
     ],
     "prompt_number": 9
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Align s1 and s2 with a right join and set value to 0 if NaN\n",
      "a, b = s1.align(s2, join='right', fill_value=0)\n",
      "side_by_side(a, b)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "2007-11-05     0.00    2007-11-05    105.48     \n",
        "2007-11-06     0.00    2007-11-06    105.27     \n",
        "2007-11-07     0.00    2007-11-07    103.69     \n",
        "2007-11-08     0.00    2007-11-08     99.05     \n",
        "2007-11-09     0.00    2007-11-09     93.58     \n",
        "2007-11-12    94.70    2007-11-12     94.70     \n",
        "2007-11-13    98.27    2007-11-13     98.27     \n",
        "2007-11-14    96.56    2007-11-14     96.56     \n",
        "2007-11-15    96.71    2007-11-15     96.71     \n",
        "2007-11-16    97.82    2007-11-16     97.82     \n",
        "dtype: float64         Name: IBM, dtype: float64\n"
       ]
      }
     ],
     "prompt_number": 10
    }
   ],
   "metadata": {}
  }
 ]
}