pandas_join_merge_dataframe.ipynb

{
 "metadata": {
  "name": "",
  "signature": "sha256:2b21d1eef58133b1a129b47fb9327d68f2a04fa7ffd051eefd69697e63482584"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "# Join And Merge Pandas Dataframe\n",
      "\n",
      "This snippit was written by [Chris R. Albon](http://www.chrisralbon.com/) and is part of his collection of [well-documented Python snippits](https://github.com/chrisalbon/code_py). All code is written in Python 3 in iPython notebook and offered under the [Creative Commons Attribution-ShareAlike 4.0 International License](http://creativecommons.org/licenses/by-sa/4.0/)."
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "### import modules"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import pandas as pd\n",
      "from IPython.display import display\n",
      "from IPython.display import Image"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 2
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "### Create a dataframe"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "raw_data = {\n",
      "        'subject_id': ['1', '2', '3', '4', '5'],\n",
      "        'first_name': ['Alex', 'Amy', 'Allen', 'Alice', 'Ayoung'], \n",
      "        'last_name': ['Anderson', 'Ackerman', 'Ali', 'Aoni', 'Atiches']}\n",
      "df_a = pd.DataFrame(raw_data, columns = ['subject_id', 'first_name', 'last_name'])\n",
      "df_a"
     ],
     "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>subject_id</th>\n",
        "      <th>first_name</th>\n",
        "      <th>last_name</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0</th>\n",
        "      <td> 1</td>\n",
        "      <td>   Alex</td>\n",
        "      <td> Anderson</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td> 2</td>\n",
        "      <td>    Amy</td>\n",
        "      <td> Ackerman</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2</th>\n",
        "      <td> 3</td>\n",
        "      <td>  Allen</td>\n",
        "      <td>      Ali</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>3</th>\n",
        "      <td> 4</td>\n",
        "      <td>  Alice</td>\n",
        "      <td>     Aoni</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>4</th>\n",
        "      <td> 5</td>\n",
        "      <td> Ayoung</td>\n",
        "      <td>  Atiches</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "<p>5 rows \u00d7 3 columns</p>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 3,
       "text": [
        "  subject_id first_name last_name\n",
        "0          1       Alex  Anderson\n",
        "1          2        Amy  Ackerman\n",
        "2          3      Allen       Ali\n",
        "3          4      Alice      Aoni\n",
        "4          5     Ayoung   Atiches\n",
        "\n",
        "[5 rows x 3 columns]"
       ]
      }
     ],
     "prompt_number": 3
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "### Create a second dataframe"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "raw_data = {\n",
      "        'subject_id': ['4', '5', '6', '7', '8'],\n",
      "        'first_name': ['Billy', 'Brian', 'Bran', 'Bryce', 'Betty'], \n",
      "        'last_name': ['Bonder', 'Black', 'Balwner', 'Brice', 'Btisan']}\n",
      "df_b = pd.DataFrame(raw_data, columns = ['subject_id', 'first_name', 'last_name'])\n",
      "df_b"
     ],
     "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>subject_id</th>\n",
        "      <th>first_name</th>\n",
        "      <th>last_name</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0</th>\n",
        "      <td> 4</td>\n",
        "      <td> Billy</td>\n",
        "      <td>  Bonder</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td> 5</td>\n",
        "      <td> Brian</td>\n",
        "      <td>   Black</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2</th>\n",
        "      <td> 6</td>\n",
        "      <td>  Bran</td>\n",
        "      <td> Balwner</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>3</th>\n",
        "      <td> 7</td>\n",
        "      <td> Bryce</td>\n",
        "      <td>   Brice</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>4</th>\n",
        "      <td> 8</td>\n",
        "      <td> Betty</td>\n",
        "      <td>  Btisan</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "<p>5 rows \u00d7 3 columns</p>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 4,
       "text": [
        "  subject_id first_name last_name\n",
        "0          4      Billy    Bonder\n",
        "1          5      Brian     Black\n",
        "2          6       Bran   Balwner\n",
        "3          7      Bryce     Brice\n",
        "4          8      Betty    Btisan\n",
        "\n",
        "[5 rows x 3 columns]"
       ]
      }
     ],
     "prompt_number": 4
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "### Create a third dataframe"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "raw_data = {\n",
      "        'subject_id': ['1', '2', '3', '4', '5', '7', '8', '9', '10', '11'],\n",
      "        'test_id': [51, 15, 15, 61, 16, 14, 15, 1, 61, 16]}\n",
      "df_n = pd.DataFrame(raw_data, columns = ['subject_id','test_id'])\n",
      "df_n"
     ],
     "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>subject_id</th>\n",
        "      <th>test_id</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0</th>\n",
        "      <td>  1</td>\n",
        "      <td> 51</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td>  2</td>\n",
        "      <td> 15</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2</th>\n",
        "      <td>  3</td>\n",
        "      <td> 15</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>3</th>\n",
        "      <td>  4</td>\n",
        "      <td> 61</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>4</th>\n",
        "      <td>  5</td>\n",
        "      <td> 16</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>5</th>\n",
        "      <td>  7</td>\n",
        "      <td> 14</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>6</th>\n",
        "      <td>  8</td>\n",
        "      <td> 15</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>7</th>\n",
        "      <td>  9</td>\n",
        "      <td>  1</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>8</th>\n",
        "      <td> 10</td>\n",
        "      <td> 61</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>9</th>\n",
        "      <td> 11</td>\n",
        "      <td> 16</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "<p>10 rows \u00d7 2 columns</p>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 5,
       "text": [
        "  subject_id  test_id\n",
        "0          1       51\n",
        "1          2       15\n",
        "2          3       15\n",
        "3          4       61\n",
        "4          5       16\n",
        "5          7       14\n",
        "6          8       15\n",
        "7          9        1\n",
        "8         10       61\n",
        "9         11       16\n",
        "\n",
        "[10 rows x 2 columns]"
       ]
      }
     ],
     "prompt_number": 5
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "### Join the two dataframes along rows"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df_new = pd.concat([df_a, df_b])\n",
      "df_new"
     ],
     "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>subject_id</th>\n",
        "      <th>first_name</th>\n",
        "      <th>last_name</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0</th>\n",
        "      <td> 1</td>\n",
        "      <td>   Alex</td>\n",
        "      <td> Anderson</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td> 2</td>\n",
        "      <td>    Amy</td>\n",
        "      <td> Ackerman</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2</th>\n",
        "      <td> 3</td>\n",
        "      <td>  Allen</td>\n",
        "      <td>      Ali</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>3</th>\n",
        "      <td> 4</td>\n",
        "      <td>  Alice</td>\n",
        "      <td>     Aoni</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>4</th>\n",
        "      <td> 5</td>\n",
        "      <td> Ayoung</td>\n",
        "      <td>  Atiches</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>0</th>\n",
        "      <td> 4</td>\n",
        "      <td>  Billy</td>\n",
        "      <td>   Bonder</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td> 5</td>\n",
        "      <td>  Brian</td>\n",
        "      <td>    Black</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2</th>\n",
        "      <td> 6</td>\n",
        "      <td>   Bran</td>\n",
        "      <td>  Balwner</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>3</th>\n",
        "      <td> 7</td>\n",
        "      <td>  Bryce</td>\n",
        "      <td>    Brice</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>4</th>\n",
        "      <td> 8</td>\n",
        "      <td>  Betty</td>\n",
        "      <td>   Btisan</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "<p>10 rows \u00d7 3 columns</p>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 6,
       "text": [
        "  subject_id first_name last_name\n",
        "0          1       Alex  Anderson\n",
        "1          2        Amy  Ackerman\n",
        "2          3      Allen       Ali\n",
        "3          4      Alice      Aoni\n",
        "4          5     Ayoung   Atiches\n",
        "0          4      Billy    Bonder\n",
        "1          5      Brian     Black\n",
        "2          6       Bran   Balwner\n",
        "3          7      Bryce     Brice\n",
        "4          8      Betty    Btisan\n",
        "\n",
        "[10 rows x 3 columns]"
       ]
      }
     ],
     "prompt_number": 6
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "### Join the two dataframes along columns"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pd.concat([df_a, df_b], axis=1)"
     ],
     "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>subject_id</th>\n",
        "      <th>first_name</th>\n",
        "      <th>last_name</th>\n",
        "      <th>subject_id</th>\n",
        "      <th>first_name</th>\n",
        "      <th>last_name</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0</th>\n",
        "      <td> 1</td>\n",
        "      <td>   Alex</td>\n",
        "      <td> Anderson</td>\n",
        "      <td> 4</td>\n",
        "      <td> Billy</td>\n",
        "      <td>  Bonder</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td> 2</td>\n",
        "      <td>    Amy</td>\n",
        "      <td> Ackerman</td>\n",
        "      <td> 5</td>\n",
        "      <td> Brian</td>\n",
        "      <td>   Black</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2</th>\n",
        "      <td> 3</td>\n",
        "      <td>  Allen</td>\n",
        "      <td>      Ali</td>\n",
        "      <td> 6</td>\n",
        "      <td>  Bran</td>\n",
        "      <td> Balwner</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>3</th>\n",
        "      <td> 4</td>\n",
        "      <td>  Alice</td>\n",
        "      <td>     Aoni</td>\n",
        "      <td> 7</td>\n",
        "      <td> Bryce</td>\n",
        "      <td>   Brice</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>4</th>\n",
        "      <td> 5</td>\n",
        "      <td> Ayoung</td>\n",
        "      <td>  Atiches</td>\n",
        "      <td> 8</td>\n",
        "      <td> Betty</td>\n",
        "      <td>  Btisan</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "<p>5 rows \u00d7 6 columns</p>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 7,
       "text": [
        "  subject_id first_name last_name subject_id first_name last_name\n",
        "0          1       Alex  Anderson          4      Billy    Bonder\n",
        "1          2        Amy  Ackerman          5      Brian     Black\n",
        "2          3      Allen       Ali          6       Bran   Balwner\n",
        "3          4      Alice      Aoni          7      Bryce     Brice\n",
        "4          5     Ayoung   Atiches          8      Betty    Btisan\n",
        "\n",
        "[5 rows x 6 columns]"
       ]
      }
     ],
     "prompt_number": 7
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "### Merge two dataframes along the subject_id value"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pd.merge(df_new, df_n, on='subject_id')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "### Merge two dataframes with both the left and right dataframes using the subject_id key"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pd.merge(df_new, df_n, left_on='subject_id', right_on='subject_id')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "### Merge with outer join\n",
      "\n",
      "\"Full outer join produces the set of all records in Table A and Table B, with matching records from both sides where available. If there is no match, the missing side will contain null.\" - [source](http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/)"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pd.merge(df_a, df_b, on='subject_id', how='outer')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "### Merge with inner join\n",
      "\n",
      "\"Inner join produces only the set of records that match in both Table A and Table B.\" - [source](http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/)"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pd.merge(df_a, df_b, on='subject_id', how='inner')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "### Merge with right join"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pd.merge(df_a, df_b, on='subject_id', how='right')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "### Merge with left join\n",
      "\n",
      "\"Left outer join produces a complete set of records from Table A, with the matching records (where available) in Table B. If there is no match, the right side will contain null.\" - [source](http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/)"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pd.merge(df_a, df_b, on='subject_id', how='left')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "### Merge while adding a suffix to duplicate column names"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pd.merge(df_a, df_b, on='subject_id', how='left', suffixes=('_left', '_right'))"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "### Merge based on indexes"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "pd.merge(df_a, df_b, right_index=True, left_index=True)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    }
   ],
   "metadata": {}
  }
 ]
}