Lec 17 -Reindexing.ipynb

{
 "metadata": {
  "name": "",
  "signature": "sha256:e4728041de7a97749774b67398234808260f8ecae049c50c482fa7766290a3d4"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#Now we'll elarn about reindexing"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 1
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import numpy as np\n",
      "from pandas import Series, DataFrame\n",
      "import pandas as pd\n",
      "from numpy.random import randn"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 23
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#Lets create a new series\n",
      "ser1 = Series([1,2,3,4],index=['A','B','C','D'])"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 4
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#Show\n",
      "ser1"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 5,
       "text": [
        "A    1\n",
        "B    2\n",
        "C    3\n",
        "D    4\n",
        "dtype: int64"
       ]
      }
     ],
     "prompt_number": 5
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#Call reindex to rearrange the data to a new index\n",
      "ser2 = ser1.reindex(['A','B','C','D','E','F'])"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 6
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#Show\n",
      "ser2"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 7,
       "text": [
        "A     1\n",
        "B     2\n",
        "C     3\n",
        "D     4\n",
        "E   NaN\n",
        "F   NaN\n",
        "dtype: float64"
       ]
      }
     ],
     "prompt_number": 7
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# We can alos fill in values for new indexes\n",
      "ser2.reindex(['A','B','C','D','E','F','G'],fill_value=0)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 13,
       "text": [
        "A     1\n",
        "B     2\n",
        "C     3\n",
        "D     4\n",
        "E   NaN\n",
        "F   NaN\n",
        "G     0\n",
        "dtype: float64"
       ]
      }
     ],
     "prompt_number": 13
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#Using a particular method for filling values\n",
      "ser3 = Series(['USA','Mexico','Canada'],index=[0,5,10])\n",
      "\n",
      "#Show\n",
      "ser3"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 15,
       "text": [
        "0        USA\n",
        "5     Mexico\n",
        "10    Canada\n",
        "dtype: object"
       ]
      }
     ],
     "prompt_number": 15
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#Can use a forward fill for interploating values vetween indices \n",
      "ser3.reindex(range(15),method='ffill')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 20,
       "text": [
        "0        USA\n",
        "1        USA\n",
        "2        USA\n",
        "3        USA\n",
        "4        USA\n",
        "5     Mexico\n",
        "6     Mexico\n",
        "7     Mexico\n",
        "8     Mexico\n",
        "9     Mexico\n",
        "10    Canada\n",
        "11    Canada\n",
        "12    Canada\n",
        "13    Canada\n",
        "14    Canada\n",
        "dtype: object"
       ]
      }
     ],
     "prompt_number": 20
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#Reindexing rows, columns or both\n",
      "\n",
      "#Lets make a datafram ewith some random values\n",
      "dframe = DataFrame(randn(25).reshape((5,5)),index=['A','B','D','E','F'],columns=['col1','col2','col3','col4','col5'])\n",
      "\n",
      "#Show\n",
      "dframe"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 29
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#Notice we forgot 'C' , lets reindex it into dframe\n",
      "dframe2 = dframe.reindex(['A','B','C','D','E','F'])\n"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 37
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#Can also explicitly reindex columns\n",
      "new_columns = ['col1','col2','col3','col4','col5','col6']\n",
      "\n",
      "dframe2.reindex(columns=new_columns)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>col1</th>\n",
        "      <th>col2</th>\n",
        "      <th>col3</th>\n",
        "      <th>col4</th>\n",
        "      <th>col5</th>\n",
        "      <th>col6</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>A</th>\n",
        "      <td> 0.442886</td>\n",
        "      <td> 0.128326</td>\n",
        "      <td> 0.091837</td>\n",
        "      <td> 0.018814</td>\n",
        "      <td>-1.419262</td>\n",
        "      <td>NaN</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>B</th>\n",
        "      <td> 0.650227</td>\n",
        "      <td> 0.438677</td>\n",
        "      <td>-0.208091</td>\n",
        "      <td> 1.263344</td>\n",
        "      <td> 0.147084</td>\n",
        "      <td>NaN</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>C</th>\n",
        "      <td>      NaN</td>\n",
        "      <td>      NaN</td>\n",
        "      <td>      NaN</td>\n",
        "      <td>      NaN</td>\n",
        "      <td>      NaN</td>\n",
        "      <td>NaN</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>D</th>\n",
        "      <td>-1.089677</td>\n",
        "      <td> 1.616700</td>\n",
        "      <td> 1.856270</td>\n",
        "      <td> 0.553392</td>\n",
        "      <td> 1.046027</td>\n",
        "      <td>NaN</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>E</th>\n",
        "      <td> 0.608385</td>\n",
        "      <td>-0.316224</td>\n",
        "      <td> 0.341510</td>\n",
        "      <td>-0.085305</td>\n",
        "      <td> 1.015604</td>\n",
        "      <td>NaN</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>F</th>\n",
        "      <td> 1.054495</td>\n",
        "      <td>-0.274723</td>\n",
        "      <td> 1.251613</td>\n",
        "      <td> 0.160329</td>\n",
        "      <td>-0.527855</td>\n",
        "      <td>NaN</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 38,
       "text": [
        "       col1      col2      col3      col4      col5  col6\n",
        "A  0.442886  0.128326  0.091837  0.018814 -1.419262   NaN\n",
        "B  0.650227  0.438677 -0.208091  1.263344  0.147084   NaN\n",
        "C       NaN       NaN       NaN       NaN       NaN   NaN\n",
        "D -1.089677  1.616700  1.856270  0.553392  1.046027   NaN\n",
        "E  0.608385 -0.316224  0.341510 -0.085305  1.015604   NaN\n",
        "F  1.054495 -0.274723  1.251613  0.160329 -0.527855   NaN"
       ]
      }
     ],
     "prompt_number": 38
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#Reindex quickly using the label-indexing with ix (we'll see this more in the future)\n",
      "\n",
      "#Show original\n",
      "dframe"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>col1</th>\n",
        "      <th>col2</th>\n",
        "      <th>col3</th>\n",
        "      <th>col4</th>\n",
        "      <th>col5</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>A</th>\n",
        "      <td> 0.442886</td>\n",
        "      <td> 0.128326</td>\n",
        "      <td> 0.091837</td>\n",
        "      <td> 0.018814</td>\n",
        "      <td>-1.419262</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>B</th>\n",
        "      <td> 0.650227</td>\n",
        "      <td> 0.438677</td>\n",
        "      <td>-0.208091</td>\n",
        "      <td> 1.263344</td>\n",
        "      <td> 0.147084</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>D</th>\n",
        "      <td>-1.089677</td>\n",
        "      <td> 1.616700</td>\n",
        "      <td> 1.856270</td>\n",
        "      <td> 0.553392</td>\n",
        "      <td> 1.046027</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>E</th>\n",
        "      <td> 0.608385</td>\n",
        "      <td>-0.316224</td>\n",
        "      <td> 0.341510</td>\n",
        "      <td>-0.085305</td>\n",
        "      <td> 1.015604</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>F</th>\n",
        "      <td> 1.054495</td>\n",
        "      <td>-0.274723</td>\n",
        "      <td> 1.251613</td>\n",
        "      <td> 0.160329</td>\n",
        "      <td>-0.527855</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 40,
       "text": [
        "       col1      col2      col3      col4      col5\n",
        "A  0.442886  0.128326  0.091837  0.018814 -1.419262\n",
        "B  0.650227  0.438677 -0.208091  1.263344  0.147084\n",
        "D -1.089677  1.616700  1.856270  0.553392  1.046027\n",
        "E  0.608385 -0.316224  0.341510 -0.085305  1.015604\n",
        "F  1.054495 -0.274723  1.251613  0.160329 -0.527855"
       ]
      }
     ],
     "prompt_number": 40
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "dframe.ix[['A','B','C','D','E','F'],new_columns]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>col1</th>\n",
        "      <th>col2</th>\n",
        "      <th>col3</th>\n",
        "      <th>col4</th>\n",
        "      <th>col5</th>\n",
        "      <th>col6</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>A</th>\n",
        "      <td> 0.442886</td>\n",
        "      <td> 0.128326</td>\n",
        "      <td> 0.091837</td>\n",
        "      <td> 0.018814</td>\n",
        "      <td>-1.419262</td>\n",
        "      <td>NaN</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>B</th>\n",
        "      <td> 0.650227</td>\n",
        "      <td> 0.438677</td>\n",
        "      <td>-0.208091</td>\n",
        "      <td> 1.263344</td>\n",
        "      <td> 0.147084</td>\n",
        "      <td>NaN</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>C</th>\n",
        "      <td>      NaN</td>\n",
        "      <td>      NaN</td>\n",
        "      <td>      NaN</td>\n",
        "      <td>      NaN</td>\n",
        "      <td>      NaN</td>\n",
        "      <td>NaN</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>D</th>\n",
        "      <td>-1.089677</td>\n",
        "      <td> 1.616700</td>\n",
        "      <td> 1.856270</td>\n",
        "      <td> 0.553392</td>\n",
        "      <td> 1.046027</td>\n",
        "      <td>NaN</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>E</th>\n",
        "      <td> 0.608385</td>\n",
        "      <td>-0.316224</td>\n",
        "      <td> 0.341510</td>\n",
        "      <td>-0.085305</td>\n",
        "      <td> 1.015604</td>\n",
        "      <td>NaN</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>F</th>\n",
        "      <td> 1.054495</td>\n",
        "      <td>-0.274723</td>\n",
        "      <td> 1.251613</td>\n",
        "      <td> 0.160329</td>\n",
        "      <td>-0.527855</td>\n",
        "      <td>NaN</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 41,
       "text": [
        "       col1      col2      col3      col4      col5  col6\n",
        "A  0.442886  0.128326  0.091837  0.018814 -1.419262   NaN\n",
        "B  0.650227  0.438677 -0.208091  1.263344  0.147084   NaN\n",
        "C       NaN       NaN       NaN       NaN       NaN   NaN\n",
        "D -1.089677  1.616700  1.856270  0.553392  1.046027   NaN\n",
        "E  0.608385 -0.316224  0.341510 -0.085305  1.015604   NaN\n",
        "F  1.054495 -0.274723  1.251613  0.160329 -0.527855   NaN"
       ]
      }
     ],
     "prompt_number": 41
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    }
   ],
   "metadata": {}
  }
 ]
}