Exercise - 4 - Generate FMI Tickers for all 50 States, Index Resetting, Merge, Concat and Join of DataFrames.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Lets build a strategy for real estate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import Quandl as qd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### So, what governs the prices of homes, and do we need to do the research to find this out? Generally, no, you don't really need to do that digging, we know the factors. \n",
    "\n",
    "The factors for home prices are governed by: The economy, interest rates, and demographics. These are the three major influences in general for real estate value. \n",
    "\n",
    "Now, of course, if you're buying land, various other things matter, how level is it, are we going to need to do some work to the land before we can actually lay foundation, how is drainage etc. If there is a house, then we have even more factors, like the roof, windows, heating/AC, floors, foundation, and so on. We can begin to consider these factors later, but first we'll start at the macro level. You will see how quickly our data sets inflate here as it is, it'll blow up fast."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 177,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "tx = qd.get(\"FMAC/HPI_TX\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 178,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Value</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1975-01-31</th>\n",
       "      <td>32.618858</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1975-02-28</th>\n",
       "      <td>32.953457</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1975-03-31</th>\n",
       "      <td>33.533513</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1975-04-30</th>\n",
       "      <td>34.368221</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1975-05-31</th>\n",
       "      <td>34.620177</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Value\n",
       "Date                 \n",
       "1975-01-31  32.618858\n",
       "1975-02-28  32.953457\n",
       "1975-03-31  33.533513\n",
       "1975-04-30  34.368221\n",
       "1975-05-31  34.620177"
      ]
     },
     "execution_count": 178,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tx.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 179,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Now this is how the data for the state of Texas looks like. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### We're going to pull housing data for the 50 states first, but then we stand to try to gather other data as well. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## As you carry on with your data science career, you will learn various constants that just happen to be because people are logical and reasonable."
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "### In our case, we need to grab the data for all of the states. How might we do this? We need the ticker information i.e. \"FMAC/HPI_STATE_NAME\" so that we can pull the data using these tickers individually.\n",
    "\n",
    "Do we need to grab every ticker manually still? \n",
    "\n",
    "No. Looking at this ticker, we see FMAC/HPI_TX. We can easily decypher this to FMAC = Freddie Mac. HPI = House Price Index. \n",
    "\n",
    "TX means Texas, the common 2-letter abbreviation for the state. \n",
    "\n",
    "From here, we can safely assume all of the tickers are built this way, so now we just need a list of the state abbreviations. We Google it, make a choice like This list of the 50 states. Now what?"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "###  Let's check out the read_html from Pandas. It's not being called \"experimental\" anymore, but I would still label this as expirimental. \n",
    "\n",
    "The standard and quality of the other IO modules is very high and dependable. This read_html is not quite up to par, but I still say it's very impressive and useful code, and just plain cool. \n",
    "\n",
    "The way it works is you just simply feed in a URL, and Pandas will extract the dataframe worthy date from tables into a dataframe. \n",
    "This means, unlike the other typical methods you will usually use, read_html ends up reading into a list of dataframes. This isn't the only one that is different, but it is different."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#First, in order to use read_html, we need html5lib. Open up cmd.exe or your terminal and do: pip install html5lib."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[               0               1               2                  3\n",
      "0   Abbreviation      State Name         Capital     Became a State\n",
      "1             AL         Alabama      Montgomery  December 14, 1819\n",
      "2             AK          Alaska          Juneau    January 3, 1959\n",
      "3             AZ         Arizona         Phoenix  February 14, 1912\n",
      "4             AR        Arkansas     Little Rock      June 15, 1836\n",
      "5             CA      California      Sacramento  September 9, 1850\n",
      "6             CO        Colorado          Denver     August 1, 1876\n",
      "7             CT     Connecticut        Hartford    January 9, 1788\n",
      "8             DE        Delaware           Dover   December 7, 1787\n",
      "9             FL         Florida     Tallahassee      March 3, 1845\n",
      "10            GA         Georgia         Atlanta    January 2, 1788\n",
      "11            HI          Hawaii        Honolulu    August 21, 1959\n",
      "12            ID           Idaho           Boise       July 3, 1890\n",
      "13            IL        Illinois     Springfield   December 3, 1818\n",
      "14            IN         Indiana    Indianapolis  December 11, 1816\n",
      "15            IA            Iowa      Des Moines  December 28, 1846\n",
      "16            KS          Kansas          Topeka   January 29, 1861\n",
      "17            KY        Kentucky       Frankfort       June 1, 1792\n",
      "18            LA       Louisiana     Baton Rouge     April 30, 1812\n",
      "19            ME           Maine         Augusta     March 15, 1820\n",
      "20            MD        Maryland       Annapolis     April 28, 1788\n",
      "21            MA   Massachusetts          Boston   February 6, 1788\n",
      "22            MI        Michigan         Lansing   January 26, 1837\n",
      "23            MN       Minnesota      Saint Paul       May 11, 1858\n",
      "24            MS     Mississippi         Jackson  December 10, 1817\n",
      "25            MO        Missouri  Jefferson City    August 10, 1821\n",
      "26            MT         Montana          Helena   November 8, 1889\n",
      "27            NE        Nebraska         Lincoln      March 1, 1867\n",
      "28            NV          Nevada     Carson City   October 31, 1864\n",
      "29            NH   New Hampshire         Concord      June 21, 1788\n",
      "30            NJ      New Jersey         Trenton  December 18, 1787\n",
      "31            NM      New Mexico        Santa Fe    January 6, 1912\n",
      "32            NY        New York          Albany      July 26, 1788\n",
      "33            NC  North Carolina         Raleigh  November 21, 1789\n",
      "34            ND    North Dakota        Bismarck   November 2, 1889\n",
      "35            OH            Ohio        Columbus      March 1, 1803\n",
      "36            OK        Oklahoma   Oklahoma City  November 16, 1907\n",
      "37            OR          Oregon           Salem  February 14, 1859\n",
      "38            PA    Pennsylvania      Harrisburg  December 12, 1787\n",
      "39            RI    Rhode Island      Providence       May 19, 1790\n",
      "40            SC  South Carolina        Columbia       May 23, 1788\n",
      "41            SD    South Dakota          Pierre   November 2, 1889\n",
      "42            TN       Tennessee       Nashville       June 1, 1796\n",
      "43            TX           Texas          Austin  December 29, 1845\n",
      "44            UT            Utah  Salt Lake City    January 4, 1896\n",
      "45            VT         Vermont      Montpelier      March 4, 1791\n",
      "46            VA        Virginia        Richmond      June 25, 1788\n",
      "47            WA      Washington         Olympia  November 11, 1889\n",
      "48            WV   West Virginia      Charleston      June 20, 1863\n",
      "49            WI       Wisconsin         Madison       May 29, 1848\n",
      "50            WY         Wyoming        Cheyenne      July 10, 1890,                                                   0   \\\n",
      "0  v t e Political divisions of the United States...   \n",
      "1     v t e Political divisions of the United States   \n",
      "2                                                NaN   \n",
      "3                                             States   \n",
      "4                                                NaN   \n",
      "5                                   National capital   \n",
      "6                                                NaN   \n",
      "7                                      Large islands   \n",
      "8                                                NaN   \n",
      "9                                      Small islands   \n",
      "\n",
      "                                                  1   2       3   \\\n",
      "0     v t e Political divisions of the United States NaN  States   \n",
      "1                                                NaN NaN     NaN   \n",
      "2                                                NaN NaN     NaN   \n",
      "3  Alabama · Alaska · Arizona · Arkansas · Califo... NaN     NaN   \n",
      "4                                                NaN NaN     NaN   \n",
      "5                               District of Columbia NaN     NaN   \n",
      "6                                                NaN NaN     NaN   \n",
      "7  American Samoa · Guam · Northern Mariana Islan... NaN     NaN   \n",
      "8                                                NaN NaN     NaN   \n",
      "9  Baker Island · Howland Island · Jarvis Island ... NaN     NaN   \n",
      "\n",
      "                                                  4   5                 6   \\\n",
      "0  Alabama · Alaska · Arizona · Arkansas · Califo... NaN  National capital   \n",
      "1                                                NaN NaN               NaN   \n",
      "2                                                NaN NaN               NaN   \n",
      "3                                                NaN NaN               NaN   \n",
      "4                                                NaN NaN               NaN   \n",
      "5                                                NaN NaN               NaN   \n",
      "6                                                NaN NaN               NaN   \n",
      "7                                                NaN NaN               NaN   \n",
      "8                                                NaN NaN               NaN   \n",
      "9                                                NaN NaN               NaN   \n",
      "\n",
      "                     7   8              9   \\\n",
      "0  District of Columbia NaN  Large islands   \n",
      "1                   NaN NaN            NaN   \n",
      "2                   NaN NaN            NaN   \n",
      "3                   NaN NaN            NaN   \n",
      "4                   NaN NaN            NaN   \n",
      "5                   NaN NaN            NaN   \n",
      "6                   NaN NaN            NaN   \n",
      "7                   NaN NaN            NaN   \n",
      "8                   NaN NaN            NaN   \n",
      "9                   NaN NaN            NaN   \n",
      "\n",
      "                                                  10  11             12  \\\n",
      "0  American Samoa · Guam · Northern Mariana Islan... NaN  Small islands   \n",
      "1                                                NaN NaN            NaN   \n",
      "2                                                NaN NaN            NaN   \n",
      "3                                                NaN NaN            NaN   \n",
      "4                                                NaN NaN            NaN   \n",
      "5                                                NaN NaN            NaN   \n",
      "6                                                NaN NaN            NaN   \n",
      "7                                                NaN NaN            NaN   \n",
      "8                                                NaN NaN            NaN   \n",
      "9                                                NaN NaN            NaN   \n",
      "\n",
      "                                                  13  \n",
      "0  Baker Island · Howland Island · Jarvis Island ...  \n",
      "1                                                NaN  \n",
      "2                                                NaN  \n",
      "3                                                NaN  \n",
      "4                                                NaN  \n",
      "5                                                NaN  \n",
      "6                                                NaN  \n",
      "7                                                NaN  \n",
      "8                                                NaN  \n",
      "9                                                NaN  ,                                                 0  \\\n",
      "0  v t e Political divisions of the United States   \n",
      "1                                             NaN   \n",
      "2                                          States   \n",
      "3                                             NaN   \n",
      "4                                National capital   \n",
      "5                                             NaN   \n",
      "6                                   Large islands   \n",
      "7                                             NaN   \n",
      "8                                   Small islands   \n",
      "\n",
      "                                                   1  \n",
      "0                                                NaN  \n",
      "1                                                NaN  \n",
      "2  Alabama · Alaska · Arizona · Arkansas · Califo...  \n",
      "3                                                NaN  \n",
      "4                               District of Columbia  \n",
      "5                                                NaN  \n",
      "6  American Samoa · Guam · Northern Mariana Islan...  \n",
      "7                                                NaN  \n",
      "8  Baker Island · Howland Island · Jarvis Island ...  ]\n"
     ]
    }
   ],
   "source": [
    "us_states = pd.read_html('https://simple.wikipedia.org/wiki/List_of_U.S._states')\n",
    "print(us_states)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Abbreviation</td>\n",
       "      <td>State Name</td>\n",
       "      <td>Capital</td>\n",
       "      <td>Became a State</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AL</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Montgomery</td>\n",
       "      <td>December 14, 1819</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>Juneau</td>\n",
       "      <td>January 3, 1959</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AZ</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>Phoenix</td>\n",
       "      <td>February 14, 1912</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AR</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>Little Rock</td>\n",
       "      <td>June 15, 1836</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              0           1            2                  3\n",
       "0  Abbreviation  State Name      Capital     Became a State\n",
       "1            AL     Alabama   Montgomery  December 14, 1819\n",
       "2            AK      Alaska       Juneau    January 3, 1959\n",
       "3            AZ     Arizona      Phoenix  February 14, 1912\n",
       "4            AR    Arkansas  Little Rock      June 15, 1836"
      ]
     },
     "execution_count": 182,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# We don't need most of this data. Some of it is good.\n",
    "us_states[0].head()"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Remember, right now, us_states is a list of dataframes, and us_states[0] is the first dataframe. \n",
    "\n",
    "To reference column 0 then, we do fiddy_states[0][0]. One is a list index, which returns a dataframe. The other is a column within the dataframe. \n",
    "\n",
    "Let's try that. Shall we?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "list"
      ]
     },
     "execution_count": 183,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(us_states)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 184,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    Abbreviation\n",
       "1              AL\n",
       "2              AK\n",
       "3              AZ\n",
       "4              AR\n",
       "Name: 0, dtype: object"
      ]
     },
     "execution_count": 184,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "us_states[0][0].head()"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Next, we notice the first item in column 0 is the word \"abbreviation,\" which we don't want. We can single that out when we iterate through all of the items in column 0 by doing column 0 [1:]. \n",
    "\n",
    "As such, our list of abbreviations is us_states[0][0][1:], and we can iterate like so:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 185,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1    AL\n",
       "2    AK\n",
       "3    AZ\n",
       "4    AR\n",
       "5    CA\n",
       "Name: 0, dtype: object"
      ]
     },
     "execution_count": 185,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "us_states[0][0][1:].head()"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Perfect! Now, we remember why we're doing this at all in the first place: We are trying to build the \"tickers\" with the state abbreviations to acquire the housing price index for each state. Okay, we can build the ticker:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 186,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "state_tickers = []\n",
    "for state in us_states[0][0][1:]:\n",
    "    state_tickers.append(' '.join(['FMAC/HPI_', state]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 187,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[u'FMAC/HPI_ AL',\n",
       " u'FMAC/HPI_ AK',\n",
       " u'FMAC/HPI_ AZ',\n",
       " u'FMAC/HPI_ AR',\n",
       " u'FMAC/HPI_ CA',\n",
       " u'FMAC/HPI_ CO',\n",
       " u'FMAC/HPI_ CT',\n",
       " u'FMAC/HPI_ DE',\n",
       " u'FMAC/HPI_ FL',\n",
       " u'FMAC/HPI_ GA',\n",
       " u'FMAC/HPI_ HI',\n",
       " u'FMAC/HPI_ ID',\n",
       " u'FMAC/HPI_ IL',\n",
       " u'FMAC/HPI_ IN',\n",
       " u'FMAC/HPI_ IA',\n",
       " u'FMAC/HPI_ KS',\n",
       " u'FMAC/HPI_ KY',\n",
       " u'FMAC/HPI_ LA',\n",
       " u'FMAC/HPI_ ME',\n",
       " u'FMAC/HPI_ MD',\n",
       " u'FMAC/HPI_ MA',\n",
       " u'FMAC/HPI_ MI',\n",
       " u'FMAC/HPI_ MN',\n",
       " u'FMAC/HPI_ MS',\n",
       " u'FMAC/HPI_ MO',\n",
       " u'FMAC/HPI_ MT',\n",
       " u'FMAC/HPI_ NE',\n",
       " u'FMAC/HPI_ NV',\n",
       " u'FMAC/HPI_ NH',\n",
       " u'FMAC/HPI_ NJ',\n",
       " u'FMAC/HPI_ NM',\n",
       " u'FMAC/HPI_ NY',\n",
       " u'FMAC/HPI_ NC',\n",
       " u'FMAC/HPI_ ND',\n",
       " u'FMAC/HPI_ OH',\n",
       " u'FMAC/HPI_ OK',\n",
       " u'FMAC/HPI_ OR',\n",
       " u'FMAC/HPI_ PA',\n",
       " u'FMAC/HPI_ RI',\n",
       " u'FMAC/HPI_ SC',\n",
       " u'FMAC/HPI_ SD',\n",
       " u'FMAC/HPI_ TN',\n",
       " u'FMAC/HPI_ TX',\n",
       " u'FMAC/HPI_ UT',\n",
       " u'FMAC/HPI_ VT',\n",
       " u'FMAC/HPI_ VA',\n",
       " u'FMAC/HPI_ WA',\n",
       " u'FMAC/HPI_ WV',\n",
       " u'FMAC/HPI_ WI',\n",
       " u'FMAC/HPI_ WY']"
      ]
     },
     "execution_count": 187,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "state_tickers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Now that we have created a list of tickers, lets see what the list contains\n",
    "state_tickers[1]"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "In our case with real estate investing, we're hoping to take the 50 dataframes with housing data and then just combine them all into one dataframe. \n",
    "\n",
    "We do this for multiple reasons. First, it is easier and just makes sense to combine these, but also it will result in less memory being used. Every dataframe has a date and value column. This date column is repeated across all the dataframes, but really they should all just share the one, effectively nearly halving our total column count."
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "There are four major ways of combining dataframes, which we'll begin covering now. The four major ways are: Concatenation, joining, merging, and appending. We'll begin with Concatenation. Here are some starting dataframes:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df1 = pd.DataFrame({'HPI':[80,85,88,89],\n",
    "                    'Int_rate':[2, 3, 2, 4],\n",
    "                    'US_GDP_Thousands':[50, 55, 65, 55]},\n",
    "                   index = [2001, 2002, 2003, 2004])\n",
    "\n",
    "df2 = pd.DataFrame({'HPI':[80,89,88,85],\n",
    "                    'Int_rate':[1, 3, 5, 7],\n",
    "                    'US_GDP_Thousands':[50, 56, 60, 71]},\n",
    "                   index = [2005, 2006, 2007, 2008])\n",
    "\n",
    "df3 = pd.DataFrame({'HPI':[80,85,88,85],\n",
    "                    'Int_rate':[2, 3, 2, 2],\n",
    "                    'Low_tier_HPI':[50, 52, 50, 53]},\n",
    "                   index = [2001, 2002, 2003, 2004])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>HPI</th>\n",
       "      <th>Int_rate</th>\n",
       "      <th>US_GDP_Thousands</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2001</th>\n",
       "      <td>80</td>\n",
       "      <td>2</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2002</th>\n",
       "      <td>85</td>\n",
       "      <td>3</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2003</th>\n",
       "      <td>88</td>\n",
       "      <td>2</td>\n",
       "      <td>65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2004</th>\n",
       "      <td>89</td>\n",
       "      <td>4</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      HPI  Int_rate  US_GDP_Thousands\n",
       "2001   80         2                50\n",
       "2002   85         3                55\n",
       "2003   88         2                65\n",
       "2004   89         4                55"
      ]
     },
     "execution_count": 133,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>HPI</th>\n",
       "      <th>Int_rate</th>\n",
       "      <th>US_GDP_Thousands</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2005</th>\n",
       "      <td>80</td>\n",
       "      <td>1</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2006</th>\n",
       "      <td>89</td>\n",
       "      <td>3</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2007</th>\n",
       "      <td>88</td>\n",
       "      <td>5</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2008</th>\n",
       "      <td>85</td>\n",
       "      <td>7</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      HPI  Int_rate  US_GDP_Thousands\n",
       "2005   80         1                50\n",
       "2006   89         3                56\n",
       "2007   88         5                60\n",
       "2008   85         7                71"
      ]
     },
     "execution_count": 134,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>HPI</th>\n",
       "      <th>Int_rate</th>\n",
       "      <th>Low_tier_HPI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2001</th>\n",
       "      <td>80</td>\n",
       "      <td>2</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2002</th>\n",
       "      <td>85</td>\n",
       "      <td>3</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2003</th>\n",
       "      <td>88</td>\n",
       "      <td>2</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2004</th>\n",
       "      <td>85</td>\n",
       "      <td>2</td>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      HPI  Int_rate  Low_tier_HPI\n",
       "2001   80         2            50\n",
       "2002   85         3            52\n",
       "2003   88         2            50\n",
       "2004   85         2            53"
      ]
     },
     "execution_count": 135,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Notice there are two major changes between these. df1 and df3 have the same index, but they have some different columns. df2 and df3 have different indexes and some differing columns. With concatenation, we can talk about various methods of bringing these together. Let's try a simple concatenation:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      HPI  Int_rate  US_GDP_Thousands\n",
      "2001   80         2                50\n",
      "2002   85         3                55\n",
      "2003   88         2                65\n",
      "2004   89         4                55\n",
      "2005   80         1                50\n",
      "2006   89         3                56\n",
      "2007   88         5                60\n",
      "2008   85         7                71\n"
     ]
    }
   ],
   "source": [
    "df4 = pd.concat([df1,df2])\n",
    "print(df4)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Easy enough. The major difference between these was merely a continuation of the index, but they shared the same columns. Now they have become a single dataframe. In our case, however, we're curious about adding columns, not rows. What happens when we combine some shared and some new:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      HPI  Int_rate  Low_tier_HPI  US_GDP_Thousands\n",
      "2001   80         2           NaN              50.0\n",
      "2002   85         3           NaN              55.0\n",
      "2003   88         2           NaN              65.0\n",
      "2004   89         4           NaN              55.0\n",
      "2005   80         1           NaN              50.0\n",
      "2006   89         3           NaN              56.0\n",
      "2007   88         5           NaN              60.0\n",
      "2008   85         7           NaN              71.0\n",
      "2001   80         2          50.0               NaN\n",
      "2002   85         3          52.0               NaN\n",
      "2003   88         2          50.0               NaN\n",
      "2004   85         2          53.0               NaN\n"
     ]
    }
   ],
   "source": [
    "df5 = pd.concat([df1,df2,df3])\n",
    "print(df5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###### Not bad, we have some NaN (not a number), because this data didn't exist for that index, but all of our data is indeed here.\n",
    "\n",
    "Those are the basics of concatenation, next up, let's cover appending. Appending is like the first example of concatenation, only a bit more forceful in that the dataframe will simply be appended to, adding to rows. Let's show an example of how it usually works, but also show where it could possibly go wrong:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      HPI  Int_rate  US_GDP_Thousands\n",
      "2001   80         2                50\n",
      "2002   85         3                55\n",
      "2003   88         2                65\n",
      "2004   89         4                55\n",
      "2005   80         1                50\n",
      "2006   89         3                56\n",
      "2007   88         5                60\n",
      "2008   85         7                71\n"
     ]
    }
   ],
   "source": [
    "df6 = df1.append(df2)\n",
    "print(df6)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "That's what we expect with an append. In most cases, you are going to do something like this, as if you're inserting a new row in a database. \n",
    "\n",
    "Dataframes were not really made to be appended efficiently, they are meant more so to be manipulated based on their starting data, but you can append if you need to. What happens when we append data with the same index?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      HPI  Int_rate  Low_tier_HPI  US_GDP_Thousands\n",
      "2001   80         2           NaN              50.0\n",
      "2002   85         3           NaN              55.0\n",
      "2003   88         2           NaN              65.0\n",
      "2004   89         4           NaN              55.0\n",
      "2001   80         2          50.0               NaN\n",
      "2002   85         3          52.0               NaN\n",
      "2003   88         2          50.0               NaN\n",
      "2004   85         2          53.0               NaN\n"
     ]
    }
   ],
   "source": [
    "df7 = df1.append(df3)\n",
    "print(df7)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Well, that's unfortunate. Some people ask why both concatenation and append exit. That's why. It's far more efficient here to combine these dataframes since the columns shared contain the same data and same index. One more example is to append possibly a series. You're more likely to be appending a series than whole dataframes given the nature of append. We have not spoken about series to this point. A series is basically a single-columned dataframe. A series does have an index, but, if you convert it to a list, it will be just those values. Whenever we say something like df['column'], the return is a series."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   HPI  Int_rate  US_GDP_Thousands\n",
      "0   80         2                50\n",
      "1   85         3                55\n",
      "2   88         2                65\n",
      "3   89         4                55\n",
      "4   80         2                50\n"
     ]
    }
   ],
   "source": [
    "s = pd.Series([80,2,50], index=['HPI','Int_rate','US_GDP_Thousands'])\n",
    "df8 = df1.append(s, ignore_index=True)\n",
    "print(df8)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "We have to ignore the index when appending a series, because that is the law, unless the series has a name.\n",
    "\n",
    "Here, we have covered Concatenating and Appending dataframes in Pandas. Next, we're going to talk about joining and merging dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Next up, merging.\n",
    "# Before that, let us rename the year column (which doesn't have the name already)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>HPI</th>\n",
       "      <th>Int_rate</th>\n",
       "      <th>US_GDP_Thousands</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2001</th>\n",
       "      <td>80</td>\n",
       "      <td>2</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2002</th>\n",
       "      <td>85</td>\n",
       "      <td>3</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2003</th>\n",
       "      <td>88</td>\n",
       "      <td>2</td>\n",
       "      <td>65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2004</th>\n",
       "      <td>89</td>\n",
       "      <td>4</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      HPI  Int_rate  US_GDP_Thousands\n",
       "2001   80         2                50\n",
       "2002   85         3                55\n",
       "2003   88         2                65\n",
       "2004   89         4                55"
      ]
     },
     "execution_count": 142,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df1.rename(columns={' ': 'Year'}, inplace=True) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>HPI</th>\n",
       "      <th>Int_rate</th>\n",
       "      <th>US_GDP_Thousands</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2001</th>\n",
       "      <td>80</td>\n",
       "      <td>2</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2002</th>\n",
       "      <td>85</td>\n",
       "      <td>3</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2003</th>\n",
       "      <td>88</td>\n",
       "      <td>2</td>\n",
       "      <td>65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2004</th>\n",
       "      <td>89</td>\n",
       "      <td>4</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      HPI  Int_rate  US_GDP_Thousands\n",
       "2001   80         2                50\n",
       "2002   85         3                55\n",
       "2003   88         2                65\n",
       "2004   89         4                55"
      ]
     },
     "execution_count": 144,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Before we go ahead and merge these dataframes, let's rename the index column\n",
    "df1.index.name = 'Year'\n",
    "df2.index.name = 'Year'\n",
    "df3.index.name = 'Year'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# That didn't work as intended. So, let's assign the index column to a newly created column called 'Year' in each dataframe\n",
    "# and then, make this new colum the index\n",
    "df1['Year'] = df1.index\n",
    "df2['Year'] = df2.index\n",
    "df3['Year'] = df3.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>HPI</th>\n",
       "      <th>Int_rate</th>\n",
       "      <th>US_GDP_Thousands</th>\n",
       "      <th>Year</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Year</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2001</th>\n",
       "      <td>80</td>\n",
       "      <td>2</td>\n",
       "      <td>50</td>\n",
       "      <td>2001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2002</th>\n",
       "      <td>85</td>\n",
       "      <td>3</td>\n",
       "      <td>55</td>\n",
       "      <td>2002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2003</th>\n",
       "      <td>88</td>\n",
       "      <td>2</td>\n",
       "      <td>65</td>\n",
       "      <td>2003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2004</th>\n",
       "      <td>89</td>\n",
       "      <td>4</td>\n",
       "      <td>55</td>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      HPI  Int_rate  US_GDP_Thousands  Year\n",
       "Year                                       \n",
       "2001   80         2                50  2001\n",
       "2002   85         3                55  2002\n",
       "2003   88         2                65  2003\n",
       "2004   89         4                55  2004"
      ]
     },
     "execution_count": 147,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Lets see if that worked\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Ok, now let's set Year as the index\n",
    "df1.set_index('Year', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>HPI</th>\n",
       "      <th>Int_rate</th>\n",
       "      <th>US_GDP_Thousands</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Year</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2001</th>\n",
       "      <td>80</td>\n",
       "      <td>2</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2002</th>\n",
       "      <td>85</td>\n",
       "      <td>3</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2003</th>\n",
       "      <td>88</td>\n",
       "      <td>2</td>\n",
       "      <td>65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2004</th>\n",
       "      <td>89</td>\n",
       "      <td>4</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      HPI  Int_rate  US_GDP_Thousands\n",
       "Year                                 \n",
       "2001   80         2                50\n",
       "2002   85         3                55\n",
       "2003   88         2                65\n",
       "2004   89         4                55"
      ]
     },
     "execution_count": 149,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# That worked a charm. Now let's do that for the other two datframes as well.\n",
    "df2.set_index('Year', inplace=True)\n",
    "df3.set_index('Year', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Now, we'll use a little trick to make the index column look and work as a normal column\n",
    "df1.reset_index(level=0, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>HPI</th>\n",
       "      <th>Int_rate</th>\n",
       "      <th>US_GDP_Thousands</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2001</td>\n",
       "      <td>80</td>\n",
       "      <td>2</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2002</td>\n",
       "      <td>85</td>\n",
       "      <td>3</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2003</td>\n",
       "      <td>88</td>\n",
       "      <td>2</td>\n",
       "      <td>65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2004</td>\n",
       "      <td>89</td>\n",
       "      <td>4</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year  HPI  Int_rate  US_GDP_Thousands\n",
       "0  2001   80         2                50\n",
       "1  2002   85         3                55\n",
       "2  2003   88         2                65\n",
       "3  2004   89         4                55"
      ]
     },
     "execution_count": 152,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# That worked again. Let's do it for df2 and df3\n",
    "df2.reset_index(level=0, inplace=True)\n",
    "df3.reset_index(level=0, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>HPI_x</th>\n",
       "      <th>Int_rate_x</th>\n",
       "      <th>US_GDP_Thousands_x</th>\n",
       "      <th>HPI_y</th>\n",
       "      <th>Int_rate_y</th>\n",
       "      <th>US_GDP_Thousands_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [Year, HPI_x, Int_rate_x, US_GDP_Thousands_x, HPI_y, Int_rate_y, US_GDP_Thousands_y]\n",
       "Index: []"
      ]
     },
     "execution_count": 154,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Now, let's merge on Year\n",
    "pd.merge(df1, df2, on='Year')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>HPI_x</th>\n",
       "      <th>Int_rate_x</th>\n",
       "      <th>US_GDP_Thousands</th>\n",
       "      <th>HPI_y</th>\n",
       "      <th>Int_rate_y</th>\n",
       "      <th>Low_tier_HPI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [Year, HPI_x, Int_rate_x, US_GDP_Thousands, HPI_y, Int_rate_y, Low_tier_HPI]\n",
       "Index: []"
      ]
     },
     "execution_count": 155,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Looks like df1 and df2 have no year data in common.\n",
    "# Lets try df2 and df3\n",
    "\n",
    "pd.merge(df2, df3, on='Year')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Nope, that didn't work either. Let's look at our dataframes before we merge them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>HPI</th>\n",
       "      <th>Int_rate</th>\n",
       "      <th>US_GDP_Thousands</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2001</td>\n",
       "      <td>80</td>\n",
       "      <td>2</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2002</td>\n",
       "      <td>85</td>\n",
       "      <td>3</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2003</td>\n",
       "      <td>88</td>\n",
       "      <td>2</td>\n",
       "      <td>65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2004</td>\n",
       "      <td>89</td>\n",
       "      <td>4</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year  HPI  Int_rate  US_GDP_Thousands\n",
       "0  2001   80         2                50\n",
       "1  2002   85         3                55\n",
       "2  2003   88         2                65\n",
       "3  2004   89         4                55"
      ]
     },
     "execution_count": 157,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>HPI</th>\n",
       "      <th>Int_rate</th>\n",
       "      <th>US_GDP_Thousands</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2005</td>\n",
       "      <td>80</td>\n",
       "      <td>1</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2006</td>\n",
       "      <td>89</td>\n",
       "      <td>3</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2007</td>\n",
       "      <td>88</td>\n",
       "      <td>5</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2008</td>\n",
       "      <td>85</td>\n",
       "      <td>7</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year  HPI  Int_rate  US_GDP_Thousands\n",
       "0  2005   80         1                50\n",
       "1  2006   89         3                56\n",
       "2  2007   88         5                60\n",
       "3  2008   85         7                71"
      ]
     },
     "execution_count": 158,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>HPI</th>\n",
       "      <th>Int_rate</th>\n",
       "      <th>Low_tier_HPI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2001</td>\n",
       "      <td>80</td>\n",
       "      <td>2</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2002</td>\n",
       "      <td>85</td>\n",
       "      <td>3</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2003</td>\n",
       "      <td>88</td>\n",
       "      <td>2</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2004</td>\n",
       "      <td>85</td>\n",
       "      <td>2</td>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year  HPI  Int_rate  Low_tier_HPI\n",
       "0  2001   80         2            50\n",
       "1  2002   85         3            52\n",
       "2  2003   88         2            50\n",
       "3  2004   85         2            53"
      ]
     },
     "execution_count": 159,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>HPI_x</th>\n",
       "      <th>Int_rate_x</th>\n",
       "      <th>US_GDP_Thousands</th>\n",
       "      <th>HPI_y</th>\n",
       "      <th>Int_rate_y</th>\n",
       "      <th>Low_tier_HPI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2001</td>\n",
       "      <td>80</td>\n",
       "      <td>2</td>\n",
       "      <td>50</td>\n",
       "      <td>80</td>\n",
       "      <td>2</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2002</td>\n",
       "      <td>85</td>\n",
       "      <td>3</td>\n",
       "      <td>55</td>\n",
       "      <td>85</td>\n",
       "      <td>3</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2003</td>\n",
       "      <td>88</td>\n",
       "      <td>2</td>\n",
       "      <td>65</td>\n",
       "      <td>88</td>\n",
       "      <td>2</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2004</td>\n",
       "      <td>89</td>\n",
       "      <td>4</td>\n",
       "      <td>55</td>\n",
       "      <td>85</td>\n",
       "      <td>2</td>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year  HPI_x  Int_rate_x  US_GDP_Thousands  HPI_y  Int_rate_y  Low_tier_HPI\n",
       "0  2001     80           2                50     80           2            50\n",
       "1  2002     85           3                55     85           3            52\n",
       "2  2003     88           2                65     88           2            50\n",
       "3  2004     89           4                55     85           2            53"
      ]
     },
     "execution_count": 160,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# df1 and df3 have common year data. Hence:\n",
    "\n",
    "pd.merge(df1, df3, on='Year')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year_x</th>\n",
       "      <th>Int_rate_x</th>\n",
       "      <th>US_GDP_Thousands_x</th>\n",
       "      <th>Year_y</th>\n",
       "      <th>Int_rate_y</th>\n",
       "      <th>US_GDP_Thousands_y</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HPI</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>2001</td>\n",
       "      <td>2</td>\n",
       "      <td>50</td>\n",
       "      <td>2005</td>\n",
       "      <td>1</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>2002</td>\n",
       "      <td>3</td>\n",
       "      <td>55</td>\n",
       "      <td>2008</td>\n",
       "      <td>7</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>2003</td>\n",
       "      <td>2</td>\n",
       "      <td>65</td>\n",
       "      <td>2007</td>\n",
       "      <td>5</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>2004</td>\n",
       "      <td>4</td>\n",
       "      <td>55</td>\n",
       "      <td>2006</td>\n",
       "      <td>3</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Year_x  Int_rate_x  US_GDP_Thousands_x  Year_y  Int_rate_y  \\\n",
       "HPI                                                               \n",
       "80     2001           2                  50    2005           1   \n",
       "85     2002           3                  55    2008           7   \n",
       "88     2003           2                  65    2007           5   \n",
       "89     2004           4                  55    2006           3   \n",
       "\n",
       "     US_GDP_Thousands_y  \n",
       "HPI                      \n",
       "80                   50  \n",
       "85                   71  \n",
       "88                   60  \n",
       "89                   56  "
      ]
     },
     "execution_count": 161,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Wohooo!!!! So we have learnt merging as well. Now, let's try the hows of merging.\n",
    "# They are fairly similar to sql commands and intuitive.\n",
    "left_merge = pd.merge(df1, df2, on='HPI', how='left')\n",
    "left_merge.set_index('HPI', inplace=True)\n",
    "left_merge"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "The resultant dataframe has the two merged dataframes side by side, with df1 on the left and df2 on the right.\n",
    "Specifying the how as 'left' makes the index of the combined dataframe similar \n",
    "to the dataframe on the left in the command i.e df1. Like you can see, the rows of df2 in the merged dataframe\n",
    "have realigned to match with the resulting new index."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>HPI</th>\n",
       "      <th>Int_rate</th>\n",
       "      <th>US_GDP_Thousands</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2001</td>\n",
       "      <td>80</td>\n",
       "      <td>2</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2002</td>\n",
       "      <td>85</td>\n",
       "      <td>3</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2003</td>\n",
       "      <td>88</td>\n",
       "      <td>2</td>\n",
       "      <td>65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2004</td>\n",
       "      <td>89</td>\n",
       "      <td>4</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year  HPI  Int_rate  US_GDP_Thousands\n",
       "0  2001   80         2                50\n",
       "1  2002   85         3                55\n",
       "2  2003   88         2                65\n",
       "3  2004   89         4                55"
      ]
     },
     "execution_count": 162,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>HPI</th>\n",
       "      <th>Int_rate</th>\n",
       "      <th>US_GDP_Thousands</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2005</td>\n",
       "      <td>80</td>\n",
       "      <td>1</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2006</td>\n",
       "      <td>89</td>\n",
       "      <td>3</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2007</td>\n",
       "      <td>88</td>\n",
       "      <td>5</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2008</td>\n",
       "      <td>85</td>\n",
       "      <td>7</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year  HPI  Int_rate  US_GDP_Thousands\n",
       "0  2005   80         1                50\n",
       "1  2006   89         3                56\n",
       "2  2007   88         5                60\n",
       "3  2008   85         7                71"
      ]
     },
     "execution_count": 163,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year_x</th>\n",
       "      <th>Int_rate_x</th>\n",
       "      <th>US_GDP_Thousands_x</th>\n",
       "      <th>Year_y</th>\n",
       "      <th>Int_rate_y</th>\n",
       "      <th>US_GDP_Thousands_y</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HPI</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>2001</td>\n",
       "      <td>2</td>\n",
       "      <td>50</td>\n",
       "      <td>2005</td>\n",
       "      <td>1</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>2002</td>\n",
       "      <td>3</td>\n",
       "      <td>55</td>\n",
       "      <td>2008</td>\n",
       "      <td>7</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>2003</td>\n",
       "      <td>2</td>\n",
       "      <td>65</td>\n",
       "      <td>2007</td>\n",
       "      <td>5</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>2004</td>\n",
       "      <td>4</td>\n",
       "      <td>55</td>\n",
       "      <td>2006</td>\n",
       "      <td>3</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Year_x  Int_rate_x  US_GDP_Thousands_x  Year_y  Int_rate_y  \\\n",
       "HPI                                                               \n",
       "80     2001           2                  50    2005           1   \n",
       "85     2002           3                  55    2008           7   \n",
       "88     2003           2                  65    2007           5   \n",
       "89     2004           4                  55    2006           3   \n",
       "\n",
       "     US_GDP_Thousands_y  \n",
       "HPI                      \n",
       "80                   50  \n",
       "85                   71  \n",
       "88                   60  \n",
       "89                   56  "
      ]
     },
     "execution_count": 164,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "outer_merge = pd.merge(df1, df2, on='HPI', how='outer')\n",
    "outer_merge.set_index('HPI', inplace=True)\n",
    "outer_merge"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "As you can see, the outer join creaates a full join, with union of keys (index)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>HPI</th>\n",
       "      <th>Int_rate</th>\n",
       "      <th>US_GDP_Thousands</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2001</td>\n",
       "      <td>80</td>\n",
       "      <td>2</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2002</td>\n",
       "      <td>85</td>\n",
       "      <td>3</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2003</td>\n",
       "      <td>88</td>\n",
       "      <td>2</td>\n",
       "      <td>65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2004</td>\n",
       "      <td>89</td>\n",
       "      <td>4</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year  HPI  Int_rate  US_GDP_Thousands\n",
       "0  2001   80         2                50\n",
       "1  2002   85         3                55\n",
       "2  2003   88         2                65\n",
       "3  2004   89         4                55"
      ]
     },
     "execution_count": 165,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Let's try right merge now. Before that, lets print the two dataframes again.\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>HPI</th>\n",
       "      <th>Int_rate</th>\n",
       "      <th>US_GDP_Thousands</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2005</td>\n",
       "      <td>80</td>\n",
       "      <td>1</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2006</td>\n",
       "      <td>89</td>\n",
       "      <td>3</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2007</td>\n",
       "      <td>88</td>\n",
       "      <td>5</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2008</td>\n",
       "      <td>85</td>\n",
       "      <td>7</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year  HPI  Int_rate  US_GDP_Thousands\n",
       "0  2005   80         1                50\n",
       "1  2006   89         3                56\n",
       "2  2007   88         5                60\n",
       "3  2008   85         7                71"
      ]
     },
     "execution_count": 166,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year_x</th>\n",
       "      <th>Int_rate_x</th>\n",
       "      <th>US_GDP_Thousands_x</th>\n",
       "      <th>Year_y</th>\n",
       "      <th>Int_rate_y</th>\n",
       "      <th>US_GDP_Thousands_y</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HPI</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>2001</td>\n",
       "      <td>2</td>\n",
       "      <td>50</td>\n",
       "      <td>2005</td>\n",
       "      <td>1</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>2002</td>\n",
       "      <td>3</td>\n",
       "      <td>55</td>\n",
       "      <td>2008</td>\n",
       "      <td>7</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>2003</td>\n",
       "      <td>2</td>\n",
       "      <td>65</td>\n",
       "      <td>2007</td>\n",
       "      <td>5</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>2004</td>\n",
       "      <td>4</td>\n",
       "      <td>55</td>\n",
       "      <td>2006</td>\n",
       "      <td>3</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Year_x  Int_rate_x  US_GDP_Thousands_x  Year_y  Int_rate_y  \\\n",
       "HPI                                                               \n",
       "80     2001           2                  50    2005           1   \n",
       "85     2002           3                  55    2008           7   \n",
       "88     2003           2                  65    2007           5   \n",
       "89     2004           4                  55    2006           3   \n",
       "\n",
       "     US_GDP_Thousands_y  \n",
       "HPI                      \n",
       "80                   50  \n",
       "85                   71  \n",
       "88                   60  \n",
       "89                   56  "
      ]
     },
     "execution_count": 167,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "right_merge = pd.merge(df1, df2, on='HPI', how='right')\n",
    "right_merge.set_index('HPI', inplace=True)\n",
    "right_merge"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "As you can see, the  resultant dataframe is ordered according to the index of df2 in ascending order\n",
    "80 -> 2005\n",
    "85 -> 2008\n",
    "88 -> 2007\n",
    "89 -> 2006"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year_x</th>\n",
       "      <th>Int_rate_x</th>\n",
       "      <th>US_GDP_Thousands_x</th>\n",
       "      <th>Year_y</th>\n",
       "      <th>Int_rate_y</th>\n",
       "      <th>US_GDP_Thousands_y</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HPI</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>2001</td>\n",
       "      <td>2</td>\n",
       "      <td>50</td>\n",
       "      <td>2005</td>\n",
       "      <td>1</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>2002</td>\n",
       "      <td>3</td>\n",
       "      <td>55</td>\n",
       "      <td>2008</td>\n",
       "      <td>7</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>2003</td>\n",
       "      <td>2</td>\n",
       "      <td>65</td>\n",
       "      <td>2007</td>\n",
       "      <td>5</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>2004</td>\n",
       "      <td>4</td>\n",
       "      <td>55</td>\n",
       "      <td>2006</td>\n",
       "      <td>3</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Year_x  Int_rate_x  US_GDP_Thousands_x  Year_y  Int_rate_y  \\\n",
       "HPI                                                               \n",
       "80     2001           2                  50    2005           1   \n",
       "85     2002           3                  55    2008           7   \n",
       "88     2003           2                  65    2007           5   \n",
       "89     2004           4                  55    2006           3   \n",
       "\n",
       "     US_GDP_Thousands_y  \n",
       "HPI                      \n",
       "80                   50  \n",
       "85                   71  \n",
       "88                   60  \n",
       "89                   56  "
      ]
     },
     "execution_count": 170,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Finally, \"inner\" is the intersection of keys, basically just what is shared between all the dataframes. \n",
    "inner_merge = pd.merge(df1, df2, on='HPI', how='inner')\n",
    "inner_merge.set_index('HPI', inplace=True)\n",
    "inner_merge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Since, we don't see a difference here, let's merge df1 and df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>HPI</th>\n",
       "      <th>Int_rate</th>\n",
       "      <th>Low_tier_HPI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2001</td>\n",
       "      <td>80</td>\n",
       "      <td>2</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2002</td>\n",
       "      <td>85</td>\n",
       "      <td>3</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2003</td>\n",
       "      <td>88</td>\n",
       "      <td>2</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2004</td>\n",
       "      <td>85</td>\n",
       "      <td>2</td>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year  HPI  Int_rate  Low_tier_HPI\n",
       "0  2001   80         2            50\n",
       "1  2002   85         3            52\n",
       "2  2003   88         2            50\n",
       "3  2004   85         2            53"
      ]
     },
     "execution_count": 172,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year_x</th>\n",
       "      <th>Int_rate_x</th>\n",
       "      <th>US_GDP_Thousands</th>\n",
       "      <th>Year_y</th>\n",
       "      <th>Int_rate_y</th>\n",
       "      <th>Low_tier_HPI</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HPI</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>2001</td>\n",
       "      <td>2</td>\n",
       "      <td>50</td>\n",
       "      <td>2001</td>\n",
       "      <td>2</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>2002</td>\n",
       "      <td>3</td>\n",
       "      <td>55</td>\n",
       "      <td>2002</td>\n",
       "      <td>3</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>2002</td>\n",
       "      <td>3</td>\n",
       "      <td>55</td>\n",
       "      <td>2004</td>\n",
       "      <td>2</td>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>2003</td>\n",
       "      <td>2</td>\n",
       "      <td>65</td>\n",
       "      <td>2003</td>\n",
       "      <td>2</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Year_x  Int_rate_x  US_GDP_Thousands  Year_y  Int_rate_y  Low_tier_HPI\n",
       "HPI                                                                        \n",
       "80     2001           2                50    2001           2            50\n",
       "85     2002           3                55    2002           3            52\n",
       "85     2002           3                55    2004           2            53\n",
       "88     2003           2                65    2003           2            50"
      ]
     },
     "execution_count": 173,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "inner_merge = pd.merge(df1, df3, on='HPI', how='inner')\n",
    "inner_merge.set_index('HPI', inplace=True)\n",
    "inner_merge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# The row '89' is missing as you can see. Cos this is just an intersection of keys (indexs)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Need a little cheat sheet?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Left - equal to left outer join SQL - use keys from left frame only\n",
    "##### Right - right outer join from SQL- use keys from right frame only.\n",
    "##### Outer - full outer join - use union of keys\n",
    "##### Inner - use only intersection of keys.\n",
    "#### Each of these has its own reasoning, but, as you can see, the default option is \"inner.\""
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [conda root]",
   "language": "python",
   "name": "conda-root-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}