stock_takeoff.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# all imports and constant definition\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "import re\n",
    "import glob\n",
    "import os\n",
    "import os.path\n",
    "import sys\n",
    "\n",
    "DAILY_DATA_FOLDER = 'E:/analytics/stock/hist-D-2016-10-10-1224'\n",
    "DAILY_SAMPLE_PATH = 'E:/analytics/stock/hist-D-2016-10-10-1224/002415.csv'\n",
    "\n",
    "WEEKLY_DATA_FOLDER = 'E:/analytics/stock/hist-w-2016-12-1-0314'\n",
    "WEEKLY_SAMPLE_PATH = 'E:/analytics/stock/hist-w-2016-12-1-0212/002643.csv'\n",
    "\n",
    "DATE_FOR_PAUSE_CHECK = '2017-03-10'\n",
    "\n",
    "pd.set_option('max_rows', 20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>close</th>\n",
       "      <th>p_change</th>\n",
       "      <th>ma5</th>\n",
       "      <th>ma10</th>\n",
       "      <th>ma20</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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2016-12-23</th>\n",
       "      <td>23.26</td>\n",
       "      <td>1.53</td>\n",
       "      <td>23.168</td>\n",
       "      <td>23.580</td>\n",
       "      <td>24.391</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-12-22</th>\n",
       "      <td>22.91</td>\n",
       "      <td>-1.08</td>\n",
       "      <td>23.274</td>\n",
       "      <td>23.769</td>\n",
       "      <td>24.459</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-12-21</th>\n",
       "      <td>23.16</td>\n",
       "      <td>0.13</td>\n",
       "      <td>23.486</td>\n",
       "      <td>23.968</td>\n",
       "      <td>24.530</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-12-20</th>\n",
       "      <td>23.13</td>\n",
       "      <td>-1.07</td>\n",
       "      <td>23.638</td>\n",
       "      <td>24.170</td>\n",
       "      <td>24.589</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-12-19</th>\n",
       "      <td>23.38</td>\n",
       "      <td>-1.72</td>\n",
       "      <td>23.842</td>\n",
       "      <td>24.403</td>\n",
       "      <td>24.642</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-12-16</th>\n",
       "      <td>23.79</td>\n",
       "      <td>-0.75</td>\n",
       "      <td>23.992</td>\n",
       "      <td>24.607</td>\n",
       "      <td>24.692</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-12-15</th>\n",
       "      <td>23.97</td>\n",
       "      <td>0.21</td>\n",
       "      <td>24.264</td>\n",
       "      <td>24.745</td>\n",
       "      <td>24.721</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-12-14</th>\n",
       "      <td>23.92</td>\n",
       "      <td>-0.95</td>\n",
       "      <td>24.450</td>\n",
       "      <td>24.888</td>\n",
       "      <td>24.741</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-12-13</th>\n",
       "      <td>24.15</td>\n",
       "      <td>0.08</td>\n",
       "      <td>24.702</td>\n",
       "      <td>24.996</td>\n",
       "      <td>24.773</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-12-12</th>\n",
       "      <td>24.13</td>\n",
       "      <td>-4.06</td>\n",
       "      <td>24.964</td>\n",
       "      <td>25.117</td>\n",
       "      <td>24.783</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-10-21</th>\n",
       "      <td>25.32</td>\n",
       "      <td>0.40</td>\n",
       "      <td>25.028</td>\n",
       "      <td>24.814</td>\n",
       "      <td>24.605</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-10-20</th>\n",
       "      <td>25.22</td>\n",
       "      <td>0.88</td>\n",
       "      <td>24.996</td>\n",
       "      <td>24.729</td>\n",
       "      <td>24.574</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-10-19</th>\n",
       "      <td>25.00</td>\n",
       "      <td>0.40</td>\n",
       "      <td>24.980</td>\n",
       "      <td>24.629</td>\n",
       "      <td>24.519</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-10-18</th>\n",
       "      <td>24.90</td>\n",
       "      <td>0.81</td>\n",
       "      <td>24.980</td>\n",
       "      <td>24.519</td>\n",
       "      <td>24.469</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-10-17</th>\n",
       "      <td>24.70</td>\n",
       "      <td>-1.83</td>\n",
       "      <td>24.780</td>\n",
       "      <td>24.439</td>\n",
       "      <td>24.453</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-10-14</th>\n",
       "      <td>25.16</td>\n",
       "      <td>0.08</td>\n",
       "      <td>24.600</td>\n",
       "      <td>24.369</td>\n",
       "      <td>24.472</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-10-13</th>\n",
       "      <td>25.14</td>\n",
       "      <td>0.56</td>\n",
       "      <td>24.462</td>\n",
       "      <td>24.305</td>\n",
       "      <td>24.479</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-10-12</th>\n",
       "      <td>25.00</td>\n",
       "      <td>4.60</td>\n",
       "      <td>24.278</td>\n",
       "      <td>24.261</td>\n",
       "      <td>24.490</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-10-11</th>\n",
       "      <td>23.90</td>\n",
       "      <td>0.42</td>\n",
       "      <td>24.058</td>\n",
       "      <td>24.217</td>\n",
       "      <td>24.474</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-10-10</th>\n",
       "      <td>23.80</td>\n",
       "      <td>-2.74</td>\n",
       "      <td>24.098</td>\n",
       "      <td>24.291</td>\n",
       "      <td>24.495</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>53 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            close  p_change     ma5    ma10    ma20\n",
       "date                                               \n",
       "2016-12-23  23.26      1.53  23.168  23.580  24.391\n",
       "2016-12-22  22.91     -1.08  23.274  23.769  24.459\n",
       "2016-12-21  23.16      0.13  23.486  23.968  24.530\n",
       "2016-12-20  23.13     -1.07  23.638  24.170  24.589\n",
       "2016-12-19  23.38     -1.72  23.842  24.403  24.642\n",
       "2016-12-16  23.79     -0.75  23.992  24.607  24.692\n",
       "2016-12-15  23.97      0.21  24.264  24.745  24.721\n",
       "2016-12-14  23.92     -0.95  24.450  24.888  24.741\n",
       "2016-12-13  24.15      0.08  24.702  24.996  24.773\n",
       "2016-12-12  24.13     -4.06  24.964  25.117  24.783\n",
       "...           ...       ...     ...     ...     ...\n",
       "2016-10-21  25.32      0.40  25.028  24.814  24.605\n",
       "2016-10-20  25.22      0.88  24.996  24.729  24.574\n",
       "2016-10-19  25.00      0.40  24.980  24.629  24.519\n",
       "2016-10-18  24.90      0.81  24.980  24.519  24.469\n",
       "2016-10-17  24.70     -1.83  24.780  24.439  24.453\n",
       "2016-10-14  25.16      0.08  24.600  24.369  24.472\n",
       "2016-10-13  25.14      0.56  24.462  24.305  24.479\n",
       "2016-10-12  25.00      4.60  24.278  24.261  24.490\n",
       "2016-10-11  23.90      0.42  24.058  24.217  24.474\n",
       "2016-10-10  23.80     -2.74  24.098  24.291  24.495\n",
       "\n",
       "[53 rows x 5 columns]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(DAILY_SAMPLE_PATH, index_col=0, parse_dates=True, \\\n",
    "                 usecols=['date', 'close', 'p_change', 'ma5', 'ma10', 'ma20'], \\\n",
    "                 error_bad_lines=False)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date\n",
       "2015-07-12   -5.413333\n",
       "2015-06-21   -3.862000\n",
       "2013-12-08   -2.440000\n",
       "2015-03-08   -2.382500\n",
       "2014-03-23   -2.336000\n",
       "                ...   \n",
       "2015-04-05    2.442000\n",
       "2013-06-16    2.645000\n",
       "2014-12-07    2.842000\n",
       "2015-01-11    3.418000\n",
       "2015-05-24    6.086000\n",
       "Name: p_change, dtype: float64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "r = df.resample('W')\n",
    "# daily average price change percent on weekly basis\n",
    "r['p_change'].mean().dropna().sort_values()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>close</th>\n",
       "      <th>p_change</th>\n",
       "      <th>ma5</th>\n",
       "      <th>ma10</th>\n",
       "      <th>ma20</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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2017-02-10</th>\n",
       "      <td>33.80</td>\n",
       "      <td>-1.43</td>\n",
       "      <td>33.984</td>\n",
       "      <td>35.437</td>\n",
       "      <td>37.753</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-02-03</th>\n",
       "      <td>34.29</td>\n",
       "      <td>-0.03</td>\n",
       "      <td>34.588</td>\n",
       "      <td>35.897</td>\n",
       "      <td>38.086</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-26</th>\n",
       "      <td>34.30</td>\n",
       "      <td>3.16</td>\n",
       "      <td>34.990</td>\n",
       "      <td>36.506</td>\n",
       "      <td>38.522</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-20</th>\n",
       "      <td>33.25</td>\n",
       "      <td>-3.00</td>\n",
       "      <td>35.366</td>\n",
       "      <td>37.216</td>\n",
       "      <td>38.985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-13</th>\n",
       "      <td>34.28</td>\n",
       "      <td>-6.90</td>\n",
       "      <td>36.082</td>\n",
       "      <td>37.795</td>\n",
       "      <td>39.505</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-06</th>\n",
       "      <td>36.82</td>\n",
       "      <td>1.43</td>\n",
       "      <td>36.890</td>\n",
       "      <td>38.348</td>\n",
       "      <td>40.101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-12-30</th>\n",
       "      <td>36.30</td>\n",
       "      <td>0.33</td>\n",
       "      <td>37.206</td>\n",
       "      <td>38.541</td>\n",
       "      <td>40.620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-12-23</th>\n",
       "      <td>36.18</td>\n",
       "      <td>-1.76</td>\n",
       "      <td>38.022</td>\n",
       "      <td>39.096</td>\n",
       "      <td>41.101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-12-16</th>\n",
       "      <td>36.83</td>\n",
       "      <td>-3.89</td>\n",
       "      <td>39.066</td>\n",
       "      <td>39.606</td>\n",
       "      <td>41.542</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            close  p_change     ma5    ma10    ma20\n",
       "date                                               \n",
       "2017-02-10  33.80     -1.43  33.984  35.437  37.753\n",
       "2017-02-03  34.29     -0.03  34.588  35.897  38.086\n",
       "2017-01-26  34.30      3.16  34.990  36.506  38.522\n",
       "2017-01-20  33.25     -3.00  35.366  37.216  38.985\n",
       "2017-01-13  34.28     -6.90  36.082  37.795  39.505\n",
       "2017-01-06  36.82      1.43  36.890  38.348  40.101\n",
       "2016-12-30  36.30      0.33  37.206  38.541  40.620\n",
       "2016-12-23  36.18     -1.76  38.022  39.096  41.101\n",
       "2016-12-16  36.83     -3.89  39.066  39.606  41.542"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(WEEKLY_SAMPLE_PATH, index_col=0, parse_dates=True, \\\n",
    "                 usecols=['date', 'close', 'p_change', 'ma5', 'ma10', 'ma20'], \\\n",
    "                 error_bad_lines=False)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.index.get_loc(df[df['p_change']<0].index[0])\n",
    "df.index[0].dayofweek"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date\n",
       "2016-01-08       NaN\n",
       "2016-01-15       NaN\n",
       "2016-01-22       NaN\n",
       "2016-01-29       NaN\n",
       "2016-02-05    37.140\n",
       "2016-02-19    35.772\n",
       "2016-02-26    35.592\n",
       "2016-03-04    34.584\n",
       "2016-03-11    33.980\n",
       "2016-03-18    32.910\n",
       "               ...  \n",
       "2016-04-29    38.478\n",
       "2016-05-06    41.772\n",
       "2016-05-13    44.952\n",
       "2016-05-20    46.850\n",
       "2016-05-27    47.968\n",
       "2016-06-03    47.926\n",
       "2016-06-08    48.250\n",
       "2016-06-17    49.130\n",
       "2016-06-24    50.660\n",
       "2016-06-27    52.744\n",
       "Name: close, dtype: float64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "r_df = df.sort_index()\n",
    "r_df.rolling(window=5)['close'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "''' detect increase trend by use of MA, applicable for both daily (kind: 'd') and weekly data (kind: 'w')\n",
    "'''\n",
    "def find_increase_trend(df, trend_threshold=3):\n",
    "    \n",
    "    if len(df) == 0:\n",
    "        return None\n",
    "    \n",
    "    s = df['ma5']\n",
    "    # the position from which (exclusive) MA starts to rise until latest\n",
    "    ma_change_point = -1\n",
    "    for i in range(0, len(s)-1):\n",
    "        if s[i] < s[i+1]:\n",
    "            ma_change_point = i \n",
    "            break\n",
    "    else:\n",
    "        ma_change_point = len(s) - 1\n",
    "    \n",
    "    # the position from which (exclusive) closing price starts to rise until latest\n",
    "    # 0 means it has been falling in recent period, may rise 'tomorrow'\n",
    "    if not (df['p_change']<0).any():\n",
    "        starting_rise_point = len(df)-1\n",
    "    else:\n",
    "        starting_rise_point = df.index.get_loc(df[df['p_change']<0].index[0])\n",
    "    \n",
    "    trend_start = starting_rise_point if starting_rise_point > ma_change_point else ma_change_point\n",
    "    # a trend is established at least lasting for 3 occurrences\n",
    "    if trend_start < trend_threshold:\n",
    "        return None\n",
    "    else:\n",
    "        startdate = df.index[trend_start]\n",
    "        # use close price of two ends to calc total increase percent\n",
    "        total_increase = round(df.iat[0, 0] / df.iat[trend_start, 0] - 1, 4) * 100\n",
    "        # the observation at change point doesn't count as increase, but only later ones\n",
    "        trend_length = trend_start\n",
    "        # mean week-over-week increase percent over this period\n",
    "        mean_increase = round(df[:trend_start]['p_change'].mean(), 2)\n",
    "        first_above_mean_position = df.index.get_loc(df[df['p_change'] >= mean_increase].index[0])\n",
    "        # num of recent consecutive obserations whose WoW increase is lower than mean\n",
    "        recent_below_mean_count = first_above_mean_position\n",
    "        std = round(df[:trend_start]['p_change'].std(), 2)\n",
    "        return startdate, total_increase, trend_length, mean_increase, std, recent_below_mean_count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(Timestamp('2016-06-03 00:00:00'),\n",
       " 527.96000000000004,\n",
       " 5,\n",
       " 45.84,\n",
       " 22.04,\n",
       " True,\n",
       " 1)"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "find_increase_trend(WEEKLY_DATA_FOLDER + '/002800.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.Timestamp('20161001') > pd.Timestamp('20000101')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def analyze_trend(folder, kind='w', trend_threshold=3, increase_threshold=10, max_recent_slowdown=1):\n",
    "    reg = re.compile(r'(\\d{6}).csv')\n",
    "    stocks = {t[1].group(1):t[0] for t in ((x, reg.search(x)) for x in glob.glob(folder + '/*.csv')) if t[1]}\n",
    "    # [os.path.isfile(x) for x in list(stocks.values())[:5]]\n",
    "    resultmap = {}\n",
    "    latest = pd.Timestamp('20000101')\n",
    "    for code in stocks:\n",
    "        try: \n",
    "#             print('processing ', code)\n",
    "            df = pd.read_csv(stocks[code], index_col=0, parse_dates=True, \\\n",
    "                             usecols=['date', 'close', 'p_change', 'ma5', 'ma10', 'ma20'], \\\n",
    "                             error_bad_lines=False)\n",
    "            if len(df) == 0:\n",
    "                continue\n",
    "            \n",
    "            # delete first entry if it doesn't stand for weekly data (whose timestamp should be Fri)\n",
    "            # usually daily data for date at retrieval is also collected\n",
    "            if kind == 'w' and df.index[0].dayofweek != 4:\n",
    "                df = df[1:]\n",
    "                \n",
    "            # latest date available in input stock data,\n",
    "            # absence of it indicates the stock's tranding is paused at that time\n",
    "            if df.index[0] > latest:\n",
    "                latest = df.index[0]\n",
    "            elif df.index[0] < latest:\n",
    "                continue\n",
    "                \n",
    "            res = find_increase_trend(df, trend_threshold)\n",
    "            ''' take as valid entry when following conditions are met:\n",
    "                    1. increasing trend lasts longer than 3 observations \n",
    "                    2. actual increase percent over the period is above 10% (MA trails behind actual varation)\n",
    "                    3. increasing trend didn't considerably slowdown lately\n",
    "            '''\n",
    "            if res and res[1] > increase_threshold and res[-1] <= max_recent_slowdown:\n",
    "                resultmap[code] = res\n",
    "        except Exception as ex:\n",
    "            print('error occurred in processing %s: %s' % (code, ex))\n",
    "    df = pd.DataFrame.from_dict(resultmap, orient='index')\n",
    "    df.columns = ['startdate', 'increase', 'length', 'mean', 'std', 'RSL']\n",
    "    print(df.head())\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "error occurred in processing 603630: index 0 is out of bounds for axis 0 with size 0\n",
      "error occurred in processing 600242: index 0 is out of bounds for axis 0 with size 0\n",
      "error occurred in processing 600645: index 0 is out of bounds for axis 0 with size 0\n",
      "error occurred in processing 600212: index 0 is out of bounds for axis 0 with size 0\n",
      "error occurred in processing 600346: index 0 is out of bounds for axis 0 with size 0\n",
      "error occurred in processing 002664: index 0 is out of bounds for axis 0 with size 0\n",
      "error occurred in processing 603986: index 0 is out of bounds for axis 0 with size 0\n",
      "error occurred in processing 603960: index 0 is out of bounds for axis 0 with size 0\n",
      "error occurred in processing 000711: index 0 is out of bounds for axis 0 with size 0\n",
      "error occurred in processing 600768: index 0 is out of bounds for axis 0 with size 0\n",
      "error occurred in processing 600654: index 0 is out of bounds for axis 0 with size 0\n",
      "error occurred in processing 002335: index 0 is out of bounds for axis 0 with size 0\n",
      "error occurred in processing 603665: index 0 is out of bounds for axis 0 with size 0\n",
      "error occurred in processing 603903: index 0 is out of bounds for axis 0 with size 0\n",
      "        startdate  increase  length  mean    std  RSL\n",
      "603189 2017-02-10     21.06       4  5.29  10.86    0\n",
      "600223 2017-02-17      2.52       3  0.84   1.09    0\n",
      "603556 2017-02-10      3.60       4  0.91   2.57    0\n",
      "603339 2017-02-17      7.68       3  2.60   5.73    0\n",
      "001979 2017-01-20      6.84       7  0.97   2.16    0\n",
      "done analyzing trend analysis, found 399 records\n"
     ]
    }
   ],
   "source": [
    "df = analyze_trend(WEEKLY_DATA_FOLDER, kind='w', increase_threshold=2, max_recent_slowdown=0)\n",
    "print('done analyzing trend analysis, found %d records' % len(df))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "TREND_ANALYSIS_OUTPUT_PATH = 'E:/analytics/stock/analysis_weekly_0314.csv'\n",
    "df.to_csv(TREND_ANALYSIS_OUTPUT_PATH)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>pe</th>\n",
       "      <th>outstanding</th>\n",
       "      <th>totals</th>\n",
       "      <th>esp</th>\n",
       "      <th>pb</th>\n",
       "      <th>timeToMarket</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>code</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>300577</th>\n",
       "      <td>21.98</td>\n",
       "      <td>0.17</td>\n",
       "      <td>0.67</td>\n",
       "      <td>0.962</td>\n",
       "      <td>6.48</td>\n",
       "      <td>2016-12-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>603416</th>\n",
       "      <td>30.06</td>\n",
       "      <td>0.25</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.641</td>\n",
       "      <td>5.06</td>\n",
       "      <td>2016-12-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>600115</th>\n",
       "      <td>11.18</td>\n",
       "      <td>84.81</td>\n",
       "      <td>144.68</td>\n",
       "      <td>0.490</td>\n",
       "      <td>1.98</td>\n",
       "      <td>1997-11-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000010</th>\n",
       "      <td>221.06</td>\n",
       "      <td>4.08</td>\n",
       "      <td>8.20</td>\n",
       "      <td>0.027</td>\n",
       "      <td>2.94</td>\n",
       "      <td>1995-10-27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>603823</th>\n",
       "      <td>25.21</td>\n",
       "      <td>0.45</td>\n",
       "      <td>2.25</td>\n",
       "      <td>0.500</td>\n",
       "      <td>4.29</td>\n",
       "      <td>2016-12-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>300576</th>\n",
       "      <td>28.41</td>\n",
       "      <td>0.20</td>\n",
       "      <td>0.80</td>\n",
       "      <td>0.345</td>\n",
       "      <td>3.39</td>\n",
       "      <td>2016-12-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>002040</th>\n",
       "      <td>190.65</td>\n",
       "      <td>2.46</td>\n",
       "      <td>2.46</td>\n",
       "      <td>0.086</td>\n",
       "      <td>7.84</td>\n",
       "      <td>2005-03-25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>603878</th>\n",
       "      <td>43.97</td>\n",
       "      <td>0.51</td>\n",
       "      <td>2.02</td>\n",
       "      <td>0.442</td>\n",
       "      <td>3.26</td>\n",
       "      <td>2016-12-19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>600523</th>\n",
       "      <td>48.81</td>\n",
       "      <td>2.89</td>\n",
       "      <td>2.89</td>\n",
       "      <td>0.350</td>\n",
       "      <td>3.10</td>\n",
       "      <td>2001-12-27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000710</th>\n",
       "      <td>0.00</td>\n",
       "      <td>1.51</td>\n",
       "      <td>1.51</td>\n",
       "      <td>-0.045</td>\n",
       "      <td>40.03</td>\n",
       "      <td>1997-04-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>300268</th>\n",
       "      <td>0.00</td>\n",
       "      <td>1.34</td>\n",
       "      <td>1.34</td>\n",
       "      <td>-0.061</td>\n",
       "      <td>26.80</td>\n",
       "      <td>2011-09-27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>002825</th>\n",
       "      <td>84.61</td>\n",
       "      <td>0.25</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.488</td>\n",
       "      <td>12.14</td>\n",
       "      <td>2016-11-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>300570</th>\n",
       "      <td>81.73</td>\n",
       "      <td>0.32</td>\n",
       "      <td>1.28</td>\n",
       "      <td>0.593</td>\n",
       "      <td>14.93</td>\n",
       "      <td>2016-12-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>002098</th>\n",
       "      <td>62.90</td>\n",
       "      <td>3.10</td>\n",
       "      <td>3.58</td>\n",
       "      <td>0.241</td>\n",
       "      <td>6.59</td>\n",
       "      <td>2006-12-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>002283</th>\n",
       "      <td>58.86</td>\n",
       "      <td>11.20</td>\n",
       "      <td>11.20</td>\n",
       "      <td>0.111</td>\n",
       "      <td>2.81</td>\n",
       "      <td>2009-08-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>600732</th>\n",
       "      <td>0.00</td>\n",
       "      <td>4.46</td>\n",
       "      <td>4.46</td>\n",
       "      <td>-0.081</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1996-08-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>600710</th>\n",
       "      <td>0.00</td>\n",
       "      <td>6.40</td>\n",
       "      <td>13.07</td>\n",
       "      <td>-0.081</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1996-07-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>300372</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.98</td>\n",
       "      <td>1.72</td>\n",
       "      <td>-0.052</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2014-01-27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000155</th>\n",
       "      <td>0.00</td>\n",
       "      <td>9.37</td>\n",
       "      <td>12.70</td>\n",
       "      <td>0.260</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2000-09-26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000033</th>\n",
       "      <td>0.00</td>\n",
       "      <td>4.30</td>\n",
       "      <td>4.30</td>\n",
       "      <td>0.012</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1994-01-03</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3018 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            pe  outstanding  totals    esp     pb timeToMarket\n",
       "code                                                          \n",
       "300577   21.98         0.17    0.67  0.962   6.48   2016-12-21\n",
       "603416   30.06         0.25    1.00  0.641   5.06   2016-12-21\n",
       "600115   11.18        84.81  144.68  0.490   1.98   1997-11-05\n",
       "000010  221.06         4.08    8.20  0.027   2.94   1995-10-27\n",
       "603823   25.21         0.45    2.25  0.500   4.29   2016-12-20\n",
       "300576   28.41         0.20    0.80  0.345   3.39   2016-12-20\n",
       "002040  190.65         2.46    2.46  0.086   7.84   2005-03-25\n",
       "603878   43.97         0.51    2.02  0.442   3.26   2016-12-19\n",
       "600523   48.81         2.89    2.89  0.350   3.10   2001-12-27\n",
       "000710    0.00         1.51    1.51 -0.045  40.03   1997-04-22\n",
       "...        ...          ...     ...    ...    ...          ...\n",
       "300268    0.00         1.34    1.34 -0.061  26.80   2011-09-27\n",
       "002825   84.61         0.25    1.00  0.488  12.14   2016-11-29\n",
       "300570   81.73         0.32    1.28  0.593  14.93   2016-12-06\n",
       "002098   62.90         3.10    3.58  0.241   6.59   2006-12-22\n",
       "002283   58.86        11.20   11.20  0.111   2.81   2009-08-21\n",
       "600732    0.00         4.46    4.46 -0.081   0.00   1996-08-16\n",
       "600710    0.00         6.40   13.07 -0.081   0.00   1996-07-01\n",
       "300372    0.00         0.98    1.72 -0.052   0.00   2014-01-27\n",
       "000155    0.00         9.37   12.70  0.260   0.00   2000-09-26\n",
       "000033    0.00         4.30    4.30  0.012   0.00   1994-01-03\n",
       "\n",
       "[3018 rows x 6 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.set_option('max_rows', 20)\n",
    "\n",
    "BASIC_DATA_PATH = r'E:\\analytics\\stock\\basics\\basic.csv'\n",
    "basic_df = pd.read_csv(BASIC_DATA_PATH, index_col=False, dtype={'code':np.str}, \\\n",
    "                       usecols=['code', 'pe', 'pb', 'outstanding', 'totals', 'esp', 'timeToMarket'], \\\n",
    "                       error_bad_lines=False)\n",
    "basic_df.set_index('code', inplace=True)\n",
    "basic_df['timeToMarket'] = pd.to_datetime(basic_df['timeToMarket'], errors='coerce', format='%Y%m%d')\n",
    "basic_df = basic_df[basic_df['timeToMarket'].notnull()]\n",
    "basic_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">2016-2</th>\n",
       "      <th colspan=\"2\" halign=\"left\">2016-3</th>\n",
       "      <th colspan=\"2\" halign=\"left\">2016-1</th>\n",
       "      <th colspan=\"2\" halign=\"left\">2015-4</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>roe</th>\n",
       "      <th>profits_yoy</th>\n",
       "      <th>roe</th>\n",
       "      <th>profits_yoy</th>\n",
       "      <th>roe</th>\n",
       "      <th>profits_yoy</th>\n",
       "      <th>roe</th>\n",
       "      <th>profits_yoy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>000001</th>\n",
       "      <td>7.35</td>\n",
       "      <td>6.10</td>\n",
       "      <td>11.02</td>\n",
       "      <td>5.52</td>\n",
       "      <td>3.70</td>\n",
       "      <td>8.12</td>\n",
       "      <td>14.94</td>\n",
       "      <td>10.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000002</th>\n",
       "      <td>5.20</td>\n",
       "      <td>10.42</td>\n",
       "      <td>8.13</td>\n",
       "      <td>20.54</td>\n",
       "      <td>0.83</td>\n",
       "      <td>28.14</td>\n",
       "      <td>19.14</td>\n",
       "      <td>15.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000004</th>\n",
       "      <td>3.60</td>\n",
       "      <td>146.30</td>\n",
       "      <td>19.25</td>\n",
       "      <td>518.44</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.54</td>\n",
       "      <td>-67.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000005</th>\n",
       "      <td>10.48</td>\n",
       "      <td>-647.59</td>\n",
       "      <td>11.00</td>\n",
       "      <td>-548.35</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-8.07</td>\n",
       "      <td>-227.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000006</th>\n",
       "      <td>2.30</td>\n",
       "      <td>-60.44</td>\n",
       "      <td>2.60</td>\n",
       "      <td>-58.64</td>\n",
       "      <td>1.11</td>\n",
       "      <td>-75.79</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000007</th>\n",
       "      <td>-7.49</td>\n",
       "      <td>258.30</td>\n",
       "      <td>1.77</td>\n",
       "      <td>71.68</td>\n",
       "      <td>2.05</td>\n",
       "      <td>-257.50</td>\n",
       "      <td>3.87</td>\n",
       "      <td>-146.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000008</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.99</td>\n",
       "      <td>126.37</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.60</td>\n",
       "      <td>2290.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000009</th>\n",
       "      <td>1.61</td>\n",
       "      <td>-38.77</td>\n",
       "      <td>4.67</td>\n",
       "      <td>-69.06</td>\n",
       "      <td>0.67</td>\n",
       "      <td>-45.77</td>\n",
       "      <td>19.70</td>\n",
       "      <td>160.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000010</th>\n",
       "      <td>1.20</td>\n",
       "      <td>1519.15</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.05</td>\n",
       "      <td>-144.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000011</th>\n",
       "      <td>-0.25</td>\n",
       "      <td>-116.84</td>\n",
       "      <td>-0.35</td>\n",
       "      <td>-115.50</td>\n",
       "      <td>0.03</td>\n",
       "      <td>61.92</td>\n",
       "      <td>7.59</td>\n",
       "      <td>-62.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>603986</th>\n",
       "      <td>14.55</td>\n",
       "      <td>44.16</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31.92</td>\n",
       "      <td>60.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>603987</th>\n",
       "      <td>6.77</td>\n",
       "      <td>9.95</td>\n",
       "      <td>11.39</td>\n",
       "      <td>10.27</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.09</td>\n",
       "      <td>46.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>603988</th>\n",
       "      <td>2.86</td>\n",
       "      <td>-21.85</td>\n",
       "      <td>3.80</td>\n",
       "      <td>-30.88</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.89</td>\n",
       "      <td>-21.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>603989</th>\n",
       "      <td>7.59</td>\n",
       "      <td>49.85</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.12</td>\n",
       "      <td>13.96</td>\n",
       "      <td>16.52</td>\n",
       "      <td>25.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>603990</th>\n",
       "      <td>5.40</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>23.31</td>\n",
       "      <td>-0.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>603993</th>\n",
       "      <td>2.92</td>\n",
       "      <td>10.56</td>\n",
       "      <td>3.30</td>\n",
       "      <td>-8.96</td>\n",
       "      <td>0.81</td>\n",
       "      <td>-53.67</td>\n",
       "      <td>4.77</td>\n",
       "      <td>-58.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>603996</th>\n",
       "      <td>0.75</td>\n",
       "      <td>-79.40</td>\n",
       "      <td>4.50</td>\n",
       "      <td>-24.49</td>\n",
       "      <td>0.19</td>\n",
       "      <td>-75.55</td>\n",
       "      <td>13.18</td>\n",
       "      <td>5.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>603997</th>\n",
       "      <td>8.28</td>\n",
       "      <td>31.63</td>\n",
       "      <td>12.67</td>\n",
       "      <td>37.09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.11</td>\n",
       "      <td>-12.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>603998</th>\n",
       "      <td>4.18</td>\n",
       "      <td>-20.66</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.77</td>\n",
       "      <td>-23.79</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>603999</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.84</td>\n",
       "      <td>-0.74</td>\n",
       "      <td>0.74</td>\n",
       "      <td>-20.19</td>\n",
       "      <td>9.86</td>\n",
       "      <td>-28.07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3101 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       2016-2             2016-3             2016-1             2015-4  \\\n",
       "          roe profits_yoy    roe profits_yoy    roe profits_yoy    roe   \n",
       "000001   7.35        6.10  11.02        5.52   3.70        8.12  14.94   \n",
       "000002   5.20       10.42   8.13       20.54   0.83       28.14  19.14   \n",
       "000004   3.60      146.30  19.25      518.44    NaN         NaN   1.54   \n",
       "000005  10.48     -647.59  11.00     -548.35    NaN         NaN  -8.07   \n",
       "000006   2.30      -60.44   2.60      -58.64   1.11      -75.79    NaN   \n",
       "000007  -7.49      258.30   1.77       71.68   2.05     -257.50   3.87   \n",
       "000008    NaN         NaN   2.99      126.37    NaN         NaN   7.60   \n",
       "000009   1.61      -38.77   4.67      -69.06   0.67      -45.77  19.70   \n",
       "000010   1.20     1519.15    NaN         NaN    NaN         NaN   4.05   \n",
       "000011  -0.25     -116.84  -0.35     -115.50   0.03       61.92   7.59   \n",
       "...       ...         ...    ...         ...    ...         ...    ...   \n",
       "603986  14.55       44.16    NaN         NaN    NaN         NaN  31.92   \n",
       "603987   6.77        9.95  11.39       10.27    NaN         NaN  14.09   \n",
       "603988   2.86      -21.85   3.80      -30.88    NaN         NaN   6.89   \n",
       "603989   7.59       49.85    NaN         NaN   3.12       13.96  16.52   \n",
       "603990   5.40         NaN    NaN         NaN    NaN         NaN  23.31   \n",
       "603993   2.92       10.56   3.30       -8.96   0.81      -53.67   4.77   \n",
       "603996   0.75      -79.40   4.50      -24.49   0.19      -75.55  13.18   \n",
       "603997   8.28       31.63  12.67       37.09    NaN         NaN  15.11   \n",
       "603998   4.18      -20.66    NaN         NaN   1.77      -23.79    NaN   \n",
       "603999    NaN         NaN   3.84       -0.74   0.74      -20.19   9.86   \n",
       "\n",
       "                    \n",
       "       profits_yoy  \n",
       "000001       10.42  \n",
       "000002       15.08  \n",
       "000004      -67.19  \n",
       "000005     -227.98  \n",
       "000006         NaN  \n",
       "000007     -146.35  \n",
       "000008     2290.28  \n",
       "000009      160.12  \n",
       "000010     -144.86  \n",
       "000011      -62.44  \n",
       "...            ...  \n",
       "603986       60.80  \n",
       "603987       46.64  \n",
       "603988      -21.29  \n",
       "603989       25.14  \n",
       "603990       -0.74  \n",
       "603993      -58.28  \n",
       "603996        5.15  \n",
       "603997      -12.54  \n",
       "603998         NaN  \n",
       "603999      -28.07  \n",
       "\n",
       "[3101 rows x 8 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "REPORT_DATA_FOLDER = r'E:/analytics/stock/report'\n",
    "\n",
    "reg = re.compile(r'(\\d{4}-\\d).csv')\n",
    "reports = {t[1].group(1):t[0] for t in ((x, reg.search(x)) for x in glob.glob(REPORT_DATA_FOLDER + '/*.csv')) if t[1]}\n",
    "report_dfs = []\n",
    "report_terms = []\n",
    "for term in reports:\n",
    "    rdf = pd.read_csv(reports[term], index_col=False, dtype={'code':np.str}, \\\n",
    "                           usecols=['code', 'roe', 'profits_yoy'], \\\n",
    "                           error_bad_lines=False)\n",
    "    rdf.set_index('code', inplace=True)\n",
    "    d = rdf.index.duplicated()\n",
    "#     print('duplicates in %s: %d (%s)' % (term, len(d[d==True]), rdf.index[d==True][:5]))\n",
    "    rdf.drop_duplicates(inplace=True)\n",
    "    report_dfs.append(rdf)\n",
    "    report_terms.append(term)\n",
    "# print(report_dfs)\n",
    "all_report_df = pd.concat(report_dfs, keys=report_terms, axis=1, join='outer')\n",
    "all_report_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "CONSOLIDATED_DATA_PATH = r'E:/analytics/stock/consolidated-W-0314.csv'\n",
    "consolidated = df.join(basic_df).join(all_report_df)\n",
    "consolidated.to_csv(CONSOLIDATED_DATA_PATH)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}