06_process_data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "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>predict</th>\n",
       "    </tr>\n",
       "    <tr>\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>2015-06-19</th>\n",
       "      <th>601766.SH</th>\n",
       "      <td>中国中车跌跌不休 为国接盘一语成谶</td>\n",
       "      <td>5月7日起中国南车、中国北车停牌并启动换股，6月8日以中国中车之名复牌，复牌当日中车一...</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-08-12</th>\n",
       "      <th>601766.SH</th>\n",
       "      <td>国企改革东风劲吹 为A股市场发展带来新动力</td>\n",
       "      <td>国企改革东风劲吹　　为A股市场发展带来新动力　　8月份以来，随着国企改革顶层设计方案出台...</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-09-01</th>\n",
       "      <th>601766.SH</th>\n",
       "      <td>中国置业投资疑遭中车抛弃 单日暴跌82%</td>\n",
       "      <td>在毫无任何征兆的情况下，中国置业投资(00736.HK)8月31日早盘狂泻81.87%，...</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-09-15</th>\n",
       "      <th>601857.SH</th>\n",
       "      <td>中石油欲加强海外资产监管</td>\n",
       "      <td>中国石油天然气集团公司党组昨日公布《关于巡视整改情况的通报》指出，存在海外投资决策不规范...</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-10-29</th>\n",
       "      <th>600466.SH</th>\n",
       "      <td>蓝光发展发布全球首创3D生物血管打印机 器官再造成为可能</td>\n",
       "      <td>欧洲发明家Charles Hull在1983年研发出第一台3D印刷机时，他也许很难想到，...</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">2022-03-19</th>\n",
       "      <th>688223.SH</th>\n",
       "      <td>208亿！晶科能源签订光伏项目投资协议</td>\n",
       "      <td>晶科能源3月18日晚间发布公告，根据战略发展需求，公司与青海省工业和信息化厅、西宁市人民...</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>688223.SH</th>\n",
       "      <td>豪气！千亿光伏组件公司宣布 拟合计208亿元投建两个光伏项目 回应新电池项目传闻</td>\n",
       "      <td>光伏组件企业晶科能源(&lt;em&gt;688223&lt;/em&gt;)拟斥资208亿元投建新项目。晶科能...</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>688298.SH</th>\n",
       "      <td>新冠抗原居家自测需求待启</td>\n",
       "      <td>“抗原检测应用于高风险、高流行率的聚集性感染人群，一般人群不要随意做抗原检测。”\\r\\n...</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>688508.SH</th>\n",
       "      <td>芯朋微2021年营收净利双增长 拟每10股派现6元</td>\n",
       "      <td>芯朋微(&lt;em&gt;688508&lt;/em&gt;.SH)3月18日公告，2021年，公司实现营业收...</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>830799.BJ</th>\n",
       "      <td>北交所首个高比例减持计划出炉 艾融软件大股东拟减持不超过11%公司股份</td>\n",
       "      <td>艾融软件(&lt;em&gt;830799&lt;/em&gt;.BJ)3月17日晚间发布公告，股东孟庆有因个人...</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>591113 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                          文章标题  \\\n",
       "时间         股票代码                                                  \n",
       "2015-06-19 601766.SH                         中国中车跌跌不休 为国接盘一语成谶   \n",
       "2015-08-12 601766.SH                     国企改革东风劲吹 为A股市场发展带来新动力   \n",
       "2015-09-01 601766.SH                      中国置业投资疑遭中车抛弃 单日暴跌82%   \n",
       "2015-09-15 601857.SH                              中石油欲加强海外资产监管   \n",
       "2015-10-29 600466.SH              蓝光发展发布全球首创3D生物血管打印机 器官再造成为可能   \n",
       "...                                                        ...   \n",
       "2022-03-19 688223.SH                       208亿！晶科能源签订光伏项目投资协议   \n",
       "           688223.SH  豪气！千亿光伏组件公司宣布 拟合计208亿元投建两个光伏项目 回应新电池项目传闻   \n",
       "           688298.SH                              新冠抗原居家自测需求待启   \n",
       "           688508.SH                 芯朋微2021年营收净利双增长 拟每10股派现6元   \n",
       "           830799.BJ       北交所首个高比例减持计划出炉 艾融软件大股东拟减持不超过11%公司股份   \n",
       "\n",
       "                                                                   文章详情  \\\n",
       "时间         股票代码                                                           \n",
       "2015-06-19 601766.SH   　　5月7日起中国南车、中国北车停牌并启动换股，6月8日以中国中车之名复牌，复牌当日中车一...   \n",
       "2015-08-12 601766.SH  　　国企改革东风劲吹　　为A股市场发展带来新动力　　8月份以来，随着国企改革顶层设计方案出台...   \n",
       "2015-09-01 601766.SH  　　在毫无任何征兆的情况下，中国置业投资(00736.HK)8月31日早盘狂泻81.87%，...   \n",
       "2015-09-15 601857.SH  　　中国石油天然气集团公司党组昨日公布《关于巡视整改情况的通报》指出，存在海外投资决策不规范...   \n",
       "2015-10-29 600466.SH  　　欧洲发明家Charles Hull在1983年研发出第一台3D印刷机时，他也许很难想到，...   \n",
       "...                                                                 ...   \n",
       "2022-03-19 688223.SH  　　晶科能源3月18日晚间发布公告，根据战略发展需求，公司与青海省工业和信息化厅、西宁市人民...   \n",
       "           688223.SH  　　光伏组件企业晶科能源(<em>688223</em>)拟斥资208亿元投建新项目。晶科能...   \n",
       "           688298.SH  　　“抗原检测应用于高风险、高流行率的聚集性感染人群，一般人群不要随意做抗原检测。”\\r\\n...   \n",
       "           688508.SH  　　芯朋微(<em>688508</em>.SH)3月18日公告，2021年，公司实现营业收...   \n",
       "           830799.BJ  　　艾融软件(<em>830799</em>.BJ)3月17日晚间发布公告，股东孟庆有因个人...   \n",
       "\n",
       "                      predict  \n",
       "时间         股票代码                \n",
       "2015-06-19 601766.SH      0.0  \n",
       "2015-08-12 601766.SH      2.0  \n",
       "2015-09-01 601766.SH      0.0  \n",
       "2015-09-15 601857.SH      0.0  \n",
       "2015-10-29 600466.SH      2.0  \n",
       "...                       ...  \n",
       "2022-03-19 688223.SH      2.0  \n",
       "           688223.SH      2.0  \n",
       "           688298.SH      2.0  \n",
       "           688508.SH      2.0  \n",
       "           830799.BJ      0.0  \n",
       "\n",
       "[591113 rows x 3 columns]"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv(\"result.csv\", encoding=\"gbk\")\n",
    "df[\"时间\"] = pd.to_datetime(df[\"时间\"])\n",
    "df.set_index([\"时间\", \"股票代码\"], inplace=True)\n",
    "df.sort_index(inplace=True)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['601766.SH', '601857.SH', '600466.SH', '600581.SH', '002157.SZ',\n",
       "       '002714.SZ', '300773.SZ', '300676.SZ', '000333.SZ', '600690.SH',\n",
       "       ...\n",
       "       '688236.SH', '833346.BJ', '001308.SZ', '688261.SH', '301116.SZ',\n",
       "       '831305.BJ', '688171.SH', '001289.SZ', '001313.SZ', '688163.SH'],\n",
       "      dtype='object', name='股票代码', length=4752)"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.index.get_level_values(1).unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "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>time</th>\n",
       "      <th>code</th>\n",
       "      <th>close</th>\n",
       "      <th>pre_close</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015-01-05</td>\n",
       "      <td>000001.XSHE</td>\n",
       "      <td>1239.48</td>\n",
       "      <td>1225.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015-01-06</td>\n",
       "      <td>000001.XSHE</td>\n",
       "      <td>1220.91</td>\n",
       "      <td>1239.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015-01-07</td>\n",
       "      <td>000001.XSHE</td>\n",
       "      <td>1197.70</td>\n",
       "      <td>1220.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015-01-08</td>\n",
       "      <td>000001.XSHE</td>\n",
       "      <td>1157.47</td>\n",
       "      <td>1197.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015-01-09</td>\n",
       "      <td>000001.XSHE</td>\n",
       "      <td>1166.75</td>\n",
       "      <td>1157.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6809893</th>\n",
       "      <td>2022-12-26</td>\n",
       "      <td>689009.XSHG</td>\n",
       "      <td>32.25</td>\n",
       "      <td>32.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6809894</th>\n",
       "      <td>2022-12-27</td>\n",
       "      <td>689009.XSHG</td>\n",
       "      <td>32.79</td>\n",
       "      <td>32.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6809895</th>\n",
       "      <td>2022-12-28</td>\n",
       "      <td>689009.XSHG</td>\n",
       "      <td>32.16</td>\n",
       "      <td>32.79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6809896</th>\n",
       "      <td>2022-12-29</td>\n",
       "      <td>689009.XSHG</td>\n",
       "      <td>30.84</td>\n",
       "      <td>32.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6809897</th>\n",
       "      <td>2022-12-30</td>\n",
       "      <td>689009.XSHG</td>\n",
       "      <td>30.49</td>\n",
       "      <td>30.84</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6809898 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               time         code    close  pre_close\n",
       "0        2015-01-05  000001.XSHE  1239.48    1225.56\n",
       "1        2015-01-06  000001.XSHE  1220.91    1239.48\n",
       "2        2015-01-07  000001.XSHE  1197.70    1220.91\n",
       "3        2015-01-08  000001.XSHE  1157.47    1197.70\n",
       "4        2015-01-09  000001.XSHE  1166.75    1157.47\n",
       "...             ...          ...      ...        ...\n",
       "6809893  2022-12-26  689009.XSHG    32.25      32.33\n",
       "6809894  2022-12-27  689009.XSHG    32.79      32.25\n",
       "6809895  2022-12-28  689009.XSHG    32.16      32.79\n",
       "6809896  2022-12-29  689009.XSHG    30.84      32.16\n",
       "6809897  2022-12-30  689009.XSHG    30.49      30.84\n",
       "\n",
       "[6809898 rows x 4 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "price_data = pd.read_csv('tot2.csv')\n",
    "price_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "将股票代码整理成更通用的格式"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "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>time</th>\n",
       "      <th>code</th>\n",
       "      <th>close</th>\n",
       "      <th>pre_close</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015-01-05</td>\n",
       "      <td>000001.SZ</td>\n",
       "      <td>1239.48</td>\n",
       "      <td>1225.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015-01-06</td>\n",
       "      <td>000001.SZ</td>\n",
       "      <td>1220.91</td>\n",
       "      <td>1239.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015-01-07</td>\n",
       "      <td>000001.SZ</td>\n",
       "      <td>1197.70</td>\n",
       "      <td>1220.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015-01-08</td>\n",
       "      <td>000001.SZ</td>\n",
       "      <td>1157.47</td>\n",
       "      <td>1197.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015-01-09</td>\n",
       "      <td>000001.SZ</td>\n",
       "      <td>1166.75</td>\n",
       "      <td>1157.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6809893</th>\n",
       "      <td>2022-12-26</td>\n",
       "      <td>689009.SH</td>\n",
       "      <td>32.25</td>\n",
       "      <td>32.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6809894</th>\n",
       "      <td>2022-12-27</td>\n",
       "      <td>689009.SH</td>\n",
       "      <td>32.79</td>\n",
       "      <td>32.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6809895</th>\n",
       "      <td>2022-12-28</td>\n",
       "      <td>689009.SH</td>\n",
       "      <td>32.16</td>\n",
       "      <td>32.79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6809896</th>\n",
       "      <td>2022-12-29</td>\n",
       "      <td>689009.SH</td>\n",
       "      <td>30.84</td>\n",
       "      <td>32.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6809897</th>\n",
       "      <td>2022-12-30</td>\n",
       "      <td>689009.SH</td>\n",
       "      <td>30.49</td>\n",
       "      <td>30.84</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6809898 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               time       code    close  pre_close\n",
       "0        2015-01-05  000001.SZ  1239.48    1225.56\n",
       "1        2015-01-06  000001.SZ  1220.91    1239.48\n",
       "2        2015-01-07  000001.SZ  1197.70    1220.91\n",
       "3        2015-01-08  000001.SZ  1157.47    1197.70\n",
       "4        2015-01-09  000001.SZ  1166.75    1157.47\n",
       "...             ...        ...      ...        ...\n",
       "6809893  2022-12-26  689009.SH    32.25      32.33\n",
       "6809894  2022-12-27  689009.SH    32.79      32.25\n",
       "6809895  2022-12-28  689009.SH    32.16      32.79\n",
       "6809896  2022-12-29  689009.SH    30.84      32.16\n",
       "6809897  2022-12-30  689009.SH    30.49      30.84\n",
       "\n",
       "[6809898 rows x 4 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "price_data[\"code\"] = price_data[\"code\"].str.replace(\"XSHE\", \"SZ\")\n",
    "price_data[\"code\"] = price_data[\"code\"].str.replace(\"XSHG\", \"SH\")\n",
    "price_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "price_data[\"time\"] = pd.to_datetime(price_data[\"time\"])\n",
    "#price_data = price_data.set_index([\"time\", \"code\"]).sort_index()\n",
    "#price_data[\"close\"].groupby(\"time\").count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "每天的新闻数据(条)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "时间\n",
       "2015-06-19       1\n",
       "2015-08-12       1\n",
       "2015-09-01       1\n",
       "2015-09-15       1\n",
       "2015-10-29       1\n",
       "              ... \n",
       "2022-03-15    1461\n",
       "2022-03-16    1690\n",
       "2022-03-17    1832\n",
       "2022-03-18    1804\n",
       "2022-03-19     277\n",
       "Name: predict, Length: 1886, dtype: int64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"predict\"].groupby(\"时间\").count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "时间\n",
       "2015-06-19       1\n",
       "2015-08-12       1\n",
       "2015-09-01       1\n",
       "2015-09-15       1\n",
       "2015-10-29       1\n",
       "              ... \n",
       "2022-03-15    1461\n",
       "2022-03-16    1682\n",
       "2022-03-17    1823\n",
       "2022-03-18    1802\n",
       "2022-03-19     276\n",
       "Name: predict, Length: 1886, dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df[df.index.get_level_values(1).isin(price_data[\"code\"])]\n",
    "df[\"predict\"].groupby(\"时间\").count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DatetimeIndex(['2015-06-19', '2015-08-12', '2015-09-01', '2015-09-15',\n",
       "               '2015-10-29', '2016-01-30', '2016-01-31', '2016-02-02',\n",
       "               '2016-03-18', '2016-08-22',\n",
       "               ...\n",
       "               '2021-10-05', '2021-10-06', '2021-11-20', '2022-01-02',\n",
       "               '2022-01-31', '2022-02-01', '2022-02-02', '2022-02-03',\n",
       "               '2022-02-04', '2022-02-05'],\n",
       "              dtype='datetime64[ns]', name='时间', length=454, freq=None)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "count = pd.DataFrame(df[\"predict\"].groupby(\"时间\").count())\n",
    "t_delete = count[count.values < 50].index.get_level_values(0)\n",
    "t_delete"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "时间\n",
       "2017-01-10      50\n",
       "2017-01-16      51\n",
       "2017-01-20      53\n",
       "2017-01-24      68\n",
       "2017-04-06      57\n",
       "              ... \n",
       "2022-03-15    1461\n",
       "2022-03-16    1682\n",
       "2022-03-17    1823\n",
       "2022-03-18    1802\n",
       "2022-03-19     276\n",
       "Name: predict, Length: 1432, dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df[~df.index.get_level_values(0).isin(t_delete.values)]\n",
    "df[\"predict\"].groupby(\"时间\").count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "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>predict</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>1432.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>1.236456</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>0.137464</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.700000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>1.149254</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>1.239148</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>1.327611</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>1.711538</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           predict\n",
       "count  1432.000000\n",
       "mean      1.236456\n",
       "std       0.137464\n",
       "min       0.700000\n",
       "25%       1.149254\n",
       "50%       1.239148\n",
       "75%       1.327611\n",
       "max       1.711538"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sentiment_index = pd.DataFrame(df[\"predict\"].groupby(\"时间\").mean())\n",
    "sentiment_index.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DatetimeIndex(['2017-01-10', '2017-01-16', '2017-01-20', '2017-01-24',\n",
       "               '2017-04-06', '2017-04-07', '2017-04-11', '2017-04-12',\n",
       "               '2017-04-14', '2017-05-26',\n",
       "               ...\n",
       "               '2022-03-10', '2022-03-11', '2022-03-12', '2022-03-13',\n",
       "               '2022-03-14', '2022-03-15', '2022-03-16', '2022-03-17',\n",
       "               '2022-03-18', '2022-03-19'],\n",
       "              dtype='datetime64[ns]', name='time', length=1432, freq=None)"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sentiment_index.index.names = [\"time\"]\n",
    "sentiment_index.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "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>time</th>\n",
       "      <th>code</th>\n",
       "      <th>close</th>\n",
       "      <th>pre_close</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>493</th>\n",
       "      <td>2017-01-10</td>\n",
       "      <td>000001.SZ</td>\n",
       "      <td>1043.67</td>\n",
       "      <td>1043.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>497</th>\n",
       "      <td>2017-01-16</td>\n",
       "      <td>000001.SZ</td>\n",
       "      <td>1042.53</td>\n",
       "      <td>1044.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>501</th>\n",
       "      <td>2017-01-20</td>\n",
       "      <td>000001.SZ</td>\n",
       "      <td>1051.65</td>\n",
       "      <td>1047.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>503</th>\n",
       "      <td>2017-01-24</td>\n",
       "      <td>000001.SZ</td>\n",
       "      <td>1057.35</td>\n",
       "      <td>1051.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>548</th>\n",
       "      <td>2017-04-06</td>\n",
       "      <td>000001.SZ</td>\n",
       "      <td>1049.37</td>\n",
       "      <td>1050.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6809700</th>\n",
       "      <td>2022-03-14</td>\n",
       "      <td>689009.SH</td>\n",
       "      <td>43.83</td>\n",
       "      <td>46.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6809701</th>\n",
       "      <td>2022-03-15</td>\n",
       "      <td>689009.SH</td>\n",
       "      <td>40.88</td>\n",
       "      <td>43.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6809702</th>\n",
       "      <td>2022-03-16</td>\n",
       "      <td>689009.SH</td>\n",
       "      <td>42.03</td>\n",
       "      <td>40.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6809703</th>\n",
       "      <td>2022-03-17</td>\n",
       "      <td>689009.SH</td>\n",
       "      <td>43.62</td>\n",
       "      <td>42.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6809704</th>\n",
       "      <td>2022-03-18</td>\n",
       "      <td>689009.SH</td>\n",
       "      <td>43.70</td>\n",
       "      <td>43.62</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4177032 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              time       code    close  pre_close\n",
       "493     2017-01-10  000001.SZ  1043.67    1043.67\n",
       "497     2017-01-16  000001.SZ  1042.53    1044.81\n",
       "501     2017-01-20  000001.SZ  1051.65    1047.09\n",
       "503     2017-01-24  000001.SZ  1057.35    1051.65\n",
       "548     2017-04-06  000001.SZ  1049.37    1050.51\n",
       "...            ...        ...      ...        ...\n",
       "6809700 2022-03-14  689009.SH    43.83      46.75\n",
       "6809701 2022-03-15  689009.SH    40.88      43.83\n",
       "6809702 2022-03-16  689009.SH    42.03      40.88\n",
       "6809703 2022-03-17  689009.SH    43.62      42.03\n",
       "6809704 2022-03-18  689009.SH    43.70      43.62\n",
       "\n",
       "[4177032 rows x 4 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "price_data = price_data[price_data[\"time\"].isin(sentiment_index.index.unique().values)]\n",
    "price_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "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>close</th>\n",
       "      <th>pre_close</th>\n",
       "      <th>predict</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>time</th>\n",
       "      <th>code</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">2017-01-10</th>\n",
       "      <th>000001.SZ</th>\n",
       "      <td>1043.67</td>\n",
       "      <td>1043.67</td>\n",
       "      <td>1.180000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000002.SZ</th>\n",
       "      <td>2539.39</td>\n",
       "      <td>2549.26</td>\n",
       "      <td>1.180000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000004.SZ</th>\n",
       "      <td>322.04</td>\n",
       "      <td>320.25</td>\n",
       "      <td>1.180000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000005.SZ</th>\n",
       "      <td>71.66</td>\n",
       "      <td>72.28</td>\n",
       "      <td>1.180000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000006.SZ</th>\n",
       "      <td>412.85</td>\n",
       "      <td>420.88</td>\n",
       "      <td>1.180000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">2022-03-18</th>\n",
       "      <th>688799.SH</th>\n",
       "      <td>37.28</td>\n",
       "      <td>36.50</td>\n",
       "      <td>1.404878</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>688800.SH</th>\n",
       "      <td>100.08</td>\n",
       "      <td>102.21</td>\n",
       "      <td>1.404878</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>688819.SH</th>\n",
       "      <td>32.42</td>\n",
       "      <td>32.22</td>\n",
       "      <td>1.404878</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>688981.SH</th>\n",
       "      <td>46.80</td>\n",
       "      <td>46.49</td>\n",
       "      <td>1.404878</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>689009.SH</th>\n",
       "      <td>43.70</td>\n",
       "      <td>43.62</td>\n",
       "      <td>1.404878</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4177032 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        close  pre_close   predict\n",
       "time       code                                   \n",
       "2017-01-10 000001.SZ  1043.67    1043.67  1.180000\n",
       "           000002.SZ  2539.39    2549.26  1.180000\n",
       "           000004.SZ   322.04     320.25  1.180000\n",
       "           000005.SZ    71.66      72.28  1.180000\n",
       "           000006.SZ   412.85     420.88  1.180000\n",
       "...                       ...        ...       ...\n",
       "2022-03-18 688799.SH    37.28      36.50  1.404878\n",
       "           688800.SH   100.08     102.21  1.404878\n",
       "           688819.SH    32.42      32.22  1.404878\n",
       "           688981.SH    46.80      46.49  1.404878\n",
       "           689009.SH    43.70      43.62  1.404878\n",
       "\n",
       "[4177032 rows x 3 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import scutquant.scutquant as q\n",
    "\n",
    "price_data = price_data.set_index([\"time\", \"code\"]).sort_index()\n",
    "data = q.join_data(price_data, sentiment_index, time=\"time\", col=[\"predict\"],index=[\"time\", \"code\"])\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "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>close</th>\n",
       "      <th>pre_close</th>\n",
       "      <th>predict</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>4.177032e+06</td>\n",
       "      <td>4.177032e+06</td>\n",
       "      <td>4.177032e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>1.053237e+02</td>\n",
       "      <td>1.053262e+02</td>\n",
       "      <td>1.244550e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>7.369390e+02</td>\n",
       "      <td>7.376773e+02</td>\n",
       "      <td>1.300715e-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>3.700000e-01</td>\n",
       "      <td>3.700000e-01</td>\n",
       "      <td>7.000000e-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>2.023000e+01</td>\n",
       "      <td>2.022000e+01</td>\n",
       "      <td>1.172840e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>3.573000e+01</td>\n",
       "      <td>3.573000e+01</td>\n",
       "      <td>1.245524e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>6.633000e+01</td>\n",
       "      <td>6.633000e+01</td>\n",
       "      <td>1.325744e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>6.779995e+04</td>\n",
       "      <td>6.779995e+04</td>\n",
       "      <td>1.711538e+00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              close     pre_close       predict\n",
       "count  4.177032e+06  4.177032e+06  4.177032e+06\n",
       "mean   1.053237e+02  1.053262e+02  1.244550e+00\n",
       "std    7.369390e+02  7.376773e+02  1.300715e-01\n",
       "min    3.700000e-01  3.700000e-01  7.000000e-01\n",
       "25%    2.023000e+01  2.022000e+01  1.172840e+00\n",
       "50%    3.573000e+01  3.573000e+01  1.245524e+00\n",
       "75%    6.633000e+01  6.633000e+01  1.325744e+00\n",
       "max    6.779995e+04  6.779995e+04  1.711538e+00"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.describe().to_csv(\"D:/Desktop/describe.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "code\n",
       "000001.SZ    1100\n",
       "000002.SZ    1100\n",
       "000004.SZ    1074\n",
       "000005.SZ    1099\n",
       "000006.SZ     990\n",
       "             ... \n",
       "688799.SH     166\n",
       "688800.SH     159\n",
       "688819.SH     282\n",
       "688981.SH     407\n",
       "689009.SH     338\n",
       "Name: close, Length: 4724, dtype: int64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.sort_index(inplace=True)\n",
    "data[\"close\"].groupby(\"code\").count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "time\n",
       "2017-01-10    2849\n",
       "2017-01-16    2856\n",
       "2017-01-20    2876\n",
       "2017-01-24    2885\n",
       "2017-04-06    2942\n",
       "              ... \n",
       "2022-03-14    4649\n",
       "2022-03-15    4653\n",
       "2022-03-16    4654\n",
       "2022-03-17    4656\n",
       "2022-03-18    4658\n",
       "Name: close, Length: 1100, dtype: int64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[\"close\"].groupby(\"time\").count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "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>close</th>\n",
       "      <th>pre_close</th>\n",
       "      <th>predict</th>\n",
       "      <th>ret</th>\n",
       "      <th>index</th>\n",
       "      <th>label</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>time</th>\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 rowspan=\"5\" valign=\"top\">2017-01-10</th>\n",
       "      <th>000001.SZ</th>\n",
       "      <td>1043.67</td>\n",
       "      <td>1043.67</td>\n",
       "      <td>1.180000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-0.000000</td>\n",
       "      <td>0.004355</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000002.SZ</th>\n",
       "      <td>2539.39</td>\n",
       "      <td>2549.26</td>\n",
       "      <td>1.180000</td>\n",
       "      <td>-0.003872</td>\n",
       "      <td>1.926607</td>\n",
       "      <td>0.003887</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000004.SZ</th>\n",
       "      <td>322.04</td>\n",
       "      <td>320.25</td>\n",
       "      <td>1.180000</td>\n",
       "      <td>0.005589</td>\n",
       "      <td>-2.781340</td>\n",
       "      <td>0.022126</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000005.SZ</th>\n",
       "      <td>71.66</td>\n",
       "      <td>72.28</td>\n",
       "      <td>1.180000</td>\n",
       "      <td>-0.008578</td>\n",
       "      <td>4.268387</td>\n",
       "      <td>0.009265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000006.SZ</th>\n",
       "      <td>412.85</td>\n",
       "      <td>420.88</td>\n",
       "      <td>1.180000</td>\n",
       "      <td>-0.019079</td>\n",
       "      <td>9.493962</td>\n",
       "      <td>0.010659</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\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 rowspan=\"5\" valign=\"top\">2022-03-16</th>\n",
       "      <th>688799.SH</th>\n",
       "      <td>35.35</td>\n",
       "      <td>35.00</td>\n",
       "      <td>1.410053</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.331179</td>\n",
       "      <td>0.021370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>688800.SH</th>\n",
       "      <td>97.66</td>\n",
       "      <td>89.40</td>\n",
       "      <td>1.410053</td>\n",
       "      <td>0.092394</td>\n",
       "      <td>3.059890</td>\n",
       "      <td>-0.020839</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>688819.SH</th>\n",
       "      <td>31.88</td>\n",
       "      <td>30.95</td>\n",
       "      <td>1.410053</td>\n",
       "      <td>0.030048</td>\n",
       "      <td>0.995143</td>\n",
       "      <td>0.006207</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>688981.SH</th>\n",
       "      <td>46.28</td>\n",
       "      <td>45.29</td>\n",
       "      <td>1.410053</td>\n",
       "      <td>0.021859</td>\n",
       "      <td>0.723929</td>\n",
       "      <td>0.006668</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>689009.SH</th>\n",
       "      <td>42.03</td>\n",
       "      <td>40.88</td>\n",
       "      <td>1.410053</td>\n",
       "      <td>0.028131</td>\n",
       "      <td>0.931645</td>\n",
       "      <td>0.001834</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4167587 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        close  pre_close   predict       ret     index  \\\n",
       "time       code                                                          \n",
       "2017-01-10 000001.SZ  1043.67    1043.67  1.180000  0.000000 -0.000000   \n",
       "           000002.SZ  2539.39    2549.26  1.180000 -0.003872  1.926607   \n",
       "           000004.SZ   322.04     320.25  1.180000  0.005589 -2.781340   \n",
       "           000005.SZ    71.66      72.28  1.180000 -0.008578  4.268387   \n",
       "           000006.SZ   412.85     420.88  1.180000 -0.019079  9.493962   \n",
       "...                       ...        ...       ...       ...       ...   \n",
       "2022-03-16 688799.SH    35.35      35.00  1.410053  0.010000  0.331179   \n",
       "           688800.SH    97.66      89.40  1.410053  0.092394  3.059890   \n",
       "           688819.SH    31.88      30.95  1.410053  0.030048  0.995143   \n",
       "           688981.SH    46.28      45.29  1.410053  0.021859  0.723929   \n",
       "           689009.SH    42.03      40.88  1.410053  0.028131  0.931645   \n",
       "\n",
       "                         label  \n",
       "time       code                 \n",
       "2017-01-10 000001.SZ  0.004355  \n",
       "           000002.SZ  0.003887  \n",
       "           000004.SZ  0.022126  \n",
       "           000005.SZ  0.009265  \n",
       "           000006.SZ  0.010659  \n",
       "...                        ...  \n",
       "2022-03-16 688799.SH  0.021370  \n",
       "           688800.SH -0.020839  \n",
       "           688819.SH  0.006207  \n",
       "           688981.SH  0.006668  \n",
       "           689009.SH  0.001834  \n",
       "\n",
       "[4167587 rows x 6 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[\"ret\"] = data[\"close\"] / data[\"pre_close\"] - 1\n",
    "data[\"ret\"].clip(-0.1, 0.1, inplace=True)\n",
    "data[\"index\"] = data[\"ret\"] / data[\"ret\"].groupby(\"time\").mean() # 个股收益与大盘收益之比, 衡量个股相对大盘的强弱\n",
    "data[\"label\"] = data[\"ret\"].groupby(\"code\").shift(-2)\n",
    "data[\"label\"].clip(-0.1, 0.1, inplace=True)\n",
    "data.dropna(inplace=True)\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "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>close</th>\n",
       "      <th>pre_close</th>\n",
       "      <th>predict</th>\n",
       "      <th>ret</th>\n",
       "      <th>index</th>\n",
       "      <th>label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>close</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.999631</td>\n",
       "      <td>1.537051e-03</td>\n",
       "      <td>0.002061</td>\n",
       "      <td>7.185206e-04</td>\n",
       "      <td>-0.001894</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pre_close</th>\n",
       "      <td>0.999631</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.527882e-03</td>\n",
       "      <td>-0.002011</td>\n",
       "      <td>6.163126e-04</td>\n",
       "      <td>-0.001924</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>predict</th>\n",
       "      <td>0.001537</td>\n",
       "      <td>0.001528</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>0.000438</td>\n",
       "      <td>-2.772162e-09</td>\n",
       "      <td>-0.012218</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ret</th>\n",
       "      <td>0.002061</td>\n",
       "      <td>-0.002011</td>\n",
       "      <td>4.377476e-04</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>2.683434e-02</td>\n",
       "      <td>0.016129</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>index</th>\n",
       "      <td>0.000719</td>\n",
       "      <td>0.000616</td>\n",
       "      <td>-2.772162e-09</td>\n",
       "      <td>0.026834</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>0.000620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>label</th>\n",
       "      <td>-0.001894</td>\n",
       "      <td>-0.001924</td>\n",
       "      <td>-1.221813e-02</td>\n",
       "      <td>0.016129</td>\n",
       "      <td>6.201820e-04</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              close  pre_close       predict       ret         index     label\n",
       "close      1.000000   0.999631  1.537051e-03  0.002061  7.185206e-04 -0.001894\n",
       "pre_close  0.999631   1.000000  1.527882e-03 -0.002011  6.163126e-04 -0.001924\n",
       "predict    0.001537   0.001528  1.000000e+00  0.000438 -2.772162e-09 -0.012218\n",
       "ret        0.002061  -0.002011  4.377476e-04  1.000000  2.683434e-02  0.016129\n",
       "index      0.000719   0.000616 -2.772162e-09  0.026834  1.000000e+00  0.000620\n",
       "label     -0.001894  -0.001924 -1.221813e-02  0.016129  6.201820e-04  1.000000"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.corr()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "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>close</th>\n",
       "      <th>pre_close</th>\n",
       "      <th>predict</th>\n",
       "      <th>ret</th>\n",
       "      <th>index</th>\n",
       "      <th>label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>close</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.999255</td>\n",
       "      <td>0.014722</td>\n",
       "      <td>0.011170</td>\n",
       "      <td>0.004320</td>\n",
       "      <td>-0.016819</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pre_close</th>\n",
       "      <td>0.999255</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.014951</td>\n",
       "      <td>-0.017959</td>\n",
       "      <td>0.004631</td>\n",
       "      <td>-0.016577</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>predict</th>\n",
       "      <td>0.014722</td>\n",
       "      <td>0.014951</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>-0.001544</td>\n",
       "      <td>0.000989</td>\n",
       "      <td>-0.019331</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ret</th>\n",
       "      <td>0.011170</td>\n",
       "      <td>-0.017959</td>\n",
       "      <td>-0.001544</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>-0.004667</td>\n",
       "      <td>-0.003720</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>index</th>\n",
       "      <td>0.004320</td>\n",
       "      <td>0.004631</td>\n",
       "      <td>0.000989</td>\n",
       "      <td>-0.004667</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.010756</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>label</th>\n",
       "      <td>-0.016819</td>\n",
       "      <td>-0.016577</td>\n",
       "      <td>-0.019331</td>\n",
       "      <td>-0.003720</td>\n",
       "      <td>0.010756</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              close  pre_close   predict       ret     index     label\n",
       "close      1.000000   0.999255  0.014722  0.011170  0.004320 -0.016819\n",
       "pre_close  0.999255   1.000000  0.014951 -0.017959  0.004631 -0.016577\n",
       "predict    0.014722   0.014951  1.000000 -0.001544  0.000989 -0.019331\n",
       "ret        0.011170  -0.017959 -0.001544  1.000000 -0.004667 -0.003720\n",
       "index      0.004320   0.004631  0.000989 -0.004667  1.000000  0.010756\n",
       "label     -0.016819  -0.016577 -0.019331 -0.003720  0.010756  1.000000"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.corr(method=\"spearman\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pickle\n",
    "\n",
    "with open(\"data.pkl\", \"wb\") as file:\n",
    "    pickle.dump(data, file)\n",
    "    file.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}