pandas 06 - Working with multiple DataFrames.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# pandas 06 - Working with multiple DataFrames\n",
    "\n",
    "\n",
    "by Nova@Douban\n",
    "\n",
    "The video record of this session is here: https://zoom.us/recording/share/jk1xzcRFDRE3SXnaPwb1UFZSx2ZEogg5Rl2ha7lA6xuwIumekTziMw\n",
    "\n",
    "---\n",
    "\n",
    "\n",
    "## 6.1 Joining and merging data\n",
    "\n",
    "### 6.1.1 Joining data\n",
    "\n",
    "Usually, in a relational database, there are four ways of joining data by index: left, right, inner and outer. We can set according to needs.\n",
    "\n",
    "`pd.DataFrame.join()` provides a similar way to handle data, which uses `how` to set the joining index."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "| Merge method\t| SQL Join Name\t| Description |\n",
    "| - | - | - |\n",
    "|left |\tLEFT OUTER JOIN\t |Use keys from left frame only         |\n",
    "|right|\tRIGHT OUTER JOIN |Use keys from right frame only        |\n",
    "|outer|\tFULL OUTER JOIN\t |Use union of keys from both frames    |\n",
    "|inner|\tINNER JOIN\tUse  |intersection of keys from both frames |\n",
    "\n",
    "<img src=\"../image/join_venn.png\">"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-25T08:15:13.920713Z",
     "start_time": "2019-01-25T08:15:13.342654Z"
    }
   },
   "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>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>Adj Close</th>\n",
       "      <th>Volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-12-28</th>\n",
       "      <td>2498.770020</td>\n",
       "      <td>2520.270020</td>\n",
       "      <td>2472.889893</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>3702620000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31</th>\n",
       "      <td>2498.939941</td>\n",
       "      <td>2509.239990</td>\n",
       "      <td>2482.820068</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>3442870000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-02</th>\n",
       "      <td>2476.959961</td>\n",
       "      <td>2519.489990</td>\n",
       "      <td>2467.469971</td>\n",
       "      <td>2510.030029</td>\n",
       "      <td>2510.030029</td>\n",
       "      <td>3733160000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-03</th>\n",
       "      <td>2491.919922</td>\n",
       "      <td>2493.139893</td>\n",
       "      <td>2443.959961</td>\n",
       "      <td>2447.889893</td>\n",
       "      <td>2447.889893</td>\n",
       "      <td>3822860000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-04</th>\n",
       "      <td>2474.330078</td>\n",
       "      <td>2538.070068</td>\n",
       "      <td>2474.330078</td>\n",
       "      <td>2531.939941</td>\n",
       "      <td>2531.939941</td>\n",
       "      <td>4213410000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Open         High          Low        Close    Adj Close  \\\n",
       "Date                                                                          \n",
       "2018-12-28  2498.770020  2520.270020  2472.889893  2485.739990  2485.739990   \n",
       "2018-12-31  2498.939941  2509.239990  2482.820068  2506.850098  2506.850098   \n",
       "2019-01-02  2476.959961  2519.489990  2467.469971  2510.030029  2510.030029   \n",
       "2019-01-03  2491.919922  2493.139893  2443.959961  2447.889893  2447.889893   \n",
       "2019-01-04  2474.330078  2538.070068  2474.330078  2531.939941  2531.939941   \n",
       "\n",
       "                Volume  \n",
       "Date                    \n",
       "2018-12-28  3702620000  \n",
       "2018-12-31  3442870000  \n",
       "2019-01-02  3733160000  \n",
       "2019-01-03  3822860000  \n",
       "2019-01-04  4213410000  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>Adj Close</th>\n",
       "      <th>Volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-12-24</th>\n",
       "      <td>2400.560059</td>\n",
       "      <td>2410.340088</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2613930000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-26</th>\n",
       "      <td>2363.120117</td>\n",
       "      <td>2467.760010</td>\n",
       "      <td>2346.580078</td>\n",
       "      <td>2467.699951</td>\n",
       "      <td>2467.699951</td>\n",
       "      <td>4233990000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-27</th>\n",
       "      <td>2442.500000</td>\n",
       "      <td>2489.100098</td>\n",
       "      <td>2397.939941</td>\n",
       "      <td>2488.830078</td>\n",
       "      <td>2488.830078</td>\n",
       "      <td>4096610000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-28</th>\n",
       "      <td>2498.770020</td>\n",
       "      <td>2520.270020</td>\n",
       "      <td>2472.889893</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>3702620000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31</th>\n",
       "      <td>2498.939941</td>\n",
       "      <td>2509.239990</td>\n",
       "      <td>2482.820068</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>3442870000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Open         High          Low        Close    Adj Close  \\\n",
       "Date                                                                          \n",
       "2018-12-24  2400.560059  2410.340088  2351.100098  2351.100098  2351.100098   \n",
       "2018-12-26  2363.120117  2467.760010  2346.580078  2467.699951  2467.699951   \n",
       "2018-12-27  2442.500000  2489.100098  2397.939941  2488.830078  2488.830078   \n",
       "2018-12-28  2498.770020  2520.270020  2472.889893  2485.739990  2485.739990   \n",
       "2018-12-31  2498.939941  2509.239990  2482.820068  2506.850098  2506.850098   \n",
       "\n",
       "                Volume  \n",
       "Date                    \n",
       "2018-12-24  2613930000  \n",
       "2018-12-26  4233990000  \n",
       "2018-12-27  4096610000  \n",
       "2018-12-28  3702620000  \n",
       "2018-12-31  3442870000  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "%load_ext memory_profiler\n",
    "\n",
    "sp0 = pd.read_csv('../data/gspc.csv', index_col='Date')\n",
    "sp1 = pd.read_csv('../data/gspc_.csv', index_col='Date')\n",
    "\n",
    "display(sp0.head(), sp1.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-25T08:15:14.021005Z",
     "start_time": "2019-01-25T08:15:13.925889Z"
    }
   },
   "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>Open_l</th>\n",
       "      <th>High_l</th>\n",
       "      <th>Low_l</th>\n",
       "      <th>Close_l</th>\n",
       "      <th>Adj Close_l</th>\n",
       "      <th>Volume_l</th>\n",
       "      <th>Open_r</th>\n",
       "      <th>High_r</th>\n",
       "      <th>Low_r</th>\n",
       "      <th>Close_r</th>\n",
       "      <th>Adj Close_r</th>\n",
       "      <th>Volume_r</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-12-28</th>\n",
       "      <td>2498.770020</td>\n",
       "      <td>2520.270020</td>\n",
       "      <td>2472.889893</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>3702620000</td>\n",
       "      <td>2498.770020</td>\n",
       "      <td>2520.27002</td>\n",
       "      <td>2472.889893</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>3.702620e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31</th>\n",
       "      <td>2498.939941</td>\n",
       "      <td>2509.239990</td>\n",
       "      <td>2482.820068</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>3442870000</td>\n",
       "      <td>2498.939941</td>\n",
       "      <td>2509.23999</td>\n",
       "      <td>2482.820068</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>3.442870e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-02</th>\n",
       "      <td>2476.959961</td>\n",
       "      <td>2519.489990</td>\n",
       "      <td>2467.469971</td>\n",
       "      <td>2510.030029</td>\n",
       "      <td>2510.030029</td>\n",
       "      <td>3733160000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-03</th>\n",
       "      <td>2491.919922</td>\n",
       "      <td>2493.139893</td>\n",
       "      <td>2443.959961</td>\n",
       "      <td>2447.889893</td>\n",
       "      <td>2447.889893</td>\n",
       "      <td>3822860000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-04</th>\n",
       "      <td>2474.330078</td>\n",
       "      <td>2538.070068</td>\n",
       "      <td>2474.330078</td>\n",
       "      <td>2531.939941</td>\n",
       "      <td>2531.939941</td>\n",
       "      <td>4213410000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 Open_l       High_l        Low_l      Close_l  Adj Close_l  \\\n",
       "Date                                                                          \n",
       "2018-12-28  2498.770020  2520.270020  2472.889893  2485.739990  2485.739990   \n",
       "2018-12-31  2498.939941  2509.239990  2482.820068  2506.850098  2506.850098   \n",
       "2019-01-02  2476.959961  2519.489990  2467.469971  2510.030029  2510.030029   \n",
       "2019-01-03  2491.919922  2493.139893  2443.959961  2447.889893  2447.889893   \n",
       "2019-01-04  2474.330078  2538.070068  2474.330078  2531.939941  2531.939941   \n",
       "\n",
       "              Volume_l       Open_r      High_r        Low_r      Close_r  \\\n",
       "Date                                                                        \n",
       "2018-12-28  3702620000  2498.770020  2520.27002  2472.889893  2485.739990   \n",
       "2018-12-31  3442870000  2498.939941  2509.23999  2482.820068  2506.850098   \n",
       "2019-01-02  3733160000          NaN         NaN          NaN          NaN   \n",
       "2019-01-03  3822860000          NaN         NaN          NaN          NaN   \n",
       "2019-01-04  4213410000          NaN         NaN          NaN          NaN   \n",
       "\n",
       "            Adj Close_r      Volume_r  \n",
       "Date                                   \n",
       "2018-12-28  2485.739990  3.702620e+09  \n",
       "2018-12-31  2506.850098  3.442870e+09  \n",
       "2019-01-02          NaN           NaN  \n",
       "2019-01-03          NaN           NaN  \n",
       "2019-01-04          NaN           NaN  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>Open_l</th>\n",
       "      <th>High_l</th>\n",
       "      <th>Low_l</th>\n",
       "      <th>Close_l</th>\n",
       "      <th>Adj Close_l</th>\n",
       "      <th>Volume_l</th>\n",
       "      <th>Open_r</th>\n",
       "      <th>High_r</th>\n",
       "      <th>Low_r</th>\n",
       "      <th>Close_r</th>\n",
       "      <th>Adj Close_r</th>\n",
       "      <th>Volume_r</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-12-24</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2400.560059</td>\n",
       "      <td>2410.340088</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2613930000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-26</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2363.120117</td>\n",
       "      <td>2467.760010</td>\n",
       "      <td>2346.580078</td>\n",
       "      <td>2467.699951</td>\n",
       "      <td>2467.699951</td>\n",
       "      <td>4233990000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-27</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2442.500000</td>\n",
       "      <td>2489.100098</td>\n",
       "      <td>2397.939941</td>\n",
       "      <td>2488.830078</td>\n",
       "      <td>2488.830078</td>\n",
       "      <td>4096610000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-28</th>\n",
       "      <td>2498.770020</td>\n",
       "      <td>2520.27002</td>\n",
       "      <td>2472.889893</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>3.702620e+09</td>\n",
       "      <td>2498.770020</td>\n",
       "      <td>2520.270020</td>\n",
       "      <td>2472.889893</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>3702620000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31</th>\n",
       "      <td>2498.939941</td>\n",
       "      <td>2509.23999</td>\n",
       "      <td>2482.820068</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>3.442870e+09</td>\n",
       "      <td>2498.939941</td>\n",
       "      <td>2509.239990</td>\n",
       "      <td>2482.820068</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>3442870000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 Open_l      High_l        Low_l      Close_l  Adj Close_l  \\\n",
       "Date                                                                         \n",
       "2018-12-24          NaN         NaN          NaN          NaN          NaN   \n",
       "2018-12-26          NaN         NaN          NaN          NaN          NaN   \n",
       "2018-12-27          NaN         NaN          NaN          NaN          NaN   \n",
       "2018-12-28  2498.770020  2520.27002  2472.889893  2485.739990  2485.739990   \n",
       "2018-12-31  2498.939941  2509.23999  2482.820068  2506.850098  2506.850098   \n",
       "\n",
       "                Volume_l       Open_r       High_r        Low_r      Close_r  \\\n",
       "Date                                                                           \n",
       "2018-12-24           NaN  2400.560059  2410.340088  2351.100098  2351.100098   \n",
       "2018-12-26           NaN  2363.120117  2467.760010  2346.580078  2467.699951   \n",
       "2018-12-27           NaN  2442.500000  2489.100098  2397.939941  2488.830078   \n",
       "2018-12-28  3.702620e+09  2498.770020  2520.270020  2472.889893  2485.739990   \n",
       "2018-12-31  3.442870e+09  2498.939941  2509.239990  2482.820068  2506.850098   \n",
       "\n",
       "            Adj Close_r    Volume_r  \n",
       "Date                                 \n",
       "2018-12-24  2351.100098  2613930000  \n",
       "2018-12-26  2467.699951  4233990000  \n",
       "2018-12-27  2488.830078  4096610000  \n",
       "2018-12-28  2485.739990  3702620000  \n",
       "2018-12-31  2506.850098  3442870000  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>Open_l</th>\n",
       "      <th>High_l</th>\n",
       "      <th>Low_l</th>\n",
       "      <th>Close_l</th>\n",
       "      <th>Adj Close_l</th>\n",
       "      <th>Volume_l</th>\n",
       "      <th>Open_r</th>\n",
       "      <th>High_r</th>\n",
       "      <th>Low_r</th>\n",
       "      <th>Close_r</th>\n",
       "      <th>Adj Close_r</th>\n",
       "      <th>Volume_r</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-12-28</th>\n",
       "      <td>2498.770020</td>\n",
       "      <td>2520.27002</td>\n",
       "      <td>2472.889893</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>3702620000</td>\n",
       "      <td>2498.770020</td>\n",
       "      <td>2520.27002</td>\n",
       "      <td>2472.889893</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>3702620000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31</th>\n",
       "      <td>2498.939941</td>\n",
       "      <td>2509.23999</td>\n",
       "      <td>2482.820068</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>3442870000</td>\n",
       "      <td>2498.939941</td>\n",
       "      <td>2509.23999</td>\n",
       "      <td>2482.820068</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>3442870000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 Open_l      High_l        Low_l      Close_l  Adj Close_l  \\\n",
       "Date                                                                         \n",
       "2018-12-28  2498.770020  2520.27002  2472.889893  2485.739990  2485.739990   \n",
       "2018-12-31  2498.939941  2509.23999  2482.820068  2506.850098  2506.850098   \n",
       "\n",
       "              Volume_l       Open_r      High_r        Low_r      Close_r  \\\n",
       "Date                                                                        \n",
       "2018-12-28  3702620000  2498.770020  2520.27002  2472.889893  2485.739990   \n",
       "2018-12-31  3442870000  2498.939941  2509.23999  2482.820068  2506.850098   \n",
       "\n",
       "            Adj Close_r    Volume_r  \n",
       "Date                                 \n",
       "2018-12-28  2485.739990  3702620000  \n",
       "2018-12-31  2506.850098  3442870000  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>Open_l</th>\n",
       "      <th>High_l</th>\n",
       "      <th>Low_l</th>\n",
       "      <th>Close_l</th>\n",
       "      <th>Adj Close_l</th>\n",
       "      <th>Volume_l</th>\n",
       "      <th>Open_r</th>\n",
       "      <th>High_r</th>\n",
       "      <th>Low_r</th>\n",
       "      <th>Close_r</th>\n",
       "      <th>Adj Close_r</th>\n",
       "      <th>Volume_r</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-12-24</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2400.560059</td>\n",
       "      <td>2410.340088</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2.613930e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-26</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2363.120117</td>\n",
       "      <td>2467.760010</td>\n",
       "      <td>2346.580078</td>\n",
       "      <td>2467.699951</td>\n",
       "      <td>2467.699951</td>\n",
       "      <td>4.233990e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-27</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2442.500000</td>\n",
       "      <td>2489.100098</td>\n",
       "      <td>2397.939941</td>\n",
       "      <td>2488.830078</td>\n",
       "      <td>2488.830078</td>\n",
       "      <td>4.096610e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-28</th>\n",
       "      <td>2498.770020</td>\n",
       "      <td>2520.270020</td>\n",
       "      <td>2472.889893</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>3.702620e+09</td>\n",
       "      <td>2498.770020</td>\n",
       "      <td>2520.270020</td>\n",
       "      <td>2472.889893</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>3.702620e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31</th>\n",
       "      <td>2498.939941</td>\n",
       "      <td>2509.239990</td>\n",
       "      <td>2482.820068</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>3.442870e+09</td>\n",
       "      <td>2498.939941</td>\n",
       "      <td>2509.239990</td>\n",
       "      <td>2482.820068</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>3.442870e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-02</th>\n",
       "      <td>2476.959961</td>\n",
       "      <td>2519.489990</td>\n",
       "      <td>2467.469971</td>\n",
       "      <td>2510.030029</td>\n",
       "      <td>2510.030029</td>\n",
       "      <td>3.733160e+09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-03</th>\n",
       "      <td>2491.919922</td>\n",
       "      <td>2493.139893</td>\n",
       "      <td>2443.959961</td>\n",
       "      <td>2447.889893</td>\n",
       "      <td>2447.889893</td>\n",
       "      <td>3.822860e+09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-04</th>\n",
       "      <td>2474.330078</td>\n",
       "      <td>2538.070068</td>\n",
       "      <td>2474.330078</td>\n",
       "      <td>2531.939941</td>\n",
       "      <td>2531.939941</td>\n",
       "      <td>4.213410e+09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 Open_l       High_l        Low_l      Close_l  Adj Close_l  \\\n",
       "Date                                                                          \n",
       "2018-12-24          NaN          NaN          NaN          NaN          NaN   \n",
       "2018-12-26          NaN          NaN          NaN          NaN          NaN   \n",
       "2018-12-27          NaN          NaN          NaN          NaN          NaN   \n",
       "2018-12-28  2498.770020  2520.270020  2472.889893  2485.739990  2485.739990   \n",
       "2018-12-31  2498.939941  2509.239990  2482.820068  2506.850098  2506.850098   \n",
       "2019-01-02  2476.959961  2519.489990  2467.469971  2510.030029  2510.030029   \n",
       "2019-01-03  2491.919922  2493.139893  2443.959961  2447.889893  2447.889893   \n",
       "2019-01-04  2474.330078  2538.070068  2474.330078  2531.939941  2531.939941   \n",
       "\n",
       "                Volume_l       Open_r       High_r        Low_r      Close_r  \\\n",
       "Date                                                                           \n",
       "2018-12-24           NaN  2400.560059  2410.340088  2351.100098  2351.100098   \n",
       "2018-12-26           NaN  2363.120117  2467.760010  2346.580078  2467.699951   \n",
       "2018-12-27           NaN  2442.500000  2489.100098  2397.939941  2488.830078   \n",
       "2018-12-28  3.702620e+09  2498.770020  2520.270020  2472.889893  2485.739990   \n",
       "2018-12-31  3.442870e+09  2498.939941  2509.239990  2482.820068  2506.850098   \n",
       "2019-01-02  3.733160e+09          NaN          NaN          NaN          NaN   \n",
       "2019-01-03  3.822860e+09          NaN          NaN          NaN          NaN   \n",
       "2019-01-04  4.213410e+09          NaN          NaN          NaN          NaN   \n",
       "\n",
       "            Adj Close_r      Volume_r  \n",
       "Date                                   \n",
       "2018-12-24  2351.100098  2.613930e+09  \n",
       "2018-12-26  2467.699951  4.233990e+09  \n",
       "2018-12-27  2488.830078  4.096610e+09  \n",
       "2018-12-28  2485.739990  3.702620e+09  \n",
       "2018-12-31  2506.850098  3.442870e+09  \n",
       "2019-01-02          NaN           NaN  \n",
       "2019-01-03          NaN           NaN  \n",
       "2019-01-04          NaN           NaN  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "join_left = pd.DataFrame.join(sp0, sp1, how='left', lsuffix='_l', rsuffix='_r')\n",
    "join_right = pd.DataFrame.join(sp0, sp1, how='right', lsuffix='_l', rsuffix='_r')\n",
    "join_inner = pd.DataFrame.join(sp0, sp1, how='inner', lsuffix='_l', rsuffix='_r')\n",
    "join_outer = pd.DataFrame.join(sp0, sp1, how='outer', lsuffix='_l', rsuffix='_r')\n",
    "\n",
    "display(join_left, join_right, join_inner, join_outer)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6.1.2 Merging data\n",
    "\n",
    "A more convenient way is to use `pd.DataFrame.merge()`, as it can automatically detect columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-25T08:15:14.119669Z",
     "start_time": "2019-01-25T08:15:14.026483Z"
    }
   },
   "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>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>Adj Close</th>\n",
       "      <th>Volume</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2498.770020</td>\n",
       "      <td>2520.270020</td>\n",
       "      <td>2472.889893</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>3702620000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2498.939941</td>\n",
       "      <td>2509.239990</td>\n",
       "      <td>2482.820068</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>3442870000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2476.959961</td>\n",
       "      <td>2519.489990</td>\n",
       "      <td>2467.469971</td>\n",
       "      <td>2510.030029</td>\n",
       "      <td>2510.030029</td>\n",
       "      <td>3733160000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2491.919922</td>\n",
       "      <td>2493.139893</td>\n",
       "      <td>2443.959961</td>\n",
       "      <td>2447.889893</td>\n",
       "      <td>2447.889893</td>\n",
       "      <td>3822860000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2474.330078</td>\n",
       "      <td>2538.070068</td>\n",
       "      <td>2474.330078</td>\n",
       "      <td>2531.939941</td>\n",
       "      <td>2531.939941</td>\n",
       "      <td>4213410000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Open         High          Low        Close    Adj Close      Volume\n",
       "0  2498.770020  2520.270020  2472.889893  2485.739990  2485.739990  3702620000\n",
       "1  2498.939941  2509.239990  2482.820068  2506.850098  2506.850098  3442870000\n",
       "2  2476.959961  2519.489990  2467.469971  2510.030029  2510.030029  3733160000\n",
       "3  2491.919922  2493.139893  2443.959961  2447.889893  2447.889893  3822860000\n",
       "4  2474.330078  2538.070068  2474.330078  2531.939941  2531.939941  4213410000"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>Adj Close</th>\n",
       "      <th>Volume</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2498.770020</td>\n",
       "      <td>2520.270020</td>\n",
       "      <td>2472.889893</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>3702620000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2498.939941</td>\n",
       "      <td>2509.239990</td>\n",
       "      <td>2482.820068</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>3442870000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2400.560059</td>\n",
       "      <td>2410.340088</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2613930000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2363.120117</td>\n",
       "      <td>2467.760010</td>\n",
       "      <td>2346.580078</td>\n",
       "      <td>2467.699951</td>\n",
       "      <td>2467.699951</td>\n",
       "      <td>4233990000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2442.500000</td>\n",
       "      <td>2489.100098</td>\n",
       "      <td>2397.939941</td>\n",
       "      <td>2488.830078</td>\n",
       "      <td>2488.830078</td>\n",
       "      <td>4096610000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Open         High          Low        Close    Adj Close      Volume\n",
       "0  2498.770020  2520.270020  2472.889893  2485.739990  2485.739990  3702620000\n",
       "1  2498.939941  2509.239990  2482.820068  2506.850098  2506.850098  3442870000\n",
       "2  2400.560059  2410.340088  2351.100098  2351.100098  2351.100098  2613930000\n",
       "3  2363.120117  2467.760010  2346.580078  2467.699951  2467.699951  4233990000\n",
       "4  2442.500000  2489.100098  2397.939941  2488.830078  2488.830078  4096610000"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>Adj Close</th>\n",
       "      <th>Volume</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2498.770020</td>\n",
       "      <td>2520.27002</td>\n",
       "      <td>2472.889893</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>3702620000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2498.939941</td>\n",
       "      <td>2509.23999</td>\n",
       "      <td>2482.820068</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>3442870000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Open        High          Low        Close    Adj Close      Volume\n",
       "0  2498.770020  2520.27002  2472.889893  2485.739990  2485.739990  3702620000\n",
       "1  2498.939941  2509.23999  2482.820068  2506.850098  2506.850098  3442870000"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>Adj Close</th>\n",
       "      <th>Volume</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2498.770020</td>\n",
       "      <td>2520.270020</td>\n",
       "      <td>2472.889893</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>3702620000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2498.939941</td>\n",
       "      <td>2509.239990</td>\n",
       "      <td>2482.820068</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>3442870000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2476.959961</td>\n",
       "      <td>2519.489990</td>\n",
       "      <td>2467.469971</td>\n",
       "      <td>2510.030029</td>\n",
       "      <td>2510.030029</td>\n",
       "      <td>3733160000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2491.919922</td>\n",
       "      <td>2493.139893</td>\n",
       "      <td>2443.959961</td>\n",
       "      <td>2447.889893</td>\n",
       "      <td>2447.889893</td>\n",
       "      <td>3822860000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2474.330078</td>\n",
       "      <td>2538.070068</td>\n",
       "      <td>2474.330078</td>\n",
       "      <td>2531.939941</td>\n",
       "      <td>2531.939941</td>\n",
       "      <td>4213410000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2400.560059</td>\n",
       "      <td>2410.340088</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2613930000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2363.120117</td>\n",
       "      <td>2467.760010</td>\n",
       "      <td>2346.580078</td>\n",
       "      <td>2467.699951</td>\n",
       "      <td>2467.699951</td>\n",
       "      <td>4233990000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2442.500000</td>\n",
       "      <td>2489.100098</td>\n",
       "      <td>2397.939941</td>\n",
       "      <td>2488.830078</td>\n",
       "      <td>2488.830078</td>\n",
       "      <td>4096610000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Open         High          Low        Close    Adj Close      Volume\n",
       "0  2498.770020  2520.270020  2472.889893  2485.739990  2485.739990  3702620000\n",
       "1  2498.939941  2509.239990  2482.820068  2506.850098  2506.850098  3442870000\n",
       "2  2476.959961  2519.489990  2467.469971  2510.030029  2510.030029  3733160000\n",
       "3  2491.919922  2493.139893  2443.959961  2447.889893  2447.889893  3822860000\n",
       "4  2474.330078  2538.070068  2474.330078  2531.939941  2531.939941  4213410000\n",
       "5  2400.560059  2410.340088  2351.100098  2351.100098  2351.100098  2613930000\n",
       "6  2363.120117  2467.760010  2346.580078  2467.699951  2467.699951  4233990000\n",
       "7  2442.500000  2489.100098  2397.939941  2488.830078  2488.830078  4096610000"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "merge_left = pd.DataFrame.merge(sp0, sp1, how='left')\n",
    "merge_right = pd.DataFrame.merge(sp0, sp1, how='right')\n",
    "merge_inner = pd.DataFrame.merge(sp0, sp1, how='inner')\n",
    "merge_outer = pd.DataFrame.merge(sp0, sp1, how='outer')\n",
    "\n",
    "display(merge_left, merge_right, merge_inner, merge_outer)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "None of the examples above contains the original index column `Date`, because pdans does not know which index from the original DataFrame should be used. To solve this problem, we need:\n",
    "\n",
    "1. reset index first;\n",
    "2. merge DataFrames;\n",
    "3. apply the index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-25T08:15:14.209682Z",
     "start_time": "2019-01-25T08:15:14.123433Z"
    }
   },
   "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>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>Adj Close</th>\n",
       "      <th>Volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-12-28</th>\n",
       "      <td>2498.770020</td>\n",
       "      <td>2520.270020</td>\n",
       "      <td>2472.889893</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>3702620000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31</th>\n",
       "      <td>2498.939941</td>\n",
       "      <td>2509.239990</td>\n",
       "      <td>2482.820068</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>3442870000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-02</th>\n",
       "      <td>2476.959961</td>\n",
       "      <td>2519.489990</td>\n",
       "      <td>2467.469971</td>\n",
       "      <td>2510.030029</td>\n",
       "      <td>2510.030029</td>\n",
       "      <td>3733160000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-03</th>\n",
       "      <td>2491.919922</td>\n",
       "      <td>2493.139893</td>\n",
       "      <td>2443.959961</td>\n",
       "      <td>2447.889893</td>\n",
       "      <td>2447.889893</td>\n",
       "      <td>3822860000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-04</th>\n",
       "      <td>2474.330078</td>\n",
       "      <td>2538.070068</td>\n",
       "      <td>2474.330078</td>\n",
       "      <td>2531.939941</td>\n",
       "      <td>2531.939941</td>\n",
       "      <td>4213410000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Open         High          Low        Close    Adj Close  \\\n",
       "Date                                                                          \n",
       "2018-12-28  2498.770020  2520.270020  2472.889893  2485.739990  2485.739990   \n",
       "2018-12-31  2498.939941  2509.239990  2482.820068  2506.850098  2506.850098   \n",
       "2019-01-02  2476.959961  2519.489990  2467.469971  2510.030029  2510.030029   \n",
       "2019-01-03  2491.919922  2493.139893  2443.959961  2447.889893  2447.889893   \n",
       "2019-01-04  2474.330078  2538.070068  2474.330078  2531.939941  2531.939941   \n",
       "\n",
       "                Volume  \n",
       "Date                    \n",
       "2018-12-28  3702620000  \n",
       "2018-12-31  3442870000  \n",
       "2019-01-02  3733160000  \n",
       "2019-01-03  3822860000  \n",
       "2019-01-04  4213410000  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>Adj Close</th>\n",
       "      <th>Volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-12-28</th>\n",
       "      <td>2498.770020</td>\n",
       "      <td>2520.270020</td>\n",
       "      <td>2472.889893</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>3702620000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31</th>\n",
       "      <td>2498.939941</td>\n",
       "      <td>2509.239990</td>\n",
       "      <td>2482.820068</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>3442870000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-24</th>\n",
       "      <td>2400.560059</td>\n",
       "      <td>2410.340088</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2613930000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-26</th>\n",
       "      <td>2363.120117</td>\n",
       "      <td>2467.760010</td>\n",
       "      <td>2346.580078</td>\n",
       "      <td>2467.699951</td>\n",
       "      <td>2467.699951</td>\n",
       "      <td>4233990000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-27</th>\n",
       "      <td>2442.500000</td>\n",
       "      <td>2489.100098</td>\n",
       "      <td>2397.939941</td>\n",
       "      <td>2488.830078</td>\n",
       "      <td>2488.830078</td>\n",
       "      <td>4096610000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Open         High          Low        Close    Adj Close  \\\n",
       "Date                                                                          \n",
       "2018-12-28  2498.770020  2520.270020  2472.889893  2485.739990  2485.739990   \n",
       "2018-12-31  2498.939941  2509.239990  2482.820068  2506.850098  2506.850098   \n",
       "2018-12-24  2400.560059  2410.340088  2351.100098  2351.100098  2351.100098   \n",
       "2018-12-26  2363.120117  2467.760010  2346.580078  2467.699951  2467.699951   \n",
       "2018-12-27  2442.500000  2489.100098  2397.939941  2488.830078  2488.830078   \n",
       "\n",
       "                Volume  \n",
       "Date                    \n",
       "2018-12-28  3702620000  \n",
       "2018-12-31  3442870000  \n",
       "2018-12-24  2613930000  \n",
       "2018-12-26  4233990000  \n",
       "2018-12-27  4096610000  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>Adj Close</th>\n",
       "      <th>Volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-12-28</th>\n",
       "      <td>2498.770020</td>\n",
       "      <td>2520.27002</td>\n",
       "      <td>2472.889893</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>3702620000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31</th>\n",
       "      <td>2498.939941</td>\n",
       "      <td>2509.23999</td>\n",
       "      <td>2482.820068</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>3442870000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Open        High          Low        Close    Adj Close  \\\n",
       "Date                                                                         \n",
       "2018-12-28  2498.770020  2520.27002  2472.889893  2485.739990  2485.739990   \n",
       "2018-12-31  2498.939941  2509.23999  2482.820068  2506.850098  2506.850098   \n",
       "\n",
       "                Volume  \n",
       "Date                    \n",
       "2018-12-28  3702620000  \n",
       "2018-12-31  3442870000  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>Adj Close</th>\n",
       "      <th>Volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-12-28</th>\n",
       "      <td>2498.770020</td>\n",
       "      <td>2520.270020</td>\n",
       "      <td>2472.889893</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>3702620000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31</th>\n",
       "      <td>2498.939941</td>\n",
       "      <td>2509.239990</td>\n",
       "      <td>2482.820068</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>3442870000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-02</th>\n",
       "      <td>2476.959961</td>\n",
       "      <td>2519.489990</td>\n",
       "      <td>2467.469971</td>\n",
       "      <td>2510.030029</td>\n",
       "      <td>2510.030029</td>\n",
       "      <td>3733160000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-03</th>\n",
       "      <td>2491.919922</td>\n",
       "      <td>2493.139893</td>\n",
       "      <td>2443.959961</td>\n",
       "      <td>2447.889893</td>\n",
       "      <td>2447.889893</td>\n",
       "      <td>3822860000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-04</th>\n",
       "      <td>2474.330078</td>\n",
       "      <td>2538.070068</td>\n",
       "      <td>2474.330078</td>\n",
       "      <td>2531.939941</td>\n",
       "      <td>2531.939941</td>\n",
       "      <td>4213410000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-24</th>\n",
       "      <td>2400.560059</td>\n",
       "      <td>2410.340088</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2613930000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-26</th>\n",
       "      <td>2363.120117</td>\n",
       "      <td>2467.760010</td>\n",
       "      <td>2346.580078</td>\n",
       "      <td>2467.699951</td>\n",
       "      <td>2467.699951</td>\n",
       "      <td>4233990000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-27</th>\n",
       "      <td>2442.500000</td>\n",
       "      <td>2489.100098</td>\n",
       "      <td>2397.939941</td>\n",
       "      <td>2488.830078</td>\n",
       "      <td>2488.830078</td>\n",
       "      <td>4096610000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Open         High          Low        Close    Adj Close  \\\n",
       "Date                                                                          \n",
       "2018-12-28  2498.770020  2520.270020  2472.889893  2485.739990  2485.739990   \n",
       "2018-12-31  2498.939941  2509.239990  2482.820068  2506.850098  2506.850098   \n",
       "2019-01-02  2476.959961  2519.489990  2467.469971  2510.030029  2510.030029   \n",
       "2019-01-03  2491.919922  2493.139893  2443.959961  2447.889893  2447.889893   \n",
       "2019-01-04  2474.330078  2538.070068  2474.330078  2531.939941  2531.939941   \n",
       "2018-12-24  2400.560059  2410.340088  2351.100098  2351.100098  2351.100098   \n",
       "2018-12-26  2363.120117  2467.760010  2346.580078  2467.699951  2467.699951   \n",
       "2018-12-27  2442.500000  2489.100098  2397.939941  2488.830078  2488.830078   \n",
       "\n",
       "                Volume  \n",
       "Date                    \n",
       "2018-12-28  3702620000  \n",
       "2018-12-31  3442870000  \n",
       "2019-01-02  3733160000  \n",
       "2019-01-03  3822860000  \n",
       "2019-01-04  4213410000  \n",
       "2018-12-24  2613930000  \n",
       "2018-12-26  4233990000  \n",
       "2018-12-27  4096610000  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "merge_left = pd.DataFrame.merge(sp0.reset_index(), sp1, how='left').set_index('Date')\n",
    "merge_right = pd.DataFrame.merge(sp0, sp1.reset_index(), how='right').set_index('Date')\n",
    "merge_inner = pd.DataFrame.merge(sp0.reset_index(), sp1, how='inner').set_index('Date')\n",
    "merge_outer = pd.DataFrame.merge(sp0.reset_index(), sp1.reset_index(), how='outer').set_index('Date')\n",
    "\n",
    "display(merge_left, merge_right, merge_inner, merge_outer)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6.1.3 Difference between merge and join\n",
    "\n",
    "By defult, `pd.merge` automatically detects columns:\n",
    "\n",
    "    > If joining columns on columns, the DataFrame indexes *will be ignored*.\n",
    "\n",
    "while `pd.join` automatically detects index.\n",
    "\n",
    "    > If you are joining on index only, you may wish to use DataFrame.join to save yourself some typing.\n",
    "    \n",
    "However, `pd.join` is faster than `pd.merge`, so choose accoridng to your needs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-25T08:15:31.676929Z",
     "start_time": "2019-01-25T08:15:14.212936Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1.29 ms ± 234 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)\n",
      "2.58 ms ± 65 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)\n",
      "4.3 ms ± 61.6 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)\n"
     ]
    }
   ],
   "source": [
    "%timeit join_inner = pd.DataFrame.join(sp0, sp1, how='inner', lsuffix='_l', rsuffix='_r')\n",
    "%timeit merge_inner = pd.DataFrame.merge(sp0, sp1, how='inner')\n",
    "%timeit merge_inner = pd.DataFrame.merge(sp0.reset_index(), sp1, how='inner').set_index('Date')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 6.2 Concatenating data\n",
    "\n",
    "Concatenation in pandas is the process of either adding rows to the end of an existing Series or DataFrame object or adding additional columns to a DataFrame.\n",
    "\n",
    "1. Concatenating automatically detects common columns names;\n",
    "\n",
    "2. If a column in the result does not exist in the object being copied, NaN values will be flled in those locations. \n",
    "\n",
    "3. Duplicate row index labels can occur.\n",
    "\n",
    "4. A concatenation of two or more DataFrame objects actually performs an __outer__ join operation along the index labels on the axis opposite to the one specifed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-25T08:15:31.762117Z",
     "start_time": "2019-01-25T08:15:31.679338Z"
    }
   },
   "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>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>Adj Close</th>\n",
       "      <th>Volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-12-28</th>\n",
       "      <td>2498.770020</td>\n",
       "      <td>2520.270020</td>\n",
       "      <td>2472.889893</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>3702620000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31</th>\n",
       "      <td>2498.939941</td>\n",
       "      <td>2509.239990</td>\n",
       "      <td>2482.820068</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>3442870000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-02</th>\n",
       "      <td>2476.959961</td>\n",
       "      <td>2519.489990</td>\n",
       "      <td>2467.469971</td>\n",
       "      <td>2510.030029</td>\n",
       "      <td>2510.030029</td>\n",
       "      <td>3733160000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-03</th>\n",
       "      <td>2491.919922</td>\n",
       "      <td>2493.139893</td>\n",
       "      <td>2443.959961</td>\n",
       "      <td>2447.889893</td>\n",
       "      <td>2447.889893</td>\n",
       "      <td>3822860000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-04</th>\n",
       "      <td>2474.330078</td>\n",
       "      <td>2538.070068</td>\n",
       "      <td>2474.330078</td>\n",
       "      <td>2531.939941</td>\n",
       "      <td>2531.939941</td>\n",
       "      <td>4213410000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-24</th>\n",
       "      <td>2400.560059</td>\n",
       "      <td>2410.340088</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2613930000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-26</th>\n",
       "      <td>2363.120117</td>\n",
       "      <td>2467.760010</td>\n",
       "      <td>2346.580078</td>\n",
       "      <td>2467.699951</td>\n",
       "      <td>2467.699951</td>\n",
       "      <td>4233990000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-27</th>\n",
       "      <td>2442.500000</td>\n",
       "      <td>2489.100098</td>\n",
       "      <td>2397.939941</td>\n",
       "      <td>2488.830078</td>\n",
       "      <td>2488.830078</td>\n",
       "      <td>4096610000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-28</th>\n",
       "      <td>2498.770020</td>\n",
       "      <td>2520.270020</td>\n",
       "      <td>2472.889893</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>3702620000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31</th>\n",
       "      <td>2498.939941</td>\n",
       "      <td>2509.239990</td>\n",
       "      <td>2482.820068</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>3442870000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Open         High          Low        Close    Adj Close  \\\n",
       "Date                                                                          \n",
       "2018-12-28  2498.770020  2520.270020  2472.889893  2485.739990  2485.739990   \n",
       "2018-12-31  2498.939941  2509.239990  2482.820068  2506.850098  2506.850098   \n",
       "2019-01-02  2476.959961  2519.489990  2467.469971  2510.030029  2510.030029   \n",
       "2019-01-03  2491.919922  2493.139893  2443.959961  2447.889893  2447.889893   \n",
       "2019-01-04  2474.330078  2538.070068  2474.330078  2531.939941  2531.939941   \n",
       "2018-12-24  2400.560059  2410.340088  2351.100098  2351.100098  2351.100098   \n",
       "2018-12-26  2363.120117  2467.760010  2346.580078  2467.699951  2467.699951   \n",
       "2018-12-27  2442.500000  2489.100098  2397.939941  2488.830078  2488.830078   \n",
       "2018-12-28  2498.770020  2520.270020  2472.889893  2485.739990  2485.739990   \n",
       "2018-12-31  2498.939941  2509.239990  2482.820068  2506.850098  2506.850098   \n",
       "\n",
       "                Volume  \n",
       "Date                    \n",
       "2018-12-28  3702620000  \n",
       "2018-12-31  3442870000  \n",
       "2019-01-02  3733160000  \n",
       "2019-01-03  3822860000  \n",
       "2019-01-04  4213410000  \n",
       "2018-12-24  2613930000  \n",
       "2018-12-26  4233990000  \n",
       "2018-12-27  4096610000  \n",
       "2018-12-28  3702620000  \n",
       "2018-12-31  3442870000  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>Adj Close</th>\n",
       "      <th>Volume</th>\n",
       "      <th>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>Adj Close</th>\n",
       "      <th>Volume</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-12-28</th>\n",
       "      <td>2498.770020</td>\n",
       "      <td>2520.270020</td>\n",
       "      <td>2472.889893</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>3.702620e+09</td>\n",
       "      <td>2498.770020</td>\n",
       "      <td>2520.270020</td>\n",
       "      <td>2472.889893</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>3.702620e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31</th>\n",
       "      <td>2498.939941</td>\n",
       "      <td>2509.239990</td>\n",
       "      <td>2482.820068</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>3.442870e+09</td>\n",
       "      <td>2498.939941</td>\n",
       "      <td>2509.239990</td>\n",
       "      <td>2482.820068</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>3.442870e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-02</th>\n",
       "      <td>2476.959961</td>\n",
       "      <td>2519.489990</td>\n",
       "      <td>2467.469971</td>\n",
       "      <td>2510.030029</td>\n",
       "      <td>2510.030029</td>\n",
       "      <td>3.733160e+09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-03</th>\n",
       "      <td>2491.919922</td>\n",
       "      <td>2493.139893</td>\n",
       "      <td>2443.959961</td>\n",
       "      <td>2447.889893</td>\n",
       "      <td>2447.889893</td>\n",
       "      <td>3.822860e+09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-04</th>\n",
       "      <td>2474.330078</td>\n",
       "      <td>2538.070068</td>\n",
       "      <td>2474.330078</td>\n",
       "      <td>2531.939941</td>\n",
       "      <td>2531.939941</td>\n",
       "      <td>4.213410e+09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-24</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2400.560059</td>\n",
       "      <td>2410.340088</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2.613930e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-26</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2363.120117</td>\n",
       "      <td>2467.760010</td>\n",
       "      <td>2346.580078</td>\n",
       "      <td>2467.699951</td>\n",
       "      <td>2467.699951</td>\n",
       "      <td>4.233990e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-27</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2442.500000</td>\n",
       "      <td>2489.100098</td>\n",
       "      <td>2397.939941</td>\n",
       "      <td>2488.830078</td>\n",
       "      <td>2488.830078</td>\n",
       "      <td>4.096610e+09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Open         High          Low        Close    Adj Close  \\\n",
       "2018-12-28  2498.770020  2520.270020  2472.889893  2485.739990  2485.739990   \n",
       "2018-12-31  2498.939941  2509.239990  2482.820068  2506.850098  2506.850098   \n",
       "2019-01-02  2476.959961  2519.489990  2467.469971  2510.030029  2510.030029   \n",
       "2019-01-03  2491.919922  2493.139893  2443.959961  2447.889893  2447.889893   \n",
       "2019-01-04  2474.330078  2538.070068  2474.330078  2531.939941  2531.939941   \n",
       "2018-12-24          NaN          NaN          NaN          NaN          NaN   \n",
       "2018-12-26          NaN          NaN          NaN          NaN          NaN   \n",
       "2018-12-27          NaN          NaN          NaN          NaN          NaN   \n",
       "\n",
       "                  Volume         Open         High          Low        Close  \\\n",
       "2018-12-28  3.702620e+09  2498.770020  2520.270020  2472.889893  2485.739990   \n",
       "2018-12-31  3.442870e+09  2498.939941  2509.239990  2482.820068  2506.850098   \n",
       "2019-01-02  3.733160e+09          NaN          NaN          NaN          NaN   \n",
       "2019-01-03  3.822860e+09          NaN          NaN          NaN          NaN   \n",
       "2019-01-04  4.213410e+09          NaN          NaN          NaN          NaN   \n",
       "2018-12-24           NaN  2400.560059  2410.340088  2351.100098  2351.100098   \n",
       "2018-12-26           NaN  2363.120117  2467.760010  2346.580078  2467.699951   \n",
       "2018-12-27           NaN  2442.500000  2489.100098  2397.939941  2488.830078   \n",
       "\n",
       "              Adj Close        Volume  \n",
       "2018-12-28  2485.739990  3.702620e+09  \n",
       "2018-12-31  2506.850098  3.442870e+09  \n",
       "2019-01-02          NaN           NaN  \n",
       "2019-01-03          NaN           NaN  \n",
       "2019-01-04          NaN           NaN  \n",
       "2018-12-24  2351.100098  2.613930e+09  \n",
       "2018-12-26  2467.699951  4.233990e+09  \n",
       "2018-12-27  2488.830078  4.096610e+09  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "cct1 = pd.concat([sp0, sp1])\n",
    "cct2 = pd.concat([sp0, sp1], axis=1, sort=False)\n",
    "\n",
    "display(cct1, cct2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6.2.2 append data\n",
    "\n",
    "Use `pd.append()`, if you would like to:\n",
    "\n",
    "1. concatenate the two specifed DataFrame objects along the row index labels.\n",
    "\n",
    "2. ensure that the resulting index does not have duplicates but preserves all of the rows, you can use the `ignore_index=True` parameter."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-25T08:15:31.808191Z",
     "start_time": "2019-01-25T08:15:31.765774Z"
    }
   },
   "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>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>Adj Close</th>\n",
       "      <th>Volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-12-28</th>\n",
       "      <td>2498.770020</td>\n",
       "      <td>2520.270020</td>\n",
       "      <td>2472.889893</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>3702620000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31</th>\n",
       "      <td>2498.939941</td>\n",
       "      <td>2509.239990</td>\n",
       "      <td>2482.820068</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>3442870000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-02</th>\n",
       "      <td>2476.959961</td>\n",
       "      <td>2519.489990</td>\n",
       "      <td>2467.469971</td>\n",
       "      <td>2510.030029</td>\n",
       "      <td>2510.030029</td>\n",
       "      <td>3733160000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-03</th>\n",
       "      <td>2491.919922</td>\n",
       "      <td>2493.139893</td>\n",
       "      <td>2443.959961</td>\n",
       "      <td>2447.889893</td>\n",
       "      <td>2447.889893</td>\n",
       "      <td>3822860000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-04</th>\n",
       "      <td>2474.330078</td>\n",
       "      <td>2538.070068</td>\n",
       "      <td>2474.330078</td>\n",
       "      <td>2531.939941</td>\n",
       "      <td>2531.939941</td>\n",
       "      <td>4213410000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-24</th>\n",
       "      <td>2400.560059</td>\n",
       "      <td>2410.340088</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2613930000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-26</th>\n",
       "      <td>2363.120117</td>\n",
       "      <td>2467.760010</td>\n",
       "      <td>2346.580078</td>\n",
       "      <td>2467.699951</td>\n",
       "      <td>2467.699951</td>\n",
       "      <td>4233990000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-27</th>\n",
       "      <td>2442.500000</td>\n",
       "      <td>2489.100098</td>\n",
       "      <td>2397.939941</td>\n",
       "      <td>2488.830078</td>\n",
       "      <td>2488.830078</td>\n",
       "      <td>4096610000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-28</th>\n",
       "      <td>2498.770020</td>\n",
       "      <td>2520.270020</td>\n",
       "      <td>2472.889893</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>3702620000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31</th>\n",
       "      <td>2498.939941</td>\n",
       "      <td>2509.239990</td>\n",
       "      <td>2482.820068</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>3442870000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Open         High          Low        Close    Adj Close  \\\n",
       "Date                                                                          \n",
       "2018-12-28  2498.770020  2520.270020  2472.889893  2485.739990  2485.739990   \n",
       "2018-12-31  2498.939941  2509.239990  2482.820068  2506.850098  2506.850098   \n",
       "2019-01-02  2476.959961  2519.489990  2467.469971  2510.030029  2510.030029   \n",
       "2019-01-03  2491.919922  2493.139893  2443.959961  2447.889893  2447.889893   \n",
       "2019-01-04  2474.330078  2538.070068  2474.330078  2531.939941  2531.939941   \n",
       "2018-12-24  2400.560059  2410.340088  2351.100098  2351.100098  2351.100098   \n",
       "2018-12-26  2363.120117  2467.760010  2346.580078  2467.699951  2467.699951   \n",
       "2018-12-27  2442.500000  2489.100098  2397.939941  2488.830078  2488.830078   \n",
       "2018-12-28  2498.770020  2520.270020  2472.889893  2485.739990  2485.739990   \n",
       "2018-12-31  2498.939941  2509.239990  2482.820068  2506.850098  2506.850098   \n",
       "\n",
       "                Volume  \n",
       "Date                    \n",
       "2018-12-28  3702620000  \n",
       "2018-12-31  3442870000  \n",
       "2019-01-02  3733160000  \n",
       "2019-01-03  3822860000  \n",
       "2019-01-04  4213410000  \n",
       "2018-12-24  2613930000  \n",
       "2018-12-26  4233990000  \n",
       "2018-12-27  4096610000  \n",
       "2018-12-28  3702620000  \n",
       "2018-12-31  3442870000  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>Adj Close</th>\n",
       "      <th>Volume</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2498.770020</td>\n",
       "      <td>2520.270020</td>\n",
       "      <td>2472.889893</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>3702620000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2498.939941</td>\n",
       "      <td>2509.239990</td>\n",
       "      <td>2482.820068</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>3442870000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2476.959961</td>\n",
       "      <td>2519.489990</td>\n",
       "      <td>2467.469971</td>\n",
       "      <td>2510.030029</td>\n",
       "      <td>2510.030029</td>\n",
       "      <td>3733160000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2491.919922</td>\n",
       "      <td>2493.139893</td>\n",
       "      <td>2443.959961</td>\n",
       "      <td>2447.889893</td>\n",
       "      <td>2447.889893</td>\n",
       "      <td>3822860000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2474.330078</td>\n",
       "      <td>2538.070068</td>\n",
       "      <td>2474.330078</td>\n",
       "      <td>2531.939941</td>\n",
       "      <td>2531.939941</td>\n",
       "      <td>4213410000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2400.560059</td>\n",
       "      <td>2410.340088</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2613930000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2363.120117</td>\n",
       "      <td>2467.760010</td>\n",
       "      <td>2346.580078</td>\n",
       "      <td>2467.699951</td>\n",
       "      <td>2467.699951</td>\n",
       "      <td>4233990000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2442.500000</td>\n",
       "      <td>2489.100098</td>\n",
       "      <td>2397.939941</td>\n",
       "      <td>2488.830078</td>\n",
       "      <td>2488.830078</td>\n",
       "      <td>4096610000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2498.770020</td>\n",
       "      <td>2520.270020</td>\n",
       "      <td>2472.889893</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>3702620000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2498.939941</td>\n",
       "      <td>2509.239990</td>\n",
       "      <td>2482.820068</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>3442870000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Open         High          Low        Close    Adj Close      Volume\n",
       "0  2498.770020  2520.270020  2472.889893  2485.739990  2485.739990  3702620000\n",
       "1  2498.939941  2509.239990  2482.820068  2506.850098  2506.850098  3442870000\n",
       "2  2476.959961  2519.489990  2467.469971  2510.030029  2510.030029  3733160000\n",
       "3  2491.919922  2493.139893  2443.959961  2447.889893  2447.889893  3822860000\n",
       "4  2474.330078  2538.070068  2474.330078  2531.939941  2531.939941  4213410000\n",
       "5  2400.560059  2410.340088  2351.100098  2351.100098  2351.100098  2613930000\n",
       "6  2363.120117  2467.760010  2346.580078  2467.699951  2467.699951  4233990000\n",
       "7  2442.500000  2489.100098  2397.939941  2488.830078  2488.830078  4096610000\n",
       "8  2498.770020  2520.270020  2472.889893  2485.739990  2485.739990  3702620000\n",
       "9  2498.939941  2509.239990  2482.820068  2506.850098  2506.850098  3442870000"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>Adj Close</th>\n",
       "      <th>Volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-12-24</th>\n",
       "      <td>2400.560059</td>\n",
       "      <td>2410.340088</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2613930000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-26</th>\n",
       "      <td>2363.120117</td>\n",
       "      <td>2467.760010</td>\n",
       "      <td>2346.580078</td>\n",
       "      <td>2467.699951</td>\n",
       "      <td>2467.699951</td>\n",
       "      <td>4233990000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-27</th>\n",
       "      <td>2442.500000</td>\n",
       "      <td>2489.100098</td>\n",
       "      <td>2397.939941</td>\n",
       "      <td>2488.830078</td>\n",
       "      <td>2488.830078</td>\n",
       "      <td>4096610000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-28</th>\n",
       "      <td>2498.770020</td>\n",
       "      <td>2520.270020</td>\n",
       "      <td>2472.889893</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>3702620000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31</th>\n",
       "      <td>2498.939941</td>\n",
       "      <td>2509.239990</td>\n",
       "      <td>2482.820068</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>3442870000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-02</th>\n",
       "      <td>2476.959961</td>\n",
       "      <td>2519.489990</td>\n",
       "      <td>2467.469971</td>\n",
       "      <td>2510.030029</td>\n",
       "      <td>2510.030029</td>\n",
       "      <td>3733160000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-03</th>\n",
       "      <td>2491.919922</td>\n",
       "      <td>2493.139893</td>\n",
       "      <td>2443.959961</td>\n",
       "      <td>2447.889893</td>\n",
       "      <td>2447.889893</td>\n",
       "      <td>3822860000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-04</th>\n",
       "      <td>2474.330078</td>\n",
       "      <td>2538.070068</td>\n",
       "      <td>2474.330078</td>\n",
       "      <td>2531.939941</td>\n",
       "      <td>2531.939941</td>\n",
       "      <td>4213410000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Open         High          Low        Close    Adj Close  \\\n",
       "Date                                                                          \n",
       "2018-12-24  2400.560059  2410.340088  2351.100098  2351.100098  2351.100098   \n",
       "2018-12-26  2363.120117  2467.760010  2346.580078  2467.699951  2467.699951   \n",
       "2018-12-27  2442.500000  2489.100098  2397.939941  2488.830078  2488.830078   \n",
       "2018-12-28  2498.770020  2520.270020  2472.889893  2485.739990  2485.739990   \n",
       "2018-12-31  2498.939941  2509.239990  2482.820068  2506.850098  2506.850098   \n",
       "2019-01-02  2476.959961  2519.489990  2467.469971  2510.030029  2510.030029   \n",
       "2019-01-03  2491.919922  2493.139893  2443.959961  2447.889893  2447.889893   \n",
       "2019-01-04  2474.330078  2538.070068  2474.330078  2531.939941  2531.939941   \n",
       "\n",
       "                Volume  \n",
       "Date                    \n",
       "2018-12-24  2613930000  \n",
       "2018-12-26  4233990000  \n",
       "2018-12-27  4096610000  \n",
       "2018-12-28  3702620000  \n",
       "2018-12-31  3442870000  \n",
       "2019-01-02  3733160000  \n",
       "2019-01-03  3822860000  \n",
       "2019-01-04  4213410000  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "apd0 = sp0.append(sp1)\n",
    "apd1 = sp0.append(sp1, ignore_index=True)\n",
    "apd2 = sp0.append(sp1).drop_duplicates().sort_index()\n",
    "\n",
    "display(apd0, apd1, apd2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6.2.3 Performance comparison of concat, merge and append\n",
    "\n",
    "In the following examples, `pd.concat` and `pd.append` have similar performance. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-25T08:15:39.194004Z",
     "start_time": "2019-01-25T08:15:31.810878Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "444 µs ± 5.33 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)\n",
      "448 µs ± 9.52 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)\n"
     ]
    }
   ],
   "source": [
    "%timeit cct1 = pd.concat([sp0, sp1])\n",
    "%timeit apd2 = sp0.append(sp1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-25T08:15:53.011985Z",
     "start_time": "2019-01-25T08:15:39.196313Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "8.27 ms ± 21.6 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)\n",
      "6.68 ms ± 3.68 ms per loop (mean ± std. dev. of 7 runs, 100 loops each)\n",
      "2.3 ms ± 85.2 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)\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>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>Adj Close</th>\n",
       "      <th>Volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-12-28</th>\n",
       "      <td>2498.770020</td>\n",
       "      <td>2520.270020</td>\n",
       "      <td>2472.889893</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>3702620000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31</th>\n",
       "      <td>2498.939941</td>\n",
       "      <td>2509.239990</td>\n",
       "      <td>2482.820068</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>3442870000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-02</th>\n",
       "      <td>2476.959961</td>\n",
       "      <td>2519.489990</td>\n",
       "      <td>2467.469971</td>\n",
       "      <td>2510.030029</td>\n",
       "      <td>2510.030029</td>\n",
       "      <td>3733160000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-03</th>\n",
       "      <td>2491.919922</td>\n",
       "      <td>2493.139893</td>\n",
       "      <td>2443.959961</td>\n",
       "      <td>2447.889893</td>\n",
       "      <td>2447.889893</td>\n",
       "      <td>3822860000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-04</th>\n",
       "      <td>2474.330078</td>\n",
       "      <td>2538.070068</td>\n",
       "      <td>2474.330078</td>\n",
       "      <td>2531.939941</td>\n",
       "      <td>2531.939941</td>\n",
       "      <td>4213410000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-24</th>\n",
       "      <td>2400.560059</td>\n",
       "      <td>2410.340088</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2613930000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-26</th>\n",
       "      <td>2363.120117</td>\n",
       "      <td>2467.760010</td>\n",
       "      <td>2346.580078</td>\n",
       "      <td>2467.699951</td>\n",
       "      <td>2467.699951</td>\n",
       "      <td>4233990000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-27</th>\n",
       "      <td>2442.500000</td>\n",
       "      <td>2489.100098</td>\n",
       "      <td>2397.939941</td>\n",
       "      <td>2488.830078</td>\n",
       "      <td>2488.830078</td>\n",
       "      <td>4096610000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Open         High          Low        Close    Adj Close  \\\n",
       "Date                                                                          \n",
       "2018-12-28  2498.770020  2520.270020  2472.889893  2485.739990  2485.739990   \n",
       "2018-12-31  2498.939941  2509.239990  2482.820068  2506.850098  2506.850098   \n",
       "2019-01-02  2476.959961  2519.489990  2467.469971  2510.030029  2510.030029   \n",
       "2019-01-03  2491.919922  2493.139893  2443.959961  2447.889893  2447.889893   \n",
       "2019-01-04  2474.330078  2538.070068  2474.330078  2531.939941  2531.939941   \n",
       "2018-12-24  2400.560059  2410.340088  2351.100098  2351.100098  2351.100098   \n",
       "2018-12-26  2363.120117  2467.760010  2346.580078  2467.699951  2467.699951   \n",
       "2018-12-27  2442.500000  2489.100098  2397.939941  2488.830078  2488.830078   \n",
       "\n",
       "                Volume  \n",
       "Date                    \n",
       "2018-12-28  3702620000  \n",
       "2018-12-31  3442870000  \n",
       "2019-01-02  3733160000  \n",
       "2019-01-03  3822860000  \n",
       "2019-01-04  4213410000  \n",
       "2018-12-24  2613930000  \n",
       "2018-12-26  4233990000  \n",
       "2018-12-27  4096610000  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>Adj Close</th>\n",
       "      <th>Volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-12-28</th>\n",
       "      <td>2498.770020</td>\n",
       "      <td>2520.270020</td>\n",
       "      <td>2472.889893</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>3702620000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31</th>\n",
       "      <td>2498.939941</td>\n",
       "      <td>2509.239990</td>\n",
       "      <td>2482.820068</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>3442870000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-02</th>\n",
       "      <td>2476.959961</td>\n",
       "      <td>2519.489990</td>\n",
       "      <td>2467.469971</td>\n",
       "      <td>2510.030029</td>\n",
       "      <td>2510.030029</td>\n",
       "      <td>3733160000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-03</th>\n",
       "      <td>2491.919922</td>\n",
       "      <td>2493.139893</td>\n",
       "      <td>2443.959961</td>\n",
       "      <td>2447.889893</td>\n",
       "      <td>2447.889893</td>\n",
       "      <td>3822860000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-04</th>\n",
       "      <td>2474.330078</td>\n",
       "      <td>2538.070068</td>\n",
       "      <td>2474.330078</td>\n",
       "      <td>2531.939941</td>\n",
       "      <td>2531.939941</td>\n",
       "      <td>4213410000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-24</th>\n",
       "      <td>2400.560059</td>\n",
       "      <td>2410.340088</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2613930000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-26</th>\n",
       "      <td>2363.120117</td>\n",
       "      <td>2467.760010</td>\n",
       "      <td>2346.580078</td>\n",
       "      <td>2467.699951</td>\n",
       "      <td>2467.699951</td>\n",
       "      <td>4233990000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-27</th>\n",
       "      <td>2442.500000</td>\n",
       "      <td>2489.100098</td>\n",
       "      <td>2397.939941</td>\n",
       "      <td>2488.830078</td>\n",
       "      <td>2488.830078</td>\n",
       "      <td>4096610000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-28</th>\n",
       "      <td>2498.770020</td>\n",
       "      <td>2520.270020</td>\n",
       "      <td>2472.889893</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>3702620000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31</th>\n",
       "      <td>2498.939941</td>\n",
       "      <td>2509.239990</td>\n",
       "      <td>2482.820068</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>3442870000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Open         High          Low        Close    Adj Close  \\\n",
       "Date                                                                          \n",
       "2018-12-28  2498.770020  2520.270020  2472.889893  2485.739990  2485.739990   \n",
       "2018-12-31  2498.939941  2509.239990  2482.820068  2506.850098  2506.850098   \n",
       "2019-01-02  2476.959961  2519.489990  2467.469971  2510.030029  2510.030029   \n",
       "2019-01-03  2491.919922  2493.139893  2443.959961  2447.889893  2447.889893   \n",
       "2019-01-04  2474.330078  2538.070068  2474.330078  2531.939941  2531.939941   \n",
       "2018-12-24  2400.560059  2410.340088  2351.100098  2351.100098  2351.100098   \n",
       "2018-12-26  2363.120117  2467.760010  2346.580078  2467.699951  2467.699951   \n",
       "2018-12-27  2442.500000  2489.100098  2397.939941  2488.830078  2488.830078   \n",
       "2018-12-28  2498.770020  2520.270020  2472.889893  2485.739990  2485.739990   \n",
       "2018-12-31  2498.939941  2509.239990  2482.820068  2506.850098  2506.850098   \n",
       "\n",
       "                Volume  \n",
       "Date                    \n",
       "2018-12-28  3702620000  \n",
       "2018-12-31  3442870000  \n",
       "2019-01-02  3733160000  \n",
       "2019-01-03  3822860000  \n",
       "2019-01-04  4213410000  \n",
       "2018-12-24  2613930000  \n",
       "2018-12-26  4233990000  \n",
       "2018-12-27  4096610000  \n",
       "2018-12-28  3702620000  \n",
       "2018-12-31  3442870000  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>Adj Close</th>\n",
       "      <th>Volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-12-24</th>\n",
       "      <td>2400.560059</td>\n",
       "      <td>2410.340088</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2351.100098</td>\n",
       "      <td>2613930000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-26</th>\n",
       "      <td>2363.120117</td>\n",
       "      <td>2467.760010</td>\n",
       "      <td>2346.580078</td>\n",
       "      <td>2467.699951</td>\n",
       "      <td>2467.699951</td>\n",
       "      <td>4233990000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-27</th>\n",
       "      <td>2442.500000</td>\n",
       "      <td>2489.100098</td>\n",
       "      <td>2397.939941</td>\n",
       "      <td>2488.830078</td>\n",
       "      <td>2488.830078</td>\n",
       "      <td>4096610000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-28</th>\n",
       "      <td>2498.770020</td>\n",
       "      <td>2520.270020</td>\n",
       "      <td>2472.889893</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>3702620000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31</th>\n",
       "      <td>2498.939941</td>\n",
       "      <td>2509.239990</td>\n",
       "      <td>2482.820068</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>3442870000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-02</th>\n",
       "      <td>2476.959961</td>\n",
       "      <td>2519.489990</td>\n",
       "      <td>2467.469971</td>\n",
       "      <td>2510.030029</td>\n",
       "      <td>2510.030029</td>\n",
       "      <td>3733160000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-03</th>\n",
       "      <td>2491.919922</td>\n",
       "      <td>2493.139893</td>\n",
       "      <td>2443.959961</td>\n",
       "      <td>2447.889893</td>\n",
       "      <td>2447.889893</td>\n",
       "      <td>3822860000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-04</th>\n",
       "      <td>2474.330078</td>\n",
       "      <td>2538.070068</td>\n",
       "      <td>2474.330078</td>\n",
       "      <td>2531.939941</td>\n",
       "      <td>2531.939941</td>\n",
       "      <td>4213410000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Open         High          Low        Close    Adj Close  \\\n",
       "Date                                                                          \n",
       "2018-12-24  2400.560059  2410.340088  2351.100098  2351.100098  2351.100098   \n",
       "2018-12-26  2363.120117  2467.760010  2346.580078  2467.699951  2467.699951   \n",
       "2018-12-27  2442.500000  2489.100098  2397.939941  2488.830078  2488.830078   \n",
       "2018-12-28  2498.770020  2520.270020  2472.889893  2485.739990  2485.739990   \n",
       "2018-12-31  2498.939941  2509.239990  2482.820068  2506.850098  2506.850098   \n",
       "2019-01-02  2476.959961  2519.489990  2467.469971  2510.030029  2510.030029   \n",
       "2019-01-03  2491.919922  2493.139893  2443.959961  2447.889893  2447.889893   \n",
       "2019-01-04  2474.330078  2538.070068  2474.330078  2531.939941  2531.939941   \n",
       "\n",
       "                Volume  \n",
       "Date                    \n",
       "2018-12-24  2613930000  \n",
       "2018-12-26  4233990000  \n",
       "2018-12-27  4096610000  \n",
       "2018-12-28  3702620000  \n",
       "2018-12-31  3442870000  \n",
       "2019-01-02  3733160000  \n",
       "2019-01-03  3822860000  \n",
       "2019-01-04  4213410000  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%timeit merge_outer = pd.DataFrame.merge(sp0.reset_index(), sp1.reset_index(), how='outer').set_index('Date')\n",
    "%timeit cct1 = pd.concat([sp0, sp1]).reset_index().drop_duplicates().set_index('Date').sort_index()\n",
    "%timeit apd2 = sp0.append(sp1).drop_duplicates().sort_index()\n",
    "\n",
    "display(merge_outer, cct1, apd2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 6.3 Stacking and unstacking data\n",
    "\n",
    "1. Stacking pivots a level of column labels to the row index. \n",
    "\n",
    "2. Unstacking performs the opposite, pivoting a level of the row index into the column index.\n",
    "\n",
    "3. Stacking and unstacking do not lose any information, but change the means by which it is organized and accessed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-25T08:15:53.059000Z",
     "start_time": "2019-01-25T08:15:53.022843Z"
    }
   },
   "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>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>Adj Close</th>\n",
       "      <th>Volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-12-28</th>\n",
       "      <td>2498.770020</td>\n",
       "      <td>2520.270020</td>\n",
       "      <td>2472.889893</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>3702620000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31</th>\n",
       "      <td>2498.939941</td>\n",
       "      <td>2509.239990</td>\n",
       "      <td>2482.820068</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>3442870000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-02</th>\n",
       "      <td>2476.959961</td>\n",
       "      <td>2519.489990</td>\n",
       "      <td>2467.469971</td>\n",
       "      <td>2510.030029</td>\n",
       "      <td>2510.030029</td>\n",
       "      <td>3733160000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-03</th>\n",
       "      <td>2491.919922</td>\n",
       "      <td>2493.139893</td>\n",
       "      <td>2443.959961</td>\n",
       "      <td>2447.889893</td>\n",
       "      <td>2447.889893</td>\n",
       "      <td>3822860000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-04</th>\n",
       "      <td>2474.330078</td>\n",
       "      <td>2538.070068</td>\n",
       "      <td>2474.330078</td>\n",
       "      <td>2531.939941</td>\n",
       "      <td>2531.939941</td>\n",
       "      <td>4213410000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Open         High          Low        Close    Adj Close  \\\n",
       "Date                                                                          \n",
       "2018-12-28  2498.770020  2520.270020  2472.889893  2485.739990  2485.739990   \n",
       "2018-12-31  2498.939941  2509.239990  2482.820068  2506.850098  2506.850098   \n",
       "2019-01-02  2476.959961  2519.489990  2467.469971  2510.030029  2510.030029   \n",
       "2019-01-03  2491.919922  2493.139893  2443.959961  2447.889893  2447.889893   \n",
       "2019-01-04  2474.330078  2538.070068  2474.330078  2531.939941  2531.939941   \n",
       "\n",
       "                Volume  \n",
       "Date                    \n",
       "2018-12-28  3702620000  \n",
       "2018-12-31  3442870000  \n",
       "2019-01-02  3733160000  \n",
       "2019-01-03  3822860000  \n",
       "2019-01-04  4213410000  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "Date                 \n",
       "2018-12-28  Open         2.498770e+03\n",
       "            High         2.520270e+03\n",
       "            Low          2.472890e+03\n",
       "            Close        2.485740e+03\n",
       "            Adj Close    2.485740e+03\n",
       "            Volume       3.702620e+09\n",
       "2018-12-31  Open         2.498940e+03\n",
       "            High         2.509240e+03\n",
       "            Low          2.482820e+03\n",
       "            Close        2.506850e+03\n",
       "            Adj Close    2.506850e+03\n",
       "            Volume       3.442870e+09\n",
       "2019-01-02  Open         2.476960e+03\n",
       "            High         2.519490e+03\n",
       "            Low          2.467470e+03\n",
       "            Close        2.510030e+03\n",
       "            Adj Close    2.510030e+03\n",
       "            Volume       3.733160e+09\n",
       "2019-01-03  Open         2.491920e+03\n",
       "            High         2.493140e+03\n",
       "            Low          2.443960e+03\n",
       "            Close        2.447890e+03\n",
       "            Adj Close    2.447890e+03\n",
       "            Volume       3.822860e+09\n",
       "2019-01-04  Open         2.474330e+03\n",
       "            High         2.538070e+03\n",
       "            Low          2.474330e+03\n",
       "            Close        2.531940e+03\n",
       "            Adj Close    2.531940e+03\n",
       "            Volume       4.213410e+09\n",
       "dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "MultiIndex(levels=[['2018-12-28', '2018-12-31', '2019-01-02', '2019-01-03', '2019-01-04'], ['Open', 'High', 'Low', 'Close', 'Adj Close', 'Volume']],\n",
       "           labels=[[0, 0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3, 3, 4, 4, 4, 4, 4, 4], [0, 1, 2, 3, 4, 5, 0, 1, 2, 3, 4, 5, 0, 1, 2, 3, 4, 5, 0, 1, 2, 3, 4, 5, 0, 1, 2, 3, 4, 5]],\n",
       "           names=['Date', None])"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "spsk = sp0.stack()\n",
    "display(sp0, spsk, spsk.index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-25T08:15:53.101937Z",
     "start_time": "2019-01-25T08:15:53.061959Z"
    }
   },
   "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>Date</th>\n",
       "      <th>2018-12-28</th>\n",
       "      <th>2018-12-31</th>\n",
       "      <th>2019-01-02</th>\n",
       "      <th>2019-01-03</th>\n",
       "      <th>2019-01-04</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Open</th>\n",
       "      <td>2.498770e+03</td>\n",
       "      <td>2.498940e+03</td>\n",
       "      <td>2.476960e+03</td>\n",
       "      <td>2.491920e+03</td>\n",
       "      <td>2.474330e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>High</th>\n",
       "      <td>2.520270e+03</td>\n",
       "      <td>2.509240e+03</td>\n",
       "      <td>2.519490e+03</td>\n",
       "      <td>2.493140e+03</td>\n",
       "      <td>2.538070e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Low</th>\n",
       "      <td>2.472890e+03</td>\n",
       "      <td>2.482820e+03</td>\n",
       "      <td>2.467470e+03</td>\n",
       "      <td>2.443960e+03</td>\n",
       "      <td>2.474330e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Close</th>\n",
       "      <td>2.485740e+03</td>\n",
       "      <td>2.506850e+03</td>\n",
       "      <td>2.510030e+03</td>\n",
       "      <td>2.447890e+03</td>\n",
       "      <td>2.531940e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Adj Close</th>\n",
       "      <td>2.485740e+03</td>\n",
       "      <td>2.506850e+03</td>\n",
       "      <td>2.510030e+03</td>\n",
       "      <td>2.447890e+03</td>\n",
       "      <td>2.531940e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Volume</th>\n",
       "      <td>3.702620e+09</td>\n",
       "      <td>3.442870e+09</td>\n",
       "      <td>3.733160e+09</td>\n",
       "      <td>3.822860e+09</td>\n",
       "      <td>4.213410e+09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Date         2018-12-28    2018-12-31    2019-01-02    2019-01-03  \\\n",
       "Open       2.498770e+03  2.498940e+03  2.476960e+03  2.491920e+03   \n",
       "High       2.520270e+03  2.509240e+03  2.519490e+03  2.493140e+03   \n",
       "Low        2.472890e+03  2.482820e+03  2.467470e+03  2.443960e+03   \n",
       "Close      2.485740e+03  2.506850e+03  2.510030e+03  2.447890e+03   \n",
       "Adj Close  2.485740e+03  2.506850e+03  2.510030e+03  2.447890e+03   \n",
       "Volume     3.702620e+09  3.442870e+09  3.733160e+09  3.822860e+09   \n",
       "\n",
       "Date         2019-01-04  \n",
       "Open       2.474330e+03  \n",
       "High       2.538070e+03  \n",
       "Low        2.474330e+03  \n",
       "Close      2.531940e+03  \n",
       "Adj Close  2.531940e+03  \n",
       "Volume     4.213410e+09  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>Adj Close</th>\n",
       "      <th>Volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-12-28</th>\n",
       "      <td>2498.770020</td>\n",
       "      <td>2520.270020</td>\n",
       "      <td>2472.889893</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>2485.739990</td>\n",
       "      <td>3.702620e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31</th>\n",
       "      <td>2498.939941</td>\n",
       "      <td>2509.239990</td>\n",
       "      <td>2482.820068</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>2506.850098</td>\n",
       "      <td>3.442870e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-02</th>\n",
       "      <td>2476.959961</td>\n",
       "      <td>2519.489990</td>\n",
       "      <td>2467.469971</td>\n",
       "      <td>2510.030029</td>\n",
       "      <td>2510.030029</td>\n",
       "      <td>3.733160e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-03</th>\n",
       "      <td>2491.919922</td>\n",
       "      <td>2493.139893</td>\n",
       "      <td>2443.959961</td>\n",
       "      <td>2447.889893</td>\n",
       "      <td>2447.889893</td>\n",
       "      <td>3.822860e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-04</th>\n",
       "      <td>2474.330078</td>\n",
       "      <td>2538.070068</td>\n",
       "      <td>2474.330078</td>\n",
       "      <td>2531.939941</td>\n",
       "      <td>2531.939941</td>\n",
       "      <td>4.213410e+09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Open         High          Low        Close    Adj Close  \\\n",
       "Date                                                                          \n",
       "2018-12-28  2498.770020  2520.270020  2472.889893  2485.739990  2485.739990   \n",
       "2018-12-31  2498.939941  2509.239990  2482.820068  2506.850098  2506.850098   \n",
       "2019-01-02  2476.959961  2519.489990  2467.469971  2510.030029  2510.030029   \n",
       "2019-01-03  2491.919922  2493.139893  2443.959961  2447.889893  2447.889893   \n",
       "2019-01-04  2474.330078  2538.070068  2474.330078  2531.939941  2531.939941   \n",
       "\n",
       "                  Volume  \n",
       "Date                      \n",
       "2018-12-28  3.702620e+09  \n",
       "2018-12-31  3.442870e+09  \n",
       "2019-01-02  3.733160e+09  \n",
       "2019-01-03  3.822860e+09  \n",
       "2019-01-04  4.213410e+09  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>Date</th>\n",
       "      <th>2018-12-28</th>\n",
       "      <th>2018-12-31</th>\n",
       "      <th>2019-01-02</th>\n",
       "      <th>2019-01-03</th>\n",
       "      <th>2019-01-04</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Open</th>\n",
       "      <td>2.498770e+03</td>\n",
       "      <td>2.498940e+03</td>\n",
       "      <td>2.476960e+03</td>\n",
       "      <td>2.491920e+03</td>\n",
       "      <td>2.474330e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>High</th>\n",
       "      <td>2.520270e+03</td>\n",
       "      <td>2.509240e+03</td>\n",
       "      <td>2.519490e+03</td>\n",
       "      <td>2.493140e+03</td>\n",
       "      <td>2.538070e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Low</th>\n",
       "      <td>2.472890e+03</td>\n",
       "      <td>2.482820e+03</td>\n",
       "      <td>2.467470e+03</td>\n",
       "      <td>2.443960e+03</td>\n",
       "      <td>2.474330e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Close</th>\n",
       "      <td>2.485740e+03</td>\n",
       "      <td>2.506850e+03</td>\n",
       "      <td>2.510030e+03</td>\n",
       "      <td>2.447890e+03</td>\n",
       "      <td>2.531940e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Adj Close</th>\n",
       "      <td>2.485740e+03</td>\n",
       "      <td>2.506850e+03</td>\n",
       "      <td>2.510030e+03</td>\n",
       "      <td>2.447890e+03</td>\n",
       "      <td>2.531940e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Volume</th>\n",
       "      <td>3.702620e+09</td>\n",
       "      <td>3.442870e+09</td>\n",
       "      <td>3.733160e+09</td>\n",
       "      <td>3.822860e+09</td>\n",
       "      <td>4.213410e+09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Date         2018-12-28    2018-12-31    2019-01-02    2019-01-03  \\\n",
       "Open       2.498770e+03  2.498940e+03  2.476960e+03  2.491920e+03   \n",
       "High       2.520270e+03  2.509240e+03  2.519490e+03  2.493140e+03   \n",
       "Low        2.472890e+03  2.482820e+03  2.467470e+03  2.443960e+03   \n",
       "Close      2.485740e+03  2.506850e+03  2.510030e+03  2.447890e+03   \n",
       "Adj Close  2.485740e+03  2.506850e+03  2.510030e+03  2.447890e+03   \n",
       "Volume     3.702620e+09  3.442870e+09  3.733160e+09  3.822860e+09   \n",
       "\n",
       "Date         2019-01-04  \n",
       "Open       2.474330e+03  \n",
       "High       2.538070e+03  \n",
       "Low        2.474330e+03  \n",
       "Close      2.531940e+03  \n",
       "Adj Close  2.531940e+03  \n",
       "Volume     4.213410e+09  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "ussk0 = spsk.unstack(level=0)\n",
    "ussk1 = spsk.unstack(level=1)\n",
    "ussk2 = spsk.unstack(level='Date')\n",
    "\n",
    "display(ussk0, ussk1, ussk2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6.3.2 Performance gain from `pd.stack`\n",
    "\n",
    "With stacked data, the lockup data with boolean operation can be significantly faster"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-25T08:16:10.516663Z",
     "start_time": "2019-01-25T08:15:53.108169Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "23 µs ± 970 ns per loop (mean ± std. dev. of 7 runs, 10000 loops each)\n",
      "370 µs ± 18.1 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)\n",
      "153 µs ± 2.61 µs per loop (mean ± std. dev. of 7 runs, 10000 loops each)\n"
     ]
    }
   ],
   "source": [
    "%timeit spsk['2018-12-28', 'Volume']\n",
    "%timeit spsk.loc['2018-12-28']['Volume']  # don't use this.\n",
    "%timeit sp0.loc['2018-12-28']['Volume']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 6.4 Melt and Pivot\n",
    "\n",
    "Melting: \n",
    "\n",
    "1. reshapes a DataFrame into a format where two or more columns, referred to as variable and value;\n",
    "2. unpivoted column labels in the variable column;\n",
    "3. moves the data from these columns into the appropriate location in the value column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-25T08:16:10.569329Z",
     "start_time": "2019-01-25T08:16:10.520237Z"
    }
   },
   "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>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Open</th>\n",
       "      <th>Close</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-12-28</td>\n",
       "      <td>2520.270020</td>\n",
       "      <td>2472.889893</td>\n",
       "      <td>2498.770020</td>\n",
       "      <td>2485.739990</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-12-31</td>\n",
       "      <td>2509.239990</td>\n",
       "      <td>2482.820068</td>\n",
       "      <td>2498.939941</td>\n",
       "      <td>2506.850098</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-01-02</td>\n",
       "      <td>2519.489990</td>\n",
       "      <td>2467.469971</td>\n",
       "      <td>2476.959961</td>\n",
       "      <td>2510.030029</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-01-03</td>\n",
       "      <td>2493.139893</td>\n",
       "      <td>2443.959961</td>\n",
       "      <td>2491.919922</td>\n",
       "      <td>2447.889893</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-01-04</td>\n",
       "      <td>2538.070068</td>\n",
       "      <td>2474.330078</td>\n",
       "      <td>2474.330078</td>\n",
       "      <td>2531.939941</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date         High          Low         Open        Close\n",
       "0  2018-12-28  2520.270020  2472.889893  2498.770020  2485.739990\n",
       "1  2018-12-31  2509.239990  2482.820068  2498.939941  2506.850098\n",
       "2  2019-01-02  2519.489990  2467.469971  2476.959961  2510.030029\n",
       "3  2019-01-03  2493.139893  2443.959961  2491.919922  2447.889893\n",
       "4  2019-01-04  2538.070068  2474.330078  2474.330078  2531.939941"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>Category</th>\n",
       "      <th>Price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-12-28</td>\n",
       "      <td>High</td>\n",
       "      <td>2520.270020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-12-31</td>\n",
       "      <td>High</td>\n",
       "      <td>2509.239990</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-01-02</td>\n",
       "      <td>High</td>\n",
       "      <td>2519.489990</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-01-03</td>\n",
       "      <td>High</td>\n",
       "      <td>2493.139893</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-01-04</td>\n",
       "      <td>High</td>\n",
       "      <td>2538.070068</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2018-12-28</td>\n",
       "      <td>Low</td>\n",
       "      <td>2472.889893</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2018-12-31</td>\n",
       "      <td>Low</td>\n",
       "      <td>2482.820068</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2019-01-02</td>\n",
       "      <td>Low</td>\n",
       "      <td>2467.469971</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2019-01-03</td>\n",
       "      <td>Low</td>\n",
       "      <td>2443.959961</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2019-01-04</td>\n",
       "      <td>Low</td>\n",
       "      <td>2474.330078</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2018-12-28</td>\n",
       "      <td>Open</td>\n",
       "      <td>2498.770020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2018-12-31</td>\n",
       "      <td>Open</td>\n",
       "      <td>2498.939941</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2019-01-02</td>\n",
       "      <td>Open</td>\n",
       "      <td>2476.959961</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2019-01-03</td>\n",
       "      <td>Open</td>\n",
       "      <td>2491.919922</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2019-01-04</td>\n",
       "      <td>Open</td>\n",
       "      <td>2474.330078</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2018-12-28</td>\n",
       "      <td>Close</td>\n",
       "      <td>2485.739990</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2018-12-31</td>\n",
       "      <td>Close</td>\n",
       "      <td>2506.850098</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2019-01-02</td>\n",
       "      <td>Close</td>\n",
       "      <td>2510.030029</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2019-01-03</td>\n",
       "      <td>Close</td>\n",
       "      <td>2447.889893</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2019-01-04</td>\n",
       "      <td>Close</td>\n",
       "      <td>2531.939941</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Date Category        Price\n",
       "0   2018-12-28     High  2520.270020\n",
       "1   2018-12-31     High  2509.239990\n",
       "2   2019-01-02     High  2519.489990\n",
       "3   2019-01-03     High  2493.139893\n",
       "4   2019-01-04     High  2538.070068\n",
       "5   2018-12-28      Low  2472.889893\n",
       "6   2018-12-31      Low  2482.820068\n",
       "7   2019-01-02      Low  2467.469971\n",
       "8   2019-01-03      Low  2443.959961\n",
       "9   2019-01-04      Low  2474.330078\n",
       "10  2018-12-28     Open  2498.770020\n",
       "11  2018-12-31     Open  2498.939941\n",
       "12  2019-01-02     Open  2476.959961\n",
       "13  2019-01-03     Open  2491.919922\n",
       "14  2019-01-04     Open  2474.330078\n",
       "15  2018-12-28    Close  2485.739990\n",
       "16  2018-12-31    Close  2506.850098\n",
       "17  2019-01-02    Close  2510.030029\n",
       "18  2019-01-03    Close  2447.889893\n",
       "19  2019-01-04    Close  2531.939941"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "price_df = sp0[['High', 'Low', 'Open', 'Close']].reset_index()\n",
    "\n",
    "mlsp = price_df.melt(id_vars=['Date'], var_name='Category', value_name='Price')\n",
    "\n",
    "display(price_df, mlsp)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-25T08:16:10.596195Z",
     "start_time": "2019-01-25T08:16:10.574006Z"
    }
   },
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">Price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Category</th>\n",
       "      <th>Close</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Open</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-12-28</th>\n",
       "      <td>2485.739990</td>\n",
       "      <td>2520.270020</td>\n",
       "      <td>2472.889893</td>\n",
       "      <td>2498.770020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31</th>\n",
       "      <td>2506.850098</td>\n",
       "      <td>2509.239990</td>\n",
       "      <td>2482.820068</td>\n",
       "      <td>2498.939941</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-02</th>\n",
       "      <td>2510.030029</td>\n",
       "      <td>2519.489990</td>\n",
       "      <td>2467.469971</td>\n",
       "      <td>2476.959961</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-03</th>\n",
       "      <td>2447.889893</td>\n",
       "      <td>2493.139893</td>\n",
       "      <td>2443.959961</td>\n",
       "      <td>2491.919922</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-04</th>\n",
       "      <td>2531.939941</td>\n",
       "      <td>2538.070068</td>\n",
       "      <td>2474.330078</td>\n",
       "      <td>2474.330078</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Price                                       \n",
       "Category          Close         High          Low         Open\n",
       "Date                                                          \n",
       "2018-12-28  2485.739990  2520.270020  2472.889893  2498.770020\n",
       "2018-12-31  2506.850098  2509.239990  2482.820068  2498.939941\n",
       "2019-01-02  2510.030029  2519.489990  2467.469971  2476.959961\n",
       "2019-01-03  2447.889893  2493.139893  2443.959961  2491.919922\n",
       "2019-01-04  2531.939941  2538.070068  2474.330078  2474.330078"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "pvsp = mlsp.pivot(index='Date', columns='Category')\n",
    "\n",
    "display(pvsp)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 6.5 Exercise\n",
    "\n",
    "1. Read the official pandoc [documentation](http://pandas.pydata.org/pandas-docs/stable/merging.html#database-style-dataframe-joining-merging)\n",
    "2. Why is the result of `unstack(level=0)` is equal to `unstack(level=1)`?\n",
    "3. How to unmelt a melted table to the original with `pd.pivot`?"
   ]
  },
  {
   "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.7.2"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": false,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "357.3333435058594px"
   },
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}