answer-keys-pydt.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Readme"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 针对python版本的`datatable`，我们也希望给出相应的解决方案，并尽量与R版本进行对比。\n",
    "- 与R版本的类似，我们只会输出结果数据集的**前5行**。\n",
    "- 其余注意事项可查看*anwer-keys-rdt.ipynb*中的readme部分。\n",
    "- 欢迎大家关注我们的微信公众号**大喵与村长的R进制**。\n",
    "- 如有任何问题请在我们的Github主页开issue。最后感谢renkun！"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Import data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class='datatable'>\n",
       "  <table class='frame'>\n",
       "  <thead>\n",
       "    <tr class='colnames'><td class='row_index'></td><th>symbol</th><th>date</th><th>pre_close</th><th>open</th><th>high</th><th>low</th><th>close</th><th>volume</th><th>amount</th><th>adj_factor</th><th>capt</th><th>index_w50</th><th>index_w300</th><th>index_w500</th><th>industry</th></tr>\n",
       "    <tr class='coltypes'><td class='row_index'></td><td class='str' title='str32'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='int' title='int32'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='float' title='float64'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='float' title='float64'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='float' title='float64'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='float' title='float64'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='float' title='float64'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='int' title='int32'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='float' title='float64'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='float' title='float64'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='float' title='float64'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='float' title='float64'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='float' title='float64'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='float' title='float64'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='str' title='str32'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td></tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr><td class='row_index'>0</td><td>600000.SH</td><td>20120104</td><td>8.49</td><td>8.54</td><td>8.56</td><td>8.39</td><td>8.41</td><td>34201379</td><td>2.9023e+08</td><td>6.65527</td><td>1.25501e+11</td><td>0.0464093</td><td>0.0212594</td><td>0</td><td>BANKS</td></tr>\n",
       "    <tr><td class='row_index'>1</td><td>600000.SH</td><td>20120105</td><td>8.41</td><td>8.47</td><td>8.82</td><td>8.47</td><td>8.65</td><td>132116203</td><td>1.14475e+09</td><td>6.65527</td><td>1.29082e+11</td><td>0.0464093</td><td>0.0212594</td><td>0</td><td>BANKS</td></tr>\n",
       "    <tr><td class='row_index'>2</td><td>600000.SH</td><td>20120106</td><td>8.65</td><td>8.63</td><td>8.78</td><td>8.62</td><td>8.71</td><td>61778687</td><td>5.37044e+08</td><td>6.65527</td><td>1.29977e+11</td><td>0.0464093</td><td>0.0212594</td><td>0</td><td>BANKS</td></tr>\n",
       "    <tr><td class='row_index'>3</td><td>600000.SH</td><td>20120109</td><td>8.71</td><td>8.72</td><td>8.99</td><td>8.68</td><td>8.95</td><td>80136249</td><td>7.1143e+08</td><td>6.65527</td><td>1.33559e+11</td><td>0.0464093</td><td>0.0212594</td><td>0</td><td>BANKS</td></tr>\n",
       "    <tr><td class='row_index'>4</td><td>600000.SH</td><td>20120110</td><td>8.95</td><td>8.95</td><td>9.1</td><td>8.88</td><td>9.07</td><td>72004632</td><td>6.47207e+08</td><td>6.65527</td><td>1.3535e+11</td><td>0.0464093</td><td>0.0212594</td><td>0</td><td>BANKS</td></tr>\n",
       "  </tbody>\n",
       "  </table>\n",
       "  <div class='footer'>\n",
       "    <div class='frame_dimensions'>5 rows &times; 15 columns</div>\n",
       "  </div>\n",
       "</div>\n"
      ],
      "text/plain": [
       "<Frame#216644240d0 5x15>"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import datatable as dt\n",
    "data = dt.fread(\"data/stock-market-data.csv\")\n",
    "data[:5,:]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 数据集为“面板数据”：包含多个股票（横截面），而每个股票则有多个按照日期排序的变量（时间序列）\n",
    "\n",
    "- 股票代码`symbol` 和日期`date`共同组成了数据集的key，也即每个唯一的`symbol` 和`date`组合决定了一个唯一的观测。\n",
    "\n",
    "- 整个数据集首先按照代码`symbol`排列，其次按照日期`date`排列。\n",
    "\n",
    "- 若干主要变量说明：\n",
    "    - `symbol`：股票代码。.SH 结尾的是沪股，.SZ 结尾的是深股\n",
    "    - `date`：日期\n",
    "    - `pre_close`： 昨收盘\n",
    "    - `open`：开盘价\n",
    "    - `high`：最高价（日内）\n",
    "    - `low`：最低价（日内）\n",
    "    - `close`：收盘价\n",
    "    - `volume`：成交量\n",
    "    - `amount`：成交金额\n",
    "    - `industry`：行业\n",
    "    - `index_w50`：该股票在上证50指数的成分比例\n",
    "    - `index_w300`：该股票在上证300指数的成分比例\n",
    "    - `index_w500`：该股票在中证500指数的成分比例"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Answer Keys"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. 哪些股票的代码中包含\"8\"这个数字？"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class='datatable'>\n",
       "  <table class='frame'>\n",
       "  <thead>\n",
       "    <tr class='colnames'><td class='row_index'></td><th>symbol</th><th>date</th><th>pre_close</th><th>open</th><th>high</th><th>low</th><th>close</th><th>volume</th><th>amount</th><th>adj_factor</th><th>capt</th><th>index_w50</th><th>index_w300</th><th>index_w500</th><th>industry</th></tr>\n",
       "    <tr class='coltypes'><td class='row_index'></td><td class='str' title='str32'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='int' title='int32'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='float' title='float64'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='float' title='float64'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='float' title='float64'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='float' title='float64'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='float' title='float64'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='int' title='int32'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='float' title='float64'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='float' title='float64'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='float' title='float64'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='float' title='float64'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='float' title='float64'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='float' title='float64'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='str' title='str32'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td></tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr><td class='row_index'>0</td><td>600008.SH</td><td>20120104</td><td>5.2</td><td>5.29</td><td>5.29</td><td>4.92</td><td>4.96</td><td>6685638</td><td>3.40151e+07</td><td>3.29223</td><td>1.0912e+10</td><td>0</td><td>0.00122996</td><td>0</td><td>UTILITIE</td></tr>\n",
       "    <tr><td class='row_index'>1</td><td>600008.SH</td><td>20120105</td><td>4.96</td><td>4.97</td><td>4.98</td><td>4.78</td><td>4.85</td><td>5600697</td><td>2.73034e+07</td><td>3.29223</td><td>1.067e+10</td><td>0</td><td>0.00122996</td><td>0</td><td>UTILITIE</td></tr>\n",
       "    <tr><td class='row_index'>2</td><td>600008.SH</td><td>20120106</td><td>4.85</td><td>4.86</td><td>5</td><td>4.78</td><td>4.98</td><td>3773453</td><td>1.8459e+07</td><td>3.29223</td><td>1.0956e+10</td><td>0</td><td>0.00122996</td><td>0</td><td>UTILITIE</td></tr>\n",
       "    <tr><td class='row_index'>3</td><td>600008.SH</td><td>20120109</td><td>4.98</td><td>4.98</td><td>5.2</td><td>4.91</td><td>5.17</td><td>5749379</td><td>2.9119e+07</td><td>3.29223</td><td>1.1374e+10</td><td>0</td><td>0.00122996</td><td>0</td><td>UTILITIE</td></tr>\n",
       "    <tr><td class='row_index'>4</td><td>600008.SH</td><td>20120110</td><td>5.17</td><td>5.17</td><td>5.32</td><td>5.13</td><td>5.28</td><td>8276808</td><td>4.33631e+07</td><td>3.29223</td><td>1.1616e+10</td><td>0</td><td>0.00122996</td><td>0</td><td>UTILITIE</td></tr>\n",
       "  </tbody>\n",
       "  </table>\n",
       "  <div class='footer'>\n",
       "    <div class='frame_dimensions'>5 rows &times; 15 columns</div>\n",
       "  </div>\n",
       "</div>\n"
      ],
      "text/plain": [
       "<Frame#2236140a930 5x15>"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[dt.re.match(dt.f.symbol, \".*8.*\"),:][:5, :]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. 每天上涨和下跌的股票有多少"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class='datatable'>\n",
       "  <table class='frame'>\n",
       "  <thead>\n",
       "    <tr class='colnames'><td class='row_index'></td><th>date</th><th>updown_tag</th><th>symbol</th></tr>\n",
       "    <tr class='coltypes'><td class='row_index'></td><td class='int' title='int32'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='str' title='str32'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='int' title='int64'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td></tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr><td class='row_index'>0</td><td>20120104</td><td>down</td><td>2007</td></tr>\n",
       "    <tr><td class='row_index'>1</td><td>20120104</td><td>steady</td><td>122</td></tr>\n",
       "    <tr><td class='row_index'>2</td><td>20120104</td><td>up</td><td>191</td></tr>\n",
       "    <tr><td class='row_index'>3</td><td>20120105</td><td>down</td><td>2071</td></tr>\n",
       "    <tr><td class='row_index'>4</td><td>20120105</td><td>steady</td><td>117</td></tr>\n",
       "  </tbody>\n",
       "  </table>\n",
       "  <div class='footer'>\n",
       "    <div class='frame_dimensions'>5 rows &times; 3 columns</div>\n",
       "  </div>\n",
       "</div>\n"
      ],
      "text/plain": [
       "<Frame#2236140e1b0 5x3>"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[:, dt.update(updown_tag = dt.ifelse(dt.f.close > dt.f.pre_close, \"up\", dt.f.close < dt.f.pre_close, \"down\", \"steady\"))]\n",
    "data[:, dt.count(dt.f.symbol), dt.by(dt.f.date, dt.f.updown_tag)][:5, :]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. 每天每个交易所上涨、下跌的股票各有多少？"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class='datatable'>\n",
       "  <table class='frame'>\n",
       "  <thead>\n",
       "    <tr class='colnames'><td class='row_index'></td><th>date</th><th>updown_tag</th><th>exch_tag</th><th>symbol</th></tr>\n",
       "    <tr class='coltypes'><td class='row_index'></td><td class='int' title='int32'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='str' title='str32'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='str' title='str32'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='int' title='int64'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td></tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr><td class='row_index'>0</td><td>20120104</td><td>down</td><td>SH</td><td>794</td></tr>\n",
       "    <tr><td class='row_index'>1</td><td>20120104</td><td>down</td><td>SZ</td><td>1213</td></tr>\n",
       "    <tr><td class='row_index'>2</td><td>20120104</td><td>steady</td><td>SH</td><td>42</td></tr>\n",
       "    <tr><td class='row_index'>3</td><td>20120104</td><td>steady</td><td>SZ</td><td>80</td></tr>\n",
       "    <tr><td class='row_index'>4</td><td>20120104</td><td>up</td><td>SH</td><td>85</td></tr>\n",
       "  </tbody>\n",
       "  </table>\n",
       "  <div class='footer'>\n",
       "    <div class='frame_dimensions'>5 rows &times; 4 columns</div>\n",
       "  </div>\n",
       "</div>\n"
      ],
      "text/plain": [
       "<Frame#2236140aae0 5x4>"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[:, dt.update(\n",
    "    updown_tag = dt.ifelse(dt.f.close > dt.f.pre_close, \"up\", dt.f.close < dt.f.pre_close, \"down\", \"steady\"),\n",
    "    exch_tag = dt.str.slice(dt.f.symbol, -2, None)           \n",
    "    )]\n",
    "data[:, dt.count(dt.f.symbol), dt.by(dt.f.date, dt.f.updown_tag, dt.f.exch_tag)][:5, :]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. 沪深300成分股中，每天上涨、下跌的股票各有多少？"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class='datatable'>\n",
       "  <table class='frame'>\n",
       "  <thead>\n",
       "    <tr class='colnames'><td class='row_index'></td><th>date</th><th>updown_tag</th><th>symbol</th></tr>\n",
       "    <tr class='coltypes'><td class='row_index'></td><td class='int' title='int32'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='str' title='str32'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='int' title='int64'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td></tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr><td class='row_index'>0</td><td>20120104</td><td>down</td><td>275</td></tr>\n",
       "    <tr><td class='row_index'>1</td><td>20120104</td><td>steady</td><td>5</td></tr>\n",
       "    <tr><td class='row_index'>2</td><td>20120104</td><td>up</td><td>20</td></tr>\n",
       "    <tr><td class='row_index'>3</td><td>20120105</td><td>down</td><td>242</td></tr>\n",
       "    <tr><td class='row_index'>4</td><td>20120105</td><td>steady</td><td>8</td></tr>\n",
       "  </tbody>\n",
       "  </table>\n",
       "  <div class='footer'>\n",
       "    <div class='frame_dimensions'>5 rows &times; 3 columns</div>\n",
       "  </div>\n",
       "</div>\n"
      ],
      "text/plain": [
       "<Frame#2236140ea50 5x3>"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[:, dt.update(updown_tag = dt.ifelse(dt.f.close > dt.f.pre_close, \"up\", dt.f.close < dt.f.pre_close, \"down\", \"steady\"))]\n",
    "data[dt.f.index_w300 > 0,:\n",
    "    ][:, dt.count(dt.f.symbol), dt.by(dt.f.date, dt.f.updown_tag)][:5, :]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. 每天每个行业各有多少只股票？"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class='datatable'>\n",
       "  <table class='frame'>\n",
       "  <thead>\n",
       "    <tr class='colnames'><td class='row_index'></td><th>date</th><th>industry</th><th>symbol</th></tr>\n",
       "    <tr class='coltypes'><td class='row_index'></td><td class='int' title='int32'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='str' title='str32'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='int' title='int64'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td></tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr><td class='row_index'>0</td><td>20120104</td><td>AERODEF</td><td>10</td></tr>\n",
       "    <tr><td class='row_index'>1</td><td>20120104</td><td>AIRLINE</td><td>12</td></tr>\n",
       "    <tr><td class='row_index'>2</td><td>20120104</td><td>AUTO</td><td>85</td></tr>\n",
       "    <tr><td class='row_index'>3</td><td>20120104</td><td>BANKS</td><td>16</td></tr>\n",
       "    <tr><td class='row_index'>4</td><td>20120104</td><td>BEV</td><td>30</td></tr>\n",
       "  </tbody>\n",
       "  </table>\n",
       "  <div class='footer'>\n",
       "    <div class='frame_dimensions'>5 rows &times; 3 columns</div>\n",
       "  </div>\n",
       "</div>\n"
      ],
      "text/plain": [
       "<Frame#2236140ac30 5x3>"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[:, dt.count(dt.f.symbol), dt.by(dt.f.date, dt.f.industry)][:5, :]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. 股票数最大的行业和总成交额最大的行业是否总是同一个行业？"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class='datatable'>\n",
       "  <table class='frame'>\n",
       "  <thead>\n",
       "    <tr class='colnames'><td class='row_index'></td><th>date</th><th>count</th></tr>\n",
       "    <tr class='coltypes'><td class='row_index'></td><td class='int' title='int32'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='int' title='int64'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td></tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr><td class='row_index'>0</td><td>20120104</td><td>1</td></tr>\n",
       "    <tr><td class='row_index'>1</td><td>20120105</td><td>2</td></tr>\n",
       "    <tr><td class='row_index'>2</td><td>20120106</td><td>1</td></tr>\n",
       "    <tr><td class='row_index'>3</td><td>20120109</td><td>2</td></tr>\n",
       "    <tr><td class='row_index'>4</td><td>20120110</td><td>2</td></tr>\n",
       "  </tbody>\n",
       "  </table>\n",
       "  <div class='footer'>\n",
       "    <div class='frame_dimensions'>5 rows &times; 2 columns</div>\n",
       "  </div>\n",
       "</div>\n"
      ],
      "text/plain": [
       "<Frame#22360722c30 5x2>"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = data[:, {\n",
    "    'symbol_num' : dt.count(dt.f.symbol),\n",
    "    'amount_num' : dt.sum(dt.f.amount)\n",
    "    }, dt.by(dt.f.date, dt.f.industry)\n",
    "    ]\n",
    "data[:, dt.update(\n",
    "    symbol_max = dt.max(dt.f.symbol_num),\n",
    "    amount_max = dt.max(dt.f.amount_num)\n",
    "    ), dt.by(dt.f.date)\n",
    "    ]\n",
    "data[(dt.f.symbol_num == dt.f.symbol_max) | (dt.f.amount_num == dt.f.amount_max), :\n",
    "    ][:, dt.count(), dt.by(dt.f.date)\n",
    "    ][:5, :]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. 每天涨幅超过5%、跌幅超过5%的股票各有多少？"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class='datatable'>\n",
       "  <table class='frame'>\n",
       "  <thead>\n",
       "    <tr class='colnames'><td class='row_index'></td><th>date</th><th>updown_percent</th><th>symbol</th></tr>\n",
       "    <tr class='coltypes'><td class='row_index'></td><td class='int' title='int32'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='str' title='str32'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='int' title='int64'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td></tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr><td class='row_index'>0</td><td>20120104</td><td>down5p</td><td>277</td></tr>\n",
       "    <tr><td class='row_index'>1</td><td>20120104</td><td>up5p</td><td>17</td></tr>\n",
       "    <tr><td class='row_index'>2</td><td>20120105</td><td>down5p</td><td>886</td></tr>\n",
       "    <tr><td class='row_index'>3</td><td>20120105</td><td>up5p</td><td>10</td></tr>\n",
       "    <tr><td class='row_index'>4</td><td>20120106</td><td>down5p</td><td>66</td></tr>\n",
       "  </tbody>\n",
       "  </table>\n",
       "  <div class='footer'>\n",
       "    <div class='frame_dimensions'>5 rows &times; 3 columns</div>\n",
       "  </div>\n",
       "</div>\n"
      ],
      "text/plain": [
       "<Frame#22360607cc0 5x3>"
      ]
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[:, dt.update(\n",
    "    updown_percent = dt.ifelse(dt.f.close/dt.f.pre_close-1 > 0.05, \"up5p\", dt.f.close/dt.f.pre_close-1 < -0.05, \"down5p\", \"others\")\n",
    "    )]\n",
    "data[dt.f.updown_percent != \"others\", :\n",
    "    ][:, dt.count(dt.f.symbol), dt.by(dt.f.date, dt.f.updown_percent)\n",
    "    ][:5, :]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8. 每天涨幅前10的股票的总成交额和跌幅前10的股票的总成交额比例是多少？"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class='datatable'>\n",
       "  <table class='frame'>\n",
       "  <thead>\n",
       "    <tr class='colnames'><td class='row_index'></td><th>date</th><th>tag</th><th>ratio</th></tr>\n",
       "    <tr class='coltypes'><td class='row_index'></td><td class='int' title='int32'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='str' title='str32'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='float' title='float64'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td></tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr><td class='row_index'>0</td><td>20120104</td><td>bottom</td><td>0.0057457</td></tr>\n",
       "    <tr><td class='row_index'>1</td><td>20120104</td><td>top</td><td>0.00830585</td></tr>\n",
       "    <tr><td class='row_index'>2</td><td>20120105</td><td>bottom</td><td>0.00435634</td></tr>\n",
       "    <tr><td class='row_index'>3</td><td>20120105</td><td>top</td><td>0.00750696</td></tr>\n",
       "    <tr><td class='row_index'>4</td><td>20120106</td><td>bottom</td><td>0.00932084</td></tr>\n",
       "  </tbody>\n",
       "  </table>\n",
       "  <div class='footer'>\n",
       "    <div class='frame_dimensions'>5 rows &times; 3 columns</div>\n",
       "  </div>\n",
       "</div>\n"
      ],
      "text/plain": [
       "<Frame#223605e5960 5x3>"
      ]
     },
     "execution_count": 181,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 计算每只股票的涨幅并按照涨幅和日期排序\n",
    "data[:, dt.update(\n",
    "    ret = dt.f.close/dt.f.pre_close - 1)]\n",
    "data = data[:, :, dt.sort(dt.f.date, -dt.f.ret)]\n",
    "\n",
    "# 计算每日的成交量\n",
    "data[:, dt.update(\n",
    "    daily_amount = dt.sum(dt.f.amount)\n",
    "    ), dt.by(dt.f.date)]\n",
    "\n",
    "# 将涨幅前十和后十的股票的成交量进行计算\n",
    "data = dt.rbind(data[:10, {\"amount\": dt.sum(dt.f.amount),\n",
    "                            \"daily_amount\": dt.f.daily_amount,\n",
    "                            \"tag\": \"top\"\n",
    "                            }, dt.by(dt.f.date)], \n",
    "                data[-10:,{\"amount\": dt.sum(dt.f.amount),\n",
    "                            \"daily_amount\": dt.f.daily_amount,\n",
    "                            \"tag\": \"bottom\"\n",
    "                            }, dt.by(dt.f.date)])\n",
    "\n",
    "# 去重后分别计算成交比例\n",
    "data = data[:, :, dt.sort(dt.f.date, dt.f.tag)]\n",
    "data = data[0, :, dt.by(dt.f.date, dt.f.tag)]\n",
    "data[:, {\"ratio\": dt.f.amount/dt.f.daily_amount}, dt.by(dt.f.date, dt.f.tag)][:5, :]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9. 每天开盘涨停的股票与收盘涨停的股票各有多少？（涨停按照收益率超过1.5%的标准计算）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class='datatable'>\n",
       "  <table class='frame'>\n",
       "  <thead>\n",
       "    <tr class='colnames'><td class='row_index'></td><th>date</th><th>up_tag</th><th>symbol</th></tr>\n",
       "    <tr class='coltypes'><td class='row_index'></td><td class='int' title='int32'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='str' title='str32'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td><td class='int' title='int64'>&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;&#x25AA;</td></tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr><td class='row_index'>0</td><td>20120104</td><td>close_stop</td><td>70</td></tr>\n",
       "    <tr><td class='row_index'>1</td><td>20120104</td><td>open_stop</td><td>325</td></tr>\n",
       "    <tr><td class='row_index'>2</td><td>20120105</td><td>close_stop</td><td>60</td></tr>\n",
       "    <tr><td class='row_index'>3</td><td>20120105</td><td>open_stop</td><td>27</td></tr>\n",
       "    <tr><td class='row_index'>4</td><td>20120106</td><td>close_stop</td><td>743</td></tr>\n",
       "  </tbody>\n",
       "  </table>\n",
       "  <div class='footer'>\n",
       "    <div class='frame_dimensions'>5 rows &times; 3 columns</div>\n",
       "  </div>\n",
       "</div>\n"
      ],
      "text/plain": [
       "<Frame#216644240a8 5x3>"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 计算开盘与收盘收益率\n",
    "data[:, dt.update(\n",
    "    close_stop = dt.ifelse(dt.f.close/dt.f.pre_close - 1 > 0.015, \"close_stop\", \"others\"),\n",
    "    open_stop = dt.ifelse(dt.f.open/dt.f.pre_close - 1 > 0.015, \"open_stop\", \"others\")\n",
    "    )]\n",
    "\n",
    "data1 = data[:, dt.count(dt.f.symbol), dt.by(dt.f.date, dt.f.close_stop)\n",
    "    ][dt.f.close_stop != \"others\", :]\n",
    "data1.names = {\"close_stop\": \"up_tag\"}\n",
    "\n",
    "data2 = data[:, dt.count(dt.f.symbol), dt.by(dt.f.date, dt.f.open_stop)\n",
    "    ][dt.f.open_stop != \"others\", :]\n",
    "data2.names = {\"open_stop\": \"up_tag\"}\n",
    "\n",
    "data = dt.rbind(data1, data2)\n",
    "data = data[:, :, dt.sort(dt.f.date)]\n",
    "data[:5, :]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "be7c5096a73329a2b1a0393d63cb94dd70da5883b6836fb3dfbdedd6f96e7abc"
  },
  "kernelspec": {
   "display_name": "Python 3.8.6 64-bit",
   "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.3"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}