py4fi_L3_pandas.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# Basic I/O in Python"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## *Pickle* objects"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Generate a list of 10,000 standard normals\n",
    "from random import gauss\n",
    "a=[gauss(0,1) for i in range(10000)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save to pickle\n",
    "import pickle\n",
    "file=open(\"c:/temp/data.pkl\",\"wb\") # open with write permission\n",
    "pickle.dump(a,file)\n",
    "file.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Open from pickle\n",
    "file=open(\"c:/temp/data.pkl\",\"rb\")\n",
    "b=pickle.load(file)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## CSV objects"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "m=np.random.standard_normal((5,3)) # generate a 5-by-3 matrix\n",
    "rownames=[\"A\",\"B\",\"C\",\"D\",\"E\"] # names of each row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "csv_file=open(\"c:/temp/data.csv\",\"w\") # open file\n",
    "header=\"row name, v1, v2, v3\\n\" # header\n",
    "for r_, (v1,v2,v3) in zip(rownames,m):\n",
    "    s=\"%s, %f, %f, %f\\n\"%(r_,v1,v2,v3) # 1 string, 3 floats\n",
    "    csv_file.write(s)\n",
    "csv_file.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "A, -0.129270, -1.452436, 0.396367\n",
      "\n",
      "B, -1.496324, 1.212597, 0.041594\n",
      "\n",
      "C, -0.764905, 1.342894, -0.963380\n",
      "\n",
      "D, -1.071333, 0.236108, 0.268311\n",
      "\n",
      "E, 0.011692, -0.121805, 0.053151\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Loading csv files from disk\n",
    "with open('c:/temp/data.csv',\"r\") as csv_file:\n",
    "    for line in csv_file:\n",
    "        print(line)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['A, -0.129270, -1.452436, 0.396367\\n',\n",
       " 'B, -1.496324, 1.212597, 0.041594\\n',\n",
       " 'C, -0.764905, 1.342894, -0.963380\\n',\n",
       " 'D, -1.071333, 0.236108, 0.268311\\n',\n",
       " 'E, 0.011692, -0.121805, 0.053151\\n']"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# read all lines at once\n",
    "csv_file=open('c:/temp/data.csv',\"r\")\n",
    "csv_file.readlines()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# The *pandas* library"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Preliminaries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# First steps\n",
    "C=pd.DataFrame([\"CAC40\",\"DAX\",\"FTSE\"],columns=[\"Index\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Index</th>\n",
       "      <th>Country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CAC40</td>\n",
       "      <td>France</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>DAX</td>\n",
       "      <td>Germany</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>FTSE</td>\n",
       "      <td>UK</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Index  Country\n",
       "0  CAC40   France\n",
       "1    DAX  Germany\n",
       "2   FTSE       UK"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "C[\"Country\"]=(\"France\",\"Germany\",\"UK\")\n",
    "C"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Loading security data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>Index</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2974</th>\n",
       "      <td>2018-09-10</td>\n",
       "      <td>30.88</td>\n",
       "      <td>31.00</td>\n",
       "      <td>30.75</td>\n",
       "      <td>30.82</td>\n",
       "      <td>34149198</td>\n",
       "      <td>BankOfAmerica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2975</th>\n",
       "      <td>2018-09-11</td>\n",
       "      <td>30.72</td>\n",
       "      <td>30.90</td>\n",
       "      <td>30.70</td>\n",
       "      <td>30.85</td>\n",
       "      <td>53071044</td>\n",
       "      <td>BankOfAmerica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2976</th>\n",
       "      <td>2018-09-12</td>\n",
       "      <td>30.88</td>\n",
       "      <td>30.96</td>\n",
       "      <td>30.38</td>\n",
       "      <td>30.43</td>\n",
       "      <td>59482315</td>\n",
       "      <td>BankOfAmerica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2977</th>\n",
       "      <td>2018-09-13</td>\n",
       "      <td>30.53</td>\n",
       "      <td>30.67</td>\n",
       "      <td>30.08</td>\n",
       "      <td>30.14</td>\n",
       "      <td>59219530</td>\n",
       "      <td>BankOfAmerica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2978</th>\n",
       "      <td>2018-09-14</td>\n",
       "      <td>30.13</td>\n",
       "      <td>30.39</td>\n",
       "      <td>30.11</td>\n",
       "      <td>30.37</td>\n",
       "      <td>33743874</td>\n",
       "      <td>BankOfAmerica</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            date   open   high    low  close    volume          Index\n",
       "2974  2018-09-10  30.88  31.00  30.75  30.82  34149198  BankOfAmerica\n",
       "2975  2018-09-11  30.72  30.90  30.70  30.85  53071044  BankOfAmerica\n",
       "2976  2018-09-12  30.88  30.96  30.38  30.43  59482315  BankOfAmerica\n",
       "2977  2018-09-13  30.53  30.67  30.08  30.14  59219530  BankOfAmerica\n",
       "2978  2018-09-14  30.13  30.39  30.11  30.37  33743874  BankOfAmerica"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Indices=pd.read_csv(\"data/hsbc_bcs_bac.csv\")\n",
    "Indices.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['date', 'open', 'high', 'low', 'close', 'volume', 'Index']"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# see all columns\n",
    "Indices.columns.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['HSBC', 'Barclays', 'BankOfAmerica']"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# What indices are there?\n",
    "Indices['Index'].drop_duplicates().tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Selection via index\n",
    "# Indices=Indices.reset_index()\n",
    "Indices=Indices.set_index(['date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>Index</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-09-11</th>\n",
       "      <td>42.530</td>\n",
       "      <td>42.920</td>\n",
       "      <td>42.46</td>\n",
       "      <td>42.81</td>\n",
       "      <td>1293230</td>\n",
       "      <td>HSBC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-09-11</th>\n",
       "      <td>9.315</td>\n",
       "      <td>9.325</td>\n",
       "      <td>9.20</td>\n",
       "      <td>9.27</td>\n",
       "      <td>1734283</td>\n",
       "      <td>Barclays</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-09-11</th>\n",
       "      <td>30.720</td>\n",
       "      <td>30.900</td>\n",
       "      <td>30.70</td>\n",
       "      <td>30.85</td>\n",
       "      <td>53071044</td>\n",
       "      <td>BankOfAmerica</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              open    high    low  close    volume          Index\n",
       "date                                                             \n",
       "2018-09-11  42.530  42.920  42.46  42.81   1293230           HSBC\n",
       "2018-09-11   9.315   9.325   9.20   9.27   1734283       Barclays\n",
       "2018-09-11  30.720  30.900  30.70  30.85  53071044  BankOfAmerica"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Indices.ix[\"2018-09-11\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Slicing the DataFrame\n",
    "HSBC=Indices[Indices['Index']==\"HSBC\"]\n",
    "Barclays=Indices[Indices['Index']==\"Barclays\"]\n",
    "BankOfAm=Indices[Indices['Index']==\"BankOfAmerica\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>Index</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>2014-10-07</th>\n",
       "      <td>39.5520</td>\n",
       "      <td>39.6064</td>\n",
       "      <td>39.2099</td>\n",
       "      <td>39.2254</td>\n",
       "      <td>1259799</td>\n",
       "      <td>HSBC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-08</th>\n",
       "      <td>39.5908</td>\n",
       "      <td>39.9874</td>\n",
       "      <td>39.4742</td>\n",
       "      <td>39.9874</td>\n",
       "      <td>3777024</td>\n",
       "      <td>HSBC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-09</th>\n",
       "      <td>39.4820</td>\n",
       "      <td>39.5675</td>\n",
       "      <td>38.9922</td>\n",
       "      <td>39.0388</td>\n",
       "      <td>2568714</td>\n",
       "      <td>HSBC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-10</th>\n",
       "      <td>38.9066</td>\n",
       "      <td>38.9999</td>\n",
       "      <td>38.6501</td>\n",
       "      <td>38.6889</td>\n",
       "      <td>4375561</td>\n",
       "      <td>HSBC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-13</th>\n",
       "      <td>39.4820</td>\n",
       "      <td>39.6064</td>\n",
       "      <td>39.1943</td>\n",
       "      <td>39.2176</td>\n",
       "      <td>1782934</td>\n",
       "      <td>HSBC</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               open     high      low    close   volume Index\n",
       "date                                                         \n",
       "2014-10-07  39.5520  39.6064  39.2099  39.2254  1259799  HSBC\n",
       "2014-10-08  39.5908  39.9874  39.4742  39.9874  3777024  HSBC\n",
       "2014-10-09  39.4820  39.5675  38.9922  39.0388  2568714  HSBC\n",
       "2014-10-10  38.9066  38.9999  38.6501  38.6889  4375561  HSBC\n",
       "2014-10-13  39.4820  39.6064  39.1943  39.2176  1782934  HSBC"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "HSBC.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# append DataFrames\n",
    "HSBC_Barclays=HSBC.append(Barclays, ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>Index</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1981</th>\n",
       "      <td>9.390</td>\n",
       "      <td>9.405</td>\n",
       "      <td>9.3600</td>\n",
       "      <td>9.37</td>\n",
       "      <td>1383662</td>\n",
       "      <td>Barclays</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1982</th>\n",
       "      <td>9.315</td>\n",
       "      <td>9.325</td>\n",
       "      <td>9.2000</td>\n",
       "      <td>9.27</td>\n",
       "      <td>1734283</td>\n",
       "      <td>Barclays</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1983</th>\n",
       "      <td>9.160</td>\n",
       "      <td>9.180</td>\n",
       "      <td>9.1095</td>\n",
       "      <td>9.17</td>\n",
       "      <td>1992662</td>\n",
       "      <td>Barclays</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1984</th>\n",
       "      <td>9.220</td>\n",
       "      <td>9.240</td>\n",
       "      <td>9.1200</td>\n",
       "      <td>9.14</td>\n",
       "      <td>2854383</td>\n",
       "      <td>Barclays</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1985</th>\n",
       "      <td>9.150</td>\n",
       "      <td>9.165</td>\n",
       "      <td>9.0800</td>\n",
       "      <td>9.09</td>\n",
       "      <td>1744981</td>\n",
       "      <td>Barclays</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       open   high     low  close   volume     Index\n",
       "1981  9.390  9.405  9.3600   9.37  1383662  Barclays\n",
       "1982  9.315  9.325  9.2000   9.27  1734283  Barclays\n",
       "1983  9.160  9.180  9.1095   9.17  1992662  Barclays\n",
       "1984  9.220  9.240  9.1200   9.14  2854383  Barclays\n",
       "1985  9.150  9.165  9.0800   9.09  1744981  Barclays"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "HSBC_Barclays.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge databases\n",
    "Indices=Indices.merge(C,on=\"Index\",how=\"left\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>Index</th>\n",
       "      <th>Country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>39.5520</td>\n",
       "      <td>39.6064</td>\n",
       "      <td>39.2099</td>\n",
       "      <td>39.2254</td>\n",
       "      <td>1259799</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>39.5908</td>\n",
       "      <td>39.9874</td>\n",
       "      <td>39.4742</td>\n",
       "      <td>39.9874</td>\n",
       "      <td>3777024</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>39.4820</td>\n",
       "      <td>39.5675</td>\n",
       "      <td>38.9922</td>\n",
       "      <td>39.0388</td>\n",
       "      <td>2568714</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>38.9066</td>\n",
       "      <td>38.9999</td>\n",
       "      <td>38.6501</td>\n",
       "      <td>38.6889</td>\n",
       "      <td>4375561</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>39.4820</td>\n",
       "      <td>39.6064</td>\n",
       "      <td>39.1943</td>\n",
       "      <td>39.2176</td>\n",
       "      <td>1782934</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      open     high      low    close   volume Index Country\n",
       "0  39.5520  39.6064  39.2099  39.2254  1259799  HSBC     NaN\n",
       "1  39.5908  39.9874  39.4742  39.9874  3777024  HSBC     NaN\n",
       "2  39.4820  39.5675  38.9922  39.0388  2568714  HSBC     NaN\n",
       "3  38.9066  38.9999  38.6501  38.6889  4375561  HSBC     NaN\n",
       "4  39.4820  39.6064  39.1943  39.2176  1782934  HSBC     NaN"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Indices.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Summary statistics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>2979.000000</td>\n",
       "      <td>2979.000000</td>\n",
       "      <td>2979.000000</td>\n",
       "      <td>2979.000000</td>\n",
       "      <td>2.979000e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>23.341606</td>\n",
       "      <td>23.489547</td>\n",
       "      <td>23.187051</td>\n",
       "      <td>23.343150</td>\n",
       "      <td>3.034012e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>12.508706</td>\n",
       "      <td>12.548031</td>\n",
       "      <td>12.472317</td>\n",
       "      <td>12.513101</td>\n",
       "      <td>4.511051e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>6.804700</td>\n",
       "      <td>6.853100</td>\n",
       "      <td>6.543300</td>\n",
       "      <td>6.804700</td>\n",
       "      <td>4.201230e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>12.774550</td>\n",
       "      <td>12.911150</td>\n",
       "      <td>12.588450</td>\n",
       "      <td>12.748600</td>\n",
       "      <td>1.916628e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>16.923900</td>\n",
       "      <td>17.035700</td>\n",
       "      <td>16.743100</td>\n",
       "      <td>16.904900</td>\n",
       "      <td>3.260279e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>33.158300</td>\n",
       "      <td>33.290800</td>\n",
       "      <td>32.960700</td>\n",
       "      <td>33.166600</td>\n",
       "      <td>6.045234e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>53.593400</td>\n",
       "      <td>53.622200</td>\n",
       "      <td>53.152100</td>\n",
       "      <td>53.363200</td>\n",
       "      <td>3.750886e+08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              open         high          low        close        volume\n",
       "count  2979.000000  2979.000000  2979.000000  2979.000000  2.979000e+03\n",
       "mean     23.341606    23.489547    23.187051    23.343150  3.034012e+07\n",
       "std      12.508706    12.548031    12.472317    12.513101  4.511051e+07\n",
       "min       6.804700     6.853100     6.543300     6.804700  4.201230e+05\n",
       "25%      12.774550    12.911150    12.588450    12.748600  1.916628e+06\n",
       "50%      16.923900    17.035700    16.743100    16.904900  3.260279e+06\n",
       "75%      33.158300    33.290800    32.960700    33.166600  6.045234e+07\n",
       "max      53.593400    53.622200    53.152100    53.363200  3.750886e+08"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Indices.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "open       1.692390e+01\n",
       "high       1.703570e+01\n",
       "low        1.674310e+01\n",
       "close      1.690490e+01\n",
       "volume     3.260279e+06\n",
       "Country             NaN\n",
       "dtype: float64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Summary stats by column\n",
    "Indices.mean()\n",
    "Indices.std()\n",
    "Indices.median()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "12.508706228458067"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Summary stats for specific column\n",
    "Indices[\"open\"].std()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>Index</th>\n",
       "      <th>Country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>39.5520</td>\n",
       "      <td>39.6064</td>\n",
       "      <td>39.2099</td>\n",
       "      <td>39.2254</td>\n",
       "      <td>1259799</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>39.5908</td>\n",
       "      <td>39.9874</td>\n",
       "      <td>39.4742</td>\n",
       "      <td>39.9874</td>\n",
       "      <td>3777024</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>39.4820</td>\n",
       "      <td>39.5675</td>\n",
       "      <td>38.9922</td>\n",
       "      <td>39.0388</td>\n",
       "      <td>2568714</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>38.9066</td>\n",
       "      <td>38.9999</td>\n",
       "      <td>38.6501</td>\n",
       "      <td>38.6889</td>\n",
       "      <td>4375561</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>39.4820</td>\n",
       "      <td>39.6064</td>\n",
       "      <td>39.1943</td>\n",
       "      <td>39.2176</td>\n",
       "      <td>1782934</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>39.2332</td>\n",
       "      <td>39.3809</td>\n",
       "      <td>39.0543</td>\n",
       "      <td>39.1399</td>\n",
       "      <td>1933295</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>38.8755</td>\n",
       "      <td>38.9300</td>\n",
       "      <td>37.9425</td>\n",
       "      <td>38.5956</td>\n",
       "      <td>3897856</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>37.9969</td>\n",
       "      <td>38.7200</td>\n",
       "      <td>37.9503</td>\n",
       "      <td>38.5334</td>\n",
       "      <td>2811671</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>38.7745</td>\n",
       "      <td>39.0583</td>\n",
       "      <td>38.6501</td>\n",
       "      <td>39.0233</td>\n",
       "      <td>2097831</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>38.6812</td>\n",
       "      <td>38.8911</td>\n",
       "      <td>38.5879</td>\n",
       "      <td>38.8911</td>\n",
       "      <td>1293619</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>39.0777</td>\n",
       "      <td>39.2643</td>\n",
       "      <td>39.0388</td>\n",
       "      <td>39.2176</td>\n",
       "      <td>1079173</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>39.2726</td>\n",
       "      <td>39.4140</td>\n",
       "      <td>39.0684</td>\n",
       "      <td>39.1077</td>\n",
       "      <td>1220001</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>39.7831</td>\n",
       "      <td>39.8223</td>\n",
       "      <td>39.5750</td>\n",
       "      <td>39.6181</td>\n",
       "      <td>2005554</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>39.7516</td>\n",
       "      <td>39.8066</td>\n",
       "      <td>39.6181</td>\n",
       "      <td>39.7831</td>\n",
       "      <td>1238468</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>39.2412</td>\n",
       "      <td>39.5553</td>\n",
       "      <td>39.2334</td>\n",
       "      <td>39.4689</td>\n",
       "      <td>1010551</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>39.7124</td>\n",
       "      <td>39.8223</td>\n",
       "      <td>39.6417</td>\n",
       "      <td>39.8223</td>\n",
       "      <td>1261213</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>39.7831</td>\n",
       "      <td>39.9323</td>\n",
       "      <td>39.5710</td>\n",
       "      <td>39.8066</td>\n",
       "      <td>1396924</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>39.2569</td>\n",
       "      <td>39.7124</td>\n",
       "      <td>39.2491</td>\n",
       "      <td>39.6181</td>\n",
       "      <td>1414834</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>40.0108</td>\n",
       "      <td>40.2071</td>\n",
       "      <td>39.9009</td>\n",
       "      <td>40.0658</td>\n",
       "      <td>1437351</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>39.4847</td>\n",
       "      <td>39.5239</td>\n",
       "      <td>39.2176</td>\n",
       "      <td>39.3433</td>\n",
       "      <td>1675728</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>39.2098</td>\n",
       "      <td>39.3904</td>\n",
       "      <td>39.1352</td>\n",
       "      <td>39.3904</td>\n",
       "      <td>1526208</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>39.5239</td>\n",
       "      <td>39.7203</td>\n",
       "      <td>39.4297</td>\n",
       "      <td>39.6574</td>\n",
       "      <td>1087882</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>39.7045</td>\n",
       "      <td>39.7909</td>\n",
       "      <td>39.4061</td>\n",
       "      <td>39.5475</td>\n",
       "      <td>722989</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>39.2412</td>\n",
       "      <td>39.3354</td>\n",
       "      <td>39.1234</td>\n",
       "      <td>39.3354</td>\n",
       "      <td>1308781</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>39.4768</td>\n",
       "      <td>39.6189</td>\n",
       "      <td>39.3311</td>\n",
       "      <td>39.5553</td>\n",
       "      <td>758835</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>39.6888</td>\n",
       "      <td>39.7595</td>\n",
       "      <td>39.5789</td>\n",
       "      <td>39.7124</td>\n",
       "      <td>791785</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>39.2334</td>\n",
       "      <td>39.4847</td>\n",
       "      <td>39.2176</td>\n",
       "      <td>39.4375</td>\n",
       "      <td>731131</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>39.3669</td>\n",
       "      <td>39.4768</td>\n",
       "      <td>39.2726</td>\n",
       "      <td>39.3826</td>\n",
       "      <td>1090781</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>39.1941</td>\n",
       "      <td>39.5082</td>\n",
       "      <td>39.1862</td>\n",
       "      <td>39.4768</td>\n",
       "      <td>1379257</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>38.9899</td>\n",
       "      <td>39.3669</td>\n",
       "      <td>38.9821</td>\n",
       "      <td>39.3040</td>\n",
       "      <td>1071482</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2949</th>\n",
       "      <td>31.0300</td>\n",
       "      <td>31.3584</td>\n",
       "      <td>31.0101</td>\n",
       "      <td>31.3584</td>\n",
       "      <td>50153768</td>\n",
       "      <td>BankOfAmerica</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2950</th>\n",
       "      <td>31.2987</td>\n",
       "      <td>31.4380</td>\n",
       "      <td>31.1740</td>\n",
       "      <td>31.3684</td>\n",
       "      <td>41261494</td>\n",
       "      <td>BankOfAmerica</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2951</th>\n",
       "      <td>31.3982</td>\n",
       "      <td>31.6470</td>\n",
       "      <td>31.3385</td>\n",
       "      <td>31.3584</td>\n",
       "      <td>43946164</td>\n",
       "      <td>BankOfAmerica</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2952</th>\n",
       "      <td>31.3584</td>\n",
       "      <td>31.7565</td>\n",
       "      <td>31.3285</td>\n",
       "      <td>31.6470</td>\n",
       "      <td>43689582</td>\n",
       "      <td>BankOfAmerica</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2953</th>\n",
       "      <td>31.5773</td>\n",
       "      <td>31.6371</td>\n",
       "      <td>31.3982</td>\n",
       "      <td>31.4480</td>\n",
       "      <td>38219057</td>\n",
       "      <td>BankOfAmerica</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2954</th>\n",
       "      <td>31.0798</td>\n",
       "      <td>31.1743</td>\n",
       "      <td>30.7513</td>\n",
       "      <td>31.0399</td>\n",
       "      <td>55679849</td>\n",
       "      <td>BankOfAmerica</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2955</th>\n",
       "      <td>30.9305</td>\n",
       "      <td>30.9703</td>\n",
       "      <td>30.3035</td>\n",
       "      <td>30.3334</td>\n",
       "      <td>63960630</td>\n",
       "      <td>BankOfAmerica</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2956</th>\n",
       "      <td>30.4627</td>\n",
       "      <td>30.7513</td>\n",
       "      <td>30.2736</td>\n",
       "      <td>30.6419</td>\n",
       "      <td>42521241</td>\n",
       "      <td>BankOfAmerica</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2957</th>\n",
       "      <td>30.3334</td>\n",
       "      <td>30.4727</td>\n",
       "      <td>30.0099</td>\n",
       "      <td>30.2139</td>\n",
       "      <td>59747876</td>\n",
       "      <td>BankOfAmerica</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2958</th>\n",
       "      <td>30.4130</td>\n",
       "      <td>30.6916</td>\n",
       "      <td>30.3732</td>\n",
       "      <td>30.5722</td>\n",
       "      <td>44015361</td>\n",
       "      <td>BankOfAmerica</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2959</th>\n",
       "      <td>30.5125</td>\n",
       "      <td>30.7066</td>\n",
       "      <td>30.3980</td>\n",
       "      <td>30.5921</td>\n",
       "      <td>39843538</td>\n",
       "      <td>BankOfAmerica</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2960</th>\n",
       "      <td>30.5423</td>\n",
       "      <td>30.7762</td>\n",
       "      <td>30.5025</td>\n",
       "      <td>30.7215</td>\n",
       "      <td>40011041</td>\n",
       "      <td>BankOfAmerica</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2961</th>\n",
       "      <td>30.7613</td>\n",
       "      <td>31.0997</td>\n",
       "      <td>30.6817</td>\n",
       "      <td>30.8708</td>\n",
       "      <td>53510571</td>\n",
       "      <td>BankOfAmerica</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2962</th>\n",
       "      <td>30.7712</td>\n",
       "      <td>30.9354</td>\n",
       "      <td>30.7314</td>\n",
       "      <td>30.8310</td>\n",
       "      <td>33062884</td>\n",
       "      <td>BankOfAmerica</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2963</th>\n",
       "      <td>30.7911</td>\n",
       "      <td>30.8310</td>\n",
       "      <td>30.5921</td>\n",
       "      <td>30.6916</td>\n",
       "      <td>38259064</td>\n",
       "      <td>BankOfAmerica</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2964</th>\n",
       "      <td>30.7911</td>\n",
       "      <td>30.9305</td>\n",
       "      <td>30.7016</td>\n",
       "      <td>30.7414</td>\n",
       "      <td>43585013</td>\n",
       "      <td>BankOfAmerica</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2965</th>\n",
       "      <td>30.8807</td>\n",
       "      <td>31.3385</td>\n",
       "      <td>30.8409</td>\n",
       "      <td>31.1594</td>\n",
       "      <td>47729273</td>\n",
       "      <td>BankOfAmerica</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2966</th>\n",
       "      <td>31.2091</td>\n",
       "      <td>31.2589</td>\n",
       "      <td>31.0399</td>\n",
       "      <td>31.1196</td>\n",
       "      <td>38538983</td>\n",
       "      <td>BankOfAmerica</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2967</th>\n",
       "      <td>31.0897</td>\n",
       "      <td>31.1594</td>\n",
       "      <td>30.9205</td>\n",
       "      <td>30.9902</td>\n",
       "      <td>46401765</td>\n",
       "      <td>BankOfAmerica</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2968</th>\n",
       "      <td>30.8708</td>\n",
       "      <td>31.0001</td>\n",
       "      <td>30.8011</td>\n",
       "      <td>30.8608</td>\n",
       "      <td>47358915</td>\n",
       "      <td>BankOfAmerica</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2969</th>\n",
       "      <td>30.7314</td>\n",
       "      <td>30.8011</td>\n",
       "      <td>30.4727</td>\n",
       "      <td>30.7812</td>\n",
       "      <td>49265952</td>\n",
       "      <td>BankOfAmerica</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2970</th>\n",
       "      <td>30.7613</td>\n",
       "      <td>31.0101</td>\n",
       "      <td>30.6717</td>\n",
       "      <td>30.9902</td>\n",
       "      <td>44745551</td>\n",
       "      <td>BankOfAmerica</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2971</th>\n",
       "      <td>30.9205</td>\n",
       "      <td>31.0897</td>\n",
       "      <td>30.8210</td>\n",
       "      <td>31.0300</td>\n",
       "      <td>48516722</td>\n",
       "      <td>BankOfAmerica</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2972</th>\n",
       "      <td>31.0500</td>\n",
       "      <td>31.1100</td>\n",
       "      <td>30.7300</td>\n",
       "      <td>30.8500</td>\n",
       "      <td>42666015</td>\n",
       "      <td>BankOfAmerica</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2973</th>\n",
       "      <td>31.0000</td>\n",
       "      <td>31.1200</td>\n",
       "      <td>30.6300</td>\n",
       "      <td>30.8600</td>\n",
       "      <td>49103817</td>\n",
       "      <td>BankOfAmerica</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2974</th>\n",
       "      <td>30.8800</td>\n",
       "      <td>31.0000</td>\n",
       "      <td>30.7500</td>\n",
       "      <td>30.8200</td>\n",
       "      <td>34149198</td>\n",
       "      <td>BankOfAmerica</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2975</th>\n",
       "      <td>30.7200</td>\n",
       "      <td>30.9000</td>\n",
       "      <td>30.7000</td>\n",
       "      <td>30.8500</td>\n",
       "      <td>53071044</td>\n",
       "      <td>BankOfAmerica</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2976</th>\n",
       "      <td>30.8800</td>\n",
       "      <td>30.9600</td>\n",
       "      <td>30.3800</td>\n",
       "      <td>30.4300</td>\n",
       "      <td>59482315</td>\n",
       "      <td>BankOfAmerica</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2977</th>\n",
       "      <td>30.5300</td>\n",
       "      <td>30.6700</td>\n",
       "      <td>30.0800</td>\n",
       "      <td>30.1400</td>\n",
       "      <td>59219530</td>\n",
       "      <td>BankOfAmerica</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2978</th>\n",
       "      <td>30.1300</td>\n",
       "      <td>30.3900</td>\n",
       "      <td>30.1100</td>\n",
       "      <td>30.3700</td>\n",
       "      <td>33743874</td>\n",
       "      <td>BankOfAmerica</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2979 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         open     high      low    close    volume          Index  Country\n",
       "0     39.5520  39.6064  39.2099  39.2254   1259799           HSBC        0\n",
       "1     39.5908  39.9874  39.4742  39.9874   3777024           HSBC        0\n",
       "2     39.4820  39.5675  38.9922  39.0388   2568714           HSBC        0\n",
       "3     38.9066  38.9999  38.6501  38.6889   4375561           HSBC        0\n",
       "4     39.4820  39.6064  39.1943  39.2176   1782934           HSBC        0\n",
       "5     39.2332  39.3809  39.0543  39.1399   1933295           HSBC        0\n",
       "6     38.8755  38.9300  37.9425  38.5956   3897856           HSBC        0\n",
       "7     37.9969  38.7200  37.9503  38.5334   2811671           HSBC        0\n",
       "8     38.7745  39.0583  38.6501  39.0233   2097831           HSBC        0\n",
       "9     38.6812  38.8911  38.5879  38.8911   1293619           HSBC        0\n",
       "10    39.0777  39.2643  39.0388  39.2176   1079173           HSBC        0\n",
       "11    39.2726  39.4140  39.0684  39.1077   1220001           HSBC        0\n",
       "12    39.7831  39.8223  39.5750  39.6181   2005554           HSBC        0\n",
       "13    39.7516  39.8066  39.6181  39.7831   1238468           HSBC        0\n",
       "14    39.2412  39.5553  39.2334  39.4689   1010551           HSBC        0\n",
       "15    39.7124  39.8223  39.6417  39.8223   1261213           HSBC        0\n",
       "16    39.7831  39.9323  39.5710  39.8066   1396924           HSBC        0\n",
       "17    39.2569  39.7124  39.2491  39.6181   1414834           HSBC        0\n",
       "18    40.0108  40.2071  39.9009  40.0658   1437351           HSBC        0\n",
       "19    39.4847  39.5239  39.2176  39.3433   1675728           HSBC        0\n",
       "20    39.2098  39.3904  39.1352  39.3904   1526208           HSBC        0\n",
       "21    39.5239  39.7203  39.4297  39.6574   1087882           HSBC        0\n",
       "22    39.7045  39.7909  39.4061  39.5475    722989           HSBC        0\n",
       "23    39.2412  39.3354  39.1234  39.3354   1308781           HSBC        0\n",
       "24    39.4768  39.6189  39.3311  39.5553    758835           HSBC        0\n",
       "25    39.6888  39.7595  39.5789  39.7124    791785           HSBC        0\n",
       "26    39.2334  39.4847  39.2176  39.4375    731131           HSBC        0\n",
       "27    39.3669  39.4768  39.2726  39.3826   1090781           HSBC        0\n",
       "28    39.1941  39.5082  39.1862  39.4768   1379257           HSBC        0\n",
       "29    38.9899  39.3669  38.9821  39.3040   1071482           HSBC        0\n",
       "...       ...      ...      ...      ...       ...            ...      ...\n",
       "2949  31.0300  31.3584  31.0101  31.3584  50153768  BankOfAmerica        0\n",
       "2950  31.2987  31.4380  31.1740  31.3684  41261494  BankOfAmerica        0\n",
       "2951  31.3982  31.6470  31.3385  31.3584  43946164  BankOfAmerica        0\n",
       "2952  31.3584  31.7565  31.3285  31.6470  43689582  BankOfAmerica        0\n",
       "2953  31.5773  31.6371  31.3982  31.4480  38219057  BankOfAmerica        0\n",
       "2954  31.0798  31.1743  30.7513  31.0399  55679849  BankOfAmerica        0\n",
       "2955  30.9305  30.9703  30.3035  30.3334  63960630  BankOfAmerica        0\n",
       "2956  30.4627  30.7513  30.2736  30.6419  42521241  BankOfAmerica        0\n",
       "2957  30.3334  30.4727  30.0099  30.2139  59747876  BankOfAmerica        0\n",
       "2958  30.4130  30.6916  30.3732  30.5722  44015361  BankOfAmerica        0\n",
       "2959  30.5125  30.7066  30.3980  30.5921  39843538  BankOfAmerica        0\n",
       "2960  30.5423  30.7762  30.5025  30.7215  40011041  BankOfAmerica        0\n",
       "2961  30.7613  31.0997  30.6817  30.8708  53510571  BankOfAmerica        0\n",
       "2962  30.7712  30.9354  30.7314  30.8310  33062884  BankOfAmerica        0\n",
       "2963  30.7911  30.8310  30.5921  30.6916  38259064  BankOfAmerica        0\n",
       "2964  30.7911  30.9305  30.7016  30.7414  43585013  BankOfAmerica        0\n",
       "2965  30.8807  31.3385  30.8409  31.1594  47729273  BankOfAmerica        0\n",
       "2966  31.2091  31.2589  31.0399  31.1196  38538983  BankOfAmerica        0\n",
       "2967  31.0897  31.1594  30.9205  30.9902  46401765  BankOfAmerica        0\n",
       "2968  30.8708  31.0001  30.8011  30.8608  47358915  BankOfAmerica        0\n",
       "2969  30.7314  30.8011  30.4727  30.7812  49265952  BankOfAmerica        0\n",
       "2970  30.7613  31.0101  30.6717  30.9902  44745551  BankOfAmerica        0\n",
       "2971  30.9205  31.0897  30.8210  31.0300  48516722  BankOfAmerica        0\n",
       "2972  31.0500  31.1100  30.7300  30.8500  42666015  BankOfAmerica        0\n",
       "2973  31.0000  31.1200  30.6300  30.8600  49103817  BankOfAmerica        0\n",
       "2974  30.8800  31.0000  30.7500  30.8200  34149198  BankOfAmerica        0\n",
       "2975  30.7200  30.9000  30.7000  30.8500  53071044  BankOfAmerica        0\n",
       "2976  30.8800  30.9600  30.3800  30.4300  59482315  BankOfAmerica        0\n",
       "2977  30.5300  30.6700  30.0800  30.1400  59219530  BankOfAmerica        0\n",
       "2978  30.1300  30.3900  30.1100  30.3700  33743874  BankOfAmerica        0\n",
       "\n",
       "[2979 rows x 7 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# dealing with missing values\n",
    "Indices.fillna(method=\"bfill\")\n",
    "Indices.fillna(method=\"ffill\")\n",
    "Indices.fillna(0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Applying functions on data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>Index</th>\n",
       "      <th>Country</th>\n",
       "      <th>ReturnOC</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>39.5520</td>\n",
       "      <td>39.6064</td>\n",
       "      <td>39.2099</td>\n",
       "      <td>39.2254</td>\n",
       "      <td>1259799</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.991743</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>39.5908</td>\n",
       "      <td>39.9874</td>\n",
       "      <td>39.4742</td>\n",
       "      <td>39.9874</td>\n",
       "      <td>3777024</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.010017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>39.4820</td>\n",
       "      <td>39.5675</td>\n",
       "      <td>38.9922</td>\n",
       "      <td>39.0388</td>\n",
       "      <td>2568714</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.988775</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>38.9066</td>\n",
       "      <td>38.9999</td>\n",
       "      <td>38.6501</td>\n",
       "      <td>38.6889</td>\n",
       "      <td>4375561</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.994405</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>39.4820</td>\n",
       "      <td>39.6064</td>\n",
       "      <td>39.1943</td>\n",
       "      <td>39.2176</td>\n",
       "      <td>1782934</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.993303</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      open     high      low    close   volume Index Country  ReturnOC\n",
       "0  39.5520  39.6064  39.2099  39.2254  1259799  HSBC     NaN  0.991743\n",
       "1  39.5908  39.9874  39.4742  39.9874  3777024  HSBC     NaN  1.010017\n",
       "2  39.4820  39.5675  38.9922  39.0388  2568714  HSBC     NaN  0.988775\n",
       "3  38.9066  38.9999  38.6501  38.6889  4375561  HSBC     NaN  0.994405\n",
       "4  39.4820  39.6064  39.1943  39.2176  1782934  HSBC     NaN  0.993303"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Open-to-close returns\n",
    "Indices['ReturnOC']=Indices['close']/Indices['open']\n",
    "Indices.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>Index</th>\n",
       "      <th>Country</th>\n",
       "      <th>ReturnOC</th>\n",
       "      <th>LogReturnOC</th>\n",
       "      <th>Green</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>39.5520</td>\n",
       "      <td>39.6064</td>\n",
       "      <td>39.2099</td>\n",
       "      <td>39.2254</td>\n",
       "      <td>1259799</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.991743</td>\n",
       "      <td>-0.008292</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>39.5908</td>\n",
       "      <td>39.9874</td>\n",
       "      <td>39.4742</td>\n",
       "      <td>39.9874</td>\n",
       "      <td>3777024</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.010017</td>\n",
       "      <td>0.009968</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>39.4820</td>\n",
       "      <td>39.5675</td>\n",
       "      <td>38.9922</td>\n",
       "      <td>39.0388</td>\n",
       "      <td>2568714</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.988775</td>\n",
       "      <td>-0.011289</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>38.9066</td>\n",
       "      <td>38.9999</td>\n",
       "      <td>38.6501</td>\n",
       "      <td>38.6889</td>\n",
       "      <td>4375561</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.994405</td>\n",
       "      <td>-0.005611</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>39.4820</td>\n",
       "      <td>39.6064</td>\n",
       "      <td>39.1943</td>\n",
       "      <td>39.2176</td>\n",
       "      <td>1782934</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.993303</td>\n",
       "      <td>-0.006719</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      open     high      low    close   volume Index Country  ReturnOC  \\\n",
       "0  39.5520  39.6064  39.2099  39.2254  1259799  HSBC     NaN  0.991743   \n",
       "1  39.5908  39.9874  39.4742  39.9874  3777024  HSBC     NaN  1.010017   \n",
       "2  39.4820  39.5675  38.9922  39.0388  2568714  HSBC     NaN  0.988775   \n",
       "3  38.9066  38.9999  38.6501  38.6889  4375561  HSBC     NaN  0.994405   \n",
       "4  39.4820  39.6064  39.1943  39.2176  1782934  HSBC     NaN  0.993303   \n",
       "\n",
       "   LogReturnOC  Green  \n",
       "0    -0.008292  False  \n",
       "1     0.009968   True  \n",
       "2    -0.011289  False  \n",
       "3    -0.005611  False  \n",
       "4    -0.006719  False  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Applying function column-wise\n",
    "Indices[\"LogReturnOC\"]=Indices[\"ReturnOC\"].map(np.log)\n",
    "Indices[\"Green\"]=Indices[\"ReturnOC\"].apply(lambda x: x>1.0)\n",
    "Indices.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>Index</th>\n",
       "      <th>Country</th>\n",
       "      <th>ReturnOC</th>\n",
       "      <th>LogReturnOC</th>\n",
       "      <th>Green</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>994</th>\n",
       "      <td>13.7784</td>\n",
       "      <td>13.9904</td>\n",
       "      <td>13.5757</td>\n",
       "      <td>13.9535</td>\n",
       "      <td>7550751</td>\n",
       "      <td>Barclays</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.012708</td>\n",
       "      <td>0.012628</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>999</th>\n",
       "      <td>12.6725</td>\n",
       "      <td>12.7554</td>\n",
       "      <td>12.3407</td>\n",
       "      <td>12.7093</td>\n",
       "      <td>8089264</td>\n",
       "      <td>Barclays</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.002904</td>\n",
       "      <td>0.002900</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1000</th>\n",
       "      <td>12.2301</td>\n",
       "      <td>12.5434</td>\n",
       "      <td>12.2301</td>\n",
       "      <td>12.4421</td>\n",
       "      <td>5028601</td>\n",
       "      <td>Barclays</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.017334</td>\n",
       "      <td>0.017186</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1002</th>\n",
       "      <td>12.9305</td>\n",
       "      <td>13.4466</td>\n",
       "      <td>12.9121</td>\n",
       "      <td>13.1517</td>\n",
       "      <td>6057250</td>\n",
       "      <td>Barclays</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.017107</td>\n",
       "      <td>0.016962</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1003</th>\n",
       "      <td>13.3545</td>\n",
       "      <td>13.4559</td>\n",
       "      <td>13.3360</td>\n",
       "      <td>13.4098</td>\n",
       "      <td>2435104</td>\n",
       "      <td>Barclays</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.004141</td>\n",
       "      <td>0.004132</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         open     high      low    close   volume     Index Country  ReturnOC  \\\n",
       "994   13.7784  13.9904  13.5757  13.9535  7550751  Barclays     NaN  1.012708   \n",
       "999   12.6725  12.7554  12.3407  12.7093  8089264  Barclays     NaN  1.002904   \n",
       "1000  12.2301  12.5434  12.2301  12.4421  5028601  Barclays     NaN  1.017334   \n",
       "1002  12.9305  13.4466  12.9121  13.1517  6057250  Barclays     NaN  1.017107   \n",
       "1003  13.3545  13.4559  13.3360  13.4098  2435104  Barclays     NaN  1.004141   \n",
       "\n",
       "      LogReturnOC  Green  \n",
       "994      0.012628   True  \n",
       "999      0.002900   True  \n",
       "1000     0.017186   True  \n",
       "1002     0.016962   True  \n",
       "1003     0.004132   True  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Selection based on multiple slicing\n",
    "IndexBarclaysPlus=Indices[(Indices[\"Index\"]==\"Barclays\") & (Indices[\"ReturnOC\"]>=1)]\n",
    "IndexBarclaysPlus.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index\n",
       "BankOfAmerica    993\n",
       "Barclays         993\n",
       "HSBC             993\n",
       "Name: ReturnOC, dtype: int64"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Group-by\n",
    "Indices.groupby(\"Index\").count()[\"ReturnOC\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index\n",
       "BankOfAmerica    0.999766\n",
       "Barclays         1.000092\n",
       "HSBC             1.000309\n",
       "Name: ReturnOC, dtype: float64"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Indices.groupby(\"Index\").mean()[\"ReturnOC\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Application: close-to-close returns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "Indices=pd.read_csv(\"data/hsbc_bcs_bac.csv\") # re-load the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "index_list=Indices[\"Index\"].drop_duplicates().tolist() # get the list of indices"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "import warnings\n",
    "import numpy as np\n",
    "warnings.filterwarnings('ignore')\n",
    "\n",
    "for idx in index_list:\n",
    "    Temp=Indices[Indices[\"Index\"]==idx] # temporary data frame\n",
    "    Temp[\"Return\"]=np.log(Temp[\"close\"]/Temp[\"close\"].shift(1))\n",
    "    try:\n",
    "        IxNew\n",
    "    except NameError:\n",
    "        IxNew=Temp\n",
    "    else:\n",
    "        IxNew=IxNew.append(Temp,ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>Index</th>\n",
       "      <th>Return</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2014-10-07</td>\n",
       "      <td>39.5520</td>\n",
       "      <td>39.6064</td>\n",
       "      <td>39.2099</td>\n",
       "      <td>39.2254</td>\n",
       "      <td>1259799</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2014-10-08</td>\n",
       "      <td>39.5908</td>\n",
       "      <td>39.9874</td>\n",
       "      <td>39.4742</td>\n",
       "      <td>39.9874</td>\n",
       "      <td>3777024</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>0.019240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2014-10-09</td>\n",
       "      <td>39.4820</td>\n",
       "      <td>39.5675</td>\n",
       "      <td>38.9922</td>\n",
       "      <td>39.0388</td>\n",
       "      <td>2568714</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>-0.024008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2014-10-10</td>\n",
       "      <td>38.9066</td>\n",
       "      <td>38.9999</td>\n",
       "      <td>38.6501</td>\n",
       "      <td>38.6889</td>\n",
       "      <td>4375561</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>-0.009003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2014-10-13</td>\n",
       "      <td>39.4820</td>\n",
       "      <td>39.6064</td>\n",
       "      <td>39.1943</td>\n",
       "      <td>39.2176</td>\n",
       "      <td>1782934</td>\n",
       "      <td>HSBC</td>\n",
       "      <td>0.013573</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date     open     high      low    close   volume Index    Return\n",
       "0  2014-10-07  39.5520  39.6064  39.2099  39.2254  1259799  HSBC       NaN\n",
       "1  2014-10-08  39.5908  39.9874  39.4742  39.9874  3777024  HSBC  0.019240\n",
       "2  2014-10-09  39.4820  39.5675  38.9922  39.0388  2568714  HSBC -0.024008\n",
       "3  2014-10-10  38.9066  38.9999  38.6501  38.6889  4375561  HSBC -0.009003\n",
       "4  2014-10-13  39.4820  39.6064  39.1943  39.2176  1782934  HSBC  0.013573"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "IxNew.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pivoting"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>Index</th>\n",
       "      <th>BankOfAmerica</th>\n",
       "      <th>Barclays</th>\n",
       "      <th>HSBC</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2014-10-07</th>\n",
       "      <td>16.1966</td>\n",
       "      <td>13.7416</td>\n",
       "      <td>39.5520</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-08</th>\n",
       "      <td>15.9137</td>\n",
       "      <td>13.7784</td>\n",
       "      <td>39.5908</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-09</th>\n",
       "      <td>16.0646</td>\n",
       "      <td>13.6218</td>\n",
       "      <td>39.4820</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-10</th>\n",
       "      <td>15.5744</td>\n",
       "      <td>13.4098</td>\n",
       "      <td>38.9066</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-13</th>\n",
       "      <td>15.5366</td>\n",
       "      <td>13.3821</td>\n",
       "      <td>39.4820</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Index       BankOfAmerica  Barclays     HSBC\n",
       "date                                        \n",
       "2014-10-07        16.1966   13.7416  39.5520\n",
       "2014-10-08        15.9137   13.7784  39.5908\n",
       "2014-10-09        16.0646   13.6218  39.4820\n",
       "2014-10-10        15.5744   13.4098  38.9066\n",
       "2014-10-13        15.5366   13.3821  39.4820"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "PivotOpen=Indices.pivot(index=\"date\",columns=\"Index\",values=\"open\")\n",
    "PivotOpen.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Running the pivot function without the values option creates a collection of tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead 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=\"3\" halign=\"left\">open</th>\n",
       "      <th colspan=\"3\" halign=\"left\">high</th>\n",
       "      <th colspan=\"3\" halign=\"left\">low</th>\n",
       "      <th colspan=\"3\" halign=\"left\">close</th>\n",
       "      <th colspan=\"3\" halign=\"left\">volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Index</th>\n",
       "      <th>BankOfAmerica</th>\n",
       "      <th>Barclays</th>\n",
       "      <th>HSBC</th>\n",
       "      <th>BankOfAmerica</th>\n",
       "      <th>Barclays</th>\n",
       "      <th>HSBC</th>\n",
       "      <th>BankOfAmerica</th>\n",
       "      <th>Barclays</th>\n",
       "      <th>HSBC</th>\n",
       "      <th>BankOfAmerica</th>\n",
       "      <th>Barclays</th>\n",
       "      <th>HSBC</th>\n",
       "      <th>BankOfAmerica</th>\n",
       "      <th>Barclays</th>\n",
       "      <th>HSBC</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",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2014-10-07</th>\n",
       "      <td>16.1966</td>\n",
       "      <td>13.7416</td>\n",
       "      <td>39.5520</td>\n",
       "      <td>16.2154</td>\n",
       "      <td>13.8316</td>\n",
       "      <td>39.6064</td>\n",
       "      <td>15.9137</td>\n",
       "      <td>13.6494</td>\n",
       "      <td>39.2099</td>\n",
       "      <td>15.9137</td>\n",
       "      <td>13.6586</td>\n",
       "      <td>39.2254</td>\n",
       "      <td>91396327</td>\n",
       "      <td>16603563</td>\n",
       "      <td>1259799</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-08</th>\n",
       "      <td>15.9137</td>\n",
       "      <td>13.7784</td>\n",
       "      <td>39.5908</td>\n",
       "      <td>16.1400</td>\n",
       "      <td>13.9904</td>\n",
       "      <td>39.9874</td>\n",
       "      <td>15.7629</td>\n",
       "      <td>13.5757</td>\n",
       "      <td>39.4742</td>\n",
       "      <td>16.1400</td>\n",
       "      <td>13.9535</td>\n",
       "      <td>39.9874</td>\n",
       "      <td>101266804</td>\n",
       "      <td>7550751</td>\n",
       "      <td>3777024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-09</th>\n",
       "      <td>16.0646</td>\n",
       "      <td>13.6218</td>\n",
       "      <td>39.4820</td>\n",
       "      <td>16.1306</td>\n",
       "      <td>13.6955</td>\n",
       "      <td>39.5675</td>\n",
       "      <td>15.6026</td>\n",
       "      <td>13.3176</td>\n",
       "      <td>38.9922</td>\n",
       "      <td>15.6403</td>\n",
       "      <td>13.3821</td>\n",
       "      <td>39.0388</td>\n",
       "      <td>121363088</td>\n",
       "      <td>7533839</td>\n",
       "      <td>2568714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-10</th>\n",
       "      <td>15.5744</td>\n",
       "      <td>13.4098</td>\n",
       "      <td>38.9066</td>\n",
       "      <td>15.8100</td>\n",
       "      <td>13.4743</td>\n",
       "      <td>38.9999</td>\n",
       "      <td>15.4235</td>\n",
       "      <td>13.1886</td>\n",
       "      <td>38.6501</td>\n",
       "      <td>15.5366</td>\n",
       "      <td>13.1978</td>\n",
       "      <td>38.6889</td>\n",
       "      <td>129552650</td>\n",
       "      <td>6477123</td>\n",
       "      <td>4375561</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-13</th>\n",
       "      <td>15.5366</td>\n",
       "      <td>13.3821</td>\n",
       "      <td>39.4820</td>\n",
       "      <td>15.7158</td>\n",
       "      <td>13.4559</td>\n",
       "      <td>39.6064</td>\n",
       "      <td>15.4612</td>\n",
       "      <td>13.1333</td>\n",
       "      <td>39.1943</td>\n",
       "      <td>15.4612</td>\n",
       "      <td>13.1517</td>\n",
       "      <td>39.2176</td>\n",
       "      <td>92674130</td>\n",
       "      <td>2569142</td>\n",
       "      <td>1782934</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    open                            high                    \\\n",
       "Index      BankOfAmerica Barclays     HSBC BankOfAmerica Barclays     HSBC   \n",
       "date                                                                         \n",
       "2014-10-07       16.1966  13.7416  39.5520       16.2154  13.8316  39.6064   \n",
       "2014-10-08       15.9137  13.7784  39.5908       16.1400  13.9904  39.9874   \n",
       "2014-10-09       16.0646  13.6218  39.4820       16.1306  13.6955  39.5675   \n",
       "2014-10-10       15.5744  13.4098  38.9066       15.8100  13.4743  38.9999   \n",
       "2014-10-13       15.5366  13.3821  39.4820       15.7158  13.4559  39.6064   \n",
       "\n",
       "                     low                           close                    \\\n",
       "Index      BankOfAmerica Barclays     HSBC BankOfAmerica Barclays     HSBC   \n",
       "date                                                                         \n",
       "2014-10-07       15.9137  13.6494  39.2099       15.9137  13.6586  39.2254   \n",
       "2014-10-08       15.7629  13.5757  39.4742       16.1400  13.9535  39.9874   \n",
       "2014-10-09       15.6026  13.3176  38.9922       15.6403  13.3821  39.0388   \n",
       "2014-10-10       15.4235  13.1886  38.6501       15.5366  13.1978  38.6889   \n",
       "2014-10-13       15.4612  13.1333  39.1943       15.4612  13.1517  39.2176   \n",
       "\n",
       "                  volume                     \n",
       "Index      BankOfAmerica  Barclays     HSBC  \n",
       "date                                         \n",
       "2014-10-07      91396327  16603563  1259799  \n",
       "2014-10-08     101266804   7550751  3777024  \n",
       "2014-10-09     121363088   7533839  2568714  \n",
       "2014-10-10     129552650   6477123  4375561  \n",
       "2014-10-13      92674130   2569142  1782934  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "PivotTable=Indices.pivot(index=\"date\",columns=\"Index\")\n",
    "PivotTable.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>Index</th>\n",
       "      <th>BankOfAmerica</th>\n",
       "      <th>Barclays</th>\n",
       "      <th>HSBC</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2014-10-07</th>\n",
       "      <td>15.9137</td>\n",
       "      <td>13.6586</td>\n",
       "      <td>39.2254</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-08</th>\n",
       "      <td>16.1400</td>\n",
       "      <td>13.9535</td>\n",
       "      <td>39.9874</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-09</th>\n",
       "      <td>15.6403</td>\n",
       "      <td>13.3821</td>\n",
       "      <td>39.0388</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-10</th>\n",
       "      <td>15.5366</td>\n",
       "      <td>13.1978</td>\n",
       "      <td>38.6889</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-13</th>\n",
       "      <td>15.4612</td>\n",
       "      <td>13.1517</td>\n",
       "      <td>39.2176</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Index       BankOfAmerica  Barclays     HSBC\n",
       "date                                        \n",
       "2014-10-07        15.9137   13.6586  39.2254\n",
       "2014-10-08        16.1400   13.9535  39.9874\n",
       "2014-10-09        15.6403   13.3821  39.0388\n",
       "2014-10-10        15.5366   13.1978  38.6889\n",
       "2014-10-13        15.4612   13.1517  39.2176"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "PivotClose=PivotTable[\"close\"]\n",
    "PivotClose.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Stacking and unstacking"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, let us index the data by date and index name:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>Return</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th>Index</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>2014-10-07</th>\n",
       "      <th>HSBC</th>\n",
       "      <td>39.5520</td>\n",
       "      <td>39.6064</td>\n",
       "      <td>39.2099</td>\n",
       "      <td>39.2254</td>\n",
       "      <td>1259799</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-08</th>\n",
       "      <th>HSBC</th>\n",
       "      <td>39.5908</td>\n",
       "      <td>39.9874</td>\n",
       "      <td>39.4742</td>\n",
       "      <td>39.9874</td>\n",
       "      <td>3777024</td>\n",
       "      <td>0.019240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-09</th>\n",
       "      <th>HSBC</th>\n",
       "      <td>39.4820</td>\n",
       "      <td>39.5675</td>\n",
       "      <td>38.9922</td>\n",
       "      <td>39.0388</td>\n",
       "      <td>2568714</td>\n",
       "      <td>-0.024008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-10</th>\n",
       "      <th>HSBC</th>\n",
       "      <td>38.9066</td>\n",
       "      <td>38.9999</td>\n",
       "      <td>38.6501</td>\n",
       "      <td>38.6889</td>\n",
       "      <td>4375561</td>\n",
       "      <td>-0.009003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-13</th>\n",
       "      <th>HSBC</th>\n",
       "      <td>39.4820</td>\n",
       "      <td>39.6064</td>\n",
       "      <td>39.1943</td>\n",
       "      <td>39.2176</td>\n",
       "      <td>1782934</td>\n",
       "      <td>0.013573</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     open     high      low    close   volume    Return\n",
       "date       Index                                                       \n",
       "2014-10-07 HSBC   39.5520  39.6064  39.2099  39.2254  1259799       NaN\n",
       "2014-10-08 HSBC   39.5908  39.9874  39.4742  39.9874  3777024  0.019240\n",
       "2014-10-09 HSBC   39.4820  39.5675  38.9922  39.0388  2568714 -0.024008\n",
       "2014-10-10 HSBC   38.9066  38.9999  38.6501  38.6889  4375561 -0.009003\n",
       "2014-10-13 HSBC   39.4820  39.6064  39.1943  39.2176  1782934  0.013573"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Multi-level index\n",
    "IxNew.set_index([\"date\",\"Index\"], inplace=True)\n",
    "IxNew.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To collapse the column of a database to a single (data) series:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date        Index        \n",
       "2014-10-07  HSBC   open      3.955200e+01\n",
       "                   high      3.960640e+01\n",
       "                   low       3.920990e+01\n",
       "                   close     3.922540e+01\n",
       "                   volume    1.259799e+06\n",
       "2014-10-08  HSBC   open      3.959080e+01\n",
       "                   high      3.998740e+01\n",
       "                   low       3.947420e+01\n",
       "                   close     3.998740e+01\n",
       "                   volume    3.777024e+06\n",
       "dtype: float64"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "IxNewStack=IxNew.stack()\n",
    "IxNewStack.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Unstack the dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>Return</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th>Index</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 rowspan=\"3\" valign=\"top\">2014-10-07</th>\n",
       "      <th>BankOfAmerica</th>\n",
       "      <td>16.1966</td>\n",
       "      <td>16.2154</td>\n",
       "      <td>15.9137</td>\n",
       "      <td>15.9137</td>\n",
       "      <td>91396327.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Barclays</th>\n",
       "      <td>13.7416</td>\n",
       "      <td>13.8316</td>\n",
       "      <td>13.6494</td>\n",
       "      <td>13.6586</td>\n",
       "      <td>16603563.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HSBC</th>\n",
       "      <td>39.5520</td>\n",
       "      <td>39.6064</td>\n",
       "      <td>39.2099</td>\n",
       "      <td>39.2254</td>\n",
       "      <td>1259799.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">2014-10-08</th>\n",
       "      <th>BankOfAmerica</th>\n",
       "      <td>15.9137</td>\n",
       "      <td>16.1400</td>\n",
       "      <td>15.7629</td>\n",
       "      <td>16.1400</td>\n",
       "      <td>101266804.0</td>\n",
       "      <td>0.014120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Barclays</th>\n",
       "      <td>13.7784</td>\n",
       "      <td>13.9904</td>\n",
       "      <td>13.5757</td>\n",
       "      <td>13.9535</td>\n",
       "      <td>7550751.0</td>\n",
       "      <td>0.021361</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             open     high      low    close       volume  \\\n",
       "date       Index                                                            \n",
       "2014-10-07 BankOfAmerica  16.1966  16.2154  15.9137  15.9137   91396327.0   \n",
       "           Barclays       13.7416  13.8316  13.6494  13.6586   16603563.0   \n",
       "           HSBC           39.5520  39.6064  39.2099  39.2254    1259799.0   \n",
       "2014-10-08 BankOfAmerica  15.9137  16.1400  15.7629  16.1400  101266804.0   \n",
       "           Barclays       13.7784  13.9904  13.5757  13.9535    7550751.0   \n",
       "\n",
       "                            Return  \n",
       "date       Index                    \n",
       "2014-10-07 BankOfAmerica       NaN  \n",
       "           Barclays            NaN  \n",
       "           HSBC                NaN  \n",
       "2014-10-08 BankOfAmerica  0.014120  \n",
       "           Barclays       0.021361  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "IxNew=IxNewStack.unstack()\n",
    "IxNew.head()"
   ]
  }
 ],
 "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.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}