Pandas 01 - Basics of Pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pandas 01 - Basics of Pandas\n",
    "\n",
    "by Nova@Douban\n",
    "\n",
    "The video record of this session is here: https://zoom.us/recording/share/L9Jwofdbg3CX2L4wLoPAVrHYyi0F0ok2_58ozScsXsmwIumekTziMw\n",
    "\n",
    "\n",
    "---\n",
    "\n",
    "## 1.1 Data Structure of pandas\n",
    "\n",
    "`pandas` significantly simplies data structures. If you used `R` or a retional database, you will find `pandas` very similar.\n",
    "\n",
    "### 1.1.1 Three primary data structures in pandas\n",
    "\n",
    "1. `Series` (a column):\n",
    "\n",
    "    1. A one-dimensional array-like object containing an array of data.\n",
    "    \n",
    "    2. A fixed-length, __ordered dict__.\n",
    "    \n",
    "    3. Automatically aligns differently-indexed data in operations\n",
    "    \n",
    "    4. The column returned when indexing a DataFrame is a view, not a copy.\n",
    "\n",
    "\n",
    "2. `DataFrame` (a collection of columns): \n",
    "\n",
    "    1. A tabular, spreadsheet-like data structure containing an ordered collection of columns;\n",
    " \n",
    "    2. __A collection of Series__.\n",
    "    \n",
    "   \n",
    "3. `index`:\n",
    "        \n",
    "    1. an Index also functions as __a fixed-size set__\n",
    "    \n",
    "    2. Index objects are __immutable__ and thus can’t be modified by the user\n",
    "    \n",
    "    3. It is a class in pandas, more complicated than the one in RDS.\n",
    "    \n",
    "        a. Identication: Indices are used to locate Series / rows / items in a DataFrame.   \n",
    "        \n",
    "        b. Alignemnt: pandas will always align with index automatically first.\n",
    "        \n",
    "        c. Selection: using index to select relevant columns/rows.\n",
    "        \n",
    "\n",
    "### 1.1.2 Example of DataFrame, Series and index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-12-28T11:47:52.640421Z",
     "start_time": "2018-12-28T11:47:52.039021Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         Date         Open         High          Low        Close  \\\n",
      "0  2018-11-23  6919.520020  6987.890137  6919.160156  6938.979980   \n",
      "1  2018-11-26  7026.500000  7083.930176  7003.120117  7081.850098   \n",
      "2  2018-11-27  7041.229980  7105.140137  7014.359863  7082.700195   \n",
      "3  2018-11-28  7135.080078  7292.709961  7090.979980  7291.589844   \n",
      "4  2018-11-29  7267.370117  7319.959961  7217.689941  7273.080078   \n",
      "\n",
      "     Adj Close      Volume  \n",
      "0  6938.979980   958950000  \n",
      "1  7081.850098  2011180000  \n",
      "2  7082.700195  2067360000  \n",
      "3  7291.589844  2390260000  \n",
      "4  7273.080078  1983460000  \n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n"
     ]
    }
   ],
   "source": [
    "# Download Nasdaq dataset: https://finance.yahoo.com/quote/%5EIXIC/history?p=%5EIXIC\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "in_file = '../data/nasdaq.csv'\n",
    "df = pd.read_csv(in_file, engine='c')\n",
    "\n",
    "print(df.head())\n",
    "print()\n",
    "print(type(df))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-12-28T11:47:52.656310Z",
     "start_time": "2018-12-28T11:47:52.644446Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    2018-11-23\n",
      "1    2018-11-26\n",
      "2    2018-11-27\n",
      "3    2018-11-28\n",
      "4    2018-11-29\n",
      "Name: Date, dtype: object\n",
      "\n",
      "<class 'pandas.core.series.Series'>\n",
      "\n"
     ]
    }
   ],
   "source": [
    "print(df['Date'].head())\n",
    "print()\n",
    "print(type(df['Date']))\n",
    "print()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-12-28T11:47:52.678080Z",
     "start_time": "2018-12-28T11:47:52.661673Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "RangeIndex(start=0, stop=20, step=1)"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-12-28T11:47:52.703980Z",
     "start_time": "2018-12-28T11:47:52.681052Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 0,  1,  2,  3,  4,  5,  6,  7,  8,  9, 10, 11, 12, 13, 14, 15, 16,\n",
       "       17, 18, 19])"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.index.values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "### 1.1.3 Two other data structure in pandas\n",
    "\n",
    "1. items:\n",
    "\n",
    "    1. The smallest unit in pandas.\n",
    "    \n",
    "    \n",
    "2. rows:\n",
    "\n",
    "    1. Row is not a primary data structure in pandas\n",
    "    \n",
    "### 1.1.4 Examples of items and rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-12-28T11:47:52.724236Z",
     "start_time": "2018-12-28T11:47:52.710937Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'2018-11-23'"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Date'][0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-12-28T11:47:52.761309Z",
     "start_time": "2018-12-28T11:47:52.731362Z"
    }
   },
   "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>Adj Close</th>\n",
       "      <th>Volume</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-11-23</td>\n",
       "      <td>6919.52002</td>\n",
       "      <td>6987.890137</td>\n",
       "      <td>6919.160156</td>\n",
       "      <td>6938.97998</td>\n",
       "      <td>6938.97998</td>\n",
       "      <td>958950000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date        Open         High          Low       Close   Adj Close  \\\n",
       "0  2018-11-23  6919.52002  6987.890137  6919.160156  6938.97998  6938.97998   \n",
       "\n",
       "      Volume  \n",
       "0  958950000  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[0:0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 1.2 Functions based on pandas\n",
    "\n",
    "<img src=\"../image/folder3.png\">\n",
    "\n",
    "\n",
    "\n",
    "### 1.2.1 Three levels of functions\n",
    "\n",
    "Each level of function only handles ite related levels of problems.\n",
    "\n",
    "1. DataFrame-level functions\n",
    "\n",
    "2. Series-level functions\n",
    "\n",
    "3. Item-level functions\n",
    "\n",
    "### 1.2.1 Example of different levels of pandas functions\n",
    "\n",
    "The following is a sample script to analyse logs:\n",
    "\n",
    "1. `prepare_overall_chat` is the overall wrapper;\n",
    "2. `clean_chat_log` is a DataFrame-level funtion;\n",
    "3. `get_all_mentions` and `count_active_user` are Series-level functions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-12-28T11:47:52.786553Z",
     "start_time": "2018-12-28T11:47:52.765005Z"
    }
   },
   "outputs": [],
   "source": [
    "def prepare_overall_chat(chat_base, time, day):\n",
    "    '''\n",
    "    an overall wrapper\n",
    "    '''\n",
    "    # 'reading starts'\n",
    "    overall = csv2pd(chat_base, time, day, HEAD_ANALYSIS, sep=',', engine='c')\n",
    "    \n",
    "    # 'overall'\n",
    "    clean_overall = clean_chat_log(overall)\n",
    "    \n",
    "    # 'cleaned kom records'\n",
    "    all_mentions = get_all_mentions(clean_overall, switch=False)\n",
    "    \n",
    "    # 'all_mentions'\n",
    "    active_user_count = count_active_user(overall, overall['RoomName'], colname='Name', header=HEAD_ACTIVE)\n",
    "    return clean_overall, all_mentions, active_user_count\n",
    "\n",
    "    \n",
    "def clean_chat_log(df):\n",
    "    '''\n",
    "    at Dataframe level\n",
    "    '''\n",
    "    # 'remove null or duplicates'\n",
    "    df = df[df.TextMsg.notnull()]\n",
    "    df = df[df.Name.notnull()]\n",
    "    df = df.drop_duplicates()\n",
    "\n",
    "    # replace ?? or ** in data\n",
    "    df.Name = df.loc[:, 'Name'].str.replace('\\?\\?|\\*\\*', '?#')\n",
    "    df.TextMsg = df.loc[:, 'TextMsg'].str.replace('\\?\\?|\\*\\*', '?#')\n",
    "\n",
    "    # 'to_uni' and 'strip new lines'\n",
    "    df = batch_to_uni(df, col_list=['Name', 'TextMsg', 'RoomName'])\n",
    "    df = batch_strip(df, col_list=['Name', 'RoomName'], strip_str='\\n\\r ')\n",
    "    return df\n",
    "\n",
    "    \n",
    "def get_all_mentions(df, switch=True):\n",
    "    '''\n",
    "    at Series level\n",
    "    '''\n",
    "    all_mentions = df[df['TextMsg'].str.contains('@')]\n",
    "    all_mentions.MsgTime = pd.to_datetime(pd.Series(all_mentions.MsgTime))  # todo fix .loc\n",
    "    if switch:\n",
    "        cleaned_mentions = pd.DataFrame.copy(all_mentions)\n",
    "        cleaned_mentions = batch_replace(cleaned_mentions, 'TextMsg', CN_PUNCS, '')\n",
    "        return all_mentions, cleaned_mentions\n",
    "    else:\n",
    "        return all_mentions\n",
    "    \n",
    "def count_active_user(df, col1, colname, header):\n",
    "    '''\n",
    "    at Series level\n",
    "    '''\n",
    "    active_user_count = df.groupby([col1])[colname].unique().apply(len)\n",
    "    active_user_count = active_user_count.subtract(1)  # Exclude 班长\n",
    "    active_user_count = active_user_count.reset_index()\n",
    "    active_user_count.columns = header\n",
    "    active_user_count = batch_to_uni(active_user_count, ['RoomName'])\n",
    "    return active_user_count"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.3 Summarizing and computing descriptive statistics\n",
    "\n",
    "\n",
    "### 1.3.1 Take a glance at the dataset\n",
    "\n",
    "__Be careful if the function should include parentheses:)__\n",
    "\n",
    "1. `DataFrame.describe()`: provide descriptive stats of the dataset\n",
    "2. `DataFrame.values`: access values of the dataset\n",
    "3. `DataFrame.head()`: access the head of the dataset\n",
    "4. `DataFrame.tail()`: access the tail of the dataset\n",
    "5. `DataFrame.shape`: provide the length and width of the dataset\n",
    "6. `DataFrame.size`: provide the product of the length and width of the dataset\n",
    "7. `DataFrame.columns`: provide the colomn names of the dataset\n",
    "8. `DataFrame.index`: provide the row index of the dataset\n",
    "9. `DataFrame.axes`: provide the colomn names and row index of the dataset\n",
    "\n",
    "### 1.3.2 Examples of a glance at the dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-12-28T11:47:52.848115Z",
     "start_time": "2018-12-28T11:47:52.798310Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>Adj Close</th>\n",
       "      <th>Volume</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>20.000000</td>\n",
       "      <td>20.000000</td>\n",
       "      <td>20.000000</td>\n",
       "      <td>20.000000</td>\n",
       "      <td>20.000000</td>\n",
       "      <td>2.000000e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>7036.433984</td>\n",
       "      <td>7094.115039</td>\n",
       "      <td>6932.403003</td>\n",
       "      <td>6996.186035</td>\n",
       "      <td>6996.186035</td>\n",
       "      <td>2.492132e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>236.773607</td>\n",
       "      <td>234.200045</td>\n",
       "      <td>268.133301</td>\n",
       "      <td>278.415894</td>\n",
       "      <td>278.415894</td>\n",
       "      <td>6.665222e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>6573.490234</td>\n",
       "      <td>6586.680176</td>\n",
       "      <td>6304.629883</td>\n",
       "      <td>6333.000000</td>\n",
       "      <td>6333.000000</td>\n",
       "      <td>9.589500e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>6911.255005</td>\n",
       "      <td>6973.870117</td>\n",
       "      <td>6842.670166</td>\n",
       "      <td>6878.972656</td>\n",
       "      <td>6878.972656</td>\n",
       "      <td>2.186262e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>7033.864990</td>\n",
       "      <td>7117.485108</td>\n",
       "      <td>6983.674805</td>\n",
       "      <td>7051.080078</td>\n",
       "      <td>7051.080078</td>\n",
       "      <td>2.443730e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>7142.332397</td>\n",
       "      <td>7227.205078</td>\n",
       "      <td>7092.375000</td>\n",
       "      <td>7165.887574</td>\n",
       "      <td>7165.887574</td>\n",
       "      <td>2.643168e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>7486.129883</td>\n",
       "      <td>7486.509766</td>\n",
       "      <td>7392.220215</td>\n",
       "      <td>7441.509766</td>\n",
       "      <td>7441.509766</td>\n",
       "      <td>4.534120e+09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              Open         High          Low        Close    Adj Close  \\\n",
       "count    20.000000    20.000000    20.000000    20.000000    20.000000   \n",
       "mean   7036.433984  7094.115039  6932.403003  6996.186035  6996.186035   \n",
       "std     236.773607   234.200045   268.133301   278.415894   278.415894   \n",
       "min    6573.490234  6586.680176  6304.629883  6333.000000  6333.000000   \n",
       "25%    6911.255005  6973.870117  6842.670166  6878.972656  6878.972656   \n",
       "50%    7033.864990  7117.485108  6983.674805  7051.080078  7051.080078   \n",
       "75%    7142.332397  7227.205078  7092.375000  7165.887574  7165.887574   \n",
       "max    7486.129883  7486.509766  7392.220215  7441.509766  7441.509766   \n",
       "\n",
       "             Volume  \n",
       "count  2.000000e+01  \n",
       "mean   2.492132e+09  \n",
       "std    6.665222e+08  \n",
       "min    9.589500e+08  \n",
       "25%    2.186262e+09  \n",
       "50%    2.443730e+09  \n",
       "75%    2.643168e+09  \n",
       "max    4.534120e+09  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-12-28T11:47:52.858589Z",
     "start_time": "2018-12-28T11:47:52.850909Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([['2018-11-23', 6919.52002, 6987.890137, 6919.160156, 6938.97998,\n",
       "        6938.97998, 958950000],\n",
       "       ['2018-11-26', 7026.5, 7083.930176000001, 7003.120117,\n",
       "        7081.850098000001, 7081.850098000001, 2011180000],\n",
       "       ['2018-11-27', 7041.22998, 7105.140137, 7014.359863, 7082.700195,\n",
       "        7082.700195, 2067360000],\n",
       "       ['2018-11-28', 7135.080078, 7292.709961, 7090.97998, 7291.589844,\n",
       "        7291.589844, 2390260000],\n",
       "       ['2018-11-29', 7267.370117, 7319.959961, 7217.689941, 7273.080078,\n",
       "        7273.080078, 1983460000],\n",
       "       ['2018-11-30', 7279.299805, 7332.790039, 7255.680176000001,\n",
       "        7330.540039, 7330.540039, 2542820000],\n",
       "       ['2018-12-03', 7486.129883, 7486.509765999999, 7392.220215,\n",
       "        7441.509765999999, 7441.509765999999, 2621020000],\n",
       "       ['2018-12-04', 7407.950195, 7421.109863, 7150.109863,\n",
       "        7158.430176000001, 7158.430176000001, 2635810000],\n",
       "       ['2018-12-06', 7017.049805, 7189.52002, 6984.339844,\n",
       "        7188.259765999999, 7188.259765999999, 2833870000],\n",
       "       ['2018-12-07', 7163.490234000001, 7205.370117, 6945.27002,\n",
       "        6969.25, 6969.25, 2475160000],\n",
       "       ['2018-12-10', 6959.629883, 7047.620117, 6878.990234000001,\n",
       "        7020.52002, 7020.52002, 2367560000],\n",
       "       ['2018-12-11', 7121.660156, 7129.830078, 6983.009765999999,\n",
       "        7031.830078, 7031.830078, 2246060000],\n",
       "       ['2018-12-12', 7127.0, 7197.290039, 7096.560059, 7098.310059,\n",
       "        7098.310059, 2412300000],\n",
       "       ['2018-12-13', 7135.279785, 7154.640137, 7034.819823999999,\n",
       "        7070.330078, 7070.330078, 2143520000],\n",
       "       ['2018-12-14', 6986.370117, 7027.169922, 6898.990234000001,\n",
       "        6910.660156, 6910.660156, 2200510000],\n",
       "       ['2018-12-17', 6886.459961, 6931.810059, 6710.009765999999,\n",
       "        6753.72998, 6753.72998, 2665240000],\n",
       "       ['2018-12-18', 6809.819823999999, 6847.27002, 6733.709961,\n",
       "        6783.910156, 6783.910156, 2595400000],\n",
       "       ['2018-12-19', 6777.589844, 6868.859863, 6586.5, 6636.830078,\n",
       "        6636.830078, 2899950000],\n",
       "       ['2018-12-20', 6607.759765999999, 6666.200195, 6447.910156,\n",
       "        6528.410156, 6528.410156, 3258090000],\n",
       "       ['2018-12-21', 6573.490234000001, 6586.680176, 6304.629883,\n",
       "        6333.0, 6333.0, 4534120000]], dtype=object)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-12-28T11:47:52.878993Z",
     "start_time": "2018-12-28T11:47:52.861182Z"
    }
   },
   "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>Adj Close</th>\n",
       "      <th>Volume</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-11-23</td>\n",
       "      <td>6919.520020</td>\n",
       "      <td>6987.890137</td>\n",
       "      <td>6919.160156</td>\n",
       "      <td>6938.979980</td>\n",
       "      <td>6938.979980</td>\n",
       "      <td>958950000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-11-26</td>\n",
       "      <td>7026.500000</td>\n",
       "      <td>7083.930176</td>\n",
       "      <td>7003.120117</td>\n",
       "      <td>7081.850098</td>\n",
       "      <td>7081.850098</td>\n",
       "      <td>2011180000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-11-27</td>\n",
       "      <td>7041.229980</td>\n",
       "      <td>7105.140137</td>\n",
       "      <td>7014.359863</td>\n",
       "      <td>7082.700195</td>\n",
       "      <td>7082.700195</td>\n",
       "      <td>2067360000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-11-28</td>\n",
       "      <td>7135.080078</td>\n",
       "      <td>7292.709961</td>\n",
       "      <td>7090.979980</td>\n",
       "      <td>7291.589844</td>\n",
       "      <td>7291.589844</td>\n",
       "      <td>2390260000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-11-29</td>\n",
       "      <td>7267.370117</td>\n",
       "      <td>7319.959961</td>\n",
       "      <td>7217.689941</td>\n",
       "      <td>7273.080078</td>\n",
       "      <td>7273.080078</td>\n",
       "      <td>1983460000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date         Open         High          Low        Close  \\\n",
       "0  2018-11-23  6919.520020  6987.890137  6919.160156  6938.979980   \n",
       "1  2018-11-26  7026.500000  7083.930176  7003.120117  7081.850098   \n",
       "2  2018-11-27  7041.229980  7105.140137  7014.359863  7082.700195   \n",
       "3  2018-11-28  7135.080078  7292.709961  7090.979980  7291.589844   \n",
       "4  2018-11-29  7267.370117  7319.959961  7217.689941  7273.080078   \n",
       "\n",
       "     Adj Close      Volume  \n",
       "0  6938.979980   958950000  \n",
       "1  7081.850098  2011180000  \n",
       "2  7082.700195  2067360000  \n",
       "3  7291.589844  2390260000  \n",
       "4  7273.080078  1983460000  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-12-28T11:47:52.903141Z",
     "start_time": "2018-12-28T11:47:52.882368Z"
    }
   },
   "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>Adj Close</th>\n",
       "      <th>Volume</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2018-12-17</td>\n",
       "      <td>6886.459961</td>\n",
       "      <td>6931.810059</td>\n",
       "      <td>6710.009766</td>\n",
       "      <td>6753.729980</td>\n",
       "      <td>6753.729980</td>\n",
       "      <td>2665240000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2018-12-18</td>\n",
       "      <td>6809.819824</td>\n",
       "      <td>6847.270020</td>\n",
       "      <td>6733.709961</td>\n",
       "      <td>6783.910156</td>\n",
       "      <td>6783.910156</td>\n",
       "      <td>2595400000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2018-12-19</td>\n",
       "      <td>6777.589844</td>\n",
       "      <td>6868.859863</td>\n",
       "      <td>6586.500000</td>\n",
       "      <td>6636.830078</td>\n",
       "      <td>6636.830078</td>\n",
       "      <td>2899950000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2018-12-20</td>\n",
       "      <td>6607.759766</td>\n",
       "      <td>6666.200195</td>\n",
       "      <td>6447.910156</td>\n",
       "      <td>6528.410156</td>\n",
       "      <td>6528.410156</td>\n",
       "      <td>3258090000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2018-12-21</td>\n",
       "      <td>6573.490234</td>\n",
       "      <td>6586.680176</td>\n",
       "      <td>6304.629883</td>\n",
       "      <td>6333.000000</td>\n",
       "      <td>6333.000000</td>\n",
       "      <td>4534120000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Date         Open         High          Low        Close  \\\n",
       "15  2018-12-17  6886.459961  6931.810059  6710.009766  6753.729980   \n",
       "16  2018-12-18  6809.819824  6847.270020  6733.709961  6783.910156   \n",
       "17  2018-12-19  6777.589844  6868.859863  6586.500000  6636.830078   \n",
       "18  2018-12-20  6607.759766  6666.200195  6447.910156  6528.410156   \n",
       "19  2018-12-21  6573.490234  6586.680176  6304.629883  6333.000000   \n",
       "\n",
       "      Adj Close      Volume  \n",
       "15  6753.729980  2665240000  \n",
       "16  6783.910156  2595400000  \n",
       "17  6636.830078  2899950000  \n",
       "18  6528.410156  3258090000  \n",
       "19  6333.000000  4534120000  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-12-28T11:47:52.936981Z",
     "start_time": "2018-12-28T11:47:52.915987Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(20, 7)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-12-28T11:47:52.949585Z",
     "start_time": "2018-12-28T11:47:52.941610Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "140"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.size"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-12-28T11:47:52.960181Z",
     "start_time": "2018-12-28T11:47:52.952861Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Date', 'Open', 'High', 'Low', 'Close', 'Adj Close', 'Volume'], dtype='object')"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-12-28T11:47:52.971291Z",
     "start_time": "2018-12-28T11:47:52.964021Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "RangeIndex(start=0, stop=20, step=1)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-12-28T11:47:52.984913Z",
     "start_time": "2018-12-28T11:47:52.973669Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[RangeIndex(start=0, stop=20, step=1),\n",
       " Index(['Date', 'Open', 'High', 'Low', 'Close', 'Adj Close', 'Volume'], dtype='object')]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.axes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-12-28T12:34:54.088767Z",
     "start_time": "2018-12-28T12:34:54.045968Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 20 entries, 0 to 19\n",
      "Data columns (total 7 columns):\n",
      "Date         20 non-null object\n",
      "Open         20 non-null float64\n",
      "High         20 non-null float64\n",
      "Low          20 non-null float64\n",
      "Close        20 non-null float64\n",
      "Adj Close    20 non-null float64\n",
      "Volume       20 non-null int64\n",
      "dtypes: float64(5), int64(1), object(1)\n",
      "memory usage: 1.2+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 1.4 Dive into index\n",
    "\n",
    "### 1.4.1 Index labels\n",
    "\n",
    "Index labels:\n",
    "    \n",
    "1. do not need to be integers;\n",
    "\n",
    "2. can have repeated labels (__Be careful, this is different from dict__);\n",
    "\n",
    "3. can have hierarchical sets of labels.\n",
    "\n",
    "### 1.4.2 Examples of index labels"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-12-28T11:47:53.010408Z",
     "start_time": "2018-12-28T11:47:52.990897Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0   -0.281210\n",
      "1    0.770726\n",
      "2   -0.176266\n",
      "3   -1.612378\n",
      "4   -1.868139\n",
      "5   -0.496955\n",
      "dtype: float64\n",
      "\n",
      "a   -0.281210\n",
      "a    0.770726\n",
      "a   -0.176266\n",
      "a   -1.612378\n",
      "a   -1.868139\n",
      "a   -0.496955\n",
      "dtype: float64\n",
      "\n",
      "MultiIndex(levels=[['a', 'b'], [-1.1125657480588875, -0.17357278036727714, -0.14227622735236414, 0.04140226853409916, 0.923176857116319, 1.2940772084264573]],\n",
      "           labels=[[0, 0, 0, 1, 1, 1], [4, 1, 3, 0, 5, 2]],\n",
      "           names=['letter', 'float'])\n",
      "\n",
      "letter  float    \n",
      "a        0.923177   -0.281210\n",
      "        -0.173573    0.770726\n",
      "         0.041402   -0.176266\n",
      "b       -1.112566   -1.612378\n",
      "         1.294077   -1.868139\n",
      "        -0.142276   -0.496955\n",
      "dtype: float64\n"
     ]
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "# The default index is int\n",
    "aray = np.random.randn(6)\n",
    "srs = pd.Series(aray)\n",
    "print(srs)\n",
    "print()\n",
    "\n",
    "# We can set repeated non-int labels to index\n",
    "ind = ['a'] * 6\n",
    "srs.index = ind\n",
    "print(srs)\n",
    "print()\n",
    "\n",
    "# We can set multi-level labels to index\n",
    "ind = zip(['a'] * 3 + ['b'] * 3, np.random.randn(6))\n",
    "ind = pd.MultiIndex.from_tuples(ind, names=['letter', 'float'])\n",
    "srs.index = ind\n",
    "print(ind)\n",
    "print()\n",
    "print(srs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-12-28T12:40:36.060328Z",
     "start_time": "2018-12-28T12:40:36.027835Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a   -0.281210\n",
       "a    0.770726\n",
       "a   -0.176266\n",
       "a   -1.612378\n",
       "a   -1.868139\n",
       "a   -0.496955\n",
       "dtype: float64"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sr1 = pd.Series(aray, index=['a'] * 6)\n",
    "sr1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "### 1.4.3 Three major usages\n",
    "\n",
    "a. Identication: Indices are used to locate Series / rows / items in a DataFrame.   \n",
    "\n",
    "b. Alignment: pandas will always align with index automatically first.\n",
    "\n",
    "c. Selection: using index to select relevant columns/rows.\n",
    "\n",
    "### 1.4.4 Examples of identication"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-12-28T12:44:22.736405Z",
     "start_time": "2018-12-28T12:44:22.689162Z"
    }
   },
   "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>Adj Close</th>\n",
       "      <th>Volume</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2018-12-14</td>\n",
       "      <td>6986.370117</td>\n",
       "      <td>7027.169922</td>\n",
       "      <td>6898.990234</td>\n",
       "      <td>6910.660156</td>\n",
       "      <td>6910.660156</td>\n",
       "      <td>2200510000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Date         Open         High          Low        Close  \\\n",
       "14  2018-12-14  6986.370117  7027.169922  6898.990234  6910.660156   \n",
       "\n",
       "      Adj Close      Volume  \n",
       "14  6910.660156  2200510000  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "index_df = df.copy()\n",
    "\n",
    "index_df[index_df['Date'] == '2018-12-14']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "### 1.4.5 Examples of alignment"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-12-28T11:48:52.767186Z",
     "start_time": "2018-12-28T11:48:52.741402Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         Date         Open         High          Low        Close  \\\n",
      "0  2018-11-23  6919.520020  6987.890137  6919.160156  6938.979980   \n",
      "1  2018-11-26  7026.500000  7083.930176  7003.120117  7081.850098   \n",
      "2  2018-11-27  7041.229980  7105.140137  7014.359863  7082.700195   \n",
      "3  2018-11-28  7135.080078  7292.709961  7090.979980  7291.589844   \n",
      "4  2018-11-29  7267.370117  7319.959961  7217.689941  7273.080078   \n",
      "\n",
      "     Adj Close      Volume    Max_diff  \n",
      "0  6938.979980   958950000   68.729981  \n",
      "1  7081.850098  2011180000   80.810059  \n",
      "2  7082.700195  2067360000   90.780274  \n",
      "3  7291.589844  2390260000  201.729981  \n",
      "4  7273.080078  1983460000  102.270020  \n"
     ]
    }
   ],
   "source": [
    "index_df['Max_diff'] = index_df['High'] - index_df['Low']\n",
    "index_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "### 1.4.6 Examples of selection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-12-28T11:48:54.255104Z",
     "start_time": "2018-12-28T11:48:54.237334Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Date     2018-12-14\n",
      "Close       6910.66\n",
      "Name: 14, dtype: object\n"
     ]
    }
   ],
   "source": [
    "index_df.loc[14, ['Date', 'Close']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-12-28T11:48:55.189637Z",
     "start_time": "2018-12-28T11:48:55.168154Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['2018-11-23', '2018-11-26', '2018-11-27', '2018-11-28', '2018-11-29',\n",
      "       '2018-11-30', '2018-12-03', '2018-12-04', '2018-12-06', '2018-12-07',\n",
      "       '2018-12-10', '2018-12-11', '2018-12-12', '2018-12-13', '2018-12-14',\n",
      "       '2018-12-17', '2018-12-18', '2018-12-19', '2018-12-20', '2018-12-21'],\n",
      "      dtype='object', name='Date')\n",
      "\n",
      "Date             NaN\n",
      "Close    6910.660156\n",
      "Name: 2018-12-14, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "index_df['Date'] = index_df['Date'].astype('str')\n",
    "index_df.set_index('Date', inplace=True)\n",
    "print(index_df.index)\n",
    "print()\n",
    "print(index_df.loc['2018-12-14', ['Date', 'Close']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-12-28T11:47:53.123177Z",
     "start_time": "2018-12-28T11:47:53.110037Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                   Open         High          Low        Close    Adj Close  \\\n",
      "2018-11-23  6919.520020  6987.890137  6919.160156  6938.979980  6938.979980   \n",
      "2018-11-26  7026.500000  7083.930176  7003.120117  7081.850098  7081.850098   \n",
      "2018-11-27  7041.229980  7105.140137  7014.359863  7082.700195  7082.700195   \n",
      "2018-11-28  7135.080078  7292.709961  7090.979980  7291.589844  7291.589844   \n",
      "2018-11-29  7267.370117  7319.959961  7217.689941  7273.080078  7273.080078   \n",
      "\n",
      "                Volume    Max_diff  \n",
      "2018-11-23   958950000   68.729981  \n",
      "2018-11-26  2011180000   80.810059  \n",
      "2018-11-27  2067360000   90.780274  \n",
      "2018-11-28  2390260000  201.729981  \n",
      "2018-11-29  1983460000  102.270020  \n"
     ]
    }
   ],
   "source": [
    "index_df.index.name = None\n",
    "print(index_df.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-12-28T11:47:53.139863Z",
     "start_time": "2018-12-28T11:47:53.126153Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         Date         Open         High          Low        Close  \\\n",
      "0  2018-11-23  6919.520020  6987.890137  6919.160156  6938.979980   \n",
      "1  2018-11-26  7026.500000  7083.930176  7003.120117  7081.850098   \n",
      "2  2018-11-27  7041.229980  7105.140137  7014.359863  7082.700195   \n",
      "3  2018-11-28  7135.080078  7292.709961  7090.979980  7291.589844   \n",
      "4  2018-11-29  7267.370117  7319.959961  7217.689941  7273.080078   \n",
      "\n",
      "     Adj Close      Volume    Max_diff  \n",
      "0  6938.979980   958950000   68.729981  \n",
      "1  7081.850098  2011180000   80.810059  \n",
      "2  7082.700195  2067360000   90.780274  \n",
      "3  7291.589844  2390260000  201.729981  \n",
      "4  7273.080078  1983460000  102.270020  \n"
     ]
    }
   ],
   "source": [
    "index_df.index.name = \"Date\"\n",
    "index_df.reset_index(inplace=True)\n",
    "print(index_df.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "### 1.4.7 Five ways of index selection\n",
    "\n",
    "1. `[]` operator: using index / column names to access data.\n",
    "2. `df.loc`: Access a group of rows and columns by label(s)\n",
    "3. `df.iloc`: Access a group of rows and columns by integer position(s)\n",
    "4. `df.at`: Access a single value for a row/column label pair.\n",
    "5. `df.iat`: Access a single value for a row/column pair by integer position."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-12-28T11:47:53.169526Z",
     "start_time": "2018-12-28T11:47:53.144080Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                   Open         High          Low        Close    Adj Close  \\\n",
      "Date                                                                          \n",
      "2018-11-27  7041.229980  7105.140137  7014.359863  7082.700195  7082.700195   \n",
      "2018-11-28  7135.080078  7292.709961  7090.979980  7291.589844  7291.589844   \n",
      "\n",
      "                Volume    Max_diff  \n",
      "Date                                \n",
      "2018-11-27  2067360000   90.780274  \n",
      "2018-11-28  2390260000  201.729981  \n",
      "\n",
      "                   Open         High          Low        Close    Adj Close  \\\n",
      "Date                                                                          \n",
      "2018-11-27  7041.229980  7105.140137  7014.359863  7082.700195  7082.700195   \n",
      "2018-11-28  7135.080078  7292.709961  7090.979980  7291.589844  7291.589844   \n",
      "\n",
      "                Volume    Max_diff  \n",
      "Date                                \n",
      "2018-11-27  2067360000   90.780274  \n",
      "2018-11-28  2390260000  201.729981  \n",
      "\n",
      "                  Open         High          Low        Close    Adj Close  \\\n",
      "Date                                                                         \n",
      "2018-11-27  7041.22998  7105.140137  7014.359863  7082.700195  7082.700195   \n",
      "\n",
      "                Volume   Max_diff  \n",
      "Date                               \n",
      "2018-11-27  2067360000  90.780274  \n",
      "\n",
      "7041.22998\n",
      "\n",
      "7041.22998\n"
     ]
    }
   ],
   "source": [
    "index_df.set_index('Date', inplace=True)\n",
    "\n",
    "print(index_df['2018-11-27':'2018-11-28'])\n",
    "print()\n",
    "print(index_df.loc['2018-11-27':'2018-11-28'])\n",
    "print()\n",
    "print(index_df.iloc[2:3])\n",
    "print()\n",
    "print(index_df.at['2018-11-27','Open'])\n",
    "print()\n",
    "print(index_df.iat[2, 0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-12-28T11:48:03.115744Z",
     "start_time": "2018-12-28T11:47:53.175722Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "124 µs ± 42.8 µs per loop (mean ± std. dev. of 7 runs, 10000 loops each)\n"
     ]
    }
   ],
   "source": [
    "%timeit index_df['2018-11-27':'2018-11-28']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-12-28T11:48:12.157624Z",
     "start_time": "2018-12-28T11:48:03.122063Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "112 µs ± 6.67 µs per loop (mean ± std. dev. of 7 runs, 10000 loops each)\n"
     ]
    }
   ],
   "source": [
    "%timeit index_df.loc['2018-11-27':'2018-11-28']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-12-28T11:48:25.491913Z",
     "start_time": "2018-12-28T11:48:12.161462Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "175 µs ± 90.1 µs per loop (mean ± std. dev. of 7 runs, 10000 loops each)\n"
     ]
    }
   ],
   "source": [
    "%timeit index_df.iloc[2:3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-12-28T11:48:32.855730Z",
     "start_time": "2018-12-28T11:48:25.499466Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "8.53 µs ± 3.88 µs per loop (mean ± std. dev. of 7 runs, 100000 loops each)\n"
     ]
    }
   ],
   "source": [
    "%timeit index_df.at['2018-11-27','Open']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-12-28T11:48:38.653643Z",
     "start_time": "2018-12-28T11:48:32.858760Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "7.2 µs ± 1.86 µs per loop (mean ± std. dev. of 7 runs, 100000 loops each)\n"
     ]
    }
   ],
   "source": [
    "%timeit index_df.iat[2, 0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__We recommend using `pd.DataFrame.iloc() / pd.DataFrame.loc()` in this case for the best performance and readibility.__\n",
    "\n",
    "---\n",
    "\n",
    "## 1.5 Exercises\n",
    "\n",
    "### 1.5.1 Reviewing\n",
    "\n",
    "Please review the code above.\n",
    "\n",
    "### 1.5.1 Refactoring\n",
    "\n",
    "If you have written pandas scripts before, try to refactor them into different levels of functions.\n",
    "\n",
    "### 1.5.3 Checking parameters\n",
    "\n",
    "Check the default and optional parameters of the following methods:\n",
    "\n",
    "1. `DataFrame.desribe()`: provide descriptive stats of the dataset\n",
    "2. `DataFrame.values`: access values of the dataset\n",
    "3. `DataFrame.head()`: access the head of the dataset\n",
    "4. `DataFrame.tail()`: access the tail of the dataset\n",
    "5. `DataFrame.shape`: provide the length and width of the dataset\n",
    "6. `DataFrame.size`: provide the product of the length and width of the dataset\n",
    "7. `DataFrame.columns`: provide the colomn names of the dataset\n",
    "8. `DataFrame.index`: provide the row index of the dataset\n",
    "9. `DataFrame.axes`: provide the colomn names and row index of the dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "To the rest sessions (outlines and video records), please scan the QR code below to pay.\n",
    "\n",
    "1. The price is 799 RMB.\n",
    "2. Please leave your email address in the __payment comment__, so I will send you the links of the rest sessions.\n",
    "\n",
    "\n",
    "<img src=\"../image/alipay.jpg\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.2"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": false,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "335.8541564941406px"
   },
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}