Kaggle_Optiver_Feature_Eng.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "_cell_guid": "b1076dfc-b9ad-4769-8c92-a6c4dae69d19",
    "_uuid": "8f2839f25d086af736a60e9eeb907d3b93b6e0e5",
    "execution": {
     "iopub.execute_input": "2023-12-11T16:08:24.399159Z",
     "iopub.status.busy": "2023-12-11T16:08:24.398751Z",
     "iopub.status.idle": "2023-12-11T16:08:25.612370Z",
     "shell.execute_reply": "2023-12-11T16:08:25.610987Z",
     "shell.execute_reply.started": "2023-12-11T16:08:24.399122Z"
    }
   },
   "outputs": [],
   "source": [
    "#!wget http://prdownloads.sourceforge.net/ta-lib/ta-lib-0.4.0-src.tar.gz\n",
    "#!tar -xzvf ta-lib-0.4.0-src.tar.gz\n",
    "#%cd ta-lib\n",
    "#!./configure --prefix=/usr\n",
    "#!make\n",
    "#!make install\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import os\n",
    "from sklearn.preprocessing import MinMaxScaler\n",
    "import warnings\n",
    "from scipy.fftpack import fft, ifft\n",
    "warnings.filterwarnings(\"ignore\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-12-10T09:07:44.062590Z",
     "iopub.status.busy": "2023-12-10T09:07:44.061130Z",
     "iopub.status.idle": "2023-12-10T09:07:44.091265Z",
     "shell.execute_reply": "2023-12-10T09:07:44.088937Z",
     "shell.execute_reply.started": "2023-12-10T09:07:44.062486Z"
    }
   },
   "outputs": [],
   "source": [
    "import optiver2023\n",
    "#env = optiver2023.make_env()\n",
    "#iter_test = env.iter_test()\n",
    "\n",
    "counter = 0\n",
    "y_min, y_max = -64, 64\n",
    "qps, predictions = [], []\n",
    "cache = pd.DataFrame()\n",
    "\n",
    "for (test, revealed_targets, sample_prediction) in iter_test:\n",
    "    a = test\n",
    "a"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.status.busy": "2023-12-10T09:04:55.498603Z",
     "iopub.status.idle": "2023-12-10T09:04:55.499079Z",
     "shell.execute_reply": "2023-12-10T09:04:55.498847Z",
     "shell.execute_reply.started": "2023-12-10T09:04:55.498826Z"
    }
   },
   "outputs": [],
   "source": [
    "stop"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.status.busy": "2023-12-10T09:04:55.500351Z",
     "iopub.status.idle": "2023-12-10T09:04:55.500779Z",
     "shell.execute_reply": "2023-12-10T09:04:55.500596Z",
     "shell.execute_reply.started": "2023-12-10T09:04:55.500576Z"
    }
   },
   "outputs": [],
   "source": [
    "from itertools import combinations\n",
    "def feat_eng_RANGE(df):\n",
    "    def get_RANGE(data,i):\n",
    "        positive_sum = data[i].diff().clip(lower=0).rolling(window=10, min_periods=10).sum()\n",
    "        absolute_sum = data[i].diff().abs().rolling(window=10, min_periods=10).sum()\n",
    "        factor = positive_sum / absolute_sum\n",
    "        factor = factor.fillna(0.5) \n",
    "        factor.dropna(inplace=True)\n",
    "        return factor\n",
    "    cols = [c for c in df.columns if c not in ['row_id', 'time_id']]\n",
    "    df = df[cols]\n",
    "    \n",
    "    prices = ['bid_price','ask_price','wap','reference_price']#range_reference_price 0.071838 range_ask_price 0.133756 range_bid_price 0.123905 range_wap 0.098298\n",
    "    for i in prices:\n",
    "        fac=df[['stock_id','date_id',i]].groupby(['stock_id','date_id']).apply(lambda x:get_RANGE(x,i))\n",
    "        fac=fac.reset_index().set_index('level_2').sort_index()\n",
    "        df['range_'+i]=np.nan\n",
    "        df['range_'+i].iloc[fac.index]=fac[0]\n",
    "        \n",
    "    df.drop(columns=[\n",
    "        'date_id', \n",
    "    ], inplace=True)\n",
    "    \n",
    "    return df.replace([np.inf, -np.inf], 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.status.busy": "2023-12-10T09:04:55.502691Z",
     "iopub.status.idle": "2023-12-10T09:04:55.503283Z",
     "shell.execute_reply": "2023-12-10T09:04:55.503066Z",
     "shell.execute_reply.started": "2023-12-10T09:04:55.503030Z"
    }
   },
   "outputs": [],
   "source": [
    "from itertools import combinations\n",
    "def feat_eng_voi(df):\n",
    "    def voi(df):\n",
    "        bid_sub_price = df['bid_price'] - df['bid_price'].shift(1)\n",
    "        ask_sub_price = df['ask_price'] - df['ask_price'].shift(1)\n",
    "        bid_sub_volume = df['bid_size'] - df['bid_size'].shift(1)\n",
    "        ask_sub_volume = df['ask_size'] - df['ask_size'].shift(1)\n",
    "        bid_volume_change = bid_sub_volume\n",
    "        ask_volume_change = ask_sub_volume\n",
    "        # bid_volume_change[bid_sub_price == 0] = bid_sub_volume[bid_sub_price == 0]\n",
    "        bid_volume_change[bid_sub_price < 0] = 0\n",
    "        bid_volume_change[bid_sub_price > 0] = df['bid_size'][bid_sub_price > 0]\n",
    "        ask_volume_change[ask_sub_price < 0] = df['ask_size'][ask_sub_price < 0]\n",
    "        ask_volume_change[ask_sub_price > 0] = 0\n",
    "        tick_fac_data = (bid_volume_change - ask_volume_change)/(df['bid_size']+ df['bid_size']).rolling(5).sum()\n",
    "        return tick_fac_data\n",
    "    cols = [c for c in df.columns if c not in ['row_id', 'time_id']]\n",
    "    df = df[cols]\n",
    "    \n",
    "    fac=df.groupby(['stock_id','date_id']).apply(voi)\n",
    "    fac=fac.reset_index().set_index('level_2').sort_index()\n",
    "    df['voi']=np.nan\n",
    "    df['voi'].iloc[fac.index]=fac[0]\n",
    "\n",
    "    df.drop(columns=[\n",
    "        'date_id', \n",
    "    ], inplace=True)\n",
    "    \n",
    "    return df.replace([np.inf, -np.inf], 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.status.busy": "2023-12-10T09:04:55.505201Z",
     "iopub.status.idle": "2023-12-10T09:04:55.505639Z",
     "shell.execute_reply": "2023-12-10T09:04:55.505454Z",
     "shell.execute_reply.started": "2023-12-10T09:04:55.505435Z"
    }
   },
   "outputs": [],
   "source": [
    "from itertools import combinations\n",
    "def feat_eng_sin(df):\n",
    "    \n",
    "    cols = [c for c in df.columns if c not in ['row_id', 'time_id']]\n",
    "    df = df[cols]\n",
    "    \n",
    "    sizes = [\"matched_size\", \"bid_size\", \"ask_size\", \"imbalance_size\"]\n",
    "    \n",
    "    for c in combinations(sizes, 2):\n",
    "        df[f'{c[0]}_logminus_{c[1]}'] = (np.log(df[f'{c[0]}']) - np.log(df[f'{c[1]}'])).astype(np.float32)\n",
    "        df[f'{c[0]}_sinminus_{c[1]}'] = (df[f'{c[0]}'] - np.sin(df[f'{c[1]}'])).astype(np.float32)\n",
    "        df[f'{c[0]}_tanminus_{c[1]}'] = (df[f'{c[0]}'] - np.tan(df[f'{c[1]}'])).astype(np.float32)\n",
    "    \n",
    "    \n",
    "    #所有价格之间做差或求和\n",
    "    prices = ['reference_price','far_price', 'near_price', 'ask_price', 'bid_price', 'wap']\n",
    "    \n",
    "    for c in combinations(prices, 2):\n",
    "        df[f'{c[0]}_logminus_{c[1]}'] = (np.log(df[f'{c[0]}']) - np.log(df[f'{c[1]}'])).astype(np.float32)\n",
    "        df[f'{c[0]}_sinminus_{c[1]}'] = (df[f'{c[0]}'] - np.sin(df[f'{c[1]}'])).astype(np.float32)\n",
    "        df[f'{c[0]}_tanminus_{c[1]}'] = (df[f'{c[0]}'] - np.tan(df[f'{c[1]}'])).astype(np.float32)\n",
    "    \n",
    "    #计算统计指标\n",
    "    for func in [\"mean\", \"std\", \"skew\", \"kurt\"]:\n",
    "        df[f\"all_prices_{func}\"] = df[prices].agg(func, axis=1)\n",
    "        df[f\"all_sizes_{func}\"] = df[sizes].agg(func, axis=1)\n",
    "    #计算所有价格和交易量的滞后项和pct\n",
    "    for col in sizes+prices:\n",
    "        for window in [1, 2, 3, 10]:\n",
    "            df[f\"{col}_shift_{window}\"] = df.groupby('stock_id')[col].shift(window)\n",
    "            df[f\"{col}_ret_{window}\"] = df.groupby('stock_id')[col].pct_change(window)\n",
    "            df[f\"{col}_diff_{window}\"] = df.groupby(\"stock_id\")[col].diff(window)\n",
    "            \n",
    "    df.drop(columns=[\n",
    "        'date_id', \n",
    "    ], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.status.busy": "2023-12-10T09:04:55.507141Z",
     "iopub.status.idle": "2023-12-10T09:04:55.507556Z",
     "shell.execute_reply": "2023-12-10T09:04:55.507365Z",
     "shell.execute_reply.started": "2023-12-10T09:04:55.507346Z"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-12-10T09:04:55.559696Z",
     "iopub.status.busy": "2023-12-10T09:04:55.559284Z",
     "iopub.status.idle": "2023-12-10T09:05:15.633176Z",
     "shell.execute_reply": "2023-12-10T09:05:15.631520Z",
     "shell.execute_reply.started": "2023-12-10T09:04:55.559664Z"
    }
   },
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "train为初始训练集\n",
    "\"\"\"\n",
    "train=pd.read_csv(\"/kaggle/input/optiver-trading-at-the-close/train.csv\")\n",
    "median_sizes = train.groupby('stock_id')['bid_size'].median() + train.groupby('stock_id')['ask_size'].median()\n",
    "std_sizes = train.groupby('stock_id')['bid_size'].std() + train.groupby('stock_id')['ask_size'].std()\n",
    "max_sizes = train.groupby('stock_id')['bid_size'].max() + train.groupby('stock_id')['ask_size'].max()\n",
    "min_sizes = train.groupby('stock_id')['bid_size'].min() + train.groupby('stock_id')['ask_size'].min()\n",
    "mean_sizes = train.groupby('stock_id')['bid_size'].mean() + train.groupby('stock_id')['ask_size'].mean()\n",
    "first_sizes = train.groupby('stock_id')['bid_size'].first() + train.groupby('stock_id')['ask_size'].first()\n",
    "last_sizes = train.groupby('stock_id')['bid_size'].last() + train.groupby('stock_id')['ask_size'].last()\n",
    "from itertools import combinations\n",
    "def feat_eng_unbalanced(df):\n",
    "    \n",
    "    cols = [c for c in df.columns if c not in ['row_id', 'time_id']]\n",
    "    df = df[cols]\n",
    "    #size类指标\n",
    "    df['imbalance_ratio'] = df['imbalance_size'] / df['matched_size']\n",
    "    df['bid_ask_volume_diff'] = df['ask_size'] - df['bid_size']\n",
    "    df['mid_price'] = (df['ask_price'] + df['bid_price']) / 2\n",
    "    df[\"volume\"] = df.eval(\"ask_size + bid_size\")\n",
    "    df[\"liquidity_imbalance\"] = df.eval(\"(bid_size-ask_size)/(bid_size+ask_size)\")\n",
    "    df[\"matched_imbalance\"] = df.eval(\"(imbalance_size-matched_size)/(matched_size+imbalance_size)\")\n",
    "    df[\"size_imbalance\"] = df.eval(\"bid_size / ask_size\")\n",
    "    \n",
    "    df['median_size'] = df['stock_id'].map(median_sizes.to_dict())\n",
    "    df['std_size'] = df['stock_id'].map(std_sizes.to_dict())\n",
    "    df['max_size'] = df['stock_id'].map(max_sizes.to_dict())\n",
    "    df['min_size'] = df['stock_id'].map(min_sizes.to_dict())\n",
    "    df['mean_size'] = df['stock_id'].map(mean_sizes.to_dict())\n",
    "    df['first_size'] = df['stock_id'].map(first_sizes.to_dict())    \n",
    "    df['last_size'] = df['stock_id'].map(last_sizes.to_dict())\n",
    "    \n",
    "    df['high_volume'] = np.where(df['volume'] > df['median_size'], 1, 0)\n",
    "    \n",
    "    \n",
    "    df['ask_x_size'] = (df.eval('ask_size*ask_price')-df.eval('bid_size*bid_price'))/df.eval('ask_size*ask_price')+df.eval('bid_size*bid_price')\n",
    "    #对所有size和price计算两方不平衡和三方不平衡以及做差\n",
    "    sizes = [\"matched_size\", \"bid_size\", \"ask_size\", \"imbalance_size\"]\n",
    "    \n",
    "    for c in combinations(sizes, 2):\n",
    "        df[f'{c[0]}_minus_{c[1]}'] = (df[f'{c[0]}'] - df[f'{c[1]}']).astype(np.float32)\n",
    "        df[f'{c[0]}_{c[1]}_imb'] = df.eval(f'({c[0]}-{c[1]})/({c[0]}+{c[1]})')\n",
    "\n",
    "    for c in combinations(sizes, 3):\n",
    "        \n",
    "        max_ = df[list(c)].max(axis=1)\n",
    "        min_ = df[list(c)].min(axis=1)\n",
    "        mid_ = df[list(c)].sum(axis=1)-min_-max_\n",
    "\n",
    "        df[f'{c[0]}_{c[1]}_{c[2]}_imb2'] = (max_-mid_)/(mid_-min_)\n",
    "    \n",
    "    \n",
    "    #所有价格之间做差或求和\n",
    "    prices = ['reference_price','far_price', 'near_price', 'ask_price', 'bid_price', 'wap']\n",
    "    \n",
    "    for c in combinations(prices, 2):\n",
    "        df[f'{c[0]}_minus_{c[1]}'] = (df[f'{c[0]}'] - df[f'{c[1]}']).astype(np.float32)\n",
    "        df[f'{c[0]}_{c[1]}_imb'] = df.eval(f'({c[0]}-{c[1]})/({c[0]}+{c[1]})')\n",
    "\n",
    "    for c in combinations(prices, 3):\n",
    "        \n",
    "        max_ = df[list(c)].max(axis=1)\n",
    "        min_ = df[list(c)].min(axis=1)\n",
    "        mid_ = df[list(c)].sum(axis=1)-min_-max_\n",
    "\n",
    "        df[f'{c[0]}_{c[1]}_{c[2]}_imb2'] = (max_-mid_)/(mid_-min_)\n",
    "        \n",
    "    df.drop(columns=[\n",
    "        'date_id', \n",
    "    ], inplace=True)\n",
    "    \n",
    "    return df.replace([np.inf, -np.inf], 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-12-10T09:05:15.636265Z",
     "iopub.status.busy": "2023-12-10T09:05:15.635866Z",
     "iopub.status.idle": "2023-12-10T09:07:41.357432Z",
     "shell.execute_reply": "2023-12-10T09:07:41.356500Z",
     "shell.execute_reply.started": "2023-12-10T09:05:15.636231Z"
    }
   },
   "outputs": [],
   "source": [
    "%%time\n",
    "data_train=feat_eng_unbalanced(train)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "data_train"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-12-10T09:07:41.359993Z",
     "iopub.status.busy": "2023-12-10T09:07:41.359278Z",
     "iopub.status.idle": "2023-12-10T09:07:42.558290Z",
     "shell.execute_reply": "2023-12-10T09:07:42.556856Z",
     "shell.execute_reply.started": "2023-12-10T09:07:41.359955Z"
    }
   },
   "outputs": [],
   "source": [
    "data_train"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-12-10T09:07:42.560593Z",
     "iopub.status.busy": "2023-12-10T09:07:42.560222Z",
     "iopub.status.idle": "2023-12-10T09:07:42.594579Z",
     "shell.execute_reply": "2023-12-10T09:07:42.593057Z",
     "shell.execute_reply.started": "2023-12-10T09:07:42.560563Z"
    }
   },
   "outputs": [],
   "source": [
    "train"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-12-10T09:07:42.600076Z",
     "iopub.status.busy": "2023-12-10T09:07:42.599620Z",
     "iopub.status.idle": "2023-12-10T09:07:43.912494Z",
     "shell.execute_reply": "2023-12-10T09:07:43.911087Z",
     "shell.execute_reply.started": "2023-12-10T09:07:42.600042Z"
    }
   },
   "outputs": [],
   "source": [
    "feat_eng_unbalanced(pd.read_csv(\"/kaggle/input/optiver-trading-at-the-close/example_test_files/test.csv\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-12-10T09:07:43.915124Z",
     "iopub.status.busy": "2023-12-10T09:07:43.914319Z",
     "iopub.status.idle": "2023-12-10T09:07:43.960176Z",
     "shell.execute_reply": "2023-12-10T09:07:43.958461Z",
     "shell.execute_reply.started": "2023-12-10T09:07:43.915075Z"
    }
   },
   "outputs": [],
   "source": [
    "pd.read_csv(\"/kaggle/input/optiver-trading-at-the-close/example_test_files/sample_submission.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-12-10T09:07:43.963524Z",
     "iopub.status.busy": "2023-12-10T09:07:43.962826Z",
     "iopub.status.idle": "2023-12-10T09:07:43.979621Z",
     "shell.execute_reply": "2023-12-10T09:07:43.977001Z",
     "shell.execute_reply.started": "2023-12-10T09:07:43.963469Z"
    }
   },
   "outputs": [],
   "source": [
    "import optiver2023\n",
    "#env = optiver2023.make_env()\n",
    "#iter_test = env.iter_test()\n",
    "a = pd.DataFrame()\n",
    "counter = 0\n",
    "y_min, y_max = -64, 64\n",
    "qps, predictions = [], []\n",
    "cache = pd.DataFrame()\n",
    "\n",
    "for (test, revealed_targets, sample_prediction) in iter_test:\n",
    "    a = test\n",
    "a"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-12-10T09:07:43.985084Z",
     "iopub.status.busy": "2023-12-10T09:07:43.983659Z",
     "iopub.status.idle": "2023-12-10T09:07:44.053265Z",
     "shell.execute_reply": "2023-12-10T09:07:44.048152Z",
     "shell.execute_reply.started": "2023-12-10T09:07:43.984804Z"
    }
   },
   "outputs": [],
   "source": [
    "import optiver2023\n",
    "#env = optiver2023.make_env()\n",
    "#iter_test = env.iter_test()\n",
    "\n",
    "counter = 0\n",
    "y_min, y_max = -64, 64\n",
    "qps, predictions = [], []\n",
    "cache = pd.DataFrame()\n",
    "\n",
    "for (test, revealed_targets, sample_prediction) in iter_test:\n",
    "    a = test\n",
    "a"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-12-11T16:08:59.800799Z",
     "iopub.status.busy": "2023-12-11T16:08:59.800391Z",
     "iopub.status.idle": "2023-12-11T16:09:21.495898Z",
     "shell.execute_reply": "2023-12-11T16:09:21.494362Z",
     "shell.execute_reply.started": "2023-12-11T16:08:59.800766Z"
    }
   },
   "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>stock_id</th>\n",
       "      <th>date_id</th>\n",
       "      <th>seconds_in_bucket</th>\n",
       "      <th>imbalance_size</th>\n",
       "      <th>imbalance_buy_sell_flag</th>\n",
       "      <th>reference_price</th>\n",
       "      <th>matched_size</th>\n",
       "      <th>far_price</th>\n",
       "      <th>near_price</th>\n",
       "      <th>bid_price</th>\n",
       "      <th>bid_size</th>\n",
       "      <th>ask_price</th>\n",
       "      <th>ask_size</th>\n",
       "      <th>wap</th>\n",
       "      <th>target</th>\n",
       "      <th>time_id</th>\n",
       "      <th>row_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3180602.69</td>\n",
       "      <td>1</td>\n",
       "      <td>0.999812</td>\n",
       "      <td>13380276.64</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.999812</td>\n",
       "      <td>60651.50</td>\n",
       "      <td>1.000026</td>\n",
       "      <td>8493.03</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>-3.029704</td>\n",
       "      <td>0</td>\n",
       "      <td>0_0_0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>166603.91</td>\n",
       "      <td>-1</td>\n",
       "      <td>0.999896</td>\n",
       "      <td>1642214.25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.999896</td>\n",
       "      <td>3233.04</td>\n",
       "      <td>1.000660</td>\n",
       "      <td>20605.09</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>-5.519986</td>\n",
       "      <td>0</td>\n",
       "      <td>0_0_1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>302879.87</td>\n",
       "      <td>-1</td>\n",
       "      <td>0.999561</td>\n",
       "      <td>1819368.03</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.999403</td>\n",
       "      <td>37956.00</td>\n",
       "      <td>1.000298</td>\n",
       "      <td>18995.00</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>-8.389950</td>\n",
       "      <td>0</td>\n",
       "      <td>0_0_2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>11917682.27</td>\n",
       "      <td>-1</td>\n",
       "      <td>1.000171</td>\n",
       "      <td>18389745.62</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.999999</td>\n",
       "      <td>2324.90</td>\n",
       "      <td>1.000214</td>\n",
       "      <td>479032.40</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>-4.010200</td>\n",
       "      <td>0</td>\n",
       "      <td>0_0_3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>447549.96</td>\n",
       "      <td>-1</td>\n",
       "      <td>0.999532</td>\n",
       "      <td>17860614.95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.999394</td>\n",
       "      <td>16485.54</td>\n",
       "      <td>1.000016</td>\n",
       "      <td>434.10</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>-7.349849</td>\n",
       "      <td>0</td>\n",
       "      <td>0_0_4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5237975</th>\n",
       "      <td>195</td>\n",
       "      <td>480</td>\n",
       "      <td>540</td>\n",
       "      <td>2440722.89</td>\n",
       "      <td>-1</td>\n",
       "      <td>1.000317</td>\n",
       "      <td>28280361.74</td>\n",
       "      <td>0.999734</td>\n",
       "      <td>0.999734</td>\n",
       "      <td>1.000317</td>\n",
       "      <td>32257.04</td>\n",
       "      <td>1.000434</td>\n",
       "      <td>319862.40</td>\n",
       "      <td>1.000328</td>\n",
       "      <td>2.310276</td>\n",
       "      <td>26454</td>\n",
       "      <td>480_540_195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5237976</th>\n",
       "      <td>196</td>\n",
       "      <td>480</td>\n",
       "      <td>540</td>\n",
       "      <td>349510.47</td>\n",
       "      <td>-1</td>\n",
       "      <td>1.000643</td>\n",
       "      <td>9187699.11</td>\n",
       "      <td>1.000129</td>\n",
       "      <td>1.000386</td>\n",
       "      <td>1.000643</td>\n",
       "      <td>205108.40</td>\n",
       "      <td>1.000900</td>\n",
       "      <td>93393.07</td>\n",
       "      <td>1.000819</td>\n",
       "      <td>-8.220077</td>\n",
       "      <td>26454</td>\n",
       "      <td>480_540_196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5237977</th>\n",
       "      <td>197</td>\n",
       "      <td>480</td>\n",
       "      <td>540</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0.995789</td>\n",
       "      <td>12725436.10</td>\n",
       "      <td>0.995789</td>\n",
       "      <td>0.995789</td>\n",
       "      <td>0.995789</td>\n",
       "      <td>16790.66</td>\n",
       "      <td>0.995883</td>\n",
       "      <td>180038.32</td>\n",
       "      <td>0.995797</td>\n",
       "      <td>1.169443</td>\n",
       "      <td>26454</td>\n",
       "      <td>480_540_197</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5237978</th>\n",
       "      <td>198</td>\n",
       "      <td>480</td>\n",
       "      <td>540</td>\n",
       "      <td>1000898.84</td>\n",
       "      <td>1</td>\n",
       "      <td>0.999210</td>\n",
       "      <td>94773271.05</td>\n",
       "      <td>0.999210</td>\n",
       "      <td>0.999210</td>\n",
       "      <td>0.998970</td>\n",
       "      <td>125631.72</td>\n",
       "      <td>0.999210</td>\n",
       "      <td>669893.00</td>\n",
       "      <td>0.999008</td>\n",
       "      <td>-1.540184</td>\n",
       "      <td>26454</td>\n",
       "      <td>480_540_198</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5237979</th>\n",
       "      <td>199</td>\n",
       "      <td>480</td>\n",
       "      <td>540</td>\n",
       "      <td>1884285.71</td>\n",
       "      <td>-1</td>\n",
       "      <td>1.002129</td>\n",
       "      <td>24073677.32</td>\n",
       "      <td>1.000859</td>\n",
       "      <td>1.001494</td>\n",
       "      <td>1.002129</td>\n",
       "      <td>250081.44</td>\n",
       "      <td>1.002447</td>\n",
       "      <td>300167.56</td>\n",
       "      <td>1.002274</td>\n",
       "      <td>-6.530285</td>\n",
       "      <td>26454</td>\n",
       "      <td>480_540_199</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5237980 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         stock_id  date_id  seconds_in_bucket  imbalance_size  \\\n",
       "0               0        0                  0      3180602.69   \n",
       "1               1        0                  0       166603.91   \n",
       "2               2        0                  0       302879.87   \n",
       "3               3        0                  0     11917682.27   \n",
       "4               4        0                  0       447549.96   \n",
       "...           ...      ...                ...             ...   \n",
       "5237975       195      480                540      2440722.89   \n",
       "5237976       196      480                540       349510.47   \n",
       "5237977       197      480                540            0.00   \n",
       "5237978       198      480                540      1000898.84   \n",
       "5237979       199      480                540      1884285.71   \n",
       "\n",
       "         imbalance_buy_sell_flag  reference_price  matched_size  far_price  \\\n",
       "0                              1         0.999812   13380276.64        NaN   \n",
       "1                             -1         0.999896    1642214.25        NaN   \n",
       "2                             -1         0.999561    1819368.03        NaN   \n",
       "3                             -1         1.000171   18389745.62        NaN   \n",
       "4                             -1         0.999532   17860614.95        NaN   \n",
       "...                          ...              ...           ...        ...   \n",
       "5237975                       -1         1.000317   28280361.74   0.999734   \n",
       "5237976                       -1         1.000643    9187699.11   1.000129   \n",
       "5237977                        0         0.995789   12725436.10   0.995789   \n",
       "5237978                        1         0.999210   94773271.05   0.999210   \n",
       "5237979                       -1         1.002129   24073677.32   1.000859   \n",
       "\n",
       "         near_price  bid_price   bid_size  ask_price   ask_size       wap  \\\n",
       "0               NaN   0.999812   60651.50   1.000026    8493.03  1.000000   \n",
       "1               NaN   0.999896    3233.04   1.000660   20605.09  1.000000   \n",
       "2               NaN   0.999403   37956.00   1.000298   18995.00  1.000000   \n",
       "3               NaN   0.999999    2324.90   1.000214  479032.40  1.000000   \n",
       "4               NaN   0.999394   16485.54   1.000016     434.10  1.000000   \n",
       "...             ...        ...        ...        ...        ...       ...   \n",
       "5237975    0.999734   1.000317   32257.04   1.000434  319862.40  1.000328   \n",
       "5237976    1.000386   1.000643  205108.40   1.000900   93393.07  1.000819   \n",
       "5237977    0.995789   0.995789   16790.66   0.995883  180038.32  0.995797   \n",
       "5237978    0.999210   0.998970  125631.72   0.999210  669893.00  0.999008   \n",
       "5237979    1.001494   1.002129  250081.44   1.002447  300167.56  1.002274   \n",
       "\n",
       "           target  time_id       row_id  \n",
       "0       -3.029704        0        0_0_0  \n",
       "1       -5.519986        0        0_0_1  \n",
       "2       -8.389950        0        0_0_2  \n",
       "3       -4.010200        0        0_0_3  \n",
       "4       -7.349849        0        0_0_4  \n",
       "...           ...      ...          ...  \n",
       "5237975  2.310276    26454  480_540_195  \n",
       "5237976 -8.220077    26454  480_540_196  \n",
       "5237977  1.169443    26454  480_540_197  \n",
       "5237978 -1.540184    26454  480_540_198  \n",
       "5237979 -6.530285    26454  480_540_199  \n",
       "\n",
       "[5237980 rows x 17 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train = pd.read_csv(\"/kaggle/input/optiver-trading-at-the-close/train.csv\")\n",
    "train"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-12-11T16:54:46.413330Z",
     "iopub.status.busy": "2023-12-11T16:54:46.412891Z",
     "iopub.status.idle": "2023-12-11T16:54:46.449510Z",
     "shell.execute_reply": "2023-12-11T16:54:46.447916Z",
     "shell.execute_reply.started": "2023-12-11T16:54:46.413292Z"
    }
   },
   "outputs": [],
   "source": [
    "from itertools import combinations\n",
    "def feat_engineering(df):\n",
    "    def voi(df):\n",
    "        bid_sub_price = df['bid_price'] - df['bid_price'].shift(1)\n",
    "        ask_sub_price = df['ask_price'] - df['ask_price'].shift(1)\n",
    "        bid_sub_volume = df['bid_size'] - df['bid_size'].shift(1)\n",
    "        ask_sub_volume = df['ask_size'] - df['ask_size'].shift(1)\n",
    "        bid_volume_change = bid_sub_volume\n",
    "        ask_volume_change = ask_sub_volume\n",
    "        # bid_volume_change[bid_sub_price == 0] = bid_sub_volume[bid_sub_price == 0]\n",
    "        bid_volume_change[bid_sub_price < 0] = 0\n",
    "        bid_volume_change[bid_sub_price > 0] = df['bid_size'][bid_sub_price > 0]\n",
    "        ask_volume_change[ask_sub_price < 0] = df['ask_size'][ask_sub_price < 0]\n",
    "        ask_volume_change[ask_sub_price > 0] = 0\n",
    "        tick_fac_data = (bid_volume_change - ask_volume_change)/(df['bid_size']+ df['bid_size']).rolling(5).sum()\n",
    "        return tick_fac_data\n",
    "    \n",
    "    def get_RANGE(data,i):\n",
    "        factor = pd.DataFrame(index=data.index)\n",
    "        positive_sum = data[i].diff().clip(lower=0).rolling(window=10, min_periods=10).sum()\n",
    "        absolute_sum = data[i].diff().abs().rolling(window=10, min_periods=10).sum()\n",
    "        factor['RANGE'] = positive_sum / absolute_sum\n",
    "        factor['RANGE'] = factor['RANGE'].fillna(0.5) \n",
    "        factor.dropna(inplace=True)\n",
    "        return factor['RANGE']\n",
    "    \n",
    "    cols = [c for c in df.columns if c not in ['row_id', 'time_id']]\n",
    "    df = df[cols]\n",
    "    prices = ['bid_price','ask_price','wap','reference_price']#range_reference_price 0.071838 range_ask_price 0.133756 range_bid_price 0.123905 range_wap 0.098298\n",
    "    for i in prices:\n",
    "        fac=df[['stock_id','date_id',i]].groupby(['stock_id','date_id']).apply(lambda x:get_RANGE(x,i))\n",
    "        fac=fac.reset_index().set_index('level_2').sort_index()\n",
    "        df['range_'+i]=np.nan\n",
    "        df['range_'+i].iloc[fac.index]=fac['RANGE']\n",
    "    print(\"RANGE FINISH\")\n",
    "    fac=df.groupby(['stock_id','date_id']).apply(voi)\n",
    "    fac=fac.reset_index().set_index('level_2').sort_index()\n",
    "    df['voi']=np.nan\n",
    "    df['voi'].iloc[fac.index]=fac[0]\n",
    "    print(\"VOI FINIAH\")\n",
    "    sizes = [\"matched_size\", \"bid_size\", \"ask_size\", \"imbalance_size\"]\n",
    "    for c in combinations(sizes, 2):\n",
    "        df[f'{c[0]}_logminus_{c[1]}'] = (np.log(df[f'{c[0]}']) - np.log(df[f'{c[1]}'])).astype(np.float32)\n",
    "        df[f'{c[0]}_sinminus_{c[1]}'] = (df[f'{c[0]}'] - np.sin(df[f'{c[1]}'])).astype(np.float32)\n",
    "        df[f'{c[0]}_tanminus_{c[1]}'] = (df[f'{c[0]}'] - np.tan(df[f'{c[1]}'])).astype(np.float32)\n",
    "    #所有价格之间做差或求和\n",
    "    prices = ['reference_price','far_price', 'near_price', 'ask_price', 'bid_price', 'wap']\n",
    "    for c in combinations(prices, 2):\n",
    "        df[f'{c[0]}_logminus_{c[1]}'] = (np.log(df[f'{c[0]}']) - np.log(df[f'{c[1]}'])).astype(np.float32)\n",
    "        df[f'{c[0]}_sinminus_{c[1]}'] = (df[f'{c[0]}'] - np.sin(df[f'{c[1]}'])).astype(np.float32)\n",
    "        df[f'{c[0]}_tanminus_{c[1]}'] = (df[f'{c[0]}'] - np.tan(df[f'{c[1]}'])).astype(np.float32)\n",
    "    #计算统计指标\n",
    "    for func in [\"mean\", \"std\", \"skew\", \"kurt\"]:\n",
    "        df[f\"all_prices_{func}\"] = df[prices].agg(func, axis=1)\n",
    "        df[f\"all_sizes_{func}\"] = df[sizes].agg(func, axis=1)\n",
    "    #计算所有价格和交易量的滞后项和pct\n",
    "    for col in sizes+prices:\n",
    "        for window in [1, 2, 3, 10]:\n",
    "            df[f\"{col}_shift_{window}\"] = df.groupby('stock_id')[col].shift(window)\n",
    "            df[f\"{col}_ret_{window}\"] = df.groupby('stock_id')[col].pct_change(window)\n",
    "            df[f\"{col}_diff_{window}\"] = df.groupby(\"stock_id\")[col].diff(window)\n",
    "    print(\"SIN FINISH\")\n",
    "    \n",
    "    median_sizes = df.groupby('stock_id')['bid_size'].median() + df.groupby('stock_id')['ask_size'].median()\n",
    "    std_sizes = df.groupby('stock_id')['bid_size'].std() + df.groupby('stock_id')['ask_size'].std()\n",
    "    max_sizes = df.groupby('stock_id')['bid_size'].max() + df.groupby('stock_id')['ask_size'].max()\n",
    "    min_sizes = df.groupby('stock_id')['bid_size'].min() + df.groupby('stock_id')['ask_size'].min()\n",
    "    mean_sizes = df.groupby('stock_id')['bid_size'].mean() + df.groupby('stock_id')['ask_size'].mean()\n",
    "    first_sizes = df.groupby('stock_id')['bid_size'].first() + df.groupby('stock_id')['ask_size'].first()\n",
    "    last_sizes = df.groupby('stock_id')['bid_size'].last() + df.groupby('stock_id')['ask_size'].last()\n",
    "    \n",
    "    df['imbalance_ratio'] = df['imbalance_size'] / df['matched_size']\n",
    "    df['bid_ask_volume_diff'] = df['ask_size'] - df['bid_size']\n",
    "    df['mid_price'] = (df['ask_price'] + df['bid_price']) / 2\n",
    "    df[\"volume\"] = df.eval(\"ask_size + bid_size\")\n",
    "    df[\"liquidity_imbalance\"] = df.eval(\"(bid_size-ask_size)/(bid_size+ask_size)\")\n",
    "    df[\"matched_imbalance\"] = df.eval(\"(imbalance_size-matched_size)/(matched_size+imbalance_size)\")\n",
    "    df[\"size_imbalance\"] = df.eval(\"bid_size / ask_size\")\n",
    "    \n",
    "    df['median_size'] = df['stock_id'].map(median_sizes.to_dict())\n",
    "    df['std_size'] = df['stock_id'].map(std_sizes.to_dict())\n",
    "    df['max_size'] = df['stock_id'].map(max_sizes.to_dict())\n",
    "    df['min_size'] = df['stock_id'].map(min_sizes.to_dict())\n",
    "    df['mean_size'] = df['stock_id'].map(mean_sizes.to_dict())\n",
    "    df['first_size'] = df['stock_id'].map(first_sizes.to_dict())    \n",
    "    df['last_size'] = df['stock_id'].map(last_sizes.to_dict())\n",
    "    \n",
    "    df['high_volume'] = np.where(df['volume'] > df['median_size'], 1, 0)\n",
    "    \n",
    "    \n",
    "    df['ask_x_size'] = (df.eval('ask_size*ask_price')-df.eval('bid_size*bid_price'))/df.eval('ask_size*ask_price')+df.eval('bid_size*bid_price')\n",
    "    #对所有size和price计算两方不平衡和三方不平衡以及做差\n",
    "    sizes = [\"matched_size\", \"bid_size\", \"ask_size\", \"imbalance_size\"]\n",
    "    \n",
    "    for c in combinations(sizes, 2):\n",
    "        df[f'{c[0]}_minus_{c[1]}'] = (df[f'{c[0]}'] - df[f'{c[1]}']).astype(np.float32)\n",
    "        df[f'{c[0]}_{c[1]}_imb'] = df.eval(f'({c[0]}-{c[1]})/({c[0]}+{c[1]})')\n",
    "\n",
    "    for c in combinations(sizes, 3):\n",
    "        \n",
    "        max_ = df[list(c)].max(axis=1)\n",
    "        min_ = df[list(c)].min(axis=1)\n",
    "        mid_ = df[list(c)].sum(axis=1)-min_-max_\n",
    "\n",
    "        df[f'{c[0]}_{c[1]}_{c[2]}_imb2'] = (max_-mid_)/(mid_-min_)\n",
    "    \n",
    "    \n",
    "    #所有价格之间做差或求和\n",
    "    prices = ['reference_price','far_price', 'near_price', 'ask_price', 'bid_price', 'wap']\n",
    "    \n",
    "    for c in combinations(prices, 2):\n",
    "        df[f'{c[0]}_minus_{c[1]}'] = (df[f'{c[0]}'] - df[f'{c[1]}']).astype(np.float32)\n",
    "        df[f'{c[0]}_{c[1]}_imb'] = df.eval(f'({c[0]}-{c[1]})/({c[0]}+{c[1]})')\n",
    "\n",
    "    for c in combinations(prices, 3):\n",
    "        \n",
    "        max_ = df[list(c)].max(axis=1)\n",
    "        min_ = df[list(c)].min(axis=1)\n",
    "        mid_ = df[list(c)].sum(axis=1)-min_-max_\n",
    "\n",
    "        df[f'{c[0]}_{c[1]}_{c[2]}_imb2'] = (max_-mid_)/(mid_-min_)\n",
    "        \n",
    "    df.drop(columns=[\n",
    "        'date_id', \n",
    "    ], inplace=True)\n",
    "    \n",
    "    return df.replace([np.inf, -np.inf], 0)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-12-11T16:54:48.623874Z",
     "iopub.status.busy": "2023-12-11T16:54:48.623431Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "RANGE FINISH\n",
      "VOI FINIAH\n",
      "SIN FINISH\n"
     ]
    }
   ],
   "source": [
    "feat_all = feat_engineering(train)\n",
    "feat_all"
   ]
  }
 ],
 "metadata": {
  "kaggle": {
   "accelerator": "none",
   "dataSources": [
    {
     "databundleVersionId": 7056235,
     "sourceId": 57891,
     "sourceType": "competition"
    }
   ],
   "dockerImageVersionId": 30615,
   "isGpuEnabled": false,
   "isInternetEnabled": true,
   "language": "python",
   "sourceType": "notebook"
  },
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.19"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}