stock_data_preprocessing.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "toc": true
   },
   "source": [
    "<h1>Table of Contents<span class=\"tocSkip\"></span></h1>\n",
    "<div class=\"toc\"><ul class=\"toc-item\"><li><span><a href=\"#Import-Data\" data-toc-modified-id=\"Import-Data-1\"><span class=\"toc-item-num\">1&nbsp;&nbsp;</span>Import Data</a></span></li><li><span><a href=\"#Data-Cleaning\" data-toc-modified-id=\"Data-Cleaning-2\"><span class=\"toc-item-num\">2&nbsp;&nbsp;</span>Data Cleaning</a></span></li><li><span><a href=\"#Merge-data-frames\" data-toc-modified-id=\"Merge-data-frames-3\"><span class=\"toc-item-num\">3&nbsp;&nbsp;</span>Merge data frames</a></span></li><li><span><a href=\"#Apply-EMD-on-data\" data-toc-modified-id=\"Apply-EMD-on-data-4\"><span class=\"toc-item-num\">4&nbsp;&nbsp;</span>Apply EMD on data</a></span></li></ul></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- This notebook contains code for stock data preprocessing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-03-06T01:22:30.296303Z",
     "start_time": "2021-03-06T01:22:29.636169Z"
    },
    "colab": {},
    "colab_type": "code",
    "id": "uuzvpDkOV3oT"
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import pylab as plt\n",
    "from PyEMD import EMD "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "X5YN-pjRLRvf"
   },
   "source": [
    "## Import Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Data source: \n",
    "    1. 龍虎榜指數 (sent) http://data.10jqka.com.cn/market/longhu/#refCountId=db_50754c78_378\n",
    "    2. 上證50指數 (sz50) http://www.cninfo.com.cn/new/index\n",
    "    3. 創業板指數 (cy) http://www.cninfo.com.cn/new/index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-03-06T01:22:30.319293Z",
     "start_time": "2021-03-06T01:22:30.297294Z"
    },
    "colab": {},
    "colab_type": "code",
    "id": "knhuZvddWz6Y"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(394, 7) (394, 7) (394, 3)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>收盘</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>0</th>\n",
       "      <td>2019年9月30日</td>\n",
       "      <td>2,897.70</td>\n",
       "      <td>2,917.07</td>\n",
       "      <td>2,933.51</td>\n",
       "      <td>2,897.68</td>\n",
       "      <td>1.92B</td>\n",
       "      <td>-1.08%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019年9月27日</td>\n",
       "      <td>2,929.47</td>\n",
       "      <td>2,929.32</td>\n",
       "      <td>2,934.33</td>\n",
       "      <td>2,916.07</td>\n",
       "      <td>2.30B</td>\n",
       "      <td>0.05%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019年9月26日</td>\n",
       "      <td>2,927.88</td>\n",
       "      <td>2,939.68</td>\n",
       "      <td>2,952.87</td>\n",
       "      <td>2,926.54</td>\n",
       "      <td>3.15B</td>\n",
       "      <td>-0.03%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019年9月25日</td>\n",
       "      <td>2,928.84</td>\n",
       "      <td>2,930.14</td>\n",
       "      <td>2,942.00</td>\n",
       "      <td>2,923.15</td>\n",
       "      <td>2.25B</td>\n",
       "      <td>-0.38%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019年9月24日</td>\n",
       "      <td>2,940.08</td>\n",
       "      <td>2,936.95</td>\n",
       "      <td>2,956.29</td>\n",
       "      <td>2,929.60</td>\n",
       "      <td>2.27B</td>\n",
       "      <td>0.31%</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           日期        收盘        开盘         高         低    交易量     涨跌幅\n",
       "0  2019年9月30日  2,897.70  2,917.07  2,933.51  2,897.68  1.92B  -1.08%\n",
       "1  2019年9月27日  2,929.47  2,929.32  2,934.33  2,916.07  2.30B   0.05%\n",
       "2  2019年9月26日  2,927.88  2,939.68  2,952.87  2,926.54  3.15B  -0.03%\n",
       "3  2019年9月25日  2,928.84  2,930.14  2,942.00  2,923.15  2.25B  -0.38%\n",
       "4  2019年9月24日  2,940.08  2,936.95  2,956.29  2,929.60  2.27B   0.31%"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Import data\n",
    "sz50 = pd.read_csv('data/stock_data/sz50.csv')\n",
    "cyb = pd.read_csv('data/stock_data/cyb.csv')\n",
    "\n",
    "sent = pd.read_csv('data/stock_data/sent_lhb.csv', header = None)\n",
    "sent.columns = ['sen', 'lhb', 'date']\n",
    "\n",
    "print(sz50.shape, cyb.shape, sent.shape)\n",
    "sz50.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "R4LitNJbogAN"
   },
   "source": [
    "- Data description for sz50 and cyb\n",
    "    1. Period: 20180222 - 20190930\n",
    "    2. Columns: 收盤、開盤、高、低、交易量、漲跌幅"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "JvwbmbDephLv"
   },
   "source": [
    "## Data Cleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-03-06T01:22:30.336312Z",
     "start_time": "2021-03-06T01:22:30.320293Z"
    },
    "colab": {},
    "colab_type": "code",
    "id": "qt0BBeTSpk15"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>收盘</th>\n",
       "      <th>开盘</th>\n",
       "      <th>高</th>\n",
       "      <th>低</th>\n",
       "      <th>交易量</th>\n",
       "      <th>涨跌幅</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2,897.70</td>\n",
       "      <td>2,917.07</td>\n",
       "      <td>2,933.51</td>\n",
       "      <td>2,897.68</td>\n",
       "      <td>1.92B</td>\n",
       "      <td>-1.08%</td>\n",
       "      <td>20190930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2,929.47</td>\n",
       "      <td>2,929.32</td>\n",
       "      <td>2,934.33</td>\n",
       "      <td>2,916.07</td>\n",
       "      <td>2.30B</td>\n",
       "      <td>0.05%</td>\n",
       "      <td>20190927</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2,927.88</td>\n",
       "      <td>2,939.68</td>\n",
       "      <td>2,952.87</td>\n",
       "      <td>2,926.54</td>\n",
       "      <td>3.15B</td>\n",
       "      <td>-0.03%</td>\n",
       "      <td>20190926</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2,928.84</td>\n",
       "      <td>2,930.14</td>\n",
       "      <td>2,942.00</td>\n",
       "      <td>2,923.15</td>\n",
       "      <td>2.25B</td>\n",
       "      <td>-0.38%</td>\n",
       "      <td>20190925</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2,940.08</td>\n",
       "      <td>2,936.95</td>\n",
       "      <td>2,956.29</td>\n",
       "      <td>2,929.60</td>\n",
       "      <td>2.27B</td>\n",
       "      <td>0.31%</td>\n",
       "      <td>20190924</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         收盘        开盘         高         低    交易量     涨跌幅      date\n",
       "0  2,897.70  2,917.07  2,933.51  2,897.68  1.92B  -1.08%  20190930\n",
       "1  2,929.47  2,929.32  2,934.33  2,916.07  2.30B   0.05%  20190927\n",
       "2  2,927.88  2,939.68  2,952.87  2,926.54  3.15B  -0.03%  20190926\n",
       "3  2,928.84  2,930.14  2,942.00  2,923.15  2.25B  -0.38%  20190925\n",
       "4  2,940.08  2,936.95  2,956.29  2,929.60  2.27B   0.31%  20190924"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Clean date column\n",
    "def clean_date(date):\n",
    "    '''Convert chinese date into date'''\n",
    "    date = date.split('年')  \n",
    "    year = date[0]\n",
    "    \n",
    "    date = date[1].split('月')\n",
    "    month = date[0]\n",
    "    if len(month) == 1:\n",
    "        month = '0' + month\n",
    "    \n",
    "    date = date[1].split('日')\n",
    "    day = date[0]\n",
    "    if len(day) == 1:\n",
    "        day = '0' + day\n",
    "        \n",
    "    return year + month + day\n",
    "\n",
    "sz50['date'] = sz50['日期'].apply(clean_date).astype(int)\n",
    "cyb['date'] = cyb['日期'].apply(clean_date).astype(int)\n",
    "\n",
    "sz50.drop('日期', axis=1, inplace=True)\n",
    "cyb.drop('日期', axis=1, inplace=True)\n",
    "\n",
    "sz50.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-03-06T01:22:30.345295Z",
     "start_time": "2021-03-06T01:22:30.338294Z"
    },
    "colab": {},
    "colab_type": "code",
    "id": "1papcOjK8wA6"
   },
   "outputs": [],
   "source": [
    "sz50['涨跌幅'] = sz50['涨跌幅'].str.strip('%')\n",
    "cyb['涨跌幅'] = cyb['涨跌幅'].str.strip('%')\n",
    "\n",
    "cyb['涨跌幅'] = pd.to_numeric(cyb['涨跌幅'])\n",
    "sz50['涨跌幅'] = pd.to_numeric(sz50['涨跌幅'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "Z25ytHHXpWq1"
   },
   "source": [
    "## Merge data frames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-03-06T01:22:30.363294Z",
     "start_time": "2021-03-06T01:22:30.346293Z"
    },
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 257
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 937,
     "status": "ok",
     "timestamp": 1573200470730,
     "user": {
      "displayName": "Soung Low",
      "photoUrl": "",
      "userId": "00570526448316647912"
     },
     "user_tz": -480
    },
    "id": "Kotmh2HKYWq1",
    "outputId": "ed80ba80-17b8-45c6-fdbb-a174133584cf"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>收盘_sz</th>\n",
       "      <th>开盘_sz</th>\n",
       "      <th>高_sz</th>\n",
       "      <th>低_sz</th>\n",
       "      <th>交易量_sz</th>\n",
       "      <th>涨跌幅_sz</th>\n",
       "      <th>date</th>\n",
       "      <th>收盘_cy</th>\n",
       "      <th>开盘_cy</th>\n",
       "      <th>高_cy</th>\n",
       "      <th>低_cy</th>\n",
       "      <th>交易量_cy</th>\n",
       "      <th>涨跌幅_cy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2,897.70</td>\n",
       "      <td>2,917.07</td>\n",
       "      <td>2,933.51</td>\n",
       "      <td>2,897.68</td>\n",
       "      <td>1.92B</td>\n",
       "      <td>-1.08</td>\n",
       "      <td>20190930</td>\n",
       "      <td>1,627.55</td>\n",
       "      <td>1,648.90</td>\n",
       "      <td>1,651.87</td>\n",
       "      <td>1,626.05</td>\n",
       "      <td>1.68B</td>\n",
       "      <td>-1.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2,929.47</td>\n",
       "      <td>2,929.32</td>\n",
       "      <td>2,934.33</td>\n",
       "      <td>2,916.07</td>\n",
       "      <td>2.30B</td>\n",
       "      <td>0.05</td>\n",
       "      <td>20190927</td>\n",
       "      <td>1,647.53</td>\n",
       "      <td>1,628.14</td>\n",
       "      <td>1,653.06</td>\n",
       "      <td>1,624.75</td>\n",
       "      <td>1.92B</td>\n",
       "      <td>1.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2,927.88</td>\n",
       "      <td>2,939.68</td>\n",
       "      <td>2,952.87</td>\n",
       "      <td>2,926.54</td>\n",
       "      <td>3.15B</td>\n",
       "      <td>-0.03</td>\n",
       "      <td>20190926</td>\n",
       "      <td>1,623.79</td>\n",
       "      <td>1,677.79</td>\n",
       "      <td>1,680.93</td>\n",
       "      <td>1,623.65</td>\n",
       "      <td>2.21B</td>\n",
       "      <td>-2.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2,928.84</td>\n",
       "      <td>2,930.14</td>\n",
       "      <td>2,942.00</td>\n",
       "      <td>2,923.15</td>\n",
       "      <td>2.25B</td>\n",
       "      <td>-0.38</td>\n",
       "      <td>20190925</td>\n",
       "      <td>1,672.65</td>\n",
       "      <td>1,686.19</td>\n",
       "      <td>1,689.23</td>\n",
       "      <td>1,670.68</td>\n",
       "      <td>2.22B</td>\n",
       "      <td>-1.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2,940.08</td>\n",
       "      <td>2,936.95</td>\n",
       "      <td>2,956.29</td>\n",
       "      <td>2,929.60</td>\n",
       "      <td>2.27B</td>\n",
       "      <td>0.31</td>\n",
       "      <td>20190924</td>\n",
       "      <td>1,694.60</td>\n",
       "      <td>1,685.12</td>\n",
       "      <td>1,706.82</td>\n",
       "      <td>1,678.18</td>\n",
       "      <td>2.28B</td>\n",
       "      <td>0.61</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      收盘_sz     开盘_sz      高_sz      低_sz 交易量_sz  涨跌幅_sz      date     收盘_cy  \\\n",
       "0  2,897.70  2,917.07  2,933.51  2,897.68  1.92B   -1.08  20190930  1,627.55   \n",
       "1  2,929.47  2,929.32  2,934.33  2,916.07  2.30B    0.05  20190927  1,647.53   \n",
       "2  2,927.88  2,939.68  2,952.87  2,926.54  3.15B   -0.03  20190926  1,623.79   \n",
       "3  2,928.84  2,930.14  2,942.00  2,923.15  2.25B   -0.38  20190925  1,672.65   \n",
       "4  2,940.08  2,936.95  2,956.29  2,929.60  2.27B    0.31  20190924  1,694.60   \n",
       "\n",
       "      开盘_cy      高_cy      低_cy 交易量_cy  涨跌幅_cy  \n",
       "0  1,648.90  1,651.87  1,626.05  1.68B   -1.21  \n",
       "1  1,628.14  1,653.06  1,624.75  1.92B    1.46  \n",
       "2  1,677.79  1,680.93  1,623.65  2.21B   -2.92  \n",
       "3  1,686.19  1,689.23  1,670.68  2.22B   -1.30  \n",
       "4  1,685.12  1,706.82  1,678.18  2.28B    0.61  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = pd.merge(sz50, cyb, on='date', suffixes=('_sz', '_cy'))\n",
    "result.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-03-06T01:22:30.386294Z",
     "start_time": "2021-03-06T01:22:30.365315Z"
    },
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 257
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 1080,
     "status": "ok",
     "timestamp": 1573200474045,
     "user": {
      "displayName": "Soung Low",
      "photoUrl": "",
      "userId": "00570526448316647912"
     },
     "user_tz": -480
    },
    "id": "LFPsmF6BX-3m",
    "outputId": "95895e10-9940-4d8e-ef24-37c0eb006a88"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>收盘_sz</th>\n",
       "      <th>开盘_sz</th>\n",
       "      <th>高_sz</th>\n",
       "      <th>低_sz</th>\n",
       "      <th>交易量_sz</th>\n",
       "      <th>涨跌幅_sz</th>\n",
       "      <th>date</th>\n",
       "      <th>收盘_cy</th>\n",
       "      <th>开盘_cy</th>\n",
       "      <th>高_cy</th>\n",
       "      <th>低_cy</th>\n",
       "      <th>交易量_cy</th>\n",
       "      <th>涨跌幅_cy</th>\n",
       "      <th>sen</th>\n",
       "      <th>lhb</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2,897.70</td>\n",
       "      <td>2,917.07</td>\n",
       "      <td>2,933.51</td>\n",
       "      <td>2,897.68</td>\n",
       "      <td>1.92B</td>\n",
       "      <td>-1.08</td>\n",
       "      <td>20190930</td>\n",
       "      <td>1,627.55</td>\n",
       "      <td>1,648.90</td>\n",
       "      <td>1,651.87</td>\n",
       "      <td>1,626.05</td>\n",
       "      <td>1.68B</td>\n",
       "      <td>-1.21</td>\n",
       "      <td>-0.049456</td>\n",
       "      <td>-0.030211</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2,929.47</td>\n",
       "      <td>2,929.32</td>\n",
       "      <td>2,934.33</td>\n",
       "      <td>2,916.07</td>\n",
       "      <td>2.30B</td>\n",
       "      <td>0.05</td>\n",
       "      <td>20190927</td>\n",
       "      <td>1,647.53</td>\n",
       "      <td>1,628.14</td>\n",
       "      <td>1,653.06</td>\n",
       "      <td>1,624.75</td>\n",
       "      <td>1.92B</td>\n",
       "      <td>1.46</td>\n",
       "      <td>-0.029732</td>\n",
       "      <td>-0.036020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2,927.88</td>\n",
       "      <td>2,939.68</td>\n",
       "      <td>2,952.87</td>\n",
       "      <td>2,926.54</td>\n",
       "      <td>3.15B</td>\n",
       "      <td>-0.03</td>\n",
       "      <td>20190926</td>\n",
       "      <td>1,623.79</td>\n",
       "      <td>1,677.79</td>\n",
       "      <td>1,680.93</td>\n",
       "      <td>1,623.65</td>\n",
       "      <td>2.21B</td>\n",
       "      <td>-2.92</td>\n",
       "      <td>-0.068419</td>\n",
       "      <td>-0.098260</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2,928.84</td>\n",
       "      <td>2,930.14</td>\n",
       "      <td>2,942.00</td>\n",
       "      <td>2,923.15</td>\n",
       "      <td>2.25B</td>\n",
       "      <td>-0.38</td>\n",
       "      <td>20190925</td>\n",
       "      <td>1,672.65</td>\n",
       "      <td>1,686.19</td>\n",
       "      <td>1,689.23</td>\n",
       "      <td>1,670.68</td>\n",
       "      <td>2.22B</td>\n",
       "      <td>-1.30</td>\n",
       "      <td>-0.005162</td>\n",
       "      <td>-0.041207</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2,940.08</td>\n",
       "      <td>2,936.95</td>\n",
       "      <td>2,956.29</td>\n",
       "      <td>2,929.60</td>\n",
       "      <td>2.27B</td>\n",
       "      <td>0.31</td>\n",
       "      <td>20190924</td>\n",
       "      <td>1,694.60</td>\n",
       "      <td>1,685.12</td>\n",
       "      <td>1,706.82</td>\n",
       "      <td>1,678.18</td>\n",
       "      <td>2.28B</td>\n",
       "      <td>0.61</td>\n",
       "      <td>0.005044</td>\n",
       "      <td>-0.002203</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      收盘_sz     开盘_sz      高_sz      低_sz 交易量_sz  涨跌幅_sz      date     收盘_cy  \\\n",
       "0  2,897.70  2,917.07  2,933.51  2,897.68  1.92B   -1.08  20190930  1,627.55   \n",
       "1  2,929.47  2,929.32  2,934.33  2,916.07  2.30B    0.05  20190927  1,647.53   \n",
       "2  2,927.88  2,939.68  2,952.87  2,926.54  3.15B   -0.03  20190926  1,623.79   \n",
       "3  2,928.84  2,930.14  2,942.00  2,923.15  2.25B   -0.38  20190925  1,672.65   \n",
       "4  2,940.08  2,936.95  2,956.29  2,929.60  2.27B    0.31  20190924  1,694.60   \n",
       "\n",
       "      开盘_cy      高_cy      低_cy 交易量_cy  涨跌幅_cy       sen       lhb  \n",
       "0  1,648.90  1,651.87  1,626.05  1.68B   -1.21 -0.049456 -0.030211  \n",
       "1  1,628.14  1,653.06  1,624.75  1.92B    1.46 -0.029732 -0.036020  \n",
       "2  1,677.79  1,680.93  1,623.65  2.21B   -2.92 -0.068419 -0.098260  \n",
       "3  1,686.19  1,689.23  1,670.68  2.22B   -1.30 -0.005162 -0.041207  \n",
       "4  1,685.12  1,706.82  1,678.18  2.28B    0.61  0.005044 -0.002203  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = pd.merge(result, sent, on='date')\n",
    "result.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-03-06T01:22:30.403294Z",
     "start_time": "2021-03-06T01:22:30.387294Z"
    },
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 257
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 766,
     "status": "ok",
     "timestamp": 1573200475829,
     "user": {
      "displayName": "Soung Low",
      "photoUrl": "",
      "userId": "00570526448316647912"
     },
     "user_tz": -480
    },
    "id": "FfTJEeq3EH3j",
    "outputId": "3a396526-2dc2-41b7-e0cb-23c1bcfd0bca"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>收盘_sz</th>\n",
       "      <th>开盘_sz</th>\n",
       "      <th>高_sz</th>\n",
       "      <th>低_sz</th>\n",
       "      <th>交易量_sz</th>\n",
       "      <th>涨跌幅_sz</th>\n",
       "      <th>date</th>\n",
       "      <th>收盘_cy</th>\n",
       "      <th>开盘_cy</th>\n",
       "      <th>高_cy</th>\n",
       "      <th>低_cy</th>\n",
       "      <th>交易量_cy</th>\n",
       "      <th>涨跌幅_cy</th>\n",
       "      <th>sen</th>\n",
       "      <th>lhb</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2,931.88</td>\n",
       "      <td>2,915.97</td>\n",
       "      <td>2,936.85</td>\n",
       "      <td>2,900.87</td>\n",
       "      <td>3.93B</td>\n",
       "      <td>2.09</td>\n",
       "      <td>20180222</td>\n",
       "      <td>1,677.76</td>\n",
       "      <td>1,661.56</td>\n",
       "      <td>1,679.79</td>\n",
       "      <td>1,650.88</td>\n",
       "      <td>1.40B</td>\n",
       "      <td>1.88</td>\n",
       "      <td>0.016575</td>\n",
       "      <td>0.054851</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2,957.17</td>\n",
       "      <td>2,941.42</td>\n",
       "      <td>2,966.75</td>\n",
       "      <td>2,926.36</td>\n",
       "      <td>3.82B</td>\n",
       "      <td>0.86</td>\n",
       "      <td>20180223</td>\n",
       "      <td>1,668.83</td>\n",
       "      <td>1,674.62</td>\n",
       "      <td>1,681.24</td>\n",
       "      <td>1,656.95</td>\n",
       "      <td>1.25B</td>\n",
       "      <td>-0.53</td>\n",
       "      <td>-0.019798</td>\n",
       "      <td>0.047175</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2,973.79</td>\n",
       "      <td>2,973.81</td>\n",
       "      <td>2,986.81</td>\n",
       "      <td>2,933.52</td>\n",
       "      <td>5.02B</td>\n",
       "      <td>0.56</td>\n",
       "      <td>20180226</td>\n",
       "      <td>1,729.15</td>\n",
       "      <td>1,683.15</td>\n",
       "      <td>1,733.71</td>\n",
       "      <td>1,673.95</td>\n",
       "      <td>1.99B</td>\n",
       "      <td>3.61</td>\n",
       "      <td>0.044863</td>\n",
       "      <td>0.097175</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2,927.10</td>\n",
       "      <td>2,979.77</td>\n",
       "      <td>2,979.77</td>\n",
       "      <td>2,918.01</td>\n",
       "      <td>4.62B</td>\n",
       "      <td>-1.57</td>\n",
       "      <td>20180227</td>\n",
       "      <td>1,743.54</td>\n",
       "      <td>1,729.94</td>\n",
       "      <td>1,757.37</td>\n",
       "      <td>1,728.31</td>\n",
       "      <td>2.28B</td>\n",
       "      <td>0.83</td>\n",
       "      <td>0.001990</td>\n",
       "      <td>0.037009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2,878.67</td>\n",
       "      <td>2,899.00</td>\n",
       "      <td>2,905.57</td>\n",
       "      <td>2,866.46</td>\n",
       "      <td>3.88B</td>\n",
       "      <td>-1.65</td>\n",
       "      <td>20180228</td>\n",
       "      <td>1,753.63</td>\n",
       "      <td>1,730.15</td>\n",
       "      <td>1,773.73</td>\n",
       "      <td>1,723.39</td>\n",
       "      <td>2.16B</td>\n",
       "      <td>0.58</td>\n",
       "      <td>-0.022372</td>\n",
       "      <td>-0.042866</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      收盘_sz     开盘_sz      高_sz      低_sz 交易量_sz  涨跌幅_sz      date     收盘_cy  \\\n",
       "0  2,931.88  2,915.97  2,936.85  2,900.87  3.93B    2.09  20180222  1,677.76   \n",
       "1  2,957.17  2,941.42  2,966.75  2,926.36  3.82B    0.86  20180223  1,668.83   \n",
       "2  2,973.79  2,973.81  2,986.81  2,933.52  5.02B    0.56  20180226  1,729.15   \n",
       "3  2,927.10  2,979.77  2,979.77  2,918.01  4.62B   -1.57  20180227  1,743.54   \n",
       "4  2,878.67  2,899.00  2,905.57  2,866.46  3.88B   -1.65  20180228  1,753.63   \n",
       "\n",
       "      开盘_cy      高_cy      低_cy 交易量_cy  涨跌幅_cy       sen       lhb  \n",
       "0  1,661.56  1,679.79  1,650.88  1.40B    1.88  0.016575  0.054851  \n",
       "1  1,674.62  1,681.24  1,656.95  1.25B   -0.53 -0.019798  0.047175  \n",
       "2  1,683.15  1,733.71  1,673.95  1.99B    3.61  0.044863  0.097175  \n",
       "3  1,729.94  1,757.37  1,728.31  2.28B    0.83  0.001990  0.037009  \n",
       "4  1,730.15  1,773.73  1,723.39  2.16B    0.58 -0.022372 -0.042866  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result.sort_values(by='date', inplace=True)\n",
    "result.reset_index(inplace=True, drop=True)\n",
    "result.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-03-06T01:22:30.414305Z",
     "start_time": "2021-03-06T01:22:30.405294Z"
    },
    "colab": {},
    "colab_type": "code",
    "id": "As3pU9h0DooI"
   },
   "outputs": [],
   "source": [
    "result.to_csv('data/stock_data/data_sz50_cy_lhb_sen.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "DaksCLh73ATI"
   },
   "source": [
    "## Apply EMD on data\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-03-06T01:22:30.420294Z",
     "start_time": "2021-03-06T01:22:30.416295Z"
    },
    "colab": {},
    "colab_type": "code",
    "id": "UUdGTbYm3FDr"
   },
   "outputs": [],
   "source": [
    "def emd(index_col, stock_col, df_name):\n",
    "    '''Apply EMD method on given stock column'''\n",
    "    x = np.array(index_col)\n",
    "    y = np.array(stock_col)\n",
    "    IMF = EMD().emd(y,x)\n",
    "    N = IMF.shape[0]+1\n",
    "\n",
    "    temp = pd.DataFrame(data=IMF.reshape((len(IMF[0]), len(IMF))))\n",
    "    temp.columns = [str(df_name) + '_' + str(i) for i in range(1, len(IMF)+1)]\n",
    "    \n",
    "    return temp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-03-06T01:22:30.484293Z",
     "start_time": "2021-03-06T01:22:30.421294Z"
    },
    "colab": {},
    "colab_type": "code",
    "id": "pVB2m8y4MVP5"
   },
   "outputs": [],
   "source": [
    "# Apply EMD on sz50 and cyb\n",
    "emd_涨跌幅_sz = emd(result.index, result['涨跌幅_sz'], 'emd_涨跌幅_sz')\n",
    "emd_涨跌幅_cy = emd(result.index, result['涨跌幅_cy'], 'emd_涨跌幅_cy')\n",
    "\n",
    "emd_涨跌幅_cy.to_csv('data/stock_data/emd_涨跌幅_cy.csv')\n",
    "emd_涨跌幅_sz.to_csv('data/stock_data/emd_涨跌幅_sz.csv')"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "collapsed_sections": [
    "R4LitNJbogAN",
    "JvwbmbDephLv"
   ],
   "name": "fyp_data_preprocess.ipynb",
   "provenance": []
  },
  "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.8.3"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": true,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}