5.1 Descriptive Statistics.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: pandas_datareader in c:\\programdata\\anaconda3\\lib\\site-packages\n",
      "Requirement already satisfied: requests-ftp in c:\\programdata\\anaconda3\\lib\\site-packages (from pandas_datareader)\n",
      "Requirement already satisfied: requests>=2.3.0 in c:\\programdata\\anaconda3\\lib\\site-packages (from pandas_datareader)\n",
      "Requirement already satisfied: requests-file in c:\\programdata\\anaconda3\\lib\\site-packages (from pandas_datareader)\n",
      "Requirement already satisfied: pandas in c:\\programdata\\anaconda3\\lib\\site-packages (from pandas_datareader)\n",
      "Requirement already satisfied: six in c:\\programdata\\anaconda3\\lib\\site-packages (from requests-file->pandas_datareader)\n",
      "Requirement already satisfied: python-dateutil>=2 in c:\\programdata\\anaconda3\\lib\\site-packages (from pandas->pandas_datareader)\n",
      "Requirement already satisfied: pytz>=2011k in c:\\programdata\\anaconda3\\lib\\site-packages (from pandas->pandas_datareader)\n",
      "Requirement already satisfied: numpy>=1.7.0 in c:\\programdata\\anaconda3\\lib\\site-packages (from pandas->pandas_datareader)\n"
     ]
    }
   ],
   "source": [
    "! pip install pandas_datareader"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas_datareader\n",
    "?pandas_datareader.DataReader"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = pandas_datareader.DataReader('BABA', data_source='yahoo')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>Volume</th>\n",
       "      <th>Adj Close</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>2017-04-17</th>\n",
       "      <td>110.910004</td>\n",
       "      <td>111.870003</td>\n",
       "      <td>110.300003</td>\n",
       "      <td>111.760002</td>\n",
       "      <td>6498300</td>\n",
       "      <td>111.760002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-04-18</th>\n",
       "      <td>111.199997</td>\n",
       "      <td>112.199997</td>\n",
       "      <td>110.866997</td>\n",
       "      <td>111.230003</td>\n",
       "      <td>8271300</td>\n",
       "      <td>111.230003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-04-19</th>\n",
       "      <td>111.989998</td>\n",
       "      <td>112.199997</td>\n",
       "      <td>110.570000</td>\n",
       "      <td>110.760002</td>\n",
       "      <td>9571500</td>\n",
       "      <td>110.760002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-04-20</th>\n",
       "      <td>111.529999</td>\n",
       "      <td>113.599998</td>\n",
       "      <td>111.480003</td>\n",
       "      <td>112.959999</td>\n",
       "      <td>13743300</td>\n",
       "      <td>112.959999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-04-21</th>\n",
       "      <td>112.800003</td>\n",
       "      <td>113.169998</td>\n",
       "      <td>112.300003</td>\n",
       "      <td>113.110001</td>\n",
       "      <td>8945100</td>\n",
       "      <td>113.110001</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Open        High         Low       Close    Volume  \\\n",
       "Date                                                                   \n",
       "2017-04-17  110.910004  111.870003  110.300003  111.760002   6498300   \n",
       "2017-04-18  111.199997  112.199997  110.866997  111.230003   8271300   \n",
       "2017-04-19  111.989998  112.199997  110.570000  110.760002   9571500   \n",
       "2017-04-20  111.529999  113.599998  111.480003  112.959999  13743300   \n",
       "2017-04-21  112.800003  113.169998  112.300003  113.110001   8945100   \n",
       "\n",
       "             Adj Close  \n",
       "Date                    \n",
       "2017-04-17  111.760002  \n",
       "2017-04-18  111.230003  \n",
       "2017-04-19  110.760002  \n",
       "2017-04-20  112.959999  \n",
       "2017-04-21  113.110001  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Open', 'High', 'Low', 'Close', 'Volume', 'Adj Close'], dtype='object')"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "652"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Close'].sum()\n",
    "df['Close'].mean()\n",
    "df['Close'].std()\n",
    "df['Close'].min()\n",
    "df['Close'].max()\n",
    "df['Close'].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Open     119.300003\n",
       "Close    119.150002\n",
       "dtype: float64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[['Open', 'Close']].min()\n",
    "df[['Open', 'Close']].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>Volume</th>\n",
       "      <th>Adj Close</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>652.000000</td>\n",
       "      <td>652.000000</td>\n",
       "      <td>652.000000</td>\n",
       "      <td>652.000000</td>\n",
       "      <td>6.520000e+02</td>\n",
       "      <td>652.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>87.538501</td>\n",
       "      <td>88.564590</td>\n",
       "      <td>86.416227</td>\n",
       "      <td>87.473037</td>\n",
       "      <td>1.663195e+07</td>\n",
       "      <td>87.473037</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>13.264536</td>\n",
       "      <td>13.262912</td>\n",
       "      <td>13.175247</td>\n",
       "      <td>13.208376</td>\n",
       "      <td>1.500740e+07</td>\n",
       "      <td>13.208376</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>57.299999</td>\n",
       "      <td>58.650002</td>\n",
       "      <td>57.200001</td>\n",
       "      <td>57.389999</td>\n",
       "      <td>3.775300e+06</td>\n",
       "      <td>57.389999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>78.620001</td>\n",
       "      <td>79.550003</td>\n",
       "      <td>78.034998</td>\n",
       "      <td>78.827502</td>\n",
       "      <td>9.638550e+06</td>\n",
       "      <td>78.827502</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>85.360000</td>\n",
       "      <td>86.134998</td>\n",
       "      <td>84.229999</td>\n",
       "      <td>85.145000</td>\n",
       "      <td>1.303220e+07</td>\n",
       "      <td>85.145000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>99.459999</td>\n",
       "      <td>100.187500</td>\n",
       "      <td>97.639997</td>\n",
       "      <td>98.860002</td>\n",
       "      <td>1.848135e+07</td>\n",
       "      <td>98.860002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>119.300003</td>\n",
       "      <td>120.000000</td>\n",
       "      <td>115.199997</td>\n",
       "      <td>119.150002</td>\n",
       "      <td>2.718794e+08</td>\n",
       "      <td>119.150002</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Open        High         Low       Close        Volume  \\\n",
       "count  652.000000  652.000000  652.000000  652.000000  6.520000e+02   \n",
       "mean    87.538501   88.564590   86.416227   87.473037  1.663195e+07   \n",
       "std     13.264536   13.262912   13.175247   13.208376  1.500740e+07   \n",
       "min     57.299999   58.650002   57.200001   57.389999  3.775300e+06   \n",
       "25%     78.620001   79.550003   78.034998   78.827502  9.638550e+06   \n",
       "50%     85.360000   86.134998   84.229999   85.145000  1.303220e+07   \n",
       "75%     99.459999  100.187500   97.639997   98.860002  1.848135e+07   \n",
       "max    119.300003  120.000000  115.199997  119.150002  2.718794e+08   \n",
       "\n",
       "        Adj Close  \n",
       "count  652.000000  \n",
       "mean    87.473037  \n",
       "std     13.208376  \n",
       "min     57.389999  \n",
       "25%     78.827502  \n",
       "50%     85.145000  \n",
       "75%     98.860002  \n",
       "max    119.150002  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>Volume</th>\n",
       "      <th>Adj Close</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-09-19</th>\n",
       "      <td>92.699997</td>\n",
       "      <td>99.699997</td>\n",
       "      <td>89.949997</td>\n",
       "      <td>93.889999</td>\n",
       "      <td>271879400</td>\n",
       "      <td>93.889999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-09-22</th>\n",
       "      <td>92.699997</td>\n",
       "      <td>92.949997</td>\n",
       "      <td>89.500000</td>\n",
       "      <td>89.889999</td>\n",
       "      <td>66657800</td>\n",
       "      <td>89.889999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-09-23</th>\n",
       "      <td>88.940002</td>\n",
       "      <td>90.480003</td>\n",
       "      <td>86.620003</td>\n",
       "      <td>87.169998</td>\n",
       "      <td>39009800</td>\n",
       "      <td>87.169998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-09-24</th>\n",
       "      <td>88.470001</td>\n",
       "      <td>90.570000</td>\n",
       "      <td>87.220001</td>\n",
       "      <td>90.570000</td>\n",
       "      <td>32088000</td>\n",
       "      <td>90.570000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-09-25</th>\n",
       "      <td>91.089996</td>\n",
       "      <td>91.500000</td>\n",
       "      <td>88.500000</td>\n",
       "      <td>88.919998</td>\n",
       "      <td>28598000</td>\n",
       "      <td>88.919998</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 Open       High        Low      Close     Volume  Adj Close\n",
       "Date                                                                        \n",
       "2014-09-19  92.699997  99.699997  89.949997  93.889999  271879400  93.889999\n",
       "2014-09-22  92.699997  92.949997  89.500000  89.889999   66657800  89.889999\n",
       "2014-09-23  88.940002  90.480003  86.620003  87.169998   39009800  87.169998\n",
       "2014-09-24  88.470001  90.570000  87.220001  90.570000   32088000  90.570000\n",
       "2014-09-25  91.089996  91.500000  88.500000  88.919998   28598000  88.919998"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df['diff'] = df['Close'] - df['Open']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df['rise'] = df['diff'] > 0 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df['fall'] = df['diff'] < 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "rise    308\n",
       "fall    341\n",
       "dtype: int64"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[['rise', 'fall']].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#df[df.index >= '2017-04-01']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "rise    8\n",
       "fall    6\n",
       "dtype: int64"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.ix[ df.index >= '2017-04-01'   ,  ['rise', 'fall']   ].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#df.index.year\n",
    "#df.index.month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"10\" halign=\"left\">rise</th>\n",
       "      <th>...</th>\n",
       "      <th colspan=\"10\" halign=\"left\">fall</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>10</th>\n",
       "      <th>...</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>10</th>\n",
       "      <th>11</th>\n",
       "      <th>12</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2014</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015</th>\n",
       "      <td>7.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>...</td>\n",
       "      <td>13.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016</th>\n",
       "      <td>8.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>...</td>\n",
       "      <td>8.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>9.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>10.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     rise                                                        ...   fall  \\\n",
       "       1     2     3     4     5     6     7     8     9     10  ...     3    \n",
       "2014  NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   3.0  11.0  ...    NaN   \n",
       "2015  7.0   6.0   9.0   5.0   9.0   7.0   7.0   9.0  12.0  17.0  ...   13.0   \n",
       "2016  8.0  11.0  14.0  10.0  12.0  11.0  11.0  15.0  12.0   5.0  ...    8.0   \n",
       "2017  9.0   7.0  13.0   8.0   NaN   NaN   NaN   NaN   NaN   NaN  ...   10.0   \n",
       "\n",
       "                                                           \n",
       "        4     5     6     7     8    9     10    11    12  \n",
       "2014   NaN   NaN   NaN   NaN   NaN  5.0  11.0   9.0  11.0  \n",
       "2015  16.0  11.0  15.0  15.0  11.0  9.0   5.0   9.0  12.0  \n",
       "2016  11.0   9.0  11.0   9.0   8.0  9.0  16.0  13.0  10.0  \n",
       "2017   6.0   NaN   NaN   NaN   NaN  NaN   NaN   NaN   NaN  \n",
       "\n",
       "[4 rows x 24 columns]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby([df.index.year, df.index.month])['rise', 'fall'].sum().unstack()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df['ret'] = df['Close'].pct_change(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>Volume</th>\n",
       "      <th>Adj Close</th>\n",
       "      <th>diff</th>\n",
       "      <th>rise</th>\n",
       "      <th>fall</th>\n",
       "      <th>ret</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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2014-09-19</th>\n",
       "      <td>92.699997</td>\n",
       "      <td>99.699997</td>\n",
       "      <td>89.949997</td>\n",
       "      <td>93.889999</td>\n",
       "      <td>271879400</td>\n",
       "      <td>93.889999</td>\n",
       "      <td>1.190002</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-09-22</th>\n",
       "      <td>92.699997</td>\n",
       "      <td>92.949997</td>\n",
       "      <td>89.500000</td>\n",
       "      <td>89.889999</td>\n",
       "      <td>66657800</td>\n",
       "      <td>89.889999</td>\n",
       "      <td>-2.809998</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>-0.042603</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-09-23</th>\n",
       "      <td>88.940002</td>\n",
       "      <td>90.480003</td>\n",
       "      <td>86.620003</td>\n",
       "      <td>87.169998</td>\n",
       "      <td>39009800</td>\n",
       "      <td>87.169998</td>\n",
       "      <td>-1.770004</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>-0.030259</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-09-24</th>\n",
       "      <td>88.470001</td>\n",
       "      <td>90.570000</td>\n",
       "      <td>87.220001</td>\n",
       "      <td>90.570000</td>\n",
       "      <td>32088000</td>\n",
       "      <td>90.570000</td>\n",
       "      <td>2.099999</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>0.039004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-09-25</th>\n",
       "      <td>91.089996</td>\n",
       "      <td>91.500000</td>\n",
       "      <td>88.500000</td>\n",
       "      <td>88.919998</td>\n",
       "      <td>28598000</td>\n",
       "      <td>88.919998</td>\n",
       "      <td>-2.169998</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>-0.018218</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 Open       High        Low      Close     Volume  Adj Close  \\\n",
       "Date                                                                           \n",
       "2014-09-19  92.699997  99.699997  89.949997  93.889999  271879400  93.889999   \n",
       "2014-09-22  92.699997  92.949997  89.500000  89.889999   66657800  89.889999   \n",
       "2014-09-23  88.940002  90.480003  86.620003  87.169998   39009800  87.169998   \n",
       "2014-09-24  88.470001  90.570000  87.220001  90.570000   32088000  90.570000   \n",
       "2014-09-25  91.089996  91.500000  88.500000  88.919998   28598000  88.919998   \n",
       "\n",
       "                diff   rise   fall       ret  \n",
       "Date                                          \n",
       "2014-09-19  1.190002   True  False       NaN  \n",
       "2014-09-22 -2.809998  False   True -0.042603  \n",
       "2014-09-23 -1.770004  False   True -0.030259  \n",
       "2014-09-24  2.099999   True  False  0.039004  \n",
       "2014-09-25 -2.169998  False   True -0.018218  "
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>10</th>\n",
       "      <th>11</th>\n",
       "      <th>12</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2014</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.007505</td>\n",
       "      <td>0.004684</td>\n",
       "      <td>0.006855</td>\n",
       "      <td>-0.003032</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015</th>\n",
       "      <td>-0.007300</td>\n",
       "      <td>-0.002288</td>\n",
       "      <td>-0.000887</td>\n",
       "      <td>-0.001013</td>\n",
       "      <td>0.004937</td>\n",
       "      <td>-0.003658</td>\n",
       "      <td>-0.002132</td>\n",
       "      <td>-0.007696</td>\n",
       "      <td>-0.005081</td>\n",
       "      <td>0.016434</td>\n",
       "      <td>0.000327</td>\n",
       "      <td>-0.001356</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016</th>\n",
       "      <td>-0.009706</td>\n",
       "      <td>0.001661</td>\n",
       "      <td>0.006436</td>\n",
       "      <td>-0.001206</td>\n",
       "      <td>0.003305</td>\n",
       "      <td>-0.001149</td>\n",
       "      <td>0.001888</td>\n",
       "      <td>0.007348</td>\n",
       "      <td>0.004273</td>\n",
       "      <td>-0.001821</td>\n",
       "      <td>-0.003607</td>\n",
       "      <td>-0.003149</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>0.007288</td>\n",
       "      <td>0.000880</td>\n",
       "      <td>0.002091</td>\n",
       "      <td>0.003469</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            1         2         3         4         5         6         7   \\\n",
       "2014       NaN       NaN       NaN       NaN       NaN       NaN       NaN   \n",
       "2015 -0.007300 -0.002288 -0.000887 -0.001013  0.004937 -0.003658 -0.002132   \n",
       "2016 -0.009706  0.001661  0.006436 -0.001206  0.003305 -0.001149  0.001888   \n",
       "2017  0.007288  0.000880  0.002091  0.003469       NaN       NaN       NaN   \n",
       "\n",
       "            8         9         10        11        12  \n",
       "2014       NaN -0.007505  0.004684  0.006855 -0.003032  \n",
       "2015 -0.007696 -0.005081  0.016434  0.000327 -0.001356  \n",
       "2016  0.007348  0.004273 -0.001821 -0.003607 -0.003149  \n",
       "2017       NaN       NaN       NaN       NaN       NaN  "
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# SELECT year, month, AVG(ret) FROM BABA GROUP BY year, month\n",
    "df.groupby([df.index.year, df.index.month])['ret'].mean().unstack()"
   ]
  }
 ],
 "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.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}