Lec 34 - Pivoting.ipynb

{
 "metadata": {
  "name": "",
  "signature": "sha256:1048b6a43fd8bac8d5856ed4783dc109901e124b69a3134cb301899bee2daf23"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import numpy as np\n",
      "import pandas as pd\n",
      "from pandas import Series, DataFrame"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 1
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Lets create some data to play with:\n",
      "\n",
      "# Note: It is not necessary to understand how this dataset was made to understand this Lecture.\n",
      "\n",
      "#import pandas testing utility\n",
      "import pandas.util.testing as tm; tm.N = 3\n",
      "\n",
      "#Create a unpivoted function\n",
      "def unpivot(frame):\n",
      "    N, K = frame.shape\n",
      "    \n",
      "    data = {'value' : frame.values.ravel('F'),\n",
      "            'variable' : np.asarray(frame.columns).repeat(N),\n",
      "            'date' : np.tile(np.asarray(frame.index), K)}\n",
      "    \n",
      "    # Return the DataFrame\n",
      "    return DataFrame(data, columns=['date', 'variable', 'value'])\n",
      "\n",
      "#Set the DataFrame we'll be using\n",
      "dframe = unpivot(tm.makeTimeDataFrame())"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 62
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#Show the \"stacked\" data, note how there are multiple variables and values for the dates\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>date</th>\n",
        "      <th>variable</th>\n",
        "      <th>value</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0 </th>\n",
        "      <td>2000-01-03</td>\n",
        "      <td> A</td>\n",
        "      <td>-0.157500</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1 </th>\n",
        "      <td>2000-01-04</td>\n",
        "      <td> A</td>\n",
        "      <td>-0.200030</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2 </th>\n",
        "      <td>2000-01-05</td>\n",
        "      <td> A</td>\n",
        "      <td> 1.395275</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>3 </th>\n",
        "      <td>2000-01-03</td>\n",
        "      <td> B</td>\n",
        "      <td> 0.553046</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>4 </th>\n",
        "      <td>2000-01-04</td>\n",
        "      <td> B</td>\n",
        "      <td> 0.393459</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>5 </th>\n",
        "      <td>2000-01-05</td>\n",
        "      <td> B</td>\n",
        "      <td> 0.176259</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>6 </th>\n",
        "      <td>2000-01-03</td>\n",
        "      <td> C</td>\n",
        "      <td> 1.652481</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>7 </th>\n",
        "      <td>2000-01-04</td>\n",
        "      <td> C</td>\n",
        "      <td> 1.645395</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>8 </th>\n",
        "      <td>2000-01-05</td>\n",
        "      <td> C</td>\n",
        "      <td> 0.311638</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>9 </th>\n",
        "      <td>2000-01-03</td>\n",
        "      <td> D</td>\n",
        "      <td>-1.394883</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>10</th>\n",
        "      <td>2000-01-04</td>\n",
        "      <td> D</td>\n",
        "      <td> 0.067484</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>11</th>\n",
        "      <td>2000-01-05</td>\n",
        "      <td> D</td>\n",
        "      <td>-0.819208</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 63,
       "text": [
        "         date variable     value\n",
        "0  2000-01-03        A -0.157500\n",
        "1  2000-01-04        A -0.200030\n",
        "2  2000-01-05        A  1.395275\n",
        "3  2000-01-03        B  0.553046\n",
        "4  2000-01-04        B  0.393459\n",
        "5  2000-01-05        B  0.176259\n",
        "6  2000-01-03        C  1.652481\n",
        "7  2000-01-04        C  1.645395\n",
        "8  2000-01-05        C  0.311638\n",
        "9  2000-01-03        D -1.394883\n",
        "10 2000-01-04        D  0.067484\n",
        "11 2000-01-05        D -0.819208"
       ]
      }
     ],
     "prompt_number": 63
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Now let's pivot the data\n",
      "\n",
      "# First two value spassed are teh row and column indexes, then finally an optional fill value\n",
      "dframe_piv = dframe.pivot('date','variable','value')\n",
      "\n",
      "#Show\n",
      "dframe_piv"
     ],
     "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>variable</th>\n",
        "      <th>A</th>\n",
        "      <th>B</th>\n",
        "      <th>C</th>\n",
        "      <th>D</th>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>date</th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>2000-01-03</th>\n",
        "      <td>-0.157500</td>\n",
        "      <td> 0.553046</td>\n",
        "      <td> 1.652481</td>\n",
        "      <td>-1.394883</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2000-01-04</th>\n",
        "      <td>-0.200030</td>\n",
        "      <td> 0.393459</td>\n",
        "      <td> 1.645395</td>\n",
        "      <td> 0.067484</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2000-01-05</th>\n",
        "      <td> 1.395275</td>\n",
        "      <td> 0.176259</td>\n",
        "      <td> 0.311638</td>\n",
        "      <td>-0.819208</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 68,
       "text": [
        "variable           A         B         C         D\n",
        "date                                              \n",
        "2000-01-03 -0.157500  0.553046  1.652481 -1.394883\n",
        "2000-01-04 -0.200030  0.393459  1.645395  0.067484\n",
        "2000-01-05  1.395275  0.176259  0.311638 -0.819208"
       ]
      }
     ],
     "prompt_number": 68
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#Next we'll learn about duplicates in DataFrames!"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 70
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    }
   ],
   "metadata": {}
  }
 ]
}