class 04 Joining Data with Pandas/join_pandas_class4.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1- `merge_ordered()`\n",
    "- In the chapter we'll learn `merge_ordered`\n",
    "- used in time-series or ordered data.\n",
    "- Check out the below diagram\n",
    "<img src='./media/merge_ordere.png' width=700 height=800>\n",
    "- the results are standard merger with `outer` join but, the result is `sorted` \n",
    "- `sorted` make it good for time series data.\n",
    "- let's discuss some of the difference between `merge()` & `merge_ordered()`\n",
    "<img src= './media/mergeVsmergeorderd.png ' width=700 height=800>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- will be using **financial, microeconomical & stock market dataset.**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## stock data \n",
    "#### creating data for apple stocks from Jan 2007 to July 2007"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#apple_stocks_data\n",
    "data_appl = { 'date': ['2007-02-01', '2007-03-01', '2007-04-01', '2007-04-01', '2007-06-01'],\n",
    "       'close': [12.087143, 13.272857, 14.257143, 17.312857, 17.434286]}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>close</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2007-02-01</td>\n",
       "      <td>12.087143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2007-03-01</td>\n",
       "      <td>13.272857</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2007-04-01</td>\n",
       "      <td>14.257143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2007-04-01</td>\n",
       "      <td>17.312857</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2007-06-01</td>\n",
       "      <td>17.434286</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date      close\n",
       "0  2007-02-01  12.087143\n",
       "1  2007-03-01  13.272857\n",
       "2  2007-04-01  14.257143\n",
       "3  2007-04-01  17.312857\n",
       "4  2007-06-01  17.434286"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "appl = pd.DataFrame(data_appl)\n",
    "appl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "#macdonald's stock data\n",
    "data_mcd = { 'date': ['2007-01-01', '2007-02-01', '2007-02-01', '2007-04-01', '2007-04-01'],\n",
    "       'close': [44.349998, 43.689999, 45.049999, 48.279999, 50.549999]}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>close</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2007-01-01</td>\n",
       "      <td>44.349998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2007-02-01</td>\n",
       "      <td>43.689999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2007-02-01</td>\n",
       "      <td>45.049999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2007-04-01</td>\n",
       "      <td>48.279999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2007-04-01</td>\n",
       "      <td>50.549999</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date      close\n",
       "0  2007-01-01  44.349998\n",
       "1  2007-02-01  43.689999\n",
       "2  2007-02-01  45.049999\n",
       "3  2007-04-01  48.279999\n",
       "4  2007-04-01  50.549999"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mcd = pd.DataFrame(data_mcd)\n",
    "mcd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Merging  stocks data using `merge_ordered()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>close_appl</th>\n",
       "      <th>close_mcd</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2007-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>44.349998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2007-02-01</td>\n",
       "      <td>12.087143</td>\n",
       "      <td>43.689999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2007-02-01</td>\n",
       "      <td>12.087143</td>\n",
       "      <td>45.049999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2007-03-01</td>\n",
       "      <td>13.272857</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2007-04-01</td>\n",
       "      <td>14.257143</td>\n",
       "      <td>48.279999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2007-04-01</td>\n",
       "      <td>14.257143</td>\n",
       "      <td>50.549999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2007-04-01</td>\n",
       "      <td>17.312857</td>\n",
       "      <td>48.279999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2007-04-01</td>\n",
       "      <td>17.312857</td>\n",
       "      <td>50.549999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2007-06-01</td>\n",
       "      <td>17.434286</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date  close_appl  close_mcd\n",
       "0  2007-01-01         NaN  44.349998\n",
       "1  2007-02-01   12.087143  43.689999\n",
       "2  2007-02-01   12.087143  45.049999\n",
       "3  2007-03-01   13.272857        NaN\n",
       "4  2007-04-01   14.257143  48.279999\n",
       "5  2007-04-01   14.257143  50.549999\n",
       "6  2007-04-01   17.312857  48.279999\n",
       "7  2007-04-01   17.312857  50.549999\n",
       "8  2007-06-01   17.434286        NaN"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge_ordered(appl, mcd, on='date', suffixes=('_appl', '_mcd'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### ffill argument (Forward fill)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src='./media/ffill.png' width=700 height=800 >"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>close_appl</th>\n",
       "      <th>close_mcd</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2007-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>44.349998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2007-02-01</td>\n",
       "      <td>12.087143</td>\n",
       "      <td>43.689999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2007-02-01</td>\n",
       "      <td>12.087143</td>\n",
       "      <td>45.049999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2007-03-01</td>\n",
       "      <td>13.272857</td>\n",
       "      <td>45.049999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2007-04-01</td>\n",
       "      <td>14.257143</td>\n",
       "      <td>48.279999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2007-04-01</td>\n",
       "      <td>14.257143</td>\n",
       "      <td>50.549999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2007-04-01</td>\n",
       "      <td>17.312857</td>\n",
       "      <td>48.279999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2007-04-01</td>\n",
       "      <td>17.312857</td>\n",
       "      <td>50.549999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2007-06-01</td>\n",
       "      <td>17.434286</td>\n",
       "      <td>50.549999</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date  close_appl  close_mcd\n",
       "0  2007-01-01         NaN  44.349998\n",
       "1  2007-02-01   12.087143  43.689999\n",
       "2  2007-02-01   12.087143  45.049999\n",
       "3  2007-03-01   13.272857  45.049999\n",
       "4  2007-04-01   14.257143  48.279999\n",
       "5  2007-04-01   14.257143  50.549999\n",
       "6  2007-04-01   17.312857  48.279999\n",
       "7  2007-04-01   17.312857  50.549999\n",
       "8  2007-06-01   17.434286  50.549999"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge_ordered(appl, mcd, on='date', suffixes=('_appl', '_mcd'), fill_method='ffill')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### When to use merge_ordered()?\n",
    "- Best choice intead of `merge()` when working with Ordered data / time series\n",
    "- Filling in missing values\n",
    "- mostly in model trainning where we aviod missing data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2- `merge_asof()`\n",
    "- similar to **merger_ordered()** left-join\n",
    "- similar feature as **merge_ordered()**\n",
    "- match on `nearest` key columns `not on exact match`\n",
    "- Merge **on** column must be sorted\n",
    "- In the table shown here, when we merge on column \"C\", we bring back all of the rows from the left table\n",
    "<img src='./media/merge_asod().png'>\n",
    "\n",
    "- However, the row selected from the right table is the last row whose \"C\" value is less than or equal to the \"C\" value in the left table. So, for example, the second row in the left table is matched with the third row in the right table. This because 3 is the closest value in the right table that is still less than or equal to 5."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**stock price data for the Visa company with entries for every hour on Nov, 11, 2017**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [],
   "source": [
    "visa = pd.read_csv('./dataset/visa.csv')\n",
    "ibm = pd.read_csv('./dataset/ibm.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(7, 2)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date_time</th>\n",
       "      <th>close</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017-11-17 16:00:00</td>\n",
       "      <td>110.320</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017-11-17 17:00:00</td>\n",
       "      <td>110.240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017-11-17 18:00:00</td>\n",
       "      <td>110.065</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017-11-17 19:00:00</td>\n",
       "      <td>110.040</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017-11-17 20:00:00</td>\n",
       "      <td>110.000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            date_time    close\n",
       "0 2017-11-17 16:00:00  110.320\n",
       "1 2017-11-17 17:00:00  110.240\n",
       "2 2017-11-17 18:00:00  110.065\n",
       "3 2017-11-17 19:00:00  110.040\n",
       "4 2017-11-17 20:00:00  110.000"
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "visa['date_time'] = pd.DatetimeIndex(visa['date_time'])\n",
    "print(visa.shape)\n",
    "visa.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(11, 2)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date_time</th>\n",
       "      <th>close</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017-11-17 15:35:00</td>\n",
       "      <td>149.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017-11-17 15:40:00</td>\n",
       "      <td>149.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017-11-17 15:45:00</td>\n",
       "      <td>148.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017-11-17 15:50:00</td>\n",
       "      <td>148.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017-11-17 15:55:00</td>\n",
       "      <td>149.11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            date_time   close\n",
       "0 2017-11-17 15:35:00  149.30\n",
       "1 2017-11-17 15:40:00  149.13\n",
       "2 2017-11-17 15:45:00  148.98\n",
       "3 2017-11-17 15:50:00  148.99\n",
       "4 2017-11-17 15:55:00  149.11"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ibm['date_time'] = pd.DatetimeIndex(ibm['date_time'])\n",
    "print(ibm.shape)\n",
    "ibm.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- First define left & right table.\n",
    "- columns on which we want to merge\n",
    "- suffixes\n",
    "- Observe the result the columns are 7 b/c of left join"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date_time</th>\n",
       "      <th>close_visa</th>\n",
       "      <th>close_ibm</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017-11-17 16:00:00</td>\n",
       "      <td>110.3200</td>\n",
       "      <td>149.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017-11-17 17:00:00</td>\n",
       "      <td>110.2400</td>\n",
       "      <td>149.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017-11-17 18:00:00</td>\n",
       "      <td>110.0650</td>\n",
       "      <td>149.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017-11-17 19:00:00</td>\n",
       "      <td>110.0400</td>\n",
       "      <td>149.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017-11-17 20:00:00</td>\n",
       "      <td>110.0000</td>\n",
       "      <td>149.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2017-11-17 21:00:00</td>\n",
       "      <td>109.9966</td>\n",
       "      <td>149.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2017-11-17 22:00:00</td>\n",
       "      <td>109.8200</td>\n",
       "      <td>149.96</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            date_time  close_visa  close_ibm\n",
       "0 2017-11-17 16:00:00    110.3200     149.25\n",
       "1 2017-11-17 17:00:00    110.2400     149.96\n",
       "2 2017-11-17 18:00:00    110.0650     149.96\n",
       "3 2017-11-17 19:00:00    110.0400     149.96\n",
       "4 2017-11-17 20:00:00    110.0000     149.96\n",
       "5 2017-11-17 21:00:00    109.9966     149.96\n",
       "6 2017-11-17 22:00:00    109.8200     149.96"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    " pd.merge_asof(left=visa, right= ibm, on='date_time', suffixes=('_visa', '_ibm'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date_time</th>\n",
       "      <th>close_visa</th>\n",
       "      <th>close_ibm</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017-11-17 16:00:00</td>\n",
       "      <td>110.3200</td>\n",
       "      <td>149.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017-11-17 17:00:00</td>\n",
       "      <td>110.2400</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017-11-17 18:00:00</td>\n",
       "      <td>110.0650</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017-11-17 19:00:00</td>\n",
       "      <td>110.0400</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017-11-17 20:00:00</td>\n",
       "      <td>110.0000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2017-11-17 21:00:00</td>\n",
       "      <td>109.9966</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2017-11-17 22:00:00</td>\n",
       "      <td>109.8200</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            date_time  close_visa  close_ibm\n",
       "0 2017-11-17 16:00:00    110.3200     149.25\n",
       "1 2017-11-17 17:00:00    110.2400        NaN\n",
       "2 2017-11-17 18:00:00    110.0650        NaN\n",
       "3 2017-11-17 19:00:00    110.0400        NaN\n",
       "4 2017-11-17 20:00:00    110.0000        NaN\n",
       "5 2017-11-17 21:00:00    109.9966        NaN\n",
       "6 2017-11-17 22:00:00    109.8200        NaN"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge_asof(visa, ibm, on=['date_time'],suffixes=('_visa','_ibm'),direction='forward')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### When to use merge_asof()\n",
    "- Data sampled from a process\n",
    "- Developing a training set (no data leakage)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Country Name</th>\n",
       "      <th>Country Code</th>\n",
       "      <th>Indicator Name</th>\n",
       "      <th>Year</th>\n",
       "      <th>Pop</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Aruba</td>\n",
       "      <td>ABW</td>\n",
       "      <td>Population, total</td>\n",
       "      <td>2010</td>\n",
       "      <td>101669.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>AFG</td>\n",
       "      <td>Population, total</td>\n",
       "      <td>2010</td>\n",
       "      <td>29185507.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Angola</td>\n",
       "      <td>AGO</td>\n",
       "      <td>Population, total</td>\n",
       "      <td>2010</td>\n",
       "      <td>23356246.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Albania</td>\n",
       "      <td>ALB</td>\n",
       "      <td>Population, total</td>\n",
       "      <td>2010</td>\n",
       "      <td>2913021.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Andorra</td>\n",
       "      <td>AND</td>\n",
       "      <td>Population, total</td>\n",
       "      <td>2010</td>\n",
       "      <td>84449.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Country Name Country Code     Indicator Name  Year         Pop\n",
       "0        Aruba          ABW  Population, total  2010    101669.0\n",
       "1  Afghanistan          AFG  Population, total  2010  29185507.0\n",
       "2       Angola          AGO  Population, total  2010  23356246.0\n",
       "3      Albania          ALB  Population, total  2010   2913021.0\n",
       "4      Andorra          AND  Population, total  2010     84449.0"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pop = pd.read_csv('./dataset/WorldBank_POP.csv')\n",
    "pop.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Country Name</th>\n",
       "      <th>Country Code</th>\n",
       "      <th>Indicator Name</th>\n",
       "      <th>Year</th>\n",
       "      <th>GDP</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>China</td>\n",
       "      <td>CHN</td>\n",
       "      <td>GDP (current US$)</td>\n",
       "      <td>2010</td>\n",
       "      <td>6.087160e+12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Germany</td>\n",
       "      <td>DEU</td>\n",
       "      <td>GDP (current US$)</td>\n",
       "      <td>2010</td>\n",
       "      <td>3.417090e+12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Japan</td>\n",
       "      <td>JPN</td>\n",
       "      <td>GDP (current US$)</td>\n",
       "      <td>2010</td>\n",
       "      <td>5.700100e+12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>United States</td>\n",
       "      <td>USA</td>\n",
       "      <td>GDP (current US$)</td>\n",
       "      <td>2010</td>\n",
       "      <td>1.499210e+13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>China</td>\n",
       "      <td>CHN</td>\n",
       "      <td>GDP (current US$)</td>\n",
       "      <td>2011</td>\n",
       "      <td>7.551500e+12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Country Name Country Code     Indicator Name  Year           GDP\n",
       "0          China          CHN  GDP (current US$)  2010  6.087160e+12\n",
       "1        Germany          DEU  GDP (current US$)  2010  3.417090e+12\n",
       "2          Japan          JPN  GDP (current US$)  2010  5.700100e+12\n",
       "3  United States          USA  GDP (current US$)  2010  1.499210e+13\n",
       "4          China          CHN  GDP (current US$)  2011  7.551500e+12"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gdp = pd.read_csv('./dataset/WorldBank_GDP.csv')\n",
    "gdp.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Country Name_pop</th>\n",
       "      <th>Country Code_pop</th>\n",
       "      <th>Indicator Name_pop</th>\n",
       "      <th>Year</th>\n",
       "      <th>Pop</th>\n",
       "      <th>Country Namegdp</th>\n",
       "      <th>Country Codegdp</th>\n",
       "      <th>Indicator Namegdp</th>\n",
       "      <th>GDP</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Aruba</td>\n",
       "      <td>ABW</td>\n",
       "      <td>Population, total</td>\n",
       "      <td>2010</td>\n",
       "      <td>101669.0</td>\n",
       "      <td>United States</td>\n",
       "      <td>USA</td>\n",
       "      <td>GDP (current US$)</td>\n",
       "      <td>1.499210e+13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>AFG</td>\n",
       "      <td>Population, total</td>\n",
       "      <td>2010</td>\n",
       "      <td>29185507.0</td>\n",
       "      <td>United States</td>\n",
       "      <td>USA</td>\n",
       "      <td>GDP (current US$)</td>\n",
       "      <td>1.499210e+13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Angola</td>\n",
       "      <td>AGO</td>\n",
       "      <td>Population, total</td>\n",
       "      <td>2010</td>\n",
       "      <td>23356246.0</td>\n",
       "      <td>United States</td>\n",
       "      <td>USA</td>\n",
       "      <td>GDP (current US$)</td>\n",
       "      <td>1.499210e+13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Albania</td>\n",
       "      <td>ALB</td>\n",
       "      <td>Population, total</td>\n",
       "      <td>2010</td>\n",
       "      <td>2913021.0</td>\n",
       "      <td>United States</td>\n",
       "      <td>USA</td>\n",
       "      <td>GDP (current US$)</td>\n",
       "      <td>1.499210e+13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Andorra</td>\n",
       "      <td>AND</td>\n",
       "      <td>Population, total</td>\n",
       "      <td>2010</td>\n",
       "      <td>84449.0</td>\n",
       "      <td>United States</td>\n",
       "      <td>USA</td>\n",
       "      <td>GDP (current US$)</td>\n",
       "      <td>1.499210e+13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2635</th>\n",
       "      <td>Kosovo</td>\n",
       "      <td>XKX</td>\n",
       "      <td>Population, total</td>\n",
       "      <td>2018</td>\n",
       "      <td>1845300.0</td>\n",
       "      <td>United States</td>\n",
       "      <td>USA</td>\n",
       "      <td>GDP (current US$)</td>\n",
       "      <td>2.049410e+13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2636</th>\n",
       "      <td>Yemen, Rep.</td>\n",
       "      <td>YEM</td>\n",
       "      <td>Population, total</td>\n",
       "      <td>2018</td>\n",
       "      <td>28498687.0</td>\n",
       "      <td>United States</td>\n",
       "      <td>USA</td>\n",
       "      <td>GDP (current US$)</td>\n",
       "      <td>2.049410e+13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2637</th>\n",
       "      <td>South Africa</td>\n",
       "      <td>ZAF</td>\n",
       "      <td>Population, total</td>\n",
       "      <td>2018</td>\n",
       "      <td>57779622.0</td>\n",
       "      <td>United States</td>\n",
       "      <td>USA</td>\n",
       "      <td>GDP (current US$)</td>\n",
       "      <td>2.049410e+13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2638</th>\n",
       "      <td>Zambia</td>\n",
       "      <td>ZMB</td>\n",
       "      <td>Population, total</td>\n",
       "      <td>2018</td>\n",
       "      <td>17351822.0</td>\n",
       "      <td>United States</td>\n",
       "      <td>USA</td>\n",
       "      <td>GDP (current US$)</td>\n",
       "      <td>2.049410e+13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2639</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>ZWE</td>\n",
       "      <td>Population, total</td>\n",
       "      <td>2018</td>\n",
       "      <td>14439018.0</td>\n",
       "      <td>United States</td>\n",
       "      <td>USA</td>\n",
       "      <td>GDP (current US$)</td>\n",
       "      <td>2.049410e+13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2640 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Country Name_pop Country Code_pop Indicator Name_pop  Year         Pop  \\\n",
       "0               Aruba              ABW  Population, total  2010    101669.0   \n",
       "1         Afghanistan              AFG  Population, total  2010  29185507.0   \n",
       "2              Angola              AGO  Population, total  2010  23356246.0   \n",
       "3             Albania              ALB  Population, total  2010   2913021.0   \n",
       "4             Andorra              AND  Population, total  2010     84449.0   \n",
       "...               ...              ...                ...   ...         ...   \n",
       "2635           Kosovo              XKX  Population, total  2018   1845300.0   \n",
       "2636      Yemen, Rep.              YEM  Population, total  2018  28498687.0   \n",
       "2637     South Africa              ZAF  Population, total  2018  57779622.0   \n",
       "2638           Zambia              ZMB  Population, total  2018  17351822.0   \n",
       "2639         Zimbabwe              ZWE  Population, total  2018  14439018.0   \n",
       "\n",
       "     Country Namegdp Country Codegdp  Indicator Namegdp           GDP  \n",
       "0      United States             USA  GDP (current US$)  1.499210e+13  \n",
       "1      United States             USA  GDP (current US$)  1.499210e+13  \n",
       "2      United States             USA  GDP (current US$)  1.499210e+13  \n",
       "3      United States             USA  GDP (current US$)  1.499210e+13  \n",
       "4      United States             USA  GDP (current US$)  1.499210e+13  \n",
       "...              ...             ...                ...           ...  \n",
       "2635   United States             USA  GDP (current US$)  2.049410e+13  \n",
       "2636   United States             USA  GDP (current US$)  2.049410e+13  \n",
       "2637   United States             USA  GDP (current US$)  2.049410e+13  \n",
       "2638   United States             USA  GDP (current US$)  2.049410e+13  \n",
       "2639   United States             USA  GDP (current US$)  2.049410e+13  \n",
       "\n",
       "[2640 rows x 9 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge_asof(pop, gdp, on=['Year'], suffixes=('_pop', 'gdp'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### comparsion"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src='./media/diff_merge_Vs_mergeordered.png' width=700 height=800>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 3- Selecting data with `.query()`\n",
    "\n",
    "`.query('SOME SELECTION STATEMENT')`\n",
    "- Accepts an input string\n",
    "    - Input string used to determine what rows are returned\n",
    "    - Input string similar to statement after **WHERE** clause in **SQL** statement\n",
    "- Prior knowledge of SQL is not necessary\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>disney</th>\n",
       "      <th>nike</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>143.009995</td>\n",
       "      <td>86.029999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-08-01</td>\n",
       "      <td>137.259995</td>\n",
       "      <td>84.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-09-01</td>\n",
       "      <td>130.320007</td>\n",
       "      <td>93.919998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>129.919998</td>\n",
       "      <td>89.550003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-11-01</td>\n",
       "      <td>151.580002</td>\n",
       "      <td>93.489998</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date      disney       nike\n",
       "0  2019-07-01  143.009995  86.029999\n",
       "1  2019-08-01  137.259995  84.500000\n",
       "2  2019-09-01  130.320007  93.919998\n",
       "3  2019-10-01  129.919998  89.550003\n",
       "4  2019-11-01  151.580002  93.489998"
      ]
     },
     "execution_count": 119,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stocks = pd.read_csv('./dataset/stocks.csv')\n",
    "stocks.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>disney</th>\n",
       "      <th>nike</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-09-01</td>\n",
       "      <td>130.320007</td>\n",
       "      <td>93.919998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-11-01</td>\n",
       "      <td>151.580002</td>\n",
       "      <td>93.489998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2019-12-01</td>\n",
       "      <td>144.630005</td>\n",
       "      <td>101.309998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>138.309998</td>\n",
       "      <td>96.300003</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date      disney        nike\n",
       "2  2019-09-01  130.320007   93.919998\n",
       "4  2019-11-01  151.580002   93.489998\n",
       "5  2019-12-01  144.630005  101.309998\n",
       "6  2020-01-01  138.309998   96.300003"
      ]
     },
     "execution_count": 120,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stocks.query('nike >= 90')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>disney</th>\n",
       "      <th>nike</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-09-01</td>\n",
       "      <td>130.320007</td>\n",
       "      <td>93.919998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>138.309998</td>\n",
       "      <td>96.300003</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date      disney       nike\n",
       "2  2019-09-01  130.320007  93.919998\n",
       "6  2020-01-01  138.309998  96.300003"
      ]
     },
     "execution_count": 121,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stocks.query('nike > 90 and disney < 140')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>disney</th>\n",
       "      <th>nike</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2019-12-01</td>\n",
       "      <td>144.630005</td>\n",
       "      <td>101.309998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>138.309998</td>\n",
       "      <td>96.300003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2020-03-01</td>\n",
       "      <td>96.599998</td>\n",
       "      <td>82.739998</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date      disney        nike\n",
       "5  2019-12-01  144.630005  101.309998\n",
       "6  2020-01-01  138.309998   96.300003\n",
       "8  2020-03-01   96.599998   82.739998"
      ]
     },
     "execution_count": 122,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stocks.query('nike > 96 or disney < 98')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {},
   "outputs": [],
   "source": [
    "stocks_long = pd.read_csv('./dataset/stocks_long.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>stock</th>\n",
       "      <th>close</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>disney</td>\n",
       "      <td>143.009995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-08-01</td>\n",
       "      <td>disney</td>\n",
       "      <td>137.259995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-09-01</td>\n",
       "      <td>disney</td>\n",
       "      <td>130.320007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>disney</td>\n",
       "      <td>129.919998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-11-01</td>\n",
       "      <td>disney</td>\n",
       "      <td>151.580002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>nike</td>\n",
       "      <td>86.029999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2019-08-01</td>\n",
       "      <td>nike</td>\n",
       "      <td>84.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2019-10-01</td>\n",
       "      <td>nike</td>\n",
       "      <td>89.550003</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date   stock       close\n",
       "0  2019-07-01  disney  143.009995\n",
       "1  2019-08-01  disney  137.259995\n",
       "2  2019-09-01  disney  130.320007\n",
       "3  2019-10-01  disney  129.919998\n",
       "4  2019-11-01  disney  151.580002\n",
       "5  2019-07-01    nike   86.029999\n",
       "6  2019-08-01    nike   84.500000\n",
       "8  2019-10-01    nike   89.550003"
      ]
     },
     "execution_count": 125,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stocks_long.query('stock==\"disney\" or (stock==\"nike\" and close < 90)')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 4- Reshaping data with `.melt()`\n",
    "- This method will unpivot the table from wide to long format.\n",
    "- long format is much more commputer friendly & wide is easily readable by humans.\n",
    "\n",
    "## WIDE vs LONG data:\n",
    "- Sometimes you will observe that:\n",
    "    - Every row relates to one subject & every columnhad different information about the attribute of that subject Data Format in this way is often called **WIDE**\n",
    "    - When informations is found over many rows & each row had one attribute about that subject. Data formatted in this way is often called **LONG** ot **TALL** \n",
    "<img src = \"./media/widevslong.png\" width = 700 height = 700>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>financial</th>\n",
       "      <th>company</th>\n",
       "      <th>2019</th>\n",
       "      <th>2018</th>\n",
       "      <th>2017</th>\n",
       "      <th>2016</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>total_revenue</td>\n",
       "      <td>twitter</td>\n",
       "      <td>3459329</td>\n",
       "      <td>3042359</td>\n",
       "      <td>2443299</td>\n",
       "      <td>2529619</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>gross_profit</td>\n",
       "      <td>twitter</td>\n",
       "      <td>2322288</td>\n",
       "      <td>2077362</td>\n",
       "      <td>1582057</td>\n",
       "      <td>1597379</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>net_income</td>\n",
       "      <td>twitter</td>\n",
       "      <td>1465659</td>\n",
       "      <td>1205596</td>\n",
       "      <td>-108063</td>\n",
       "      <td>-456873</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>total_revenue</td>\n",
       "      <td>facebook</td>\n",
       "      <td>70697000</td>\n",
       "      <td>55838000</td>\n",
       "      <td>40653000</td>\n",
       "      <td>27638000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>gross_profit</td>\n",
       "      <td>facebook</td>\n",
       "      <td>57927000</td>\n",
       "      <td>46483000</td>\n",
       "      <td>35199000</td>\n",
       "      <td>23849000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>net_income</td>\n",
       "      <td>facebook</td>\n",
       "      <td>18485000</td>\n",
       "      <td>22112000</td>\n",
       "      <td>15934000</td>\n",
       "      <td>10217000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       financial   company      2019      2018      2017      2016\n",
       "0  total_revenue   twitter   3459329   3042359   2443299   2529619\n",
       "1   gross_profit   twitter   2322288   2077362   1582057   1597379\n",
       "2     net_income   twitter   1465659   1205596   -108063   -456873\n",
       "3  total_revenue  facebook  70697000  55838000  40653000  27638000\n",
       "4   gross_profit  facebook  57927000  46483000  35199000  23849000\n",
       "5     net_income  facebook  18485000  22112000  15934000  10217000"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "social_fin = pd.read_csv('./dataset/social_fin.csv')\n",
    "social_fin"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>financial</th>\n",
       "      <th>company</th>\n",
       "      <th>variable</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>total_revenue</td>\n",
       "      <td>twitter</td>\n",
       "      <td>2019</td>\n",
       "      <td>3459329</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>gross_profit</td>\n",
       "      <td>twitter</td>\n",
       "      <td>2019</td>\n",
       "      <td>2322288</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>net_income</td>\n",
       "      <td>twitter</td>\n",
       "      <td>2019</td>\n",
       "      <td>1465659</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>total_revenue</td>\n",
       "      <td>facebook</td>\n",
       "      <td>2019</td>\n",
       "      <td>70697000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>gross_profit</td>\n",
       "      <td>facebook</td>\n",
       "      <td>2019</td>\n",
       "      <td>57927000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       financial   company variable     value\n",
       "0  total_revenue   twitter     2019   3459329\n",
       "1   gross_profit   twitter     2019   2322288\n",
       "2     net_income   twitter     2019   1465659\n",
       "3  total_revenue  facebook     2019  70697000\n",
       "4   gross_profit  facebook     2019  57927000"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "social_fin_tall = social_fin.melt(id_vars=['financial', 'company'])\n",
    "social_fin_tall.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>financial</th>\n",
       "      <th>company</th>\n",
       "      <th>variable</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>total_revenue</td>\n",
       "      <td>twitter</td>\n",
       "      <td>2018</td>\n",
       "      <td>3042359</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>gross_profit</td>\n",
       "      <td>twitter</td>\n",
       "      <td>2018</td>\n",
       "      <td>2077362</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>net_income</td>\n",
       "      <td>twitter</td>\n",
       "      <td>2018</td>\n",
       "      <td>1205596</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>total_revenue</td>\n",
       "      <td>facebook</td>\n",
       "      <td>2018</td>\n",
       "      <td>55838000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>gross_profit</td>\n",
       "      <td>facebook</td>\n",
       "      <td>2018</td>\n",
       "      <td>46483000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>net_income</td>\n",
       "      <td>facebook</td>\n",
       "      <td>2018</td>\n",
       "      <td>22112000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>total_revenue</td>\n",
       "      <td>twitter</td>\n",
       "      <td>2017</td>\n",
       "      <td>2443299</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>gross_profit</td>\n",
       "      <td>twitter</td>\n",
       "      <td>2017</td>\n",
       "      <td>1582057</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>net_income</td>\n",
       "      <td>twitter</td>\n",
       "      <td>2017</td>\n",
       "      <td>-108063</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>total_revenue</td>\n",
       "      <td>facebook</td>\n",
       "      <td>2017</td>\n",
       "      <td>40653000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>gross_profit</td>\n",
       "      <td>facebook</td>\n",
       "      <td>2017</td>\n",
       "      <td>35199000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>net_income</td>\n",
       "      <td>facebook</td>\n",
       "      <td>2017</td>\n",
       "      <td>15934000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        financial   company variable     value\n",
       "0   total_revenue   twitter     2018   3042359\n",
       "1    gross_profit   twitter     2018   2077362\n",
       "2      net_income   twitter     2018   1205596\n",
       "3   total_revenue  facebook     2018  55838000\n",
       "4    gross_profit  facebook     2018  46483000\n",
       "5      net_income  facebook     2018  22112000\n",
       "6   total_revenue   twitter     2017   2443299\n",
       "7    gross_profit   twitter     2017   1582057\n",
       "8      net_income   twitter     2017   -108063\n",
       "9   total_revenue  facebook     2017  40653000\n",
       "10   gross_profit  facebook     2017  35199000\n",
       "11     net_income  facebook     2017  15934000"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "social_fin_tall = social_fin.melt(id_vars=['financial', 'company'],\n",
    "                                 value_vars=['2018', '2017'])\n",
    "social_fin_tall"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>financial</th>\n",
       "      <th>company</th>\n",
       "      <th>year</th>\n",
       "      <th>dollars</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>total_revenue</td>\n",
       "      <td>twitter</td>\n",
       "      <td>2018</td>\n",
       "      <td>3042359</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>gross_profit</td>\n",
       "      <td>twitter</td>\n",
       "      <td>2018</td>\n",
       "      <td>2077362</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>net_income</td>\n",
       "      <td>twitter</td>\n",
       "      <td>2018</td>\n",
       "      <td>1205596</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>total_revenue</td>\n",
       "      <td>facebook</td>\n",
       "      <td>2018</td>\n",
       "      <td>55838000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>gross_profit</td>\n",
       "      <td>facebook</td>\n",
       "      <td>2018</td>\n",
       "      <td>46483000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       financial   company  year   dollars\n",
       "0  total_revenue   twitter  2018   3042359\n",
       "1   gross_profit   twitter  2018   2077362\n",
       "2     net_income   twitter  2018   1205596\n",
       "3  total_revenue  facebook  2018  55838000\n",
       "4   gross_profit  facebook  2018  46483000"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "social_fin_tall = social_fin.melt(id_vars=['financial', 'company'],\n",
    "                                 value_vars=['2018', '2017'],\n",
    "                                 var_name='year',\n",
    "                                 value_name= 'dollars')\n",
    "social_fin_tall.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### PRACTISE: "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1 - In this exercise, you want to analyze stock returns from the S&P 500. You believe there may be a relationship between the returns of the S&P 500 and the GDP of the US. Merge the different datasets together to compute the correlation.\n",
    "\n",
    "Two tables have been provided for you, named `sp500`, and `gdp`. As always, pandas has been imported for you as pd."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "gdp = pd.read_csv('./dataset/practise_dataset/gdp.csv')\n",
    "sp500 = pd.read_csv('./dataset/practise_dataset/sp_500.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Use `merge_ordered()` to merge `gdp` and `sp500` using a left join on year and date. Save the results as `gdp_sp500`.\n",
    "- Print `gdp_sp500` and look at the returns for the year 2018."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>countyr code</th>\n",
       "      <th>year</th>\n",
       "      <th>gdp</th>\n",
       "      <th>date</th>\n",
       "      <th>returns</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>USA</td>\n",
       "      <td>2010</td>\n",
       "      <td>1.500000e+13</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>12.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>USA</td>\n",
       "      <td>2011</td>\n",
       "      <td>1.550000e+13</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>USA</td>\n",
       "      <td>2012</td>\n",
       "      <td>1.620000e+13</td>\n",
       "      <td>2012.0</td>\n",
       "      <td>13.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>USA</td>\n",
       "      <td>2012</td>\n",
       "      <td>1.620000e+13</td>\n",
       "      <td>2012.0</td>\n",
       "      <td>13.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>USA</td>\n",
       "      <td>2013</td>\n",
       "      <td>1.680000e+13</td>\n",
       "      <td>2013.0</td>\n",
       "      <td>29.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>USA</td>\n",
       "      <td>2014</td>\n",
       "      <td>1.750000e+13</td>\n",
       "      <td>2014.0</td>\n",
       "      <td>11.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>USA</td>\n",
       "      <td>2015</td>\n",
       "      <td>1.820000e+13</td>\n",
       "      <td>2015.0</td>\n",
       "      <td>-0.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>USA</td>\n",
       "      <td>2016</td>\n",
       "      <td>1.870000e+13</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>9.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>USA</td>\n",
       "      <td>2017</td>\n",
       "      <td>1.950000e+13</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>19.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>USA</td>\n",
       "      <td>2018</td>\n",
       "      <td>2.050000e+13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  countyr code   year           gdp    date  returns\n",
       "0           USA  2010  1.500000e+13  2010.0    12.78\n",
       "1           USA  2011  1.550000e+13  2011.0     0.00\n",
       "2           USA  2012  1.620000e+13  2012.0    13.41\n",
       "3           USA  2012  1.620000e+13  2012.0    13.41\n",
       "4           USA  2013  1.680000e+13  2013.0    29.60\n",
       "5           USA  2014  1.750000e+13  2014.0    11.39\n",
       "6           USA  2015  1.820000e+13  2015.0    -0.73\n",
       "7           USA  2016  1.870000e+13  2016.0     9.54\n",
       "8           USA  2017  1.950000e+13  2017.0    19.42\n",
       "9           USA  2018  2.050000e+13     NaN      NaN"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Use merge_ordered() to merge gdp and sp500 on year and date\n",
    "gdp_sp500 = pd.merge_ordered(gdp, sp500, left_on= 'year', right_on='date', \n",
    "                             how='left')\n",
    "\n",
    "# Print gdp_sp500\n",
    "gdp_sp500"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Use `merge_ordered()`, again similar to before, to merge `gdp` and `sp500` use the function's ability to interpolate missing data to forward fill the missing value for returns, assigning this table to the variable `gdp_sp500`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>countyr code</th>\n",
       "      <th>year</th>\n",
       "      <th>gdp</th>\n",
       "      <th>date</th>\n",
       "      <th>returns</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>USA</td>\n",
       "      <td>2010</td>\n",
       "      <td>1.500000e+13</td>\n",
       "      <td>2010</td>\n",
       "      <td>12.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>USA</td>\n",
       "      <td>2011</td>\n",
       "      <td>1.550000e+13</td>\n",
       "      <td>2011</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>USA</td>\n",
       "      <td>2012</td>\n",
       "      <td>1.620000e+13</td>\n",
       "      <td>2012</td>\n",
       "      <td>13.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>USA</td>\n",
       "      <td>2012</td>\n",
       "      <td>1.620000e+13</td>\n",
       "      <td>2012</td>\n",
       "      <td>13.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>USA</td>\n",
       "      <td>2013</td>\n",
       "      <td>1.680000e+13</td>\n",
       "      <td>2013</td>\n",
       "      <td>29.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>USA</td>\n",
       "      <td>2014</td>\n",
       "      <td>1.750000e+13</td>\n",
       "      <td>2014</td>\n",
       "      <td>11.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>USA</td>\n",
       "      <td>2015</td>\n",
       "      <td>1.820000e+13</td>\n",
       "      <td>2015</td>\n",
       "      <td>-0.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>USA</td>\n",
       "      <td>2016</td>\n",
       "      <td>1.870000e+13</td>\n",
       "      <td>2016</td>\n",
       "      <td>9.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>USA</td>\n",
       "      <td>2017</td>\n",
       "      <td>1.950000e+13</td>\n",
       "      <td>2017</td>\n",
       "      <td>19.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>USA</td>\n",
       "      <td>2018</td>\n",
       "      <td>2.050000e+13</td>\n",
       "      <td>2017</td>\n",
       "      <td>19.42</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  countyr code   year           gdp  date  returns\n",
       "0           USA  2010  1.500000e+13  2010    12.78\n",
       "1           USA  2011  1.550000e+13  2011     0.00\n",
       "2           USA  2012  1.620000e+13  2012    13.41\n",
       "3           USA  2012  1.620000e+13  2012    13.41\n",
       "4           USA  2013  1.680000e+13  2013    29.60\n",
       "5           USA  2014  1.750000e+13  2014    11.39\n",
       "6           USA  2015  1.820000e+13  2015    -0.73\n",
       "7           USA  2016  1.870000e+13  2016     9.54\n",
       "8           USA  2017  1.950000e+13  2017    19.42\n",
       "9           USA  2018  2.050000e+13  2017    19.42"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Use merge_ordered() to merge gdp and sp500, interpolate missing value\n",
    "gdp_sp500 = pd.merge_ordered(gdp, sp500, left_on= 'year',\n",
    "                             right_on= 'date', how = 'left', \n",
    "                             fill_method = 'ffill')\n",
    "\n",
    "\n",
    "# Print gdp_sp509\n",
    "gdp_sp500"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Subset the `gdp_sp500` table, select the gdp and returns columns, and save as `gdp_returns`.\n",
    "- Print the correlation matrix of the `gdp_returns` table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "              gdp   returns\n",
      "gdp      1.000000  0.220321\n",
      "returns  0.220321  1.000000\n"
     ]
    }
   ],
   "source": [
    "# Use merge_ordered() to merge gdp and sp500, interpolate missing value\n",
    "gdp_sp500 = pd.merge_ordered(gdp, sp500, left_on='year', right_on='date', \n",
    "                             how='left',  fill_method='ffill')\n",
    "\n",
    "# Subset the gdp and returns columns\n",
    "gdp_returns = gdp_sp500[['gdp', 'returns']]\n",
    "\n",
    "# Print gdp_returns correlation\n",
    "print (gdp_returns.corr())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### merge_ordered() caution, multiple columns\n",
    "When using merge_ordered() to merge on multiple columns, the order is important when you combine it with the forward fill feature. The function sorts the merge on columns in the order provided. In this exercise, we will merge GDP and population data from the World Bank for the Australia and Sweden, reversing the order of the merge on columns. The frequency of the series are different, the GDP values are quarterly, and the population is yearly. Use the forward fill feature to fill in the missing data. Depending on the order provided, the fill forward will use unintended data to fill in the missing values.\n",
    "\n",
    "The tables `gdp` and `pop` have been loaded."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "gdp = pd.read_csv('./dataset/practise_dataset/gdp_1.csv')\n",
    "pop = pd.read_csv('./dataset/practise_dataset/pop.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>country</th>\n",
       "      <th>gdp</th>\n",
       "      <th>series_code_x</th>\n",
       "      <th>pop</th>\n",
       "      <th>series_code_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1990-01-01</td>\n",
       "      <td>Australia</td>\n",
       "      <td>158051.132</td>\n",
       "      <td>NYGDPMKTPSAKD</td>\n",
       "      <td>17065100</td>\n",
       "      <td>SP.POP.TOTL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1990-01-01</td>\n",
       "      <td>Sweden</td>\n",
       "      <td>79837.846</td>\n",
       "      <td>NYGDPMKTPSAKD</td>\n",
       "      <td>8558835</td>\n",
       "      <td>SP.POP.TOTL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1990-04-01</td>\n",
       "      <td>Australia</td>\n",
       "      <td>158263.582</td>\n",
       "      <td>NYGDPMKTPSAKD</td>\n",
       "      <td>8558835</td>\n",
       "      <td>SP.POP.TOTL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1990-04-01</td>\n",
       "      <td>Sweden</td>\n",
       "      <td>80582.286</td>\n",
       "      <td>NYGDPMKTPSAKD</td>\n",
       "      <td>8558835</td>\n",
       "      <td>SP.POP.TOTL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1990-07-01</td>\n",
       "      <td>Australia</td>\n",
       "      <td>157329.279</td>\n",
       "      <td>NYGDPMKTPSAKD</td>\n",
       "      <td>8558835</td>\n",
       "      <td>SP.POP.TOTL</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date    country         gdp  series_code_x       pop series_code_y\n",
       "0  1990-01-01  Australia  158051.132  NYGDPMKTPSAKD  17065100   SP.POP.TOTL\n",
       "1  1990-01-01     Sweden   79837.846  NYGDPMKTPSAKD   8558835   SP.POP.TOTL\n",
       "2  1990-04-01  Australia  158263.582  NYGDPMKTPSAKD   8558835   SP.POP.TOTL\n",
       "3  1990-04-01     Sweden   80582.286  NYGDPMKTPSAKD   8558835   SP.POP.TOTL\n",
       "4  1990-07-01  Australia  157329.279  NYGDPMKTPSAKD   8558835   SP.POP.TOTL"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge gdp and pop on date and country with fill and notice rows 2 and 3\n",
    "ctry_date = pd.merge_ordered(gdp, pop, on = ['date','country'] ,\n",
    "                             fill_method='ffill')\n",
    "\n",
    "# Print ctry_date\n",
    "ctry_date.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Phillips curve using merge_ordered()\n",
    "There is an economic theory developed by A. W. Phillips which states that inflation and unemployment have an inverse relationship. The theory claims that with economic growth comes inflation, which in turn should lead to more jobs and less unemployment.\n",
    "\n",
    "You will take two tables of data from the U.S. Bureau of Labor Statistics, containing unemployment and inflation data over different periods, and create a Phillips curve. The tables have different frequencies. One table has a data entry every six months, while the other has a data entry every month. You will need to use the entries where you have data within both tables.\n",
    "\n",
    "The tables `unemployment` and `inflation` have been loaded below:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment = pd.read_csv('./dataset/practise_dataset/unemployment.csv')\n",
    "inflation = pd.read_csv('./dataset/practise_dataset/inflation.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Use `merge_ordered()` to merge the `inflation` and `unemployment` tables on date with an inner join, and save the results as `inflation_unemploy`.\n",
    "- Print the `inflation_unemploy`variable.\n",
    "- Using `inflation_unemploy`, create a scatter plot with `unemployment_rate` on the horizontal axis and `cpi (inflation)` on the vertical axis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         date      cpi     seriesid                  data_type  \\\n",
      "0  2014-01-01  235.288  CUSR0000SA0  SEASONALLY ADJUSTED INDEX   \n",
      "1  2014-06-01  237.231  CUSR0000SA0  SEASONALLY ADJUSTED INDEX   \n",
      "2  2015-01-01  234.718  CUSR0000SA0  SEASONALLY ADJUSTED INDEX   \n",
      "3  2015-06-01  237.684  CUSR0000SA0  SEASONALLY ADJUSTED INDEX   \n",
      "4  2016-01-01  237.833  CUSR0000SA0  SEASONALLY ADJUSTED INDEX   \n",
      "5  2016-06-01  240.167  CUSR0000SA0  SEASONALLY ADJUSTED INDEX   \n",
      "6  2017-01-01  243.780  CUSR0000SA0  SEASONALLY ADJUSTED INDEX   \n",
      "7  2017-06-01  244.182  CUSR0000SA0  SEASONALLY ADJUSTED INDEX   \n",
      "8  2018-01-01  248.884  CUSR0000SA0  SEASONALLY ADJUSTED INDEX   \n",
      "9  2018-06-01  251.134  CUSR0000SA0  SEASONALLY ADJUSTED INDEX   \n",
      "\n",
      "   unemployment_rate  \n",
      "0                6.7  \n",
      "1                6.1  \n",
      "2                5.6  \n",
      "3                5.3  \n",
      "4                5.0  \n",
      "5                4.9  \n",
      "6                4.7  \n",
      "7                4.3  \n",
      "8                4.1  \n",
      "9                4.0  \n"
     ]
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAY4AAAEHCAYAAAC5u6FsAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjMuNCwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy8QVMy6AAAACXBIWXMAAAsTAAALEwEAmpwYAAAcbElEQVR4nO3df3Rc5X3n8fdn8ER2I5II2xDXMhUBkwBFEemE0PWGBFpol7YmRGFDTkpomiyhB3IghWOT0FLanLREaXB295ziJYaGbp2kBJGYUPKDQ5xsfjQkspFljJLlRw2W4xihmmC3siIz3/1jrpbxeCTr2nM1mtHndc6cufe5z33meXRtfXR/zL2KCMzMzKYrV+8OmJlZY3FwmJlZKg4OMzNLxcFhZmapODjMzCyVefXuwExYtGhRdHR01LsbZmYNZdOmTc9HxOLK8jkRHB0dHfT19dW7G2ZmDUXSM9XKfajKzMxScXCYmVkqDg4zM0vFwWFmZqk4OMzMLBUHxxRG9o2xZccLjOwbq3dXzMxmjTlxOe6R2NC/k9W9A+RzOcaLRXq6O1nZtbTe3TIzqzvvcVQxsm+M1b0D7B8vsnfsAPvHi6zqHfCeh5kZDo6qhvaMks8d/KPJ53IM7RmtU4/MzGYPB0cV7W0LGC8WDyobLxZpb1tQpx6Zmc0eDo4qFra20NPdyfx8jmNb5jE/n6Onu5OFrS317pqZWd355PgkVnYtZcUpixjaM0p72wKHhplZwsExhYWtLQ4MM7MKPlRlZmapODjMzCwVB4eZmaXi4DAzs1QcHGZmloqDw8zMUsksOCQtk7RR0qCkbZKuTcpvkbRTUn/yuqhsnY9KelLSTyX9ziTtHifpIUlPJO9tWY3BzMwOleUexwHg+og4DTgHuFrS6cmyNRHRlbweBEiWXQacAfwu8HeSjqnS7o3AwxGxHHg4mTczsxmSWXBExK6I2JxM7wUGganuS34x8MWIGIuIfwWeBM6epN7dyfTdwDtq1mkzMzusGTnHIakDOAt4JCm6RtKApLvKDjUtBXaUrTZE9aA5ISJ2QSmcgOMn+cwrJfVJ6hseHq7FMMzMjBkIDkmtQC9wXUS8CNwOnAx0AbuAT09UrbJ6HOnnRsQdEVGIiMLixYuPtBkzM6uQaXBIylMKjfURcR9AROyOiJciogh8lpcPRw0By8pWbwd+VqXZ3ZKWJO0vAZ7Lqv9mZnaoLK+qEnAnMBgRt5WVLymrdgnwWDJ9P3CZpBZJJwHLgR9Vafp+4Ipk+gpgQ637bmZmk8vy7rgrgMuBrZL6k7KPAe+R1EXpMNR24EMAEbFN0j3A45SuyLo6Il4CkLQOWBsRfcCtwD2SPgA8C1ya4RjMzKyCIo74NELDKBQK0dfXV+9umJk1FEmbIqJQWe5vjpuZWSoODjMzS8XBYWZmqTg4zMwsFQdHjYzsG2PLjhcY2TdW766YmWUqy8tx54wN/TtZ3TtAPpdjvFikp7uTlV1T3ZbLzKxxeY/jKI3sG2N17wD7x4vsHTvA/vEiq3oHvOdhZk3LwXGUhvaMks8d/GPM53IM7RmtU4/MzLLl4DhK7W0LGC8WDyobLxZpb1tQpx6ZmWXLwXGUFra20NPdyfx8jmNb5jE/n6Onu5OFrS317pqZWSZ8crwGVnYtZcUpixjaM0p72wKHhpk1NQdHjSxsbXFgmNmc4ENVZmaWioPDzMxScXCYmVkqDg4zM0vFwWFmZqk4OMzMLBUHh5mZpeLgMDOzVBwcZmaWSmbBIWmZpI2SBiVtk3RtxfIbJIWkRcn8eyX1l72KkrqqtHuLpJ1l9S7KagxmZnaoLG85cgC4PiI2SzoW2CTpoYh4XNIy4ALg2YnKEbEeWA8g6UxgQ0T0T9L2moj42wz7bmZmk8hsjyMidkXE5mR6LzAITDwWbw2wCohJVn8P8IWs+mZmZkduRs5xSOoAzgIekbQS2BkRW6ZY5d1MHRzXSBqQdJektkk+80pJfZL6hoeHj7jvZmZ2sMyDQ1Ir0AtcR+nw1U3AzVPUfwvwHxHx2CRVbgdOBrqAXcCnq1WKiDsiohARhcWLFx9x/83M7GCZBoekPKXQWB8R91H6hX8SsEXSdqAd2CzptWWrXcYUexsRsTsiXoqIIvBZ4Oys+m9mZofK7OS4JAF3AoMRcRtARGwFji+rsx0oRMTzyXwOuBQ4d4p2l0TErmT2EmCyPRMzM8tAlnscK4DLgfNTXDp7LjAUEU+XF0paJ6mQzPZI2ippADgP+EjNe25mZpPKbI8jIr4H6DB1Oirmvw2cU6XeB8umL69ND83M7Ej4m+NmZpaKg8PMzFJxcJiZWSoODjMzS8XBYWZmqTg4zMwsFQeHmZml4uAwM7NUHBxmZpaKg8PMzFJxcJiZWSoODjMzS8XBYWZmqTg4zMwsFQeHmZml4uAwM7NUHBxmZpaKg8PMzFJxcJiZWSoODjMzS8XBYWZmqWQWHJKWSdooaVDSNknXViy/QVJIWpTMd0galdSfvNZO0u5xkh6S9ETy3pbVGMzM7FBZ7nEcAK6PiNOAc4CrJZ0OpVABLgCerVjnqYjoSl5XTdLujcDDEbEceDiZNzOzGZJZcETErojYnEzvBQaBpcniNcAqII6g6YuBu5Ppu4F3HF1PzcwsjRk5xyGpAzgLeETSSmBnRGypUvUkSY9K+o6kt07S3AkRsQtK4QQcP8lnXimpT1Lf8PBwDUZhZmYA87L+AEmtQC9wHaXDVzcBF1apugs4MSJGJP0G8BVJZ0TEi0fyuRFxB3AHQKFQOJI9GzMzqyLTPQ5JeUqhsT4i7gNOBk4CtkjaDrQDmyW9NiLGImIEICI2AU8Bp1ZpdrekJUn7S4DnshyDmZkdLMurqgTcCQxGxG0AEbE1Io6PiI6I6ACGgDdFxM8lLZZ0TLLu64DlwNNVmr4fuCKZvgLYkNUYzMzsUFnucawALgfOL7vE9qIp6p8LDEjaAtwLXBUR/wYgaZ2kQlLvVuACSU9QujLr1uyGYGZmlRTR/If/C4VC9PX11bsbZmYNRdKmiChUlvub42ZmloqDowGM7Btjy44XGNk3Vu+umJllfzmuHZ0N/TtZ3TtAPpdjvFikp7uTlV1LD7+imVlGvMcxi43sG2N17wD7x4vsHTvA/vEiq3oHvOdhZnXl4JjFhvaMks8dvInyuRxDe0br1CMzMwfHrNbetoDxYvGgsvFikfa2BXXqkZmZg2NWW9jaQk93J/PzOY5tmcf8fI6e7k4WtrbUu2uH5RP6Zs3LJ8dnuZVdS1lxyiKG9ozS3ragIULDJ/TNmpuDowEsbG1piMCAg0/o76d0mG1V7wArTlnUMGMws6n5UJXVlE/omzU/B4fVlE/omzU/B4fVVCOf0Dez6fE5Dqu5Rjyhb2bT5+CwTDTSCX0zS8eHqszMLJUp9zgkfSYirpP0VeCQB3dExMrMemZmZrPS4Q5V/e/k/W+z7oiZmTWGKYMjIjYl79+R9ArgDZT2PH4aEb+cgf6ZmdksM62T45J+D1gLPAUIOEnShyLia1l2zszMZp/pXlX1aeC8iHgSQNLJwD8DDg4zszlmuldVPTcRGomngecy6I+Zmc1y0w2ObZIelPRHkq4Avgr8WNI7Jb2z2gqSlknaKGlQ0jZJ11Ysv0FSSFqUzF8gaZOkrcn7+ZO0e4uknZL6k9dFKcZrZmZHabqHquYDu4G3JfPDQBvwB5ROlt9XZZ0DwPURsVnSscAmSQ9FxOOSlgEXAM+W1X8e+IOI+JmkXwe+AUx2L+41EeErvczM6mC6wZEDro2IFwAktQGfjoj3T7ZCROwCdiXTeyUNUgqCx4E1wCpgQ1n9R8tW3wbMl9QSEX4SkJnZLDLdQ1WdE6EBEBF7gLOm+yGSOpL6j0haCeyMiC1TrNINPDpFaFwjaUDSXUmIVfvMKyX1SeobHh6eblfNzOwwphscufJf0JKOY/qX8rYCvcB1lA5f3QTcPEX9M4BPAh+apMrtwMlAF6U9mk9XqxQRd0REISIKixcvnk5XzcxsGtJcjvsDSfdSOqfxX4FPHG4lSXlKobE+Iu6TdCZwErBFEkA7sFnS2RHxc0ntwJeB90XEU9XajIjdZe1/FnhgmmMwM7MamFZwRMQ/SOoDzqf0BcB3RsTjU62jUjLcCQxGxG1JO1uB48vqbAcKEfG8pNdQ+m7IRyPi+1O0uyQ5fwJwCfDYdMZgZma1Me3bqidBMWVYVFgBXA5sldSflH0sIh6cpP41wCnAn0v686Tswoh4TtI6YG1E9AE9kroo7flsZ/JDWmZmlgFFHHLT26ZTKBSir6+v3t0wM2sokjZFRKGy3M/jMDOzVBwcZmaWioPDzMxScXCYmVkqDg4zM0vFwWFmZqk4OMzMLBUHh5mZpeLgMDOzVBwcZmaWioPDzMxScXCYmVkqDg4zM0vFwWFmZqk4OMzMLBUHh5mZpeLgMDOzVBwcZmaWioPDzMxScXBYwxnZN8aWHS8wsm+s3l0xm5Pm1bsDZmls6N/J6t4B8rkc48UiPd2drOxaWu9umc0pme1xSFomaaOkQUnbJF1bsfwGSSFpUVnZRyU9Kemnkn5nknaPk/SQpCeS97asxmCzy8i+MVb3DrB/vMjesQPsHy+yqnfAex5mMyzLQ1UHgOsj4jTgHOBqSadDKVSAC4BnJyonyy4DzgB+F/g7ScdUafdG4OGIWA48nMzbHDC0Z5R87uB/svlcjqE9o3XqkdnclFlwRMSuiNicTO8FBoGJYwprgFVAlK1yMfDFiBiLiH8FngTOrtL0xcDdyfTdwDtq33ubjdrbFjBeLB5UNl4s0t62oE49MpubZuTkuKQO4CzgEUkrgZ0RsaWi2lJgR9n8EC8HTbkTImIXlMIJOH6Sz7xSUp+kvuHh4aMdgs0CC1tb6OnuZH4+x7Et85ifz9HT3cnC1pZ6d81sTsn85LikVqAXuI7S4aubgAurVa1SFlXKpiUi7gDuACgUCkfcjs0uK7uWsuKURQztGaW9bYFDw6wOMg0OSXlKobE+Iu6TdCZwErBFEkA7sFnS2ZT2MJaVrd4O/KxKs7slLYmIXZKWAM9lOQabfRa2tjgwzOooy6uqBNwJDEbEbQARsTUijo+IjojooBQWb4qInwP3A5dJapF0ErAc+FGVpu8HrkimrwA2ZDUGMzM7VJbnOFYAlwPnS+pPXhdNVjkitgH3AI8DXweujoiXACStk1RIqt4KXCDpCUpXZt2a4RjMzKyCIpr/8H+hUIi+vr56d8PMrKFI2hQRhcpy33LEzMxScXCYmVkqDg4zM0vFwWFmZqk4OMzMLBUHh5mZpeLgMDOzVBwcZmaWioPDzMxScXCYmVkqDg4zM0vFwWFmZqk4OMzMLBUHh5mZpeLgMDOzVBwcZmaWioPDzMxScXCYmVkqDg5raiP7xtiy4wVG9o3VuytmTWNevTtglpUN/TtZ3TtAPpdjvFikp7uTlV1L69KXkX1jDO0Zpb1tAQtbW+rSB7NacXBYUxrZN8bq3gH2jxfZTxGAVb0DrDhl0Yz/4p5NAWZWC5kdqpK0TNJGSYOStkm6Nin/uKQBSf2SvinpV5Py9yZlE6+ipK4q7d4iaWdZvYuyGoM1rqE9o+RzB//zzudyDO0ZndF+lAfY3rED7B8vsqp3wIfOrKFleY7jAHB9RJwGnANcLel04FMR0RkRXcADwM0AEbE+IrqS8suB7RHRP0nbaybqRsSDGY7BGlR72wLGi8WDysaLRdrbFsxoP2ZLgJnVUmbBERG7ImJzMr0XGASWRsSLZdVeCUSV1d8DfCGrvlnzW9jaQk93J/PzOY5tmcf8fI6e7s4ZP0w1WwLMrJZm5ByHpA7gLOCRZP4TwPuAXwDnVVnl3cDFUzR5jaT3AX2U9mr21LTD1hRWdi1lxSmL6npSeiLAVlWc4/AJcmtkiqj2B38NP0BqBb4DfCIi7qtY9lFgfkT8RVnZW4B1EXHmJO2dADxPaU/l48CSiPjjKvWuBK4EOPHEE3/jmWeeqdGIzNLzVVUzwz/n2pK0KSIKh5RnGRyS8pTOY3wjIm6rsvzXgH+OiF8vK1sDDEfEX0+j/Q7ggfL1qykUCtHX15e2+2bWQHz1Wu1NFhxZXlUl4E5gsDw0JC0vq7YS+EnZshxwKfDFKdpdUjZ7CfBYrfpsZo3JV6/NrCzPcaygdHXUVkn9SdnHgA9Iej1QBJ4Bripb51xgKCKeLm9I0jpgbUT0AT3JZboBbAc+lOEYzKwBTFy9NvGdHXj56jUfsqq9zIIjIr4HqMqiSS+fjYhvU7p0t7L8g2XTl9eif2bWPHz12szyvarMrOHNlsuv5wrfcsTMmsJsuPx6rnBwmFnTWNja4sCYAT5UZWZmqTg4zMwsFQeHmZml4uAwM7NUHBxmZpaKg8PMzFJxcJiZWSoODjMzS8XBYWZmqTg4zMwsFQeHmZml4uAwM7NUHBxmZpaKg8PMzFJxcJiZWSoODjOzJjWyb4wtO15gZN9YTdv1g5zMzJrQhv6drO4dIJ/LMV4s0tPdycqupTVp23scZmZNZmTfGKt7B9g/XmTv2AH2jxdZ1TtQsz2PzIJD0jJJGyUNStom6dqk/OOSBiT1S/qmpF9NyjskjSbl/ZLWTtLucZIekvRE8t6W1RjMzBrR0J5R8rmDf73nczmG9ozWpP0s9zgOANdHxGnAOcDVkk4HPhURnRHRBTwA3Fy2zlMR0ZW8rpqk3RuBhyNiOfBwMm9mZon2tgWMF4sHlY0Xi7S3LahJ+5kFR0TsiojNyfReYBBYGhEvllV7JRApm74YuDuZvht4x1F21cysqSxsbaGnu5P5+RzHtsxjfj5HT3cnC1tbatL+jJwcl9QBnAU8ksx/Angf8AvgvLKqJ0l6FHgR+LOI+G6V5k6IiF1QCidJx2fZdzOzRrSyaykrTlnE0J5R2tsW1Cw0YAZOjktqBXqB6yb2NiLipohYBqwHrkmq7gJOjIizgD8FPi/pVUfxuVdK6pPUNzw8fHSDMGsSWV2eabPTwtYW3rjsNTUNDcg4OCTlKYXG+oi4r0qVzwPdABExFhEjyfQm4Cng1Crr7Ja0JGl/CfBctc+OiDsiohARhcWLFx/9YMwa3Ib+naz45Lf4w3WPsOKT3+L+/p317pI1qCyvqhJwJzAYEbeVlS8vq7YS+ElSvljSMcn064DlwNNVmr4fuCKZvgLYUPvemzWXrC/PtLkly3McK4DLga2S+pOyjwEfkPR6oAg8A0xcPXUu8FeSDgAvAVdFxL8BSFoHrI2IPuBW4B5JHwCeBS7NcAxmTWHi8sz9vHylzcTlmbU+jGHNL7PgiIjvAaqy6MFJ6vdSOqxVbdkHy6ZHgN+qRR/N5oqsL8+0ucXfHDebA7K+PNPmFt+rymyOyPLyTJtbHBxmc8jC1hYHhh01H6oyM7NUHBxmZpaKg8PMzFJxcJiZWSoODjMzS0URae9q3ngkDVP6lvqRWAQ8X8PuzEbNPkaPr/E1+xhn6/h+LSIOudnfnAiOoyGpLyIK9e5Hlpp9jB5f42v2MTba+HyoyszMUnFwmJlZKg6Ow7uj3h2YAc0+Ro+v8TX7GBtqfD7HYWZmqXiPw8zMUnFwmJlZKg6OMpKOkfSopAeqLJOk/yHpSUkDkt5Ujz4ejcOM7+2SfiGpP3ndXI8+Hg1J2yVtTfrfV2V5Q2/DaYyvobehpNdIulfSTyQNSvrNiuUNvf1gWmNsiG3o26of7FpgEHhVlWX/hdJz0JcDbwFuT94byVTjA/huRPz+DPYnC+dFxGRfpGqGbTjV+KCxt+F/B74eEe+S9ArgVyqWN8P2O9wYoQG2ofc4EpLagd8D1k1S5WLgH6Lkh8BrJC2ZsQ4epWmMby5o6G3YzCS9CjgXuBMgIn4ZES9UVGvo7TfNMTYEB8fLPgOsAoqTLF8K7CibH0rKGsVnmHp8AL8paYukr0k6Y2a6VVMBfFPSJklXVlne6NvwcOODxt2GrwOGgb9PDqeuk/TKijqNvv2mM0ZogG3o4AAk/T7wXERsmqpalbKGuJZ5muPbTOm+NG8E/ifwlZnoW42tiIg3UTqkcbWkcyuWN+w2TBxufI28DecBbwJuj4izgH8Hbqyo0+jbbzpjbIht6OAoWQGslLQd+CJwvqR/rKgzBCwrm28HfjYz3Ttqhx1fRLwYEfuS6QeBvKRFM97ToxARP0venwO+DJxdUaWRt+Fhx9fg23AIGIqIR5L5eyn9kq2s07Dbj2mMsVG2oYMDiIiPRkR7RHQAlwHfiog/rKh2P/C+5MqOc4BfRMSume7rkZjO+CS9VpKS6bMp/dsYmfHOHiFJr5R07MQ0cCHwWEW1ht2G0xlfI2/DiPg5sEPS65Oi3wIer6jWsNsPpjfGRtmGvqpqCpKuAoiItcCDwEXAk8B/AO+vY9dqomJ87wL+RNIBYBS4LBrrtgInAF9O/s/NAz4fEV9vom04nfE1+jb8MLA+udroaeD9TbT9JhxujA2xDX3LETMzS8WHqszMLBUHh5mZpeLgMDOzVBwcZmaWioPDzMxScXCYmVkqDg6zo5TcCvuQW9XXU9Kn/1SjtrokXVSLtqw5ODjMmtPbgWkHh6SpvgzcRemLd2aAg8MamKQOSY+Vzd8g6RZJ35b0SUk/kvR/Jb01WX6MpE9J+nHyIKAPJeVvl/QdSfck9W+V9N5k/a2STk7qfU7SWknfTeod8swEScdJ+krS/g8ldUrKSXpC0uKkTk6lhxEtStq8XdJGSU9Lepuku1R6yM/nytq9UNK/SNos6UuSWpPy7ZL+MinfKukNkjqAq4CPqPQwoLdO8vP7nKTbJG0EPinpbEk/UOnOrT+Q9PrkG85/Bbw7aevdye1P7kp+jo9KurgmG9QahoPDmtW8iDgbuA74i6TsA5Tub/Rm4M3Af5N0UrLsjZQedHUmcDlwarL+Okq3iZjQAbyN0rNN1kqaX/G5fwk8GhGdwMcoPT+iCPwj8N6kzm8DW8oeyNQGnA98BPgqsAY4AzgzOUy0CPgz4LeTu+P2AX9a9pnPJ+W3AzdExHZgLbAmIroi4rtT/JxOTdq9HvgJcG5y59abgb+OiF8m0/+UtPVPwE2U7nf2ZuA84FOqfntwa1K+V5U1q/uS902UftlD6caAnZLelcy/mtLT5H4J/HjihnmSngK+mdTZSumX44R7kiB4QtLTwBsqPvc/A90AEfEtSQslvRq4C9hA6bkofwz8fdk6X42IkLQV2B0RW5N+bEv63g6cDnw/uVfVK4B/mWSs7zzcD6bClyLipWT61cDdkpZTul15fpJ1LqR0t+Ubkvn5wImUni5pc4CDwxrZAQ7eay7/638seX+Jl/+dC/hwRHyjvBFJby+rD6WHXY2VTZf/P6m8uVvlfNVnRkTEDkm7JZ1P6XGn7y1bXv5Zlf2Yl4zhoYh4T5W2y9cvH+t0/XvZ9MeBjRFxSXK469uTrCOgOyJ+mvKzrEn4UJU1st3A8clf9S3A4Z7T/A1Kdx7NA0g69QgOsVyanKM4mdIT3Sp/ef4fklBIAun5iHgxWbaO0iGre8r+yp+OHwIrJJ2StPsrkk49zDp7gWNTfAaU9jh2JtN/NEVb3wA+LP3/23+flfJzrME5OKxhRcQ4pRO3jwAPUDpGP5V1lJ5/sDk5qf6/SP8X+k+B7wBfA66KiP0Vy28BCpIGgFuBK8qW3Q+0cvBhqsOKiGFKv8i/kLT7Qw49RFbpq8AlU50cr6IH+BtJ3weOKSvfCJw+cXKc0p5JHhhIfo4fn/5orBn4tupm05Rc5fRARNx7hOsXKJ2wnu4vcrNZyec4zGaApBuBP+HgcxtmDcl7HGZNTtJNwKUVxV+KiE/Uoz/W+BwcZmaWik+Om5lZKg4OMzNLxcFhZmapODjMzCyV/wcHkQNSFu6ABAAAAABJRU5ErkJggg==\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Use merge_ordered() to merge inflation, unemployment with inner join\n",
    "inflation_unemploy = pd.merge_ordered(inflation, unemployment, on='date', how='inner')\n",
    "\n",
    "# Print inflation_unemploy \n",
    "print(inflation_unemploy)\n",
    "\n",
    "# Plot a scatter plot of unemployment_rate vs cpi of inflation_unemploy\n",
    "inflation_unemploy.plot(kind = 'scatter', x= 'unemployment_rate', y = 'cpi')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Using merge_asof() to study stocks\n",
    "You have a feed of stock market prices that you record. You attempt to track the price every five minutes. Still, due to some network latency, the prices you record are roughly every 5 minutes. You pull your price logs for three banks, JP Morgan (JPM), Wells Fargo (WFC), and Bank Of America (BAC). You want to know how the price change of the two other banks compare to JP Morgan. Therefore, you will need to merge these three logs into one table. Afterward, you will use the `pandas .diff()` method to compute the price change over time. Finally, plot the price changes so you can review your analysis.\n",
    "\n",
    "The three log files have been loaded for you as tables named `jpm`, `wells`, and `bac`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Use `merge_asof()` to merge `jpm (left table)` and `wells` together on the `date_time` column, where the rows with the `nearest` times are matched, and with `suffixes=('', '_wells')`. Save to `jpm_wells`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Use `merge_asof()` to merge `jpm_wells (left table)` and `bac` together on the `date_time` column, where the rows with the `closest` times are matched, and with `suffixes=('_jpm', '_bac')`. Save to `jpm_wells_bac`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Using `price_diffs`, create a line plot of the `close` price of `JPM, WFC, and BAC` only."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [],
   "source": [
    "jpm = pd.read_csv('./dataset/practise_dataset/jmp.csv')\n",
    "wells = pd.read_csv('./dataset/practise_dataset/wells.csv')\n",
    "bac = pd.read_csv('./dataset/practise_dataset/bac.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [],
   "source": [
    "jpm['date_time'] = pd.DatetimeIndex(jpm['date_time'])\n",
    "wells['date_time'] = pd.DatetimeIndex(wells['date_time'])\n",
    "bac['date_time'] = pd.DatetimeIndex(bac['date_time'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAXwAAAD4CAYAAADvsV2wAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjMuNCwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy8QVMy6AAAACXBIWXMAAAsTAAALEwEAmpwYAABZAklEQVR4nO3dd3hU1dbA4d9O7400SCENQg0t9N4RFBRpIogFe8GOXq/989q4dq+ISBMUaQIiIkWKCEjvNQ0SWnpCejKzvz9OEgIkpMwkEzL7fR6eJDMn56yEZGXPPmuvLaSUKIqiKA2fhakDUBRFUeqGSviKoihmQiV8RVEUM6ESvqIoiplQCV9RFMVMWJk6gJvx9PSUQUFBpg5DURTllrFv375kKaVXec/V64QfFBTE3r17TR2GoijKLUMIcbai59SUjqIoiplQCV9RFMVMqISvKIpiJlTCVxRFMRMq4SuKopgJlfAVRVHMhEr4iqIoZsIoCV8IMUwIcUoIESWEeKWc5+8VQhwu/rdDCNHOGNdVas/O6BQOJ6SbOgxFUYzI4IQvhLAEvgZuA1oB9wghWl13WCzQV0oZAbwLzDL0ukrtSc0uYOr8PfzfbydMHYqiKEZkjBF+FyBKShkjpSwAFgOjyh4gpdwhpUwr/nAX4G+E6yq15Nut0WQX6IhOzDJ1KIqiGJExEr4fEF/m44TixyryEPB7RU8KIR4RQuwVQuxNSkoyQnhKdSRm5jF/ZxwONpakZBeQll1g6pAURTESYyR8Uc5j5e6bKIToj5bwp1d0MinlLCllpJQy0sur3P4/Si36enMUhTrJy0PDAYhOUqN8RWkojJHwE4CAMh/7AxeuP0gIEQHMBkZJKVOMcF3FyBLScvhx9znGRfozoIUPAFFqWkdRGgxjJPw9QDMhRLAQwgaYAKwue4AQIhBYAUyWUp42wjWVWvDVn1EIBE8NaIafuz22VhZqhK8oDYjB7ZGllEVCiKeAPwBLYI6U8pgQ4rHi52cCbwCNgP8JIQCKpJSRhl5bMZ645GyW7ktgcrem+LnZAxDs6Uh0UraJI1MUxViM0g9fSrkWWHvdYzPLvD8VmGqMaym14/NNZ7C2FDzRP7T0sTBvJw4nZJgwKkVRjEmttFU4ffkKKw+eZ0qPILyd7UofD/VyIj4th7xCnQmjUxTFWFTCV/h0w2kcbax4rE/oNY+HejshJcQmq2kdRWkIVMI3c0fPZ/D70Us82CsYd0eba54L83ICVGmmojQUKuGbuU82nMbV3pqpvYNveC7EyxEhVGmmojQUKuGbsX1n0/jzZCKP9g3Bxc76huftrC3xd7dXlTqK0kCohG/G/rv+FJ5ONtzfI6jCY0K9nNQIX1EaCJXwzdSOqGR2RKfweL8wHGwqrs4N83IiJikLvb7cbhmKotxCVMI3Q1JK/rvhNL4udtzbNfCmx4Z6O5FfpOd8em4dRacoSm1RCd8MbTmdxL6zaTw1IAw7a8ubHhvmrVXqRKlKHUW55amEb2aklPx3/SkCPOwZFxlQ6fGhJaWZah5fUW55KuGbmT+OXebo+UymDWyOjVXl//0ejja4O1irWnxFaQBUwjcjOr3kkw2nCPFy5M72Tar8eWHeTkQnqtJMRbnVqYRvRtYcvsDpy1k8N6g5VpZV/68P9XJSc/iK0gCohG8minR6Pt1wmha+zoxo27hanxvm7URqdgGpartDRbmlqYRvJlbsP09cSg4vDAnHwqK8XSkrVnLjNkaN8huUxCt5PDhvD5tOXDZ1KEodUQnfDOQX6fh80xna+bsyqKV3tT+/tDRTVeo0GKnZBUya/Q9/nkxk2uKDnE1R92jMgUr4ZuDnPfGcT8/lhSHhFO84Vi1N3NR2hw1JRm4hk7//h7MpOcwY2w4LAU/+uJ/8IrXvQUOnEn4Dl1ug48s/o+gS5EHvZp41OoelhSDY01GN8BuArPwi7p+7m9OXr/Dt5E6M6eTPx2PbcfR8Ju+vPWnq8JRaphJ+A7dw11mSruTzwpDmNRrdlwjzdlJdM29xeYU6ps7fw+GEDL68pyP9wrXpvaGtfXmgZxDzdsSx7uglE0ep1CaV8BuwrPwivtkaTe9mnnQNaWTQudR2h7e2/CIdj/6wj39iU/lkXDuGtfG95vlXb2tJhL8rLy87RHxqjomiVGqbSvgN2Ly/Y0nNLuCFIeEGnytMbXd4yyrU6Xn6xwNsPZ3Eh6MjGNXe74ZjbKws+OqejkgJT/10gIIivQkiVWqbURK+EGKYEOKUECJKCPFKOc+3EELsFELkCyFeNMY1lZvLyCnk220xDGrpQ/sAN4PPV1Kaqebxby06veSFJYdYf/wyb49szbjOFfdPCmzkwEdjIjgUn85H69R8fkNkcMIXQlgCXwO3Aa2Ae4QQra47LBV4Bphh6PWUqvnurxiu5BXx/ODmRjlfyXaHqlLn1qHXS15dcZjVhy7wym0tmHKTjW5K3Na2Mfd1b8rs7bFsPK7q8xsaY4zwuwBRUsoYKWUBsBgYVfYAKWWilHIPUGiE6ymVSMnKZ87fsdwe0ZhWTVyMck613eGtRUrJ278eY8neBJ4Z2IzH+oZW+XP/NbwlrZu48MLSQ2ofhAbGGAnfD4gv83FC8WM1IoR4RAixVwixNykpyeDgzNHMrdHkFep4dpBxRvclwtR2h7cEKSUfrDvJ/J1neaRPCM8Nalatz7eztuTriR3R6SVP/7ifQp2az28ojJHwy6v1q/F+eFLKWVLKSCllpJeXlwFhmafLmXks2HmWuzr4l66QNZZQtd3hLeGLTVF8uzWGyd2a8uptLWpUjhvk6cj7o9uy/1w6M9afqoUoFVMwRsJPAMreCfIHLhjhvEoNfL05Cp1eMm1g9UZ1VaG2O6z/Zm2L5tONpxnTyZ+3R7Y2aO3FHe2aMLFrIN9ujWHzyUQjRqmYijES/h6gmRAiWAhhA0wAVhvhvEo1xafm8NPuc4zrHEBgIwejn19td1i//bAzjv+sPcntEY358O6IajfJK88bt7eiha8zzy85yMUM9Yf+VmdwwpdSFgFPAX8AJ4AlUspjQojHhBCPAQghfIUQCcDzwL+FEAlCCOPcTVRKffnnGYQQPD0gzDgnlNdO3ajtDuuvJXvjeX3VMQa19OHT8e2xNEKyh+L5/Hs7kl+k55mfDlCk5vNvaUapw5dSrpVSNpdShkop3yt+bKaUcmbx+5eklP5SShcppVvx+5nGuLaiiUnKYvn+80zq2pTGrvaGnSw7BWYPhtVPX/Owh6MNHo42qjTTRLIKsth4diM6/bWrnVcfusAryw/Tu5knX03sgHU1NrepilAvJ/5zV1v2xKXx6cbTRj23UrfUStsG4vNNZ7CxtODxflUvvytXdgosGAUJu+HAQkiOuubpUC9Htd2hCSTmJDJl3RSe2/Ic0/+aTqFOq3Bef+wSz/18kMggD2ZNjsTO2rJWrn9nBz/GRwbwvy3RbDutquduVSrhNwBL9sSz+tAF7u8ZhJezbc1PlJ0CC0ZC8mm461uwtIGdX15zSJi32u6wrsVkxDB57WTir8QztvlY/oj7g+e2PMfGk+d56scDtPVzZc79nbG3qZ1kX+Ktka1p5u3Ecz8f5HJmXq1eS6kdKuHf4n785xwvLz9M72ZehlXmlCT7lCi45ydOB3Ykvu1dcPAnyLpaoRHqpbY7rEsHEw9y3+/3kafLY+6wubzR/Q1e7/Y6WxO2Mm3zU4R4WzH/gS442VrVeiz2Nlp9fk6BjmmLD6Cr5fLc7Pwi5v0dyxurjjbopn15hTpOX77CuqOX+GZLNC8vO8RzPx+slWvV/k+JUmt+2HWW11cepX+4F99M6lTzl/Nlk/2EH9lkDS+uGY+/vQ+rdAVY/PMtDHwdKHPjNikLD0cPY30pSjk2n9vMS9tewsfBh5mDZxLgrFU/h9kPRn/5NBbeS3APmIeFVTfAuk5iaubjzDujWvPSssN8vumM0Vp3lHUxI5d5O+L46Z9zZOYVAZCZW8in49sbVGZqSjq95EJ6LrHJ2cQkZWlvk7OJTc7mfHruNfURXs62tPB1Rkpp9K9XJfxb1Ly/Y3nr1+MMaunN1/d2xNbKgGQ//w5IjYZ7fuJXsnl96+s0smtEXPZ5tjXvQ78930Gv58DWqbQ0Mzoxi85BKuHXliWnlvDeP+/RyqMVXw/6Gg877Xt99HwGU+bsxtOxJ0937ch7e1/joT8e4tvB3+Ju514nsY2NDGBnTApf/nmGrsEe9Ayr2cY61zt6PoPZf8Ww5vBF9FJyW5vGPNgrmJ3RycxYf5owbyeeGmD89SXGIqUkNbugOKmXJHQtucel5FzTgdTJ1opgT0c6BrozppM/wZ6OhHg6EeTpgLNd7f3xbnAJX6eXPPfzQfqFezG6o7+pw6kVs/+K4f9+O8HQ1j58eU9HbKxqODOXnQzzR5Ym+6W6FN7d+S6dfTvzaf9PGbN6DPOsbeiXlwH7F0D3J0q3O1QtFmqHlJKvD37Nt4e/pbdfb17q8B4nz+uISTpLbHI2K/Yn4GJnzaKpXfF3d8Db2YnntzzPg388yKzBs/ByqJvV6e+OasOh+HSmLT7I2mm98Ha2q9F59HrJnycTmb09hl0xqTjaWHJf9yAe6BlEgIcDxG2no24Dse3vYMb604R4OTG8bWMjfzU1p9dLPtt0hq2nk4hNyip9RQJgbSlo2siRYE9H+od7E+ypvR/s5YiXk61JXq0IKevvMvnIyEi5d+/e6n/e/21kcCtv3h8dUQtRmdbMrdF88PtJhrf15fMJBpTgXZPsF7MgL56P935Mb7/efNLvE+ys7Pjh+A98tOcjftR70zb9MjxzACytue3zv/B1sWXuA12M+8WZqYycQmKSs4hKyuSn6E85k7sJx/zuZMSPIrdMu0E7awtaNXbhk3HtCfJ0LH1898XdPPXnU3jZe/HdkO9o4tSkTuI+eSmTUV/9TWSQOwse7Fqt2v/cAh3L9ycwZ3ssMcnZNHa144GeQYzvHIirffEI9/BSWPk46Asp7Pc644935/jFTJY82p0If7fa+aKqQUrJv1ceZdE/5+gS5EG4r3NpQg/xdMTPzR4rI5fIVoUQYp+UMrLc5xpiwr/rf3/jYGPJoqndaiEq0/l6cxQf/3GKO9o14dNx7Wr+w1Qm2csJP/Ft9mm+Pvg1g5sO5sPeH2Jtqf3CZRdmM3jpYLq7BPPfvWtg9HcQMY6nftzP4YQMtr3c34hfXcOWV6jjbEoOsclZ2kv9JG3+NjY5m5TsAhAF2Pv9iJXzSeyzh9LKfiwhns6lySPY0xFfF7sKV88eTDzIExufwNHGkdlDZtPUpWmdfF2Ld5/jlRVHeH5wc56pQtFA4pU8fth5loW7zpKWU0hbP1em9g5meNvG1w5ednwJ6/8NTXuBnSucWU/qpA3csSSNIr2eVU/2wte1Zq8qjEFKyXu/nWD29lge6xvK9GHh9eb+ws0SfoOb0gEI9HBg/7k0U4dhVJ9vPMOnG09zZ/smzBhraLK/A1JjkBN+4tOMg8w9OpeRoSN5u8fbWFlc/ZFwtHZkbPhY5h2bR7x3cwL+/hzajiXM24nfjlwkr1BXa3XfDcE/MSl8tTmq3Btz3s62BHs6MqS1D77uRaxPeZ+E7NO80uXf3NNyfLWv1d67Pd8P/Z5HNzzK/evuZ9bgWTRzr/357vGdtfn8zzaepkuwB90q2Erz5KVMvv8rllUHL1Co1zOopQ9TewXTJdjj2kSp12uJftfX0PourTw4/wp83RWPDdP4fvIv3P3tHqYu2MOSR7vjYGOaFPbphtPM3h7L/T2C6lWyr0yDLMsM9HDgQnpeg2jrKqXkk/Wn+HTjae7u6M9/x7WvebLPSipO9rHo7/mJ/yTvZO7RuYwPH8+7Pd+9JtmXmNhiIhbCgoWBreHyUYj+k1Avtd1hZaSUvLbyKCcuXqFTU3emDWzG5xPas+bpXhx9eyi7XxvEz49254nB7mxIf4PLebF80v+TGiX7Ei0btWTusLkIBA/+8SDHUo4Z8SsqnxCC9+5qS1AjR5756QApWfmlz0kp2Xo6icnf/8Owz/7i18MXGN85gE3P9+W7+yLpGtLo2kRZlA/LH9KSfdfH4e45YGULjp5w+ydw8RAtor7ni3s6cOxCJi8sOWSSzq3/2xLFF39GMT4ygDdub3XLJHtooAk/wMMBnV5yMf3WXhwipeTjP06V/nB9PCai5j1SspK00svUWIruWcQbF/9k8anF3N/6fl7r+hoWovwfBR9HH4YHD+eX9GNkuDSGvz+/2kRN3bit0OGEDKISs3hhSHM+n9CBZwc1Z1R7P9r4uZbWzB9POc7ktZNJy0vjuyHfMTBwoMHXDXULZf6w+ThYOTD1j6kcSDxg8Dkr42RrxVcTO5KeW8hzSw6RV6jj5z3nGPrZNqbM2c3JS1d4aWg4O18ZyLt3tiHEq5y23XkZsPBuOLYCBr8Lw94HizI/k61GQZsxsPVDBrpd5rXhLfn96CU+2VC3rR7m/h3LR+tOMap9E/4zuq1RGtTVpYaZ8N21TpHxaTkmjqTmpJS8//tJ/rclmoldA3nfkB+uMiP7wgmLmB6/llXRq3ii/RM83+n5SkcoU1pPIVeXx5Jm3SF2KyGFZ0y+3WFugY4P150kPad+LgBbti8BWysLRkSUX1Gy4/wOHlj3ADaWNvxw2w908O5gtGsHuAQw/7b5eNp78uiGR9l5YafRzl2RVk1ceOP2Vmw7nUTHdzcwffkRLITgv2PbsX16f57sH4a7o035n5x5AebcBud2aveJej4D5f1MDv8Y7D1g5RM81N2PCZ0D+GpzFL8cSKjdL67Y4t3nePvX4wxt7cOMse2M1qCuLjXIhF/SGvhc6q2Z8KWUvLvmBLO2xXBf96a8d2cbw5N9Whz5Exby3NlfWH92PS9Gvsjj7R6v0svR5u7N6dmkJ4tyYiiwdcH2n6/wd7c36Qh/08nLfLMlmnk74ox30vws0Bm+C2d+kY7Vhy4wtLUvLuXUVP8a/StPbnoSf2d/Fg5fSIhbiMHXvJ6voy9zh83Fz8mPpzY9xZb4LUa/xvXu7RrIlO5N6RHaiEVTu/L7tN7c3cn/5mtEkk7B90Mg/SzcuxQixl3ztF7qySsqfqXu4AF3fAaXjyD++oR3RrWhW4gH05cdYd/Z1Nr7woCVB87z6i9H6Nvciy/uMX6Durpya0ZdCV8XO6wtxS2Z8KWUvLX6GHP+juWBnkGGbWJRJtnnjF/Ak9GL2Zawjde7vc6U1lOqdaopraeQkpfKb636w/GVdHO7YtL9bf+OSgG0PkJGWeKfGgtftIePQmDp/XBosbYorQb+PJFIRm4hd3e6dh2IlJLvj3zPv7b/i44+HZk3bB7eDt6Gx14BT3tP5g6dSzP3Zjy3+TnWxa6rtWuBNp//9qg2zJ7SmZ5hnpX/3J7bpSX7onx4YC2EDih96mzmWb7Y/wXDlg+j+0/d+WzfZ+QU5kCLERAxAf6agU3iYb65txNN3Ox4ZME+4mvp933d0Yu8sPQQ3YIb8e3kTjVf5FgPNMiEb2kh8Hd3uOUSvl6v1fXO33mWh3sHG3ZDKCsJ5t8OaXFkjp/Ho2d+YM/lPbzX6z3GhY+r/POv061xN1p4tGCeLgW9sGRc0WpikrJqvZ9KRXZGJ+NiZ8WFjDy2nTGwe2NeBvw0QRvdtxoJZ3fAL4/CjDD4fij89QlcPn7D/gAVWbYvAR8XW3qVWYGq0+v4YPcHfLb/M4YFDeObQd/gbONsWNxV4Gbnxuwhs4nwimD6X9P55cwvtX7NKjmxRuvK6ugJUzdA43ZkFmSy9PRSJq+dzO2/3M73R78nxC2EwU0H8/3R7xm5ciTr4tYhh70PDp6w8gncbSWzp3SmQKdn6vy9XMkz/BVaWZtPJvL0Twdo5+/K7Cm11420rjTIhA/g725Pwi2U8PV6yb9+OcKif87xWN9Q/jW8pQHJPrE42Z8lbdwcpp6cw9GUo8zoO4M7Qu+o0SmFEExpPYWYK+fY3nIwHZLX4FCUzgUTbHd4Pj2XuJQcnugfRiNHGxbvPlfzk+mKYNmDWh+h8T/AqK/h+ZPw8Gbo8zIU5cGmt+Gb7vB5BKx9CaI2aqPSciRdyWfL6STu6uBfOsebr8vnpW0v8ePJH5ncajIf9vkQG8sK5rNvRq/TShSrycnGiW8GfUNX3668seMNfjzxY/WvbUx7ZsOSyeDTBt0Dv7M9J4GXt77MgCUDeGfnO1wpuMJznZ5jw5gNzBw0k4/6fMSC2xbgbufOS1tf4uHtLxM96F+QeAy2fkSYtxPf3NuJqKQspi0+aLRByI6oZB5buI9wX2fmPtAFxwoa1OUV5XGl4Ar1eU1TiQZZhw9aaebaIxdNHUaV6PSS6csPs2xfAk/1D+OFIc0NTPZ3QPo5ksbM5uHjs0jISuDz/p/Tx7+PQXEODRrKZ/s+Y56dpI8+j8mWG4lKGqgtga9DO6O1qZa+zb1Iyy7g++2xJF7Jq9ny/vWvaQn8js8huPj7Y2EBfh21f/1f1W4qnlkPp/+A/T/A7llg7Qih/aH5MGg2BJx9AFh18Dw6vWRMJz8AMvIzmLZ5Gvsu7+PFyBcrn0qTEnJStD9AKVGQfKb4/WhIjQF9IUSMh77TwSO4yl+mg7UDXw38ihe3vsj7u98ntyiXh9o+VP3vlyGkhD//D/6aQXSz/qxq1p3f1t5DYm4irrau3BV2F6PCRtG60Y3TmB28O7B4xGKWnl7KFwe+YMylfdwb3p3H/v4MpxbD6dWsE2+NbM3rK4/y/toT/Pv2VgaFuu9sKlMX7KVpIwcWPNj16urfMuKvxLPoxCJWnFlBblEuVsIKF1sX3GzdcLN1w9XWVXvfzu3Gx4rfd7V1xdqibhrfQQNP+Gk5hWTmFZZ746y+0OklLy49xC8HzvPsoGZMG9jMKMn+wuhvmHr8fyTnJvO/gf+jS2PD2yBYW1gzudVkZuydwcGQPkyJ/oPVF5+H8Nqbhy7PjuhkPBxtCPdxZnznAL7dFsOyfQk80a+aWzvu+R7+mQndnoRO91d8nEsT7flO90NhLsT+BafXaX8ATq7RjmnSEdl8GAf2etPOvxlh3s5czLrIE5ueIC4zjg96f8CIkBFXz1mQrSXxkmSecuZqks/LuHqchbWW2Bs1g2aDQVcA++bBkaXQcQr0eQlcqtZbxsbShv/2+y+vbX+Nz/Z/Rk5RDk+1f6pu6sh1haSvepLfY39jdVhrjhZFY3kyjt5+vXkl7BX6+vet9FWPpYUlE1pMYEjQEL7Y/wULzqxgrX9jnl/zCCMe2M7kbk2JTsxi9vZYwrydmNAlsEahHknI4P45e/BxsWPh1K54XFdddDDxIAuOL2DTuU1YYMFtwbcR7hFOen466fnpZORnkJ6fTkJWAseSj5Gen06BvuJqMidrp2v/ENg442PnwfNdX61R/DfTIFsrAPx+5CKPL9rPb8/0onUTVyNHZhxFOj3PLTnEr4cu8MLg5jxtSD/7rESYdztkxHP2rq+Zevwbsguy+WbwN7Tzame0mLMKshi8bDC93Vvy0T/LWdXkeUY98qbRzl8ZKSU9PviTjk3d+WxCW6wtrBn/7U4uZeax+YV+Va9mitkCP4yGsIFwz2KwqMHcrJTaYrTi5C8T9iKQpNh5szSkFXPyE7CwsOSz5vfRrUiUGbFHw5UL157LxR88w6BR2X+h4BoIlteNyzIvwl8ztMRvYQVdHoaez4Fj+atcr6fT63hn1zusOLOCSS0n8Xyn50vbaRhbob6Qv89uYvW2t9ksr1AkBOHu4YwMHcnwkOF42te80+aRpCO8t3U6x7Lj6WjTiH8N/ZZQ12Y8OH8vO6KSWfBQF3qEVu/8Jy9lMmHWLhxtrFj6WHeauGnbhRbpi9h0bhMLji/gcNJhnG2cGdd8HPe0uAcfR5+bnlNKSW5RbukfgrT8tNL3y/6BSM9PJyMvg/S0aOwKc/nl3p1gW86ahUqYXS8d0Fqt3v7ldmZO6sSwNr5GjsxwhTo9zy4+yG9HLvLysPDqj05LSAlRm2DddMi8wJk7P+Pho9+gl3q+HfwtLRu1NG7gwH/3/pcfjv/AVxesCSvMxPe14zVLmDUQm5xN/xlbeHhIEcvPv01vv960sBvDh6sz+XFqV3pUpVVv8hmYPRBc/ODBP8DOxSixfbx8K4eivuOS73EuU8jg7ByeS00joKh48w47N/Bspo3WG4VeTeweIWBTg2mxtDjY8iEcXqxNMXV/Aro/qfWeqYRe6vloz0csOrEIAAcrh3KnHEqmI2543NYNJ2unCl8dnEo9xaroVfwWvYbU/DQ8dDqGe3dmVI9XaeHRovpfawV0eh2/LB/H51dOcMXKmgkt7mFyi0eYMvsISVfyWflkT4LLNJq7meikLMZ/uwtLC1j6aA8CGzmQVZDFL1G/sOjEIs5nnSfAOYDJrSYzKnQUDta1MJV5eCmsmAo9noEh79boFGaZ8DNyC2n39npeG96Sh/sYv87ZEAVFep7+aT9/HLtc8/ikhJjNsPl9bf9Z1wCODfoXjx77HzYWNsweMrtW6rsBLmVf4rblt9FO35J5cb/B2Hla35M6sOifs/x79T8Etv0GGysrsguyuVJ4BbIjiHSdwNx7K7kpnZMKswdp0yYP/wnuxmkytufifh769XWk7TlaNWrFSx2eJTIvF65cvprcHWpp/4CkU7D5PTi+Cuzdoeez0OWRSv+ISCnZeG4jsRmx144089JLR5xXCq4gKT9HWAmr0uRf9u2J1BOcTD2JlbCiX4GOkenp9BrxP6xb3l4LXzyQl0nGN9350smaJTZ63O3cuS/8Cb5c7YqHgx2/PNETV4ebv4KJT81h7MydFOr0/PxodxwdrrDoxCKWn1lOVmEWHb07cl/r++jn3w/L2hrcJJ2CWf2hcQRM+RVq+Kqr1hO+EGIY8DlgCcyWUn5w3fOi+PnhQA5wv5Ryf2XnNSThA7R7ez0j2zXh3Tvb1PgctWHF/gSeX3KIf49oydTeNUjKsdtg83+0lYkuftDnRQ40acMTW6bhYuPC7CGzCXAJMH7gZbz616v8EbuRuTFXaO3jjdVjW8tfHWlkTyzax44rM7BwPMVPI37C19GXH47/wPeHF1Ao8xgYMJhpnZ4s/4+drhB+uAvi/9F+oQIN76Z6Pus8n+77lD/i/kBf6MLk8Md4ude9FbaqqFUXDmo3RaM2gJOPNr/fcQpY1aAiqJhOryOzIPPGqYdy3k/L06YqfBx8uMOzPcO3f4ebrggmLoGAWm6lHb0ZfriT450n8x+RzqGkQ4S6tOb4kYF0btKWeQ90qXCx1MWMXMZ9u5PM3CLeG+/KtsTlrI9bD8DgpoO5r9V9tPVqW7vxF2TDdwO0G/aP/lXl+zLlqdWEL4SwBE4Dg4EEYA9wj5TyeJljhgNPoyX8rsDnUsqulZ3b0IR/x5fb8XC0Yf6D9atv+/u/n2DO9lhOvntb9ZZnx/0NW95Hxv3FWdfGHGo9nEOOzhxKOUpUehSBzoF8N+Q7fB1rfwrrVOopxvw6hhZJYSzN+lNLoMGGVQFVRq+XdPz8HXQey3gp8iXua31f6XN7z51n4tIPcfLaSZHM57bg23is3WMEuxZXskgJv06D/fPhrlnQruZNykC7lzH7yGx+OP4DFsICT90QEhN68M8rt5mkB/o1zu6ETe/AuR3aPYB+r2iVPdffC6gtURvh5/vAoRFMXqFNY9WFX5+FffPQP/A7qwsv8+m+T0nNS6MgrQsjAx/io7u63TAFlXQln3Hf/k2S7gAtwg9wKuMQjtaOjGk2hoktJ9bN3gJSwopHtBvx962EkH4Gna622yN3AaKklDHFF1sMjAKOlzlmFLBAan9ddgkh3IQQjaWURq+bzC3KZf6x+UR4RhDo4cCJi5nGvoTB4pKzCfRwqHKyz4nZwtG/3uNQ6kkOOTpzOKw5abo8uLgBZ2tn2nq1ZVDgIMa3GF+6FV5tC/cIp4NnF/YXnuBKgQfOf39e6wl/U/QRitxWEebUkUmtJl3zXGSgH63tx5OePISRfc7w06mfWBe3jhHBI3is3WMEHlujJfveLxiU7HV6Hb9E/cKXB74kNS+V20Nu577wx7njs6M80DPI9MkeoGl3beVq9CbY9C6segK2fwoDXoOWo65tSmYMBdlayWhKFFw8pPWy92oJk5aBcx3ePxvyLkRtwmLVk9z52HYGBA7gm4PfsOjEj6xNO4Lu9/v5eNgjpVMyFzIyGPfjF6S7/omwSeZKURNeinyJ0c1G42RT/ZulNbZvLhxZAv1fMzjZV8YYCd8PiC/zcQLaKL6yY/yAGxK+EOIR4BGAwMDql1VZF+az7PBsDjgHEuTxHhuOX0avl/Wqq11scjbBnuX/QEkpSbiSwMGkgxyK3cDh8zs4LfPQCQEebgS7BNHPuwPtvNrRzqsdIW4hppk+AB5p9yCPJz/G1z7deSXqN7h0FHxrZ/qsQFfAf/b+G6m34Z2e/1fu13xP50BeXp5OL88p3Nf6PuYdm8fik4tZG/Mbd1y5wiPhQwjo/+8ax7Dr4i4+3vMxp9NO08G7A18N+Iq2Xm2Z+3csRXp5QysFkxICwgZB6EA48as2x7/0fvBtCwPe0Eo8qzMFpyvS+t1cX0KaEg2Z5689ttkQuPt7o90MrzJbZxj1ldYV9s93cRn2PtO7TGdk6J08tOZfbEj6HyNXbOLV7s+y4/xuFh3/Gb1DDsFOLXiy03QGBQ4qt0V4rbpwEH6frv0/9X6x1i9njK+uvJ+a6+eJqnKM9qCUs4BZoE3pVDcYKxsnxuTq+Fp/hrbBqRTo9Fy+kkdjV/vqnqpW6PWSuJQc+jbX9h7NLcrlWPIxDiUdKv2Xmqc1gnLQ64kolEz1jaRdxGQiGnfB1bb+lJj29OuBta4Jv1gmMd3aEbHjSxj9ba1c64v9X5BcEItr1iNE+JZ/f+L2do15Z81xftp9jk/GteeFyBeY4t2d73+dwlInR9YUnmHUrnd4OOJh/Jz8qnztuIw4/rv3v2xJ2IKfkx8f9/2YoU2Hlk4PLN+fQBs/F1r41nGCqwohtHYRLUZoUwab/wM/joWArjDwDQjqdfVYKSHrcpnFXlFX1wqkxYL+6n6tpRVHwX3KqTiqWlVMrQjpC50fhl3fQIvbIagnLRuFs378YkbM+ZJzRct5fOPjIAW6rNY833UqD0UOME1P+9w0WHIfOHppXUKN/cqrHMZI+AlA2d9Af+BCDY4xDksrRnd5npkHP+Ti5ZnABM6l5NSbhH8hI5cCfRbRuh8Zv+YMp1NPUyS1X6QgB1965etpl5xKO2wJ6/wEll0fq1Etbl0QQhBiM4JTuu/4u81t9Dq0DAa+Dq7GHenuuLCD+cfnQ0YP+gb0q/A4BxsrRrVvwvL9Cbx5R2tcdel4Ln+U6ToLHhjzI9/HrWHp6aWsil7FXWF38UjEIze935GRn8HMQzNZfHIxtla2TOs4jcmtJmNraVt6zMlLmRw9n8lbdxi2srPWWVhCuwnQejQc+AG2fQzzRkBwX62fTUlyLyjTAdXKDjxCwbsltLzj2jUCVaz5N4lBb2k3rlc9AY/vABtHnOys+XHi44z8ujmZFvvR5wbx5djBptsQXUpY+aT26uiB3+vs+2mMhL8HaCaECAbOAxOAidcdsxp4qnh+vyuQURvz9yW8201iwP7P2JZ1ABsxknOpOXStYOu1uhabnI2t1yb2pe0k0jeSB9o8QDsrVyKOrcX92DqthrrHs9Dl0bp/SVwD3X0GcuLsz8yxzKeXlNrIauh7Rjt/Wl4a/97+b5o4NOXUyWH06Hvz/8d7ugSy6J9z/LovhkmnnobsJHhgLd4+bXnVpy0PtHmA2Udms/zMclZGreTuZnczte3UaxbPFOoLWXJqCf87+D+yCrMY3Ww0T7Z/stxFQsv3JWBtKRjZvuqvGEzKygY6PwTtJ2o9bXb+T5uqaRQGgd2vLvhqFKYtBquDUafR2TrBqP/BvOGw8W0Y/hEAfm72zJ7ck2mLHXnurmamS/ag3ec49RsM+6D2K5jKMDjhSymLhBBPAX+glWXOkVIeE0I8Vvz8TGAtWoVOFFpZ5gOGXvemhGBcu4fZcPRrerstIT7NeCtNDXXqchLWrnvp7z+Uz9rcD1s/0OqnbV2h37+g22NVWjhTXzT3dqfwQE/2WP/OiVa30XLfPOjzolYPbiApJW/seIP0/HTu9HmVU7KA7qE3T/ht/Fxp08QZ323ToeAfbY2AX8fS530dffl3t3/zUJuHmHVkFstOL2PFmRWMDR/LQ20e4njKcWbsnUFcZhzdGnfjxcgXCfcIL/daRTo9vxy4wIAW3jcsv6/3rO2hx9Pav4YoqKe2TeI/32ivToJ7A9Ah0J1tL/c3bWxnd8LGt6DlSOj6WJ1e2ih3KKSUa9GSetnHZpZ5XwJPGuNaVdW146MEHf2WHLejJCYlA83r8vIV2nLxN4RlPg8nx8E3PcDGSevK2P0JoyTJuhbm7URBehecG29hvqszHxRkwd45WjWMgZaeXsqW+C28FPkSG3a50MI3H08n20o/791GG+iQupmLHZ+ncQULwho7NebN7m8yte1UZh2exeKTi1l8cjE6qSPIJYivBnxFH/8+N53b3XYmieSsfO7uWI9u1ipXDXwDzvxRPLVTszYFRpeVBMse0Bb8jfqqTtaulHULvl6rGiEEY5uN4aidFY0vzTJ1OIBW0ncy+3dC8m1pfXoz9H4enj2slcvdgskeINjTESHtae4wiHWXdnEppA/smgmFhu0nHJMew8d7PqZHkx6MbX4Pe+JSKx3dA3B8NR3OfMGv+p58UXhnpYf7Ofnxdo+3+fXOX5nYciKvdnmVFaNW0Degb6U38pbtS6CRow39W9Rt8zilimwctKmd9HjY8Iapo9HaWy9/SLtZO26BSV7JN9iEDzCq01NYS0GK9fYa715kTFsTtpIvkngyPUFbBTnwjdpbbl9H7KwtCXB3wDFPe5m8sEkIZCfC4Z9rfM4CXQHT/5qOvZU9/9fz/zgcn0leob7yRlgXDmobl/h3Zkfrt1h16CJZ+UU3/5xiAS4BvNz5ZSa2nFildrXpOQVsPJ7IyPZNbtnt7sxC0+5af6G932urcU1p64cQuxWGz9DKY02gQf+kutq6Em7biT+cbEnf8qGpw+GHYwvwKpS013tqzZEaiFAvR84n2zE0aCjLEv/hSuMI2PEF6PU1Ot8X+7/gZOpJ3un5Dl4OXuyITsFCQJfgm/xxzLyo7Vpl7wETfmRMt2bkFOj49VDtFIP9eugCBTq9ms65FQz4t3YTevXTkGeihZhRG2HrR9D+Xug42TQx0MATPkDPwEnkWFiw7vTPkHG+8k+oJadST7E3cR+Tr6Rzsv0bYF2DzTrqqTBvJ2KSspjc8j6yC7NZFtpZK/M7tbbyT75OSQnm+PDx9CsuwdwZnUJbP9dyN6EAoCAHFt+j/TJP/BmcvOkY6EZzHyfDdsO6iWX7z9PC15nWTep/JZXZs7aHO2dqJZDra77wrsYyEmD5w+DdShvdm1CDT/g9/DtgkefNUid75Jb3TRbHwoPfYKfX45jeEseWg0wWR20I9XIiv0iPq2UwXX27sjDtEIVugdoovxpKSjBDXEN4IVK76ZtTUMSB+DS6VzSdo9fDyse16Zwx35eu9BVCMKFzIIcSMjh+wbijuqjEKxyKT2dMJ3/TLNhRqi+gs1aRtH++1k68rugKYekD2ttxC2rWBtuIGnzCb9rIkZy0npy2sebQiWWQHFXnMaTkJLM2/k9uzy5gRv6UCtsq3KpCvbWvJyoxiymtp5CYk8i61oO1rpTndlXpHGVLMD/q8xH2VtpCuT1xaRTqJD0qumG75X04vhIGvwPht13z1OiOfthYWbB4j3FH+cv2ncfSQjDqVqm9VzT9/gWe4drUTmps3Vxzw5ta+/JRX2ob3JhYg0/4Ho42WOd2who7fnZxgc3/V+cxLN3xfxQgCbQdQZ6dF+6V9Oa+1YR5aQk/OimLXn69CHUNZV5OLNLeA/7+vErnKCnBfLbjs9fUve+ITsbaUhAZVE4V0+GlsO0j6DCp3HpyNwcbhrfx5ZcD58kt0NXoa7ueTi/55UAC/Zp74eVceYmoUo9Y28Fd32gtiL/sqLU1iN9de9c7vgp2fa0toqyj/SIq0+ATvhCCQHd3POjBH472pJ1Ypb38ryOFOWn8HL+Rnjor/mQswV4V7xJ0q3J3tMHD0YaoxCyEEExpPYXT6VHsbHuHNo+fdOqmn1+2BPP6Lpg7o1PoEOCOg03xkpGsJDj4o/bLuuoJaNoTRnxaYT3zhC6BXMkrMtqG9tujkrmcmc+Y+tQoTak6v07wzAGtaCJmC3w/GGYPhmMrtbJJY0mJhlVPgV8kDKn7QWZFGnzCBwjwcECf0Z1C9Kxy99R6hdeRdeufI9lCMLnj08Sk5BPcyLRzeLUlzMuJ6CStD8uIkBF42Xsx3+IKWNlry8grcH0JZtkumBk5hRw9n87IxqmwbYa2U9WMZtqcffxubWQ/fuFNN/joGuxBiKej0aZ1lu1LwNXemgEtVe39LculCQx+G547Drd9pJURL50CX3TQWoPkXzHs/IW5sGSK1r9o7DyDNqAxNrNI+IEeDlxMcqOjd0eWNPJGH70JYv+q9evKS8dYmLiTYAsHOra6j/PpuQ1u/r5EqLcj0UnZANhY2jCx5UR2XN7LqbYjtZr8zPJH2CUlmG/3eBsvB62DKIV5cHo9mcuf5i+bZ5h0YCL8+a42Auv3Kjy6DZ4/Abd/Wuk6BiEE4zsHsCcujahEw36RM3ILWX/sEqPaN8HWqm728FVqka0TdH0Unt6vDRycG8O6V+CT1rD+9ZpX9a19CS4f0TpgutXuznPVZRYJP8DdntxCHbc1HU18YSa7PPxg09tax7raIiUH1z7FcVsbJrV/jHOpuQAEeTbMEX6olxOp2QWkZhcAMLb5WOyt7FngZKe11f1n5g2fU7YEs79bC9g3D366Bz4Khh/H4hO7kuOEUDTiC3jhNDyyGfpNh8btqrUk/e5O/lhbChbvjq/84Jv47fBF8otU7X2DY2Gp9dt56A+YugnCBsDOr+DzCFg+FS4cqPq5Dv6odSPt/aK250A9YxYJP7B4GiXYoRsedh787B8OCXvg1O+1d9FDP/FD3jlcLO24vcV4YpO16Y6QBjvCv3rjFrRFb3c3u5u15//iUovbYO/caxa9pOWk8O+t0wmxcuGFQ+vhkxbaFoSXjmiLUyYtZ7TTQhYEvodV5yng7FPudavC08mWwa18WL4/gfyims/TLt+fQDNvJyL8b53mdko1+Udq0zDPHNQam51aB7P6wdwRcHLtzRcTXj4Ga56HoN7Q/191FHD1mEfC99AS/qX0Iu4Mu5MtWTFcahRydZrA2HJSubDxDTY5OHB3iwk4WDsQm5wDNNwRfkmlTlTi1X7qk1pNQo+eH30CID9DG+Wf/A256ineXNiH9Pw0Pjx7GnsrO63NxOM74dkjMGIGST69OZqYX3k7hSqa0DmQtJxC/jh2uUafH5OUxb6zadytau/Ng3tTrc3388dgyHtaC+nF98BXkVpb6YKca4/Pv6IVEti5aLt9WdTPKT+zSPj+7lqSPZeSw9jmY5FSsiK8FyQe13YBMrZN77DYpghhYcE9LbStAWKTs/B0ssXZrmGVZJbwc7PH1sqC6DIJ38/JjyFNh7D0wjaygnpq2+wtnsjSuN/ZbGvBNL/BtJh2Ah5ar3XX9GlVOlWzK0brfVRh/X019QrzxN/dvsYrb1fsP4+FgLs6qNp7s2LnCj2e0kb8Y+ZoH//2AnzaStsv+MolbWq4pLZ/zFyDXo3WNrNI+HbWlvi42HIuNQd/Z396+vVkefoxCn3balu+FRUY72IJe8nZP59lbh4MDBxEYydtk4W45BxCPE249Vsts7AQhHg5EZWUdc3j97e+n6zCLJa36Ae9XyBmzCw+buROjyY9mDzov9puS+XYEZ2Ms52V0VoXWFgIJnQOYEd0CmdTsqv1uXq9ZMX+BHo388LHpeG0xFCqwdIK2twND/8JD6zTyoH/+i981hbm3wHHftF2ewvqaepIb8osEj5AgLsD51K1l2Hjmo8jMTeJre1Hay/V9s83zkX0OljzHL96NuaKLGRyq6tNkmKSsxvsdE6JMO+rpZklWnu2JtInkoUJG8jp8yLTY5dhb+VwQwnm9XZEp9A1uBFWRuxEOTYyAEsLweI91bt5uzMmhQsZear2XtFegTbtDhMWwTP7odP9cH4fhI+AHtNMHV2lzCbhB3o4kJCmVcr08e+Dr6MvP2eegKa9tC52BdUb9ZVrz/foLx1mYSMf2jRqQzsvbaetK3mFJGflN9iSzBKhXo4kpOWSV3jtfZH7W9/PpexL3L/u/htLMMuRkJbD2ZQco03nlPBxsaN/uDdL9yZQqKt6J8/l+xJwtrNicKv6+1JdMQGPEBj+MbwcC+N/uCW2g6z/ERpJgIeDtoF4kR5LC0vGNBvDrou7ONvjMW3hxa5vDLvAlcvw57vsCOlKXH4y97a6t/TmXlzxDdtgMxjhSwkxSdf+8ezt35sQ1xBOpJ7QSjADb77F3M7o4vn7MOPvQ3xPlwCSs/LZdCKxSsdn5Rfx+9FL3NGuCXbW9fNGnGJi1nb19ibt9cwm4Qd6OCAlnE/XRvl3N78bK2HF0sxT0Pw2+PsLyEmt+QXWvwZFeSz09MXL3ouhTYeWPhVbPGfc8Ef415ZmlrAQFkzvPJ1hQcNKu2DezM7oFBo52hDu42z0GPs298LXxa7KK2/XHrlIbqFO1d4rDYLZJPyA4tLMknl8T3tPBgQOYGX0SvL6TYf8TPj7s5qdPGYrHFlKTNcH+TvpAOPDx2NtebUaJ7Z4xNu0gbZVKBHs6YgQ15Zmlujh14OP+35c2gWzIlJKdkSn0D20Ua2UP1pZWjAu0p+tp5NK//jfzLJ9CYR4OtIx0M3osShKXTObhF9Six+ferV+dlz4ODLyM1ifGw8R4+CfbytsAVChogKtTMs9iEWOdthY2DA2fOw1h8QmZ+HnZt/gpwRKtju8foRfHbHJ2VzKzDNa/X15xnXWlrsvqeTm7bmUHHbHpqrae6XBMCjhCyE8hBAbhBBnit+WuxO3EGKOECJRCHHUkOsZwtvZFhsri2sSfhffLgS5BPHzqZ+1Hi36Iq3dbnXs/BJSzpAx+G1Wx65lRMgIPOyu7e8Sm5LT4Ct0SoR6OZY7wq+qHdHGrb8vj7+7A72bebFkbzw6fcXtNZbvT0Co2nulATF0hP8KsElK2QzYVPxxeeYBwwy8lkEsLAQB7valUzqgNdYaFz6Ow0mHOUk+dHoA9i/QWptWRVocbP0YWo5kuT6VPF0e97a895pDpJTEJmUR3IBr8MsK83YiNjn7pon0ZnZGp9DE1a7Wp7/u6RzAxYw8tp4u/+atXi9ZcSCBnqGeNHG7+TSUotwqDE34o4CSIvb5wJ3lHSSl3AYYcEfUOAI8HK5J+AAjQ0diZ2nHklNLoM9LYGmjLcaqit9fAWFB4ZB3+fHEj3T17XrN5h0AaTmFZOYVEdTIPBJ+yXaH59Mqnx+/nl4v2RmTQvdQz1qfQhnY0gdPJxt+qqCh2u64VOJTc7m7kxrdKw2HoQnfR0p5EaD4rcFNwoUQjwgh9goh9iYlJRl6umsEejhcM6UDWpOvYcHDWBOzhixbR61h0tFlcPHwzU92ci2c/h36vcKmjJNczrl8w+geuNo0zcs8En6Yd/mVOlVx6vIVUrMLanU6p4SNlQVjOgXw58lEEjPzbnh++b4EnGytGNrat9ZjUZS6UmnCF0JsFEIcLeffqNoISEo5S0oZKaWM9PKqeHFOTQR6OJCZV0RGTuE1j49rPo7colzWxKyBntPAzk1rrFaRgmz4fbq2C323x1l0fBEBzgH08e9zw6GlTdPMaIQPNUv4f0clA9C9DhI+wITOAej0kqX7Eq55PKdA2yFreFvfqzttKUoDUGnCl1IOklK2KeffKuCyEKIxQPHbqq1mMZHrSzNLtPFsQ0uPlvx86meknSv0ehbOrIezO8s/0bYZkHEORnzC0bRTHEw6yMQWE7EsZ/FFbHIWlhai9NoNnbujDY2Ktzusrp3RKQR7OtbZnHmQpyPdQxqxeM859GXuOaw7eonsAh1jOtWvzSsUxVCGTumsBqYUvz8FWGXg+WpVgHv5CV8Iwfjw8USlR3Ew6aC26bCTL2x868ZNUpJOaVv2tb8XmnZn4YmFOFo7cmfYneVeMy45hwB3e6yN2BOmvgv1urGnTmWKdHr+iU2tk+mcsiZ0CSA+Nbe0Ogi02vtADwc6l7dxuqLcwgzNQh8Ag4UQZ4DBxR8jhGgihFhbcpAQ4idgJxAuhEgQQjxk4HVrJMBDGzlen/ABbgu+DSdrJ61E08YB+r4E8bu0kX4JKbWaextHGPwOiTmJ/BH7B3eF3YWTTfmraGOSs82mQqdEqHf1SzOPnM8gK7+oVuvvyzO0tS9uDtb8VLzyNiEth50xKdzdUdXeKw2PQQlfSpkipRwopWxW/Da1+PELUsrhZY67R0rZWEppLaX0l1J+b2jgNeFsZ42How3xaTcmfAdrB+4IvYP1cetJzUuFjlPAPVjreV2yy82RpRD3Fwx6Exw9WXxyMTqpY2Jxz/vrSSmJS85u8C0Vrhfq5URaTmHpdodVUTLC7hZy8z1qjc3O2pLRHfxZf+wSKVn5/LL/PFLC6I6qOkdpeMxnnqFYQDmVOiXGNR9Hob6QlVErwdIa+r+mbUZ8bAXkpsMfr4FfJ+h4P3lFeSw7vYx+Af0IcCl/rvdyZj65hboG3zTteiXbHVZnlL8zOoUWvs40crKtrbAqdE+XAAp1kuX7E1i+P4FuIR5mc89FMS/ml/CvW3xVVph7GJ18OrH01FL0Uq9teODTRqvY2fgW5CTDiE/AwoK1sWtJy09jUstJFV4rNtk8mqZdL6yalTr5RTr2xKXW+XROiWY+znRq6s6Xf0YRl5KjGqUpDZbZJfxADwfOp+VWuBJ0fPh4ErIS2Hlhp9bfeuAb2orafXOh88PQpD1SShaeWEhz9+Z09u1c4bVKEr65tFUoofUNuna7w5s5cC6d/CJ9nd+wLWtC5wCu5BXhYGPJ8LaNTRaHotQms0z4RXrJxYzyV4IOChyEh52HdvMWoNkQbZMUJ18Y8BoAuy/t5kzaGSa1nHTTG3txKdnYWFnQxNW8luZbWAhCPG/c7rAiO6JTsBDQpY7n78u6PaIJbg7W3B7RGEdbVXuvNExm95MdWKYWv2Rz87KsLa25K+wu5h6by6XsS/g6+sKkZVCYq21gDCw8vhB3W3eGhwy/4fPLiknKJqiRAxYW5lftEertxMH4tCoduzM6mbb+briYcIN3extL1k3rg4u92f1KKGbE7Eb4AeW0Sb7e2PCxSClZdnqZ9oC1PThoo89zmefYmrCVceHjsLW8+Q3GuBTzK8ksEeblVO52h9fLKSjiwLl0k07nlPB1tVMra5UGzewSfmNXOywtRIU3bgH8nPzo5deLFWdWUKi/tg3Djyd/xNLCkvHh4296HZ1eci4lhyAzTfih3o7lbnd4vd2xqRTpZb1I+IrS0JldwreytMDPzZ741Jt3cxwfPp6k3CS2xG8pfSyrIIuVUSsZFjTspptwA1xIz6VApyfEXBN+caVOZfP4O6NTsLG0ILKp6ebvFcVcmF3CB20e/2YjfIBefr1o7Nj46s1b4JeoX8guzL5pKWaJmJIKHTNpmna9ku0OK6vU2RGdQodAN+xtGvZuYIpSH5hlwr/Z4qsSlhaWjGk+hn8u/kNcRhw6vY5FJxbRwbsDrT1bV3qNuJIafDNpi3y9qmx3mJFTyNELGSarv1cUc2OmCd+elOwCsvKLbnrc6GajsRJWLDm9hK0JWzmfdb7cnvfliU3OxtHGEi8TrBytL8K8nW662nZXbApSQo8wNX+vKHXBLBN+eRual8fT3pOBTQeyKmoVc4/OxdfRl4GBA6t0jdjkbIK9HM26AVeol+NNtzvcGZ2CvbUl7fzd6jYwRTFTKuFXYnz4eDILMkt73ltZVK1sLzY522zn70uEed98u8Md0cl0DvbAxsosfwwVpc6Z5W9aYAUboZQn0ieSYNdg7K3sGd1sdJXOX1CkJyEtx2wrdErcbPerpCv5nL6cpcoxFaUOmeUqE1d7a5xtrao0whdC8J9e/yEtLw1XW9cqnf9cag56idnW4JcoLc1MzKJ/i2u3O94Zo7VDVglfUeqOWSZ8IbQtB6sywgdtC8TquNol07wTfsl2h+WN8HdGJ+NsZ0XrJlX7I6ooiuHMckoHtGmd+Armlg0VpxJ+qVCv8it1/o5KoVtIIyzNsM+QopiK+Sb8Rlotvr6CChJDxCRn4+5gjZuDjdHPfasJ9b5xf9v41BzOpebQU03nKEqdMtuEH+BuT36RnqSsfKOfO84M97GtSKiX4w3bHZbO34epBVeKUpfMN+FXo1KnumKTs83+hm2JsHK2O9wZnYKnkw3NvM1rJzBFMTWzTfjVqcWvjpyCIi5l5pl9SWaJ60szpZTsiE6me6inWS9KUxRTMCjhCyE8hBAbhBBnit+6l3NMgBBisxDihBDimBBimiHXNBY/d3uEMP4IPy5ZO58a4WtKtjssGeHHJGdzOTNflWMqigkYOsJ/BdgkpWwGbCr++HpFwAtSypZAN+BJIUQrA69rMFsrS3xd7Iyf8FNUhU5ZJdsdlozwd0Sr+ntFMRVDE/4oYH7x+/OBO68/QEp5UUq5v/j9K8AJwM/A6xpFVbpmVlesmbdFLk9omSZqO6OT8XOzL51SUxSl7hia8H2klBdBS+yA980OFkIEAR2Afwy8rlFUpS9+dcUmZ+PjYqs2wi4jzMuJ8+m55BQUsTM6he6hjdT8vaKYQKVZSQixEfAt56nXqnMhIYQTsBx4VkqZeZPjHgEeAQgMDKzOJaot0MOBy5n55BXqsLM2zgYcqmnajUq2O/z9yCXScgrVdI6imEilCV9KOaii54QQl4UQjaWUF4UQjYHECo6zRkv2i6SUKyq53ixgFkBkZKTxV0WVEeBhD0BCWm5p+aCh4pKzGdLaxyjnaihKvrcLdp0FUBueKIqJGDqlsxqYUvz+FGDV9QcI7bX798AJKeUnBl7PqIxdmpmRW0hKdoEa4V8nqJEjFgIOxacT4uWIr6udqUNSFLNkaML/ABgshDgDDC7+GCFEEyHE2uJjegKTgQFCiIPF/4YbeF2jMPbiK9VDp3x21pal32s1naMopmPQnUUpZQpwwxZQUsoLwPDi97cD9fIOnZeTLXbWFkYb4asumRUL9XLibEqOms5RFBMy25W2oLVJNmalTmxyNkJojdmUazXzcUII6BaiRviKYipmXzsY4G7chO/vbo+tlXEqfhqSqb1C6BXmiYej6iCqKKaiEr6HA7tiUpBSGlwbHpeiSjIr4uVsi5ezl6nDUBSzZtZTOqBV6mQX6EjLKTToPFJKYpOyVdM0RVHqLZXwjVSpk5JdwJX8ItU0TVGUesvsE76xSjNVhY6iKPWdSvjFq20NLc1UCV9RlPrO7BO+g40Vnk62nEsxPOFbWwr83OyNFJmiKIpxmX3CBwj0sCc+zbCEH5ecTYCHA1aW6luqKEr9pLIT2jy+MebwVYWOoij1mUr4aJU6F9JzKdTpa/T5er1UbZEVRan3VMJHG+HrJVxIz63R51/KzCO/SE+wl0r4iqLUXyrhU7ZNcs0SfmmFjhrhK4pSj6mEj+G1+KUJX43wFUWpx1TCB3xd7LC2FAYlfDtrC3yc1cYeiqLUXyrhA5YWAn93hxovvoorvmFrYVEv2/4riqIAKuGXCvBwqHEtfmxytlphqyhKvacSfrEAd/saTekU6fScS81RCV9RlHpPJfxigR4OpOcUkpFbvTbJCWm5FOmlSviKotR7KuEXu1qaWb1RfmyKapqmKMqtQSX8YgE1TfhJKuErinJrUAm/WGnCr+aN27iUbJztrNRerYqi1HsGJXwhhIcQYoMQ4kzxW/dyjrETQuwWQhwSQhwTQrxtyDVri6u9Na721tW+cVvSNM3Q/XAVRVFqm6Ej/FeATVLKZsCm4o+vlw8MkFK2A9oDw4QQ3Qy8bq0I9HDgXDXbK8QmZ6ttDRVFuSVYGfj5o4B+xe/PB7YA08seIKWUQFbxh9bF/6SB160VgR4OHL+YWeXj8wp1nE/PZUwn/1qMSlFuHYWFhSQkJJCXl2fqUBo8Ozs7/P39sba2rvLnGJrwfaSUFwGklBeFEN7lHSSEsAT2AWHA11LKfyo6oRDiEeARgMDAQAPDq54ADwc2HL+MTi+xrMKq2fjUHKRUN2wVpURCQgLOzs4EBQWpac5aJKUkJSWFhIQEgoODq/x5lU7pCCE2CiGOlvNvVDWC00kp2wP+QBchRJubHDtLShkppYz08vKq6iWMIsDDngKdnsuZVRudxKh9bBXlGnl5eTRq1Egl+1omhKBRo0bVfiVV6QhfSjnoJhe9LIRoXDy6bwwkVnKudCHEFmAYcLRakdaBwDJdM5tUYW/auOKEr+bwFeUqlezrRk2+z4betF0NTCl+fwqw6voDhBBeQgi34vftgUHASQOvWysCq9kmOTY5G08nG1zsqj6HpiiKYiqGJvwPgMFCiDPA4OKPEUI0EUKsLT6mMbBZCHEY2ANskFKuMfC6taKJmz0WAhKqkfDVtoaKotwqDEr4UsoUKeVAKWWz4repxY9fkFIOL37/sJSyg5QyQkrZRkr5jjECrw3WlhY0dq16EzXVJVNRbg1vvfUWM2bMqNVr7N27l2eeeaZWr2EoQ6t0GhytFr/yhJ+dX0TilXw1f68oCgCRkZFERkaaOoybUgn/OoEeDmw6edN7z8DVbQ1DVMJXlHK9/esxjl+o+rqWqmjVxIU372hd6XELFixgxowZCCGIiIggNDS09LmDBw/y2GOPkZOTQ2hoKHPmzMHd3Z0vvviCmTNnYmVlRatWrVi8eDHZ2dk8/fTTHDlyhKKiIt566y1GjSq/QHHLli3MmDGDNWvW8NZbbxEdHc358+eJj4/n5Zdf5uGHH2bLli28+eab+Pj4cPDgQUaPHk3btm35/PPPyc3NZeXKldfEamwq4V8nsJEDyVn55BQU4WBT8bcnLkXtY6so9dGxY8d47733+Pvvv/H09CQ1NZUvvvii9Pn77ruPL7/8kr59+/LGG2/w9ttv89lnn/HBBx8QGxuLra0t6enpALz33nsMGDCAOXPmkJ6eTpcuXRg0aBCOjpX/3h8+fJhdu3aRnZ1Nhw4dGDFiBACHDh3ixIkTeHh4EBISwtSpU9m9ezeff/45X375JZ999lltfFsAlfBv4O+ulWMmpOXS3Me5wuNKumQ29VAJX1HKU5WReG34888/GTNmDJ6engB4eHiUPpeRkUF6ejp9+/YFYMqUKYwdOxaAiIgI7r33Xu68807uvPNOANavX8/q1atL5//z8vI4d+4cLVu2rDSOUaNGYW9vj729Pf3792f37t24ubnRuXNnGjduDEBoaChDhgwBoG3btmzevNk434QKqIR/ndLSzJScmyf85GyauNphb2NZV6EpilIFUsoa1aj/9ttvbNu2jdWrV/Puu+9y7NgxpJQsX76c8PDwap/v+hhKPra1tS19zMLCovRjCwsLioqKqn2d6lDtka9T1Vr82BTVNE1R6qOBAweyZMkSUlJSAEhNTS19ztXVFXd3d/766y8AfvjhB/r27Yteryc+Pp7+/fvz0UcfkZ6eTlZWFkOHDuXLL79EawkGBw4cqHIcq1atIi8vj5SUFLZs2ULnzp2N+FXWjBrhX8fD0QZHG8vKE35yNiPaNq6jqBRFqarWrVvz2muv0bdvXywtLenQoQNBQUGlz8+fP7/0pm1ISAhz585Fp9MxadIkMjIykFLy3HPP4ebmxuuvv86zzz5LREQEUkqCgoJYs6biZURlR/VdunRhxIgRnDt3jtdff50mTZpw+vTp2vzSK6US/nWEEAR4OJBwk41Q0rILSM8pVDX4ilJPTZkyhSlTppT7XPv27dm1a9cNj2/fvv2Gx+zt7fn222+rdM2UlJRr7hc0b96cWbNmXXNMv3796NevX+nHW7ZsqfC52qASfjkCPBw4W1yFUx61j62iKGWtXr2a1157jTlz5pg6lJtSCb8cgR4O/HUmqcKbP6ppmqKYrz/++IPp06/Z9oPg4GBOnrzaIuytt96q46iqRiX8cgR6OJBXqCcpKx9vZ7sbno9NzsbSQhDg7mCC6BRFMaWhQ4cydOhQU4dRI6pKpxwBHlotfnwF2x3GJmfj726PjZX69imKcutQGascJaWZ8RVU6qimaYqi3IpUwi+Hv3vFtfhSSuJUW2RFUW5BKuGXw87aEh8X23ITftKVfLILdISoHjqKotxiVMKvQIB7+W2SS7pkqhG+otw66qIfviGcnJwAiIuLo02bCrf8Npiq0qlAoIcDu2JSbng8Vm1crihV8/srcOmIcc/p2xZu+8C45zQjaoRfgQAPBy5m5pFfpLvm8diUbGwsLaq0ybmiKKaxYMECIiIiaNeuHZMnT77muYMHD9KtWzciIiK46667SEtLA+CLL76gVatWREREMGHCBACys7N58MEH6dy5Mx06dGDVqhu27S41fPhwDh8+DECHDh145x1tc7/XX3+d2bNnA/Dxxx/TuXNnIiIiePPNN2/6NRw7dowuXbrQvn17IiIiOHPmTM2+GWWoEX4FAj0ckBLOp+US4uVU+nhsUjZNGzlgaVH9bnyKYlZMNBI3VT/8Pn368NdffxEUFISVlRV///03oLVsmDRpEuvXr+fMmTPs3r0bKSUjR45k27Zt9OnTp9yvY+bMmUybNo17772XgoICdDpducdVhxrhVyCwUfmVOnEpqiRTUeqz6vbD37ZtG3C1H/7ChQuxstLGwuvXr+eDDz6gffv29OvXr7Qffnl69+7Ntm3b2L59OyNGjCArK4ucnBzi4uIIDw9n/fr1rF+/ng4dOtCxY0dOnjx501F79+7d+c9//sOHH37I2bNnsbc3fFZBjfArULKKNj7t6uIrvV4Sl5JD/3BvU4WlKEolTNUPv3Pnzuzdu5eQkBAGDx5McnIy3333HZ06dSqN69VXX+XRRx+tUjwTJ06ka9eu/PbbbwwdOpTZs2czYMCAan9dZRk0whdCeAghNgghzhS/db/JsZZCiANCiIp7i9Yj3s622FhZXLP46kJGLgVFejXCV5R6zFT98G1sbAgICGDJkiV069aN3r17M2PGDHr37g1oLRnmzJlDVlYWAOfPnycxseL9s2NiYggJCeGZZ55h5MiRpfcHDGHoCP8VYJOU8gMhxCvFH0+v4NhpwAnAxcBr1gkLC0GAuz3nUq4m/FjVNE1R6j1T9sPv3bs3mzZtwsHBgd69e5OQkFCa8IcMGcKJEyfo3r07oJViLly4EG/v8mcMfv75ZxYuXIi1tTW+vr688cYbBn9vRMlfrhp9shCngH5SyotCiMbAFinlDa99hBD+wHzgPeB5KeXtVTl/ZGSk3Lt3b43jM9QDc3dzOTOftdO0/7Afdsbx+qpj7P7XQLxdbmyqpijm7sSJE1Xa71UxjvK+30KIfVLKyPKON/SmrY+U8iJA8duKJrc/A14G9JWdUAjxiBBirxBib1JSkoHhGSbAw4H41JzSl3Mxydk42lji5WxbyWcqiqLUP5VO6QghNgK+5Tz1WlUuIIS4HUiUUu4TQvSr7Hgp5SxgFmgj/Kpco7YEejhwJb+IjNxC3BxstB46no41uiGkKErDUFE//F9++cVEEVVdpQlfSjmooueEEJeFEI3LTOmUdweiJzBSCDEcsANchBALpZSTahx1HQkos6G5m4MNscnZtPZzNXFUiqKYkjn3w18NlGwcOQW4YRmalPJVKaW/lDIImAD8eSske7jaJvlcag6FOj3xabmEqBu2iqLcogxN+B8Ag4UQZ4DBxR8jhGgihFhraHCmVnaEH5+ag04vVdM0RVFuWQaVZUopU4CB5Tx+ARhezuNbgC2GXLMuOdla4eFoQ3xq7tWmaaotsqIotyjVWqESJZU6pQlfjfAVRblFqYRfiUAPrS9+bHI2bg7WuDvamDokRVGqqS764c+bN4+nnnqqVq9hKNVLpxIB7vasPXKRqMQsNX+vKNXw4e4POZl60qjnbOHRguldKlrMr1RGjfArEejhgE4vORCfrip0FOUWYYp++ADx8fEMGzaM8PBw3n777dLH77zzTjp16kTr1q2ZNWtW6ePr1q2jY8eOtGvXjoEDb7gdanxSynr7r1OnTtLU/j6TJJtOXyObTl8jP9942tThKEq9dvz4cVOHII8ePSqbN28uk5KSpJRSpqSkyDfffFN+/PHHUkop27ZtK7ds2SKllPL111+X06ZNk1JK2bhxY5mXlyellDItLU1KKeWrr74qf/jhh9LHmjVrJrOyssq97ty5c6Wvr69MTk6WOTk5snXr1nLPnj2lMUgpSx9PTk6WiYmJ0t/fX8bExFxzTHWU9/0G9soKcqoa4VeipDQT1LaGinIrMFU/fIDBgwfTqFEj7O3tGT16NNu3bwe0Vw/t2rWjW7duxMfHc+bMGXbt2kWfPn0IDg6+Ic7aoubwK9HY1Q5LC4FOL1XCV5RbgDRRP3zghusKIdiyZQsbN25k586dODg4lP7hqGmchlAj/EpYWVrgV7x/rWqLrCj1n6n64QNs2LCB1NRUcnNzWblyJT179iQjIwN3d3ccHBw4efIku3btArQdrbZu3UpsbOwNcdYWNcKvgkAPB/IKdTjZqm+XotR3puyH36tXLyZPnkxUVBQTJ04kMjKStm3bMnPmTCIiIggPD6dbt24AeHl5MWvWLEaPHo1er8fb25sNGzbU6vfGoH74tc3U/fBLbDudROKVfMZ08jd1KIpSr6l++HWruv3w1ZC1Cvo09zJ1CIqiKAZTCV9RFKUaGnQ/fEVRlOowRfVJXaov/fBrMh2vqnQURTEaOzs7UlJSapSMlKqTUpKSkoKdXfX21lYjfEVRjMbf35+EhARMvR+1ObCzs8Pfv3qFJCrhK4piNNbW1qUrR5X6R03pKIqimAmV8BVFUcyESviKoihmol6vtBVCJAFnTR1HMU8g2dRBVKK+x1jf44P6H2N9jw9UjMZgSHxNpZTlrhat1wm/PhFC7K1ouXJ9Ud9jrO/xQf2Psb7HBypGY6it+NSUjqIoiplQCV9RFMVMqIRfdbMqP8Tk6nuM9T0+qP8x1vf4QMVoDLUSn5rDVxRFMRNqhK8oimImVMJXFEUxEyrhV0IIESCE2CyEOCGEOCaEmGbqmMojhLAUQhwQQlS8/5oJCSHchBDLhBAni7+X3U0dU1lCiOeK/3+PCiF+EkJUrw1h7cQ0RwiRKIQ4WuYxDyHEBiHEmeK37vUwxo+L/58PCyF+EUK41af4yjz3ohBCCiE8TRFbmTjKjVEI8bQQ4lTxz+VHxriWSviVKwJekFK2BLoBTwohWpk4pvJMA06YOoib+BxYJ6VsAbSjHsUqhPADngEipZRtAEtggmmjAmAeMOy6x14BNkkpmwGbij82pXncGOMGoI2UMgI4Dbxa10GVMY8b40MIEQAMBs7VdUDlmMd1MQoh+gOjgAgpZWtghjEupBJ+JaSUF6WU+4vfv4KWqPxMG9W1hBD+wAhgtqljKY8QwgXoA3wPIKUskFKmmzSoG1kB9kIIK8ABuGDieJBSbgNSr3t4FDC/+P35wJ11GdP1yotRSrleSllU/OEuwGSbQVfwPQT4FHgZMHnVSgUxPg58IKXMLz4m0RjXUgm/GoQQQUAH4B8Th3K9z9B+ePUmjqMiIUASMLd42mm2EMLR1EGVkFKeRxtBnQMuAhlSyvWmjapCPlLKi6ANRgBvE8dTmQeB300dRFlCiJHAeSnlIVPHchPNgd5CiH+EEFuFEJ2NcVKV8KtICOEELAeelVJmmjqeEkKI24FEKeU+U8dyE1ZAR+AbKWUHIBvTT0WUKp4HHwUEA00ARyHEJNNGdesTQryGNiW6yNSxlBBCOACvAW+YOpZKWAHuaNPILwFLhBH2jVQJvwqEENZoyX6RlHKFqeO5Tk9gpBAiDlgMDBBCLDRtSDdIABKklCWvjJah/QGoLwYBsVLKJCllIbAC6GHimCpyWQjRGKD4rVFe6hubEGIKcDtwr6xfi31C0f6wHyr+nfEH9gshfE0a1Y0SgBVSsxvt1bvBN5dVwq9E8V/V74ETUspPTB3P9aSUr0op/aWUQWg3Gv+UUtar0amU8hIQL4QIL35oIHDchCFd7xzQTQjhUPz/PZB6dFP5OquBKcXvTwFWmTCWcgkhhgHTgZFSyhxTx1OWlPKIlNJbShlU/DuTAHQs/hmtT1YCAwCEEM0BG4zQ3VMl/Mr1BCajjZwPFv8bbuqgbkFPA4uEEIeB9sB/TBvOVcWvPJYB+4EjaL8XJl96L4T4CdgJhAshEoQQDwEfAIOFEGfQqkw+qIcxfgU4AxuKf19m1rP46pUKYpwDhBSXai4GphjjlZJqraAoimIm1AhfURTFTKiEryiKYiZUwlcURTETKuEriqKYCZXwFUVRzIRK+IqiKGZCJXxFURQz8f+rML+sHcS3kwAAAABJRU5ErkJggg==\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Use merge_asof() to merge jpm and wells\n",
    "jpm_wells = pd.merge_asof(jpm, wells, on= 'date_time', suffixes=('', '_wells'), direction = 'nearest')\n",
    "\n",
    "\n",
    "# Use merge_asof() to merge jpm_wells and bac\n",
    "jpm_wells_bac = pd.merge_asof(jpm_wells, bac,  on= 'date_time', suffixes=('_jpm', '_bac'), direction='nearest')\n",
    "\n",
    "\n",
    "# Compute price diff\n",
    "price_diffs = jpm_wells_bac.diff()\n",
    "\n",
    "# Plot the price diff of the close of jpm, wells and bac only\n",
    "price_diffs.plot(y=['close_jpm', 'close_wells', 'close_bac'])\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### `.melt()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(12, 4)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country</th>\n",
       "      <th>indicator</th>\n",
       "      <th>year</th>\n",
       "      <th>annual</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Brazil</td>\n",
       "      <td>Inflation %</td>\n",
       "      <td>2017</td>\n",
       "      <td>3.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Canada</td>\n",
       "      <td>Inflation %</td>\n",
       "      <td>2017</td>\n",
       "      <td>1.60</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  country    indicator  year  annual\n",
       "0  Brazil  Inflation %  2017    3.45\n",
       "1  Canada  Inflation %  2017    1.60"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "inflation = pd.read_csv('./dataset/inflation.csv')\n",
    "print(inflation.shapepe)\n",
    "inflation.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-20-aecc5aeb93cc>:1: FutureWarning: This dataframe has a column name that matches the 'value_name' column name of the resulting Dataframe. In the future this will raise an error, please set the 'value_name' parameter of DataFrame.melt to a unique name.\n",
      "  inflation.melt(id_vars=['country','indicator'], var_name='year', value_name='annual').head()\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country</th>\n",
       "      <th>indicator</th>\n",
       "      <th>year</th>\n",
       "      <th>annual</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Brazil</td>\n",
       "      <td>Inflation %</td>\n",
       "      <td>year</td>\n",
       "      <td>2017.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Canada</td>\n",
       "      <td>Inflation %</td>\n",
       "      <td>year</td>\n",
       "      <td>2017.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>France</td>\n",
       "      <td>Inflation %</td>\n",
       "      <td>year</td>\n",
       "      <td>2017.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>India</td>\n",
       "      <td>Inflation %</td>\n",
       "      <td>year</td>\n",
       "      <td>2017.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Brazil</td>\n",
       "      <td>Inflation %</td>\n",
       "      <td>year</td>\n",
       "      <td>2018.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  country    indicator  year  annual\n",
       "0  Brazil  Inflation %  year  2017.0\n",
       "1  Canada  Inflation %  year  2017.0\n",
       "2  France  Inflation %  year  2017.0\n",
       "3   India  Inflation %  year  2017.0\n",
       "4  Brazil  Inflation %  year  2018.0"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "inflation.melt(id_vars=['country','indicator'], var_name='year', value_name='annual').head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}