{"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":"# Transforming 1m data we get from Binance\n\nForm and size modification.\n\nData set available here: https://www.kaggle.com/lucasmorin/gresearch-1m-data-from-binance\n\nRemaining points to deal with:\n- Vwap calculation (or import ?)\n- target calculation: code used from (https://www.kaggle.com/alexfir/recreating-target). We are discussing the remaining errors. \n- Filtering 0 volume data. (inclusion in target calculation ?)","metadata":{}},{"cell_type":"code","source":"import numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\nimport os\nimport time\nfrom datetime import datetime\n\nasset_details = pd.read_csv('../input/g-research-crypto-forecasting/asset_details.csv')","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2021-11-06T21:32:40.863939Z","iopub.execute_input":"2021-11-06T21:32:40.864495Z","iopub.status.idle":"2021-11-06T21:32:40.886425Z","shell.execute_reply.started":"2021-11-06T21:32:40.86439Z","shell.execute_reply":"2021-11-06T21:32:40.885539Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"dict_ticker = {'Bitcoin Cash':'BCH',\n'Binance Coin':'BNB',\n'Bitcoin':'BTC',\n'EOS.IO':'EOS',\n'Ethereum Classic':'ETC',\n'Ethereum':'ETH',\n'Litecoin':'LTC',\n'Monero':'XMR',\n'TRON':'TRX',\n'Stellar':'XLM',\n'Cardano':'ADA',\n'IOTA':'IOTA',\n'Maker':'MKR',\n'Dogecoin':'DOGE'}\n\nchunks = []\n\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    if dirname == '/kaggle/input/raw-crypto-1m-data-from-binance/compressed':\n        for filename in filenames:\n            ticker = filename.split('-')[0]\n            full_name = [i for i in dict_ticker if dict_ticker[i]==ticker][0]\n            Asset_ID = asset_details[asset_details.Asset_Name == full_name].Asset_ID.values[0]\n            \n            data = pd.read_parquet(dirname+'/'+filename, engine='pyarrow')\n            data = data.reset_index()\n            data = data.rename(columns={'open_time':'timestamp','open':'Open','high':'High','low':'Low','close':'Close','quote_asset_volume':'Volume','number_of_trades':'Count'})\n            data.timestamp = (pd.to_datetime(data.timestamp).astype(int)/ 10**9).astype(int)\n\n            #definition not okay so far\n            data['VWAP'] = (data.Low * data.taker_buy_base_asset_volume + data.High * data.taker_buy_quote_asset_volume)/(data.taker_buy_base_asset_volume + data.taker_buy_quote_asset_volume)\n            \n            data = data.drop('volume',axis=1)\n            \n            data['Asset_ID'] = Asset_ID\n            \n            price_column = 'Close'\n\n            data['Time'] = pd.to_datetime(data['timestamp'], unit='s')\n            data.sort_values(by='Time', inplace=True)\n            data.set_index(keys='Time', inplace=True)\n            data['p1'] = data[price_column].shift(freq='-1T')\n            data['p16'] = data[price_column].shift(freq='-16T')\n            data['r'] = np.log(data.p16/data.p1)\n            data.drop(['p1', 'p16'], axis=1, inplace=True)\n            data.reset_index(inplace=True)\n\n            chunks.append(data)\n\ndata = pd.concat(chunks)\ndata.sort_values(by='timestamp', inplace=True)","metadata":{"execution":{"iopub.status.busy":"2021-11-06T21:32:40.888287Z","iopub.execute_input":"2021-11-06T21:32:40.888863Z","iopub.status.idle":"2021-11-06T21:32:51.139232Z","shell.execute_reply.started":"2021-11-06T21:32:40.888829Z","shell.execute_reply":"2021-11-06T21:32:51.138244Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{"execution":{"iopub.status.busy":"2021-11-06T21:32:51.140625Z","iopub.execute_input":"2021-11-06T21:32:51.140942Z","iopub.status.idle":"2021-11-06T21:32:54.520849Z","shell.execute_reply.started":"2021-11-06T21:32:51.140899Z","shell.execute_reply":"2021-11-06T21:32:54.519902Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"data['w'] = data['Asset_ID'].map(asset_details.set_index(keys='Asset_ID')['Weight'])\nweight_sum = asset_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\ndata.set_index(keys=['Time'], inplace=True)\ndata['m'] = m\ndata.reset_index(inplace=True)\n\ndata['m2'] = data.m ** 2\ndata['mr'] = data.r * data.m\n\nids = list(asset_details.Asset_ID)\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=3750).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']\n\ndata['Target'] = data['r'] - data['beta'] * data['m']","metadata":{"execution":{"iopub.status.busy":"2021-11-06T21:32:54.522688Z","iopub.execute_input":"2021-11-06T21:32:54.523205Z","iopub.status.idle":"2021-11-06T21:32:54.882281Z","shell.execute_reply.started":"2021-11-06T21:32:54.523159Z","shell.execute_reply":"2021-11-06T21:32:54.881325Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"data = data.drop(['taker_buy_base_asset_volume','taker_buy_quote_asset_volume','r','w','weighted_asset_r','m','m2','mr','mr_rolling','m2_rolling','beta','Time'],axis=1)\n\ncol_ordered = ['timestamp','Asset_ID','Count','Open','High','Low','Close','Volume','VWAP','Target']\ndata = data[col_ordered]\n\ndtype0 = {'Asset_ID': 'int8', 'Count': 'int32', 'Count': 'int32',\n       'Open': 'float32', 'High': 'float32', 'Low': 'float32', 'Close': 'float32',\n       'Volume': 'float32', 'VWAP': 'float32'}\n\ndata = data.astype(dtype0)","metadata":{"execution":{"iopub.status.busy":"2021-11-06T21:33:08.995853Z","iopub.execute_input":"2021-11-06T21:33:08.996265Z","iopub.status.idle":"2021-11-06T21:33:11.735474Z","shell.execute_reply.started":"2021-11-06T21:33:08.996217Z","shell.execute_reply":"2021-11-06T21:33:11.734645Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"data.to_parquet('add_train.parquet')","metadata":{"execution":{"iopub.status.busy":"2021-11-06T21:35:39.44425Z","iopub.execute_input":"2021-11-06T21:35:39.444622Z","iopub.status.idle":"2021-11-06T21:35:39.48519Z","shell.execute_reply.started":"2021-11-06T21:35:39.444575Z","shell.execute_reply":"2021-11-06T21:35:39.484365Z"},"trusted":true},"execution_count":null,"outputs":[]}]}