Lec 30 -Merge on Index.ipynb

{
 "metadata": {
  "name": "",
  "signature": "sha256:9d2b78dc10a7c3289dc813b27868f18aacabaa7b752e0f5e67f3d853d15fce61"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import pandas as pd\n",
      "from pandas import Series, DataFrame\n",
      "import numpy as np\n",
      "#Now we'll learn how to merge on an index"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 12
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Lets get two dframes\n",
      "\n",
      "df_left = DataFrame({'key': ['X','Y','Z','X','Y'],\n",
      "                  'data': range(5)})\n",
      "df_right = DataFrame({'group_data': [10, 20]}, index=['X', 'Y'])"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 4
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#Show\n",
      "df_left"
     ],
     "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>data</th>\n",
        "      <th>key</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0</th>\n",
        "      <td> 0</td>\n",
        "      <td> X</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td> 1</td>\n",
        "      <td> Y</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2</th>\n",
        "      <td> 2</td>\n",
        "      <td> Z</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>3</th>\n",
        "      <td> 3</td>\n",
        "      <td> X</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>4</th>\n",
        "      <td> 4</td>\n",
        "      <td> Y</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 5,
       "text": [
        "   data key\n",
        "0     0   X\n",
        "1     1   Y\n",
        "2     2   Z\n",
        "3     3   X\n",
        "4     4   Y"
       ]
      }
     ],
     "prompt_number": 5
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#Show\n",
      "df_right"
     ],
     "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>group_data</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>X</th>\n",
        "      <td> 10</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>Y</th>\n",
        "      <td> 20</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 7,
       "text": [
        "   group_data\n",
        "X          10\n",
        "Y          20"
       ]
      }
     ],
     "prompt_number": 7
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#Now merge, we'll use the key for the left Dframe, and the index for the right\n",
      "pd.merge(df_left,df_right,left_on='key',right_index=True)"
     ],
     "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>data</th>\n",
        "      <th>key</th>\n",
        "      <th>group_data</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0</th>\n",
        "      <td> 0</td>\n",
        "      <td> X</td>\n",
        "      <td> 10</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>3</th>\n",
        "      <td> 3</td>\n",
        "      <td> X</td>\n",
        "      <td> 10</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td> 1</td>\n",
        "      <td> Y</td>\n",
        "      <td> 20</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>4</th>\n",
        "      <td> 4</td>\n",
        "      <td> Y</td>\n",
        "      <td> 20</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 8,
       "text": [
        "   data key  group_data\n",
        "0     0   X          10\n",
        "3     3   X          10\n",
        "1     1   Y          20\n",
        "4     4   Y          20"
       ]
      }
     ],
     "prompt_number": 8
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# We can also get a union by using outer\n",
      "pd.merge(df_left,df_right,left_on='key',right_index=True,how='outer')"
     ],
     "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>data</th>\n",
        "      <th>key</th>\n",
        "      <th>group_data</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0</th>\n",
        "      <td> 0</td>\n",
        "      <td> X</td>\n",
        "      <td> 10</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>3</th>\n",
        "      <td> 3</td>\n",
        "      <td> X</td>\n",
        "      <td> 10</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td> 1</td>\n",
        "      <td> Y</td>\n",
        "      <td> 20</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>4</th>\n",
        "      <td> 4</td>\n",
        "      <td> Y</td>\n",
        "      <td> 20</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2</th>\n",
        "      <td> 2</td>\n",
        "      <td> Z</td>\n",
        "      <td>NaN</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 10,
       "text": [
        "   data key  group_data\n",
        "0     0   X          10\n",
        "3     3   X          10\n",
        "1     1   Y          20\n",
        "4     4   Y          20\n",
        "2     2   Z         NaN"
       ]
      }
     ],
     "prompt_number": 10
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#Now let's try something a little more complicated, remember hierarchal index?\n",
      "df_left_hr = DataFrame({'key1': ['SF','SF','SF','LA','LA'],\n",
      "                   'key2': [10, 20, 30, 20, 30],\n",
      "                   'data_set': np.arange(5.)})\n",
      "df_right_hr = DataFrame(np.arange(10).reshape((5, 2)),\n",
      "                   index=[['LA','LA','SF','SF','SF'],\n",
      "                          [20, 10, 10, 10, 20]],\n",
      "                   columns=['col_1', 'col_2'])\n"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 13
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#SHOW\n",
      "df_left_hr"
     ],
     "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>data_set</th>\n",
        "      <th>key1</th>\n",
        "      <th>key2</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0</th>\n",
        "      <td> 0</td>\n",
        "      <td> SF</td>\n",
        "      <td> 10</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td> 1</td>\n",
        "      <td> SF</td>\n",
        "      <td> 20</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2</th>\n",
        "      <td> 2</td>\n",
        "      <td> SF</td>\n",
        "      <td> 30</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>3</th>\n",
        "      <td> 3</td>\n",
        "      <td> LA</td>\n",
        "      <td> 20</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>4</th>\n",
        "      <td> 4</td>\n",
        "      <td> LA</td>\n",
        "      <td> 30</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 14,
       "text": [
        "   data_set key1  key2\n",
        "0         0   SF    10\n",
        "1         1   SF    20\n",
        "2         2   SF    30\n",
        "3         3   LA    20\n",
        "4         4   LA    30"
       ]
      }
     ],
     "prompt_number": 14
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#Show, this has a index hierarchy\n",
      "df_right_hr"
     ],
     "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></th>\n",
        "      <th>col_1</th>\n",
        "      <th>col_2</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th rowspan=\"2\" valign=\"top\">LA</th>\n",
        "      <th>20</th>\n",
        "      <td> 0</td>\n",
        "      <td> 1</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>10</th>\n",
        "      <td> 2</td>\n",
        "      <td> 3</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th rowspan=\"3\" valign=\"top\">SF</th>\n",
        "      <th>10</th>\n",
        "      <td> 4</td>\n",
        "      <td> 5</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>10</th>\n",
        "      <td> 6</td>\n",
        "      <td> 7</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>20</th>\n",
        "      <td> 8</td>\n",
        "      <td> 9</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 15,
       "text": [
        "       col_1  col_2\n",
        "LA 20      0      1\n",
        "   10      2      3\n",
        "SF 10      4      5\n",
        "   10      6      7\n",
        "   20      8      9"
       ]
      }
     ],
     "prompt_number": 15
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Now we can merge the left by using keys and the right by its index\n",
      "pd.merge(df_left_hr,df_right_hr,left_on=['key1','key2'],right_index=True)"
     ],
     "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>data_set</th>\n",
        "      <th>key1</th>\n",
        "      <th>key2</th>\n",
        "      <th>col_1</th>\n",
        "      <th>col_2</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0</th>\n",
        "      <td> 0</td>\n",
        "      <td> SF</td>\n",
        "      <td> 10</td>\n",
        "      <td> 4</td>\n",
        "      <td> 5</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>0</th>\n",
        "      <td> 0</td>\n",
        "      <td> SF</td>\n",
        "      <td> 10</td>\n",
        "      <td> 6</td>\n",
        "      <td> 7</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td> 1</td>\n",
        "      <td> SF</td>\n",
        "      <td> 20</td>\n",
        "      <td> 8</td>\n",
        "      <td> 9</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>3</th>\n",
        "      <td> 3</td>\n",
        "      <td> LA</td>\n",
        "      <td> 20</td>\n",
        "      <td> 0</td>\n",
        "      <td> 1</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 16,
       "text": [
        "   data_set key1  key2  col_1  col_2\n",
        "0         0   SF    10      4      5\n",
        "0         0   SF    10      6      7\n",
        "1         1   SF    20      8      9\n",
        "3         3   LA    20      0      1"
       ]
      }
     ],
     "prompt_number": 16
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# We can alo keep a union by choosing 'outer' method\n",
      "pd.merge(df_left_hr,df_right_hr,left_on=['key1','key2'],right_index=True,how='outer')"
     ],
     "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>data_set</th>\n",
        "      <th>key1</th>\n",
        "      <th>key2</th>\n",
        "      <th>col_1</th>\n",
        "      <th>col_2</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0</th>\n",
        "      <td>  0</td>\n",
        "      <td> SF</td>\n",
        "      <td> 10</td>\n",
        "      <td>  4</td>\n",
        "      <td>  5</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>0</th>\n",
        "      <td>  0</td>\n",
        "      <td> SF</td>\n",
        "      <td> 10</td>\n",
        "      <td>  6</td>\n",
        "      <td>  7</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td>  1</td>\n",
        "      <td> SF</td>\n",
        "      <td> 20</td>\n",
        "      <td>  8</td>\n",
        "      <td>  9</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2</th>\n",
        "      <td>  2</td>\n",
        "      <td> SF</td>\n",
        "      <td> 30</td>\n",
        "      <td>NaN</td>\n",
        "      <td>NaN</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>3</th>\n",
        "      <td>  3</td>\n",
        "      <td> LA</td>\n",
        "      <td> 20</td>\n",
        "      <td>  0</td>\n",
        "      <td>  1</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>4</th>\n",
        "      <td>  4</td>\n",
        "      <td> LA</td>\n",
        "      <td> 30</td>\n",
        "      <td>NaN</td>\n",
        "      <td>NaN</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>4</th>\n",
        "      <td>NaN</td>\n",
        "      <td> LA</td>\n",
        "      <td> 10</td>\n",
        "      <td>  2</td>\n",
        "      <td>  3</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 17,
       "text": [
        "   data_set key1  key2  col_1  col_2\n",
        "0         0   SF    10      4      5\n",
        "0         0   SF    10      6      7\n",
        "1         1   SF    20      8      9\n",
        "2         2   SF    30    NaN    NaN\n",
        "3         3   LA    20      0      1\n",
        "4         4   LA    30    NaN    NaN\n",
        "4       NaN   LA    10      2      3"
       ]
      }
     ],
     "prompt_number": 17
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# WE can also you .join()\n",
      "\n",
      "# Shown on our first two DataFrames\n",
      "df_left.join(df_right)"
     ],
     "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>data</th>\n",
        "      <th>key</th>\n",
        "      <th>group_data</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0</th>\n",
        "      <td> 0</td>\n",
        "      <td> X</td>\n",
        "      <td>NaN</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td> 1</td>\n",
        "      <td> Y</td>\n",
        "      <td>NaN</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2</th>\n",
        "      <td> 2</td>\n",
        "      <td> Z</td>\n",
        "      <td>NaN</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>3</th>\n",
        "      <td> 3</td>\n",
        "      <td> X</td>\n",
        "      <td>NaN</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>4</th>\n",
        "      <td> 4</td>\n",
        "      <td> Y</td>\n",
        "      <td>NaN</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 23,
       "text": [
        "   data key  group_data\n",
        "0     0   X         NaN\n",
        "1     1   Y         NaN\n",
        "2     2   Z         NaN\n",
        "3     3   X         NaN\n",
        "4     4   Y         NaN"
       ]
      }
     ],
     "prompt_number": 23
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Next we'll learn about the concatenate function!"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    }
   ],
   "metadata": {}
  }
 ]
}