{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"Fork from https://www.kaggle.com/alexfir/recreating-target with min_periods=3750 in rolling, when the previous 3750 rows there is a gap in the data βM(t) = 0 \n\nIn this notebook we try to recreate Target calculation described in [Tutorial to the G-Research Crypto Competition](https://www.kaggle.com/cstein06/tutorial-to-the-g-research-crypto-competition/notebook#Building-your-prediction-model).\n","metadata":{}},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import os\nimport numpy as np\nimport pandas as pd\n\ndirectory = '../input/g-research-crypto-forecasting'\nfile_path = os.path.join(directory, 'train.csv')\ndtypes = {\n    'timestamp': np.int64,\n    'Asset_ID': np.int8,\n    'Count': np.int32,\n    'Open': np.float64,\n    'High': np.float64,\n    'Low': np.float64,\n    'Close': np.float64,\n    'Volume': np.float64,\n    'VWAP': np.float64,\n    'Target': np.float64,\n}\ndata = pd.read_csv(file_path, dtype=dtypes)\ndata['Time'] = pd.to_datetime(data['timestamp'], unit='s')\ndata.drop('timestamp', axis=1, inplace=True)\n\nfile_path = os.path.join(directory, 'asset_details.csv')\ndetails = pd.read_csv(file_path)","metadata":{"execution":{"iopub.status.busy":"2021-11-09T17:03:33.620247Z","iopub.execute_input":"2021-11-09T17:03:33.620712Z","iopub.status.idle":"2021-11-09T17:04:34.719055Z","shell.execute_reply.started":"2021-11-09T17:03:33.620628Z","shell.execute_reply":"2021-11-09T17:04:34.718136Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Then calculate return as per formula below. \n\n$$R^a(t) = log (P^a(t+16)\\ /\\ P^a(t+1))$$\n\nThis is done for each asset separately. We do not know which price should be used. There are five different prices: Open, High, Low, Close, and VWAP. Probably there is a mix like Open price for time + 1 min, and Close for time + 16 min. We use **Close** price in calculation below.","metadata":{}},{"cell_type":"code","source":"price_column = 'Close'\nids = list(details.Asset_ID)\nchunks = []\nfor id in ids:    \n    asset = data[data.Asset_ID == id].copy()\n    asset.sort_values(by='Time', inplace=True)\n    asset.set_index(keys='Time', inplace=True)\n    asset['p1'] = asset[price_column].shift(freq='-1T')\n    asset['p16'] = asset[price_column].shift(freq='-16T')\n    asset['r'] = np.log(asset.p16/asset.p1)\n    asset.drop(['p1', 'p16'], axis=1, inplace=True)\n    asset.reset_index(inplace=True)\n    chunks.append(asset)\n\ndata = pd.concat(chunks)\ndata.sort_values(by='Time', inplace=True)","metadata":{"execution":{"iopub.status.busy":"2021-11-09T17:04:34.720739Z","iopub.execute_input":"2021-11-09T17:04:34.720973Z","iopub.status.idle":"2021-11-09T17:04:55.267397Z","shell.execute_reply.started":"2021-11-09T17:04:34.720944Z","shell.execute_reply":"2021-11-09T17:04:55.266585Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Next, assign weight for each row. And calculate M(t). Note that M(t) is the same for all assets and depend only on time.\n\n$$M(t) = \\frac{\\sum_a w^a R^a(t)}{\\sum_a w^a}$$\n\nWe do not know if ${\\sum_a w^a}$ should be calculated for all assets or only for assets having data at time t.\n","metadata":{}},{"cell_type":"code","source":"data['w'] = data['Asset_ID'].map(details.set_index(keys='Asset_ID')['Weight'])\nweight_sum = details.Weight.sum()\n\ndata['weighted_asset_r'] = data.w * data.r\ntime_group = data.groupby('Time')\n\nm = time_group['weighted_asset_r'].sum() / time_group['w'].sum()\n#m = time_group['weighted_asset_r'].sum() / weight_sum\n\ndata.set_index(keys=['Time'], inplace=True)\ndata['m'] = m\ndata.reset_index(inplace=True)","metadata":{"execution":{"iopub.status.busy":"2021-11-09T17:04:55.268727Z","iopub.execute_input":"2021-11-09T17:04:55.269058Z","iopub.status.idle":"2021-11-09T17:04:58.413392Z","shell.execute_reply.started":"2021-11-09T17:04:55.269015Z","shell.execute_reply":"2021-11-09T17:04:58.41262Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"After that, Beta is calculated. Bracket $\\langle .\\rangle$ represent the rolling average over time (3750 minute windows).\n\n$$\\beta^a = \\frac{\\langle M \\cdot R^a \\rangle}{\\langle M^2 \\rangle}$$","metadata":{}},{"cell_type":"code","source":"data['m2'] = data.m ** 2\ndata['mr'] = data.r * data.m\n\nchunks = []\nfor id in ids:\n    # type: pd.DataFrame\n    asset = data[data.Asset_ID == id].copy()\n    asset.sort_values(by='Time', inplace=True)\n    asset.set_index(keys='Time', inplace=True)\n    #asset['mr_rolling'] = asset['mr'].rolling(window='3750T', min_periods=1).mean()\n    asset['mr_rolling'] = asset['mr'].rolling(window='3750T', min_periods=3750).mean()\n    #asset['m2_rolling'] = asset['m2'].rolling(window='3750T', min_periods=1).mean()\n    asset['m2_rolling'] = asset['m2'].rolling(window='3750T', min_periods=3750).mean()\n    asset.reset_index(inplace=True)\n    chunks.append(asset)\n    debug = 1\n\ndata = pd.concat(chunks)\ndata.sort_values(by='Time', inplace=True)\ndata['beta'] = data['mr_rolling'] / data['m2_rolling']","metadata":{"execution":{"iopub.status.busy":"2021-11-09T17:04:58.415685Z","iopub.execute_input":"2021-11-09T17:04:58.416126Z","iopub.status.idle":"2021-11-09T17:05:16.226396Z","shell.execute_reply.started":"2021-11-09T17:04:58.416082Z","shell.execute_reply":"2021-11-09T17:05:16.225366Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"And finallly Target is calculated.\n$$\\text{Target}^a(t) = R^a(t) - \\beta^a M(t)$$","metadata":{}},{"cell_type":"code","source":"data['Target_recreated'] = data['r'] - data['beta'] * data['m']","metadata":{"execution":{"iopub.status.busy":"2021-11-09T17:05:16.22759Z","iopub.execute_input":"2021-11-09T17:05:16.227863Z","iopub.status.idle":"2021-11-09T17:05:16.382979Z","shell.execute_reply.started":"2021-11-09T17:05:16.227831Z","shell.execute_reply":"2021-11-09T17:05:16.382263Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Now we compare given and recreated Target.","metadata":{}},{"cell_type":"code","source":"data['Target_diff'] = np.abs(data['Target'] - data['Target_recreated'])\n\nprint(f'Average absolute error {data.Target_diff.mean()}')\nprint(f'Max absolute error {data.Target_diff.max()}')","metadata":{"execution":{"iopub.status.busy":"2021-11-09T17:05:16.384176Z","iopub.execute_input":"2021-11-09T17:05:16.384435Z","iopub.status.idle":"2021-11-09T17:05:16.703137Z","shell.execute_reply.started":"2021-11-09T17:05:16.384405Z","shell.execute_reply":"2021-11-09T17:05:16.702288Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"data['Target'].agg(['min', 'max'])","metadata":{"execution":{"iopub.status.busy":"2021-11-09T17:05:16.704217Z","iopub.execute_input":"2021-11-09T17:05:16.704472Z","iopub.status.idle":"2021-11-09T17:05:16.786925Z","shell.execute_reply.started":"2021-11-09T17:05:16.704445Z","shell.execute_reply":"2021-11-09T17:05:16.786155Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"(data.Target_recreated < -0.509351).sum()","metadata":{"execution":{"iopub.status.busy":"2021-11-09T17:05:16.788121Z","iopub.execute_input":"2021-11-09T17:05:16.78833Z","iopub.status.idle":"2021-11-09T17:05:16.835231Z","shell.execute_reply.started":"2021-11-09T17:05:16.788301Z","shell.execute_reply":"2021-11-09T17:05:16.834421Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"(data.Target_recreated > 0.96417).sum()","metadata":{"execution":{"iopub.status.busy":"2021-11-09T17:05:16.836437Z","iopub.execute_input":"2021-11-09T17:05:16.836728Z","iopub.status.idle":"2021-11-09T17:05:16.884097Z","shell.execute_reply.started":"2021-11-09T17:05:16.836694Z","shell.execute_reply":"2021-11-09T17:05:16.883156Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"How Many Row with N=0","metadata":{}},{"cell_type":"code","source":"","metadata":{"execution":{"iopub.status.busy":"2021-11-09T17:05:29.35099Z","iopub.execute_input":"2021-11-09T17:05:29.351598Z","iopub.status.idle":"2021-11-09T17:05:29.396484Z","shell.execute_reply.started":"2021-11-09T17:05:29.351551Z","shell.execute_reply":"2021-11-09T17:05:29.395696Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"data['betaNA']=data['beta'].isna().astype('int')\ndata.groupby('Asset_ID').betaNA.mean()","metadata":{"execution":{"iopub.status.busy":"2021-11-09T17:07:57.301311Z","iopub.execute_input":"2021-11-09T17:07:57.301639Z","iopub.status.idle":"2021-11-09T17:07:57.965701Z","shell.execute_reply.started":"2021-11-09T17:07:57.301606Z","shell.execute_reply":"2021-11-09T17:07:57.964833Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}