数据校验.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-09-02T01:43:44.077753Z",
     "start_time": "2019-09-02T01:43:42.487862Z"
    }
   },
   "outputs": [],
   "source": [
    "from dqt import DBUtils, get_fut_min, get_fut_taq\n",
    "import pandas as pd\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-09-02T01:43:44.819122Z",
     "start_time": "2019-09-02T01:43:44.790394Z"
    }
   },
   "outputs": [],
   "source": [
    "dbu = DBUtils()\n",
    "varieties = ['SP', 'EG']#CY，SN，FU，'SC'\n",
    "varieties = pd.Series(varieties)\n",
    "\n",
    "all_varieties = ['A', 'AG', 'AL', 'AU', 'AP', 'BU', 'B', 'C', 'CF', 'CJ', 'CS', 'CU', 'CY', 'EG', 'FG', 'HC', 'I', 'J',\n",
    "                'JD', 'JM', 'L', 'M', 'MA', 'NI', 'NR', 'OI', 'P', 'PB', 'PP', 'RB', 'RM', 'RU', 'SC', 'SP', 'SR', 'T',\n",
    "                'TA', 'TF', 'TS', 'UR', 'V', 'Y', 'ZC', 'ZN']\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-09-02T01:44:07.715704Z",
     "start_time": "2019-09-02T01:43:45.675378Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "variety:  SP\n",
      "summary_df:               date           \n",
      "            first       last\n",
      "symbol                      \n",
      "SP1906 2018-11-27 2019-04-29\n",
      "SP1909 2019-04-30 2019-08-07\n",
      "SP2001 2019-08-08 2019-08-30\n",
      "variety:  EG\n",
      "summary_df:               date           \n",
      "            first       last\n",
      "symbol                      \n",
      "EG1906 2018-12-10 2019-04-26\n",
      "EG1909 2019-04-29 2019-07-31\n",
      "EG2001 2019-08-01 2019-09-03\n"
     ]
    }
   ],
   "source": [
    "c = list ()\n",
    "for i,variety in varieties.iteritems():\n",
    "    print('variety: ', variety)\n",
    "    df = dbu.get_main_daily(variety=[variety], start_date='2014-01-01')\n",
    "    summary_df = df.groupby('symbol').agg({'date': ['first', 'last']})\n",
    "    print('summary_df: ', summary_df)\n",
    "    sleep(1000)\n",
    "    a = list()\n",
    "    b = list()\n",
    "    for i, contract in summary_df.iterrows():\n",
    "        min_df = get_fut_min(contract.name, str(contract[('date', 'first')].date()), str(contract[('date', 'last')].date()))\n",
    "        taq_df = get_fut_taq(contract.name, str(contract[('date', 'first')].date()), str(contract[('date', 'last')].date()))\n",
    "        print(taq_df.groupby(taq_df.index))\n",
    "        a.append(min_df.groupby('trading_date').count()['datetime'])\n",
    "        b.append(taq_df.groupby(taq_df.index).count()['datetime'])\n",
    "        del min_df\n",
    "        del taq_df\n",
    "\n",
    "    merge_a = pd.concat(a)\n",
    "    merge_b = pd.concat(b)\n",
    "    del a  \n",
    "    del b\n",
    "\n",
    "    c.append({'期货':variety,'taq数据少':merge_b[merge_b == merge_b.describe()['min']], '日数据存在而taq数据缺少':pd.Index(df['date']).difference(merge_b.index),'大于30000条记录':merge_b[merge_b>30000]})\n",
    "\n",
    "c = pd.DataFrame(c)    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                                              taq数据少  \\\n",
      "0  trading_date\n",
      "2019-04-30    11015\n",
      "Name: datetim...   \n",
      "1  trading_date\n",
      "2019-02-18    5569\n",
      "Name: datetime...   \n",
      "\n",
      "                                          大于30000条记录  \\\n",
      "0  trading_date\n",
      "2018-11-29    37984\n",
      "2018-11-30   ...   \n",
      "1  trading_date\n",
      "2019-04-04    32846\n",
      "2019-04-16   ...   \n",
      "\n",
      "                                       日数据存在而taq数据缺少  期货  \n",
      "0  DatetimeIndex([], dtype='datetime64[ns]', name...  SP  \n",
      "1  DatetimeIndex(['2019-09-02'], dtype='datetime6...  EG  \n"
     ]
    }
   ],
   "source": [
    "print(c)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-06-10T02:52:08.137921Z",
     "start_time": "2019-06-10T02:52:08.031374Z"
    }
   },
   "outputs": [],
   "source": [
    "c.to_csv(\"futures.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-09-02T01:46:53.920302Z",
     "start_time": "2019-09-02T01:46:53.879039Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                            datetime    last  volume   turnover     oi  \\\n",
      "trading_date                                                             \n",
      "2018-11-27   2018-11-27 07:05:02.000  5980.0       0          0      0   \n",
      "2018-11-27   2018-11-27 08:59:00.500  5900.0      86    5074000     86   \n",
      "2018-11-27   2018-11-27 09:00:00.500  5900.0      92    5428000     92   \n",
      "2018-11-27   2018-11-27 09:00:01.000  5860.0     100    5896000    100   \n",
      "2018-11-27   2018-11-27 09:00:01.500  5806.0     106    6246480    106   \n",
      "2018-11-27   2018-11-27 09:00:02.000  5856.0     114    6714800    114   \n",
      "2018-11-27   2018-11-27 09:00:02.500  5854.0     124    7298760    124   \n",
      "2018-11-27   2018-11-27 09:00:03.000  5854.0     128    7532920    128   \n",
      "2018-11-27   2018-11-27 09:00:03.500  5854.0     152    8929960    138   \n",
      "2018-11-27   2018-11-27 09:00:04.000  5828.0     160    9395320    146   \n",
      "2018-11-27   2018-11-27 09:00:04.500  5850.0     164    9628880    150   \n",
      "2018-11-27   2018-11-27 09:00:05.000  5806.0     168    9862080    152   \n",
      "2018-11-27   2018-11-27 09:00:05.500  5854.0     170    9979160    152   \n",
      "2018-11-27   2018-11-27 09:00:06.000  5814.0     172   10095440    150   \n",
      "2018-11-27   2018-11-27 09:00:06.500  5814.0     172   10095440    150   \n",
      "2018-11-27   2018-11-27 09:00:07.000  5814.0     172   10095440    150   \n",
      "2018-11-27   2018-11-27 09:00:07.500  5814.0     172   10095440    150   \n",
      "2018-11-27   2018-11-27 09:00:08.000  5818.0     174   10211800    150   \n",
      "2018-11-27   2018-11-27 09:00:08.500  5816.0     178   10444440    152   \n",
      "2018-11-27   2018-11-27 09:00:09.000  5818.0     182   10677160    156   \n",
      "2018-11-27   2018-11-27 09:00:09.500  5818.0     184   10793520    156   \n",
      "2018-11-27   2018-11-27 09:00:10.000  5822.0     194   11376360    150   \n",
      "2018-11-27   2018-11-27 09:00:10.500  5818.0     198   11609080    154   \n",
      "2018-11-27   2018-11-27 09:00:11.000  5820.0     200   11725480    154   \n",
      "2018-11-27   2018-11-27 09:00:11.500  5820.0     200   11725480    154   \n",
      "2018-11-27   2018-11-27 09:00:12.000  5820.0     200   11725480    154   \n",
      "2018-11-27   2018-11-27 09:00:12.500  5814.0     202   11841760    156   \n",
      "2018-11-27   2018-11-27 09:00:13.000  5814.0     204   11958040    158   \n",
      "2018-11-27   2018-11-27 09:00:13.500  5814.0     204   11958040    158   \n",
      "2018-11-27   2018-11-27 09:00:14.000  5816.0     206   12074360    160   \n",
      "...                              ...     ...     ...        ...    ...   \n",
      "2019-08-30   2019-08-30 14:58:43.000  4558.0   11762  539688680  12578   \n",
      "2019-08-30   2019-08-30 14:58:45.000  4558.0   11782  540600280  12578   \n",
      "2019-08-30   2019-08-30 14:58:45.500  4558.0   11782  540600280  12578   \n",
      "2019-08-30   2019-08-30 14:58:48.000  4558.0   11782  540600280  12578   \n",
      "2019-08-30   2019-08-30 14:59:01.500  4558.0   11782  540600280  12578   \n",
      "2019-08-30   2019-08-30 14:59:02.000  4560.0   11788  540873880  12578   \n",
      "2019-08-30   2019-08-30 14:59:02.500  4560.0   11788  540873880  12578   \n",
      "2019-08-30   2019-08-30 14:59:10.000  4556.0   11794  541147280  12574   \n",
      "2019-08-30   2019-08-30 14:59:10.500  4556.0   11808  541785120  12574   \n",
      "2019-08-30   2019-08-30 14:59:11.000  4556.0   11808  541785120  12574   \n",
      "2019-08-30   2019-08-30 14:59:11.500  4556.0   11808  541785120  12574   \n",
      "2019-08-30   2019-08-30 14:59:12.000  4556.0   11808  541785120  12574   \n",
      "2019-08-30   2019-08-30 14:59:12.500  4556.0   11808  541785120  12574   \n",
      "2019-08-30   2019-08-30 14:59:13.000  4556.0   11808  541785120  12574   \n",
      "2019-08-30   2019-08-30 14:59:19.000  4556.0   11808  541785120  12574   \n",
      "2019-08-30   2019-08-30 14:59:24.000  4556.0   11808  541785120  12574   \n",
      "2019-08-30   2019-08-30 14:59:29.000  4556.0   11808  541785120  12574   \n",
      "2019-08-30   2019-08-30 14:59:30.500  4556.0   11808  541785120  12574   \n",
      "2019-08-30   2019-08-30 14:59:32.500  4558.0   11828  542696720  12574   \n",
      "2019-08-30   2019-08-30 14:59:33.000  4558.0   11830  542787880  12574   \n",
      "2019-08-30   2019-08-30 14:59:34.000  4558.0   11830  542787880  12574   \n",
      "2019-08-30   2019-08-30 14:59:41.000  4558.0   11830  542787880  12574   \n",
      "2019-08-30   2019-08-30 14:59:45.500  4558.0   11830  542787880  12574   \n",
      "2019-08-30   2019-08-30 14:59:47.500  4558.0   11830  542787880  12574   \n",
      "2019-08-30   2019-08-30 14:59:54.500  4558.0   11830  542787880  12574   \n",
      "2019-08-30   2019-08-30 14:59:55.500  4560.0   11834  542970280  12570   \n",
      "2019-08-30   2019-08-30 14:59:56.000  4560.0   11834  542970280  12570   \n",
      "2019-08-30   2019-08-30 14:59:57.500  4560.0   11834  542970280  12570   \n",
      "2019-08-30   2019-08-30 15:00:00.500  4560.0   11834  542970280  12570   \n",
      "2019-08-30   2019-08-30 15:20:15.000  4560.0   11834  542970280  12570   \n",
      "\n",
      "                  a1  a1v      b1  b1v  symbol  \n",
      "trading_date                                    \n",
      "2018-11-27       0.0    0     0.0    0  SP1909  \n",
      "2018-11-27    5900.0  302  5800.0    1  SP1909  \n",
      "2018-11-27    5860.0   97  5802.0    2  SP1909  \n",
      "2018-11-27    5858.0   12  5802.0    2  SP1909  \n",
      "2018-11-27    5856.0   14  5806.0    1  SP1909  \n",
      "2018-11-27    5854.0    4  5828.0    1  SP1909  \n",
      "2018-11-27    5854.0    1  5804.0    1  SP1909  \n",
      "2018-11-27    5852.0    3  5806.0    4  SP1909  \n",
      "2018-11-27    5806.0    2  5802.0    1  SP1909  \n",
      "2018-11-27    5850.0    1  5804.0    1  SP1909  \n",
      "2018-11-27    5856.0    4  5806.0    1  SP1909  \n",
      "2018-11-27    5854.0    3  5814.0    1  SP1909  \n",
      "2018-11-27    5850.0    2  5814.0    1  SP1909  \n",
      "2018-11-27    5848.0    1  5816.0    1  SP1909  \n",
      "2018-11-27    5846.0    1  5816.0    2  SP1909  \n",
      "2018-11-27    5842.0    2  5816.0    2  SP1909  \n",
      "2018-11-27    5842.0    2  5818.0    1  SP1909  \n",
      "2018-11-27    5842.0    2  5816.0    2  SP1909  \n",
      "2018-11-27    5818.0    1  5814.0    1  SP1909  \n",
      "2018-11-27    5842.0    2  5818.0    2  SP1909  \n",
      "2018-11-27    5840.0    2  5820.0    1  SP1909  \n",
      "2018-11-27    5842.0    2  5818.0    2  SP1909  \n",
      "2018-11-27    5820.0    1  5814.0    1  SP1909  \n",
      "2018-11-27    5840.0    2  5814.0    1  SP1909  \n",
      "2018-11-27    5840.0    2  5814.0    4  SP1909  \n",
      "2018-11-27    5836.0    2  5814.0    4  SP1909  \n",
      "2018-11-27    5836.0    2  5816.0    4  SP1909  \n",
      "2018-11-27    5834.0    1  5816.0    4  SP1909  \n",
      "2018-11-27    5834.0    1  5816.0    4  SP1909  \n",
      "2018-11-27    5820.0    2  5816.0    6  SP1909  \n",
      "...              ...  ...     ...  ...     ...  \n",
      "2019-08-30    4560.0    1  4558.0   11  SP1909  \n",
      "2019-08-30    4560.0    1  4558.0    1  SP1909  \n",
      "2019-08-30    4560.0    1  4558.0    1  SP1909  \n",
      "2019-08-30    4560.0    3  4558.0    1  SP1909  \n",
      "2019-08-30    4560.0    3  4558.0    1  SP1909  \n",
      "2019-08-30    4562.0   12  4560.0    9  SP1909  \n",
      "2019-08-30    4562.0   12  4558.0    1  SP1909  \n",
      "2019-08-30    4556.0    7  4554.0    1  SP1909  \n",
      "2019-08-30    4562.0   13  4560.0    9  SP1909  \n",
      "2019-08-30    4562.0   12  4554.0    2  SP1909  \n",
      "2019-08-30    4562.0   12  4554.0    1  SP1909  \n",
      "2019-08-30    4562.0   12  4558.0   28  SP1909  \n",
      "2019-08-30    4562.0   12  4558.0   28  SP1909  \n",
      "2019-08-30    4562.0   12  4558.0   28  SP1909  \n",
      "2019-08-30    4562.0   12  4558.0   28  SP1909  \n",
      "2019-08-30    4562.0   14  4558.0   28  SP1909  \n",
      "2019-08-30    4562.0   14  4558.0   28  SP1909  \n",
      "2019-08-30    4562.0    4  4558.0   28  SP1909  \n",
      "2019-08-30    4562.0    4  4558.0   18  SP1909  \n",
      "2019-08-30    4562.0    4  4558.0   17  SP1909  \n",
      "2019-08-30    4562.0    4  4558.0   18  SP1909  \n",
      "2019-08-30    4562.0    4  4558.0   18  SP1909  \n",
      "2019-08-30    4562.0    4  4560.0    2  SP1909  \n",
      "2019-08-30    4562.0    4  4560.0    2  SP1909  \n",
      "2019-08-30    4562.0    2  4560.0    2  SP1909  \n",
      "2019-08-30    4562.0    2  4558.0   21  SP1909  \n",
      "2019-08-30    4562.0    2  4558.0   38  SP1909  \n",
      "2019-08-30    4562.0    2  4558.0   21  SP1909  \n",
      "2019-08-30    4562.0    2  4558.0   21  SP1909  \n",
      "2019-08-30    4562.0    2  4558.0   21  SP1909  \n",
      "\n",
      "[3214137 rows x 10 columns]\n"
     ]
    }
   ],
   "source": [
    "symbol = 'SP1909'\n",
    "taq_df = get_fut_taq(symbol, start_date='2010-01-01')\n",
    "print(taq_df)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [],
   "source": [
    "taq_df.to_csv(\"futures.csv\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:anaconda3]",
   "language": "python",
   "name": "conda-env-anaconda3-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}