{"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":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load\n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n\n# Input data files are available in the read-only \"../input/\" directory\n# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\n\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))\n\n# You can write up to 20GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using \"Save & Run All\" \n# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2021-12-05T13:47:50.311544Z","iopub.execute_input":"2021-12-05T13:47:50.312375Z","iopub.status.idle":"2021-12-05T13:47:50.347213Z","shell.execute_reply.started":"2021-12-05T13:47:50.312275Z","shell.execute_reply":"2021-12-05T13:47:50.346395Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"from numba import jit\nimport plotly as pl\nimport seaborn as sns\nfrom datetime import datetime\nimport os\nfrom functools import reduce","metadata":{"execution":{"iopub.status.busy":"2021-12-05T13:47:50.349191Z","iopub.execute_input":"2021-12-05T13:47:50.349508Z","iopub.status.idle":"2021-12-05T13:47:51.819897Z","shell.execute_reply.started":"2021-12-05T13:47:50.349464Z","shell.execute_reply":"2021-12-05T13:47:51.819115Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"path = \"../input/g-research-crypto-forecasting/\"\n!ls $path\nos.chdir(path)","metadata":{"execution":{"iopub.status.busy":"2021-12-05T13:47:51.821105Z","iopub.execute_input":"2021-12-05T13:47:51.82227Z","iopub.status.idle":"2021-12-05T13:47:52.579575Z","shell.execute_reply.started":"2021-12-05T13:47:51.822238Z","shell.execute_reply":"2021-12-05T13:47:52.57862Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"os.listdir()","metadata":{"execution":{"iopub.status.busy":"2021-12-05T13:47:52.581787Z","iopub.execute_input":"2021-12-05T13:47:52.582045Z","iopub.status.idle":"2021-12-05T13:47:52.590754Z","shell.execute_reply.started":"2021-12-05T13:47:52.582015Z","shell.execute_reply":"2021-12-05T13:47:52.5901Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"data = pd.read_csv('train.csv')\nasset_ids = pd.read_csv('asset_details.csv')","metadata":{"execution":{"iopub.status.busy":"2021-12-05T13:47:52.591676Z","iopub.execute_input":"2021-12-05T13:47:52.592202Z","iopub.status.idle":"2021-12-05T13:48:53.386499Z","shell.execute_reply.started":"2021-12-05T13:47:52.592166Z","shell.execute_reply":"2021-12-05T13:48:53.38567Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"data.head(3)","metadata":{"execution":{"iopub.status.busy":"2021-12-05T13:48:53.387687Z","iopub.execute_input":"2021-12-05T13:48:53.387935Z","iopub.status.idle":"2021-12-05T13:48:53.411915Z","shell.execute_reply.started":"2021-12-05T13:48:53.387904Z","shell.execute_reply":"2021-12-05T13:48:53.41103Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"asset_ids.head(3)","metadata":{"execution":{"iopub.status.busy":"2021-12-05T13:48:53.415192Z","iopub.execute_input":"2021-12-05T13:48:53.415421Z","iopub.status.idle":"2021-12-05T13:48:53.425441Z","shell.execute_reply.started":"2021-12-05T13:48:53.415392Z","shell.execute_reply":"2021-12-05T13:48:53.424595Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"data.columns = ['timestamp','asset_id','count','open','high','low','close','volume','vwap','target']\nasset_ids.columns = ['asset_id','weight','asset_name']","metadata":{"execution":{"iopub.status.busy":"2021-12-05T13:48:53.426729Z","iopub.execute_input":"2021-12-05T13:48:53.426971Z","iopub.status.idle":"2021-12-05T13:48:53.435733Z","shell.execute_reply.started":"2021-12-05T13:48:53.426945Z","shell.execute_reply":"2021-12-05T13:48:53.435071Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"@jit()\ndef null_count(s):\n    \n    return s.isna().sum()","metadata":{"execution":{"iopub.status.busy":"2021-12-05T13:48:53.437108Z","iopub.execute_input":"2021-12-05T13:48:53.437318Z","iopub.status.idle":"2021-12-05T13:48:53.682236Z","shell.execute_reply.started":"2021-12-05T13:48:53.437291Z","shell.execute_reply":"2021-12-05T13:48:53.68139Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"data.groupby(by='asset_id').agg({'target':null_count})","metadata":{"execution":{"iopub.status.busy":"2021-12-05T13:48:53.685089Z","iopub.execute_input":"2021-12-05T13:48:53.685307Z","iopub.status.idle":"2021-12-05T13:48:55.428516Z","shell.execute_reply.started":"2021-12-05T13:48:53.68528Z","shell.execute_reply":"2021-12-05T13:48:55.427627Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Checking gaps between records for each asset\n\ngrp_asset = data.groupby('asset_id')\nasset_dict = {}\n\nfor asset, grp in grp_asset:\n    \n    grp['delta_time'] = grp['timestamp'].diff(periods=1)\n    asset_dict[asset] = grp\n    \n    \n    ","metadata":{"execution":{"iopub.status.busy":"2021-12-05T13:48:55.429643Z","iopub.execute_input":"2021-12-05T13:48:55.429896Z","iopub.status.idle":"2021-12-05T13:48:59.225374Z","shell.execute_reply.started":"2021-12-05T13:48:55.429861Z","shell.execute_reply":"2021-12-05T13:48:59.224519Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"asset_ids[asset_ids['asset_id'] == 0].iloc[0,2]","metadata":{"execution":{"iopub.status.busy":"2021-12-05T13:48:59.227142Z","iopub.execute_input":"2021-12-05T13:48:59.227714Z","iopub.status.idle":"2021-12-05T13:48:59.237184Z","shell.execute_reply.started":"2021-12-05T13:48:59.227666Z","shell.execute_reply":"2021-12-05T13:48:59.236302Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\ndelta_grps = []\nfor key,val in asset_dict.items():\n    \n    asset_name =asset_ids[asset_ids['asset_id'] == key].iloc[0,2]\n    \n    \n    \n    \n    \n    \n    t= pd.DataFrame(val.delta_time.value_counts())\n    t.columns = [asset_name]\n    delta_grps.append(t)\n    \n\ndelta_df = reduce(lambda left,right: pd.merge(left,right,left_index=True,right_index=True, how='outer' ), delta_grps)   \n    \n    ","metadata":{"execution":{"iopub.status.busy":"2021-12-05T13:48:59.23884Z","iopub.execute_input":"2021-12-05T13:48:59.239198Z","iopub.status.idle":"2021-12-05T13:48:59.699285Z","shell.execute_reply.started":"2021-12-05T13:48:59.239168Z","shell.execute_reply":"2021-12-05T13:48:59.698507Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"delta_grps = []\nfor key,val in asset_dict.items():\n    \n    asset_name =asset_ids[asset_ids['asset_id'] == key].iloc[0,2]\n     \n    t= pd.DataFrame(val.delta_time.value_counts())\n    t.columns = [asset_name]\n    delta_grps.append(t)\n    \n\ndelta_df = reduce(lambda left,right: pd.merge(left,right,left_index=True,right_index=True, how='outer' ), delta_grps)   \n    \n    ","metadata":{"execution":{"iopub.status.busy":"2021-12-05T13:48:59.700482Z","iopub.execute_input":"2021-12-05T13:48:59.700702Z","iopub.status.idle":"2021-12-05T13:49:00.004538Z","shell.execute_reply.started":"2021-12-05T13:48:59.700675Z","shell.execute_reply":"2021-12-05T13:49:00.003626Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"delta_df","metadata":{"execution":{"iopub.status.busy":"2021-12-05T13:49:00.006244Z","iopub.execute_input":"2021-12-05T13:49:00.006571Z","iopub.status.idle":"2021-12-05T13:49:00.047095Z","shell.execute_reply.started":"2021-12-05T13:49:00.006526Z","shell.execute_reply":"2021-12-05T13:49:00.046124Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"delta_df.describe()","metadata":{"execution":{"iopub.status.busy":"2021-12-05T13:49:00.048828Z","iopub.execute_input":"2021-12-05T13:49:00.049165Z","iopub.status.idle":"2021-12-05T13:49:00.104797Z","shell.execute_reply.started":"2021-12-05T13:49:00.049121Z","shell.execute_reply":"2021-12-05T13:49:00.103961Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"delta_df.head(25)","metadata":{"execution":{"iopub.status.busy":"2021-12-05T13:49:00.105933Z","iopub.execute_input":"2021-12-05T13:49:00.106259Z","iopub.status.idle":"2021-12-05T13:49:00.160006Z","shell.execute_reply.started":"2021-12-05T13:49:00.106225Z","shell.execute_reply":"2021-12-05T13:49:00.159197Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### ","metadata":{}},{"cell_type":"code","source":"delta_grps = []\nfor key,val in asset_dict.items():\n    \n    asset_name =asset_ids[asset_ids['asset_id'] == key].iloc[0,2]\n    val\n    \n     \n    t= pd.DataFrame(val.delta_time.value_counts())\n    t.columns = [asset_name]\n    delta_grps.append(t)\n    \n\ndelta_df = reduce(lambda left,right: pd.merge(left,right,left_index=True,right_index=True, how='outer' ), delta_grps)   \n    ","metadata":{"execution":{"iopub.status.busy":"2021-12-05T13:49:00.161595Z","iopub.execute_input":"2021-12-05T13:49:00.161829Z","iopub.status.idle":"2021-12-05T13:49:00.507371Z","shell.execute_reply.started":"2021-12-05T13:49:00.1618Z","shell.execute_reply":"2021-12-05T13:49:00.506667Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Identify common range of gaps if any across currencies\n# Identify common null value of targets across currencies if any","metadata":{"execution":{"iopub.status.busy":"2021-12-05T13:49:00.508297Z","iopub.execute_input":"2021-12-05T13:49:00.508613Z","iopub.status.idle":"2021-12-05T13:49:00.512423Z","shell.execute_reply.started":"2021-12-05T13:49:00.508585Z","shell.execute_reply":"2021-12-05T13:49:00.511487Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"na_targs=[]\nfor key,val in asset_dict.items():\n    temp=[]\n    \n    temp.append(asset_ids[asset_ids['asset_id'] == key].iloc[0,2])\n    temp.append(val.target.isna().sum())\n    temp.append(len(val))\n    beg= val.timestamp.astype('datetime64[s]').min()\n    end= val.timestamp.astype('datetime64[s]').max()\n    \n    temp.append(beg)\n    temp.append(end)\n    \n    na_targs.append(temp)\n    \nevent_rate = pd.DataFrame(na_targs)\nevent_rate.columns = ['crypto','nas','total','start','last']","metadata":{"execution":{"iopub.status.busy":"2021-12-05T13:49:00.513793Z","iopub.execute_input":"2021-12-05T13:49:00.514036Z","iopub.status.idle":"2021-12-05T13:49:04.604796Z","shell.execute_reply.started":"2021-12-05T13:49:00.514007Z","shell.execute_reply":"2021-12-05T13:49:04.603989Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"event_rate['na_rate'] = (event_rate['nas']*100)/event_rate['total']","metadata":{"execution":{"iopub.status.busy":"2021-12-05T13:49:04.606116Z","iopub.execute_input":"2021-12-05T13:49:04.606779Z","iopub.status.idle":"2021-12-05T13:49:04.613994Z","shell.execute_reply.started":"2021-12-05T13:49:04.606731Z","shell.execute_reply":"2021-12-05T13:49:04.613049Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"event_rate","metadata":{"execution":{"iopub.status.busy":"2021-12-05T13:49:04.615201Z","iopub.execute_input":"2021-12-05T13:49:04.616239Z","iopub.status.idle":"2021-12-05T13:49:04.634985Z","shell.execute_reply.started":"2021-12-05T13:49:04.616197Z","shell.execute_reply":"2021-12-05T13:49:04.634388Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"na_spread = []\nfor key,val in asset_dict.items():\n    \n    temp=[]    \n    asset_name = asset_ids[asset_ids['asset_id'] == key].iloc[0,2]    \n    first_found=False\n    counter=0    \n\n    for i,row in val.iterrows():\n        temp_slice=[]        \n        if pd.isna(row.target) and not(first_found):\n            counter +=1            \n            beg = row.timestamp\n            first_found=True\n            lst_timestamp = row.timestamp            \n            \n        elif pd.isna(row.target) and first_found:             \n                lst_timestamp = row.timestamp\n                counter+=1\n                \n        elif not(pd.isna(row.target)) and first_found:\n            \n            temp_slice.append(asset_name)\n            temp_slice.append(beg)\n            temp_slice.append(lst_timestamp)\n            temp_slice.append(counter)          \n            counter=0\n            first_found=False           \n            temp.append(temp_slice)           \n        \n        elif not(pd.isna(row.target)) and not(first_found):          \n            pass\n        \n    na_spread.append(pd.DataFrame(temp, columns = ['crypto','start','end','counter']))\n    \n    \n    \nna_spread_raw = pd.concat(na_spread,axis=0)\n            \n   ","metadata":{"execution":{"iopub.status.busy":"2021-12-05T13:49:04.635888Z","iopub.execute_input":"2021-12-05T13:49:04.636107Z","iopub.status.idle":"2021-12-05T14:22:24.046613Z","shell.execute_reply.started":"2021-12-05T13:49:04.636071Z","shell.execute_reply":"2021-12-05T14:22:24.045581Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"na_spread_raw","metadata":{"execution":{"iopub.status.busy":"2021-12-05T14:22:24.048394Z","iopub.execute_input":"2021-12-05T14:22:24.048833Z","iopub.status.idle":"2021-12-05T14:22:24.067508Z","shell.execute_reply.started":"2021-12-05T14:22:24.048781Z","shell.execute_reply":"2021-12-05T14:22:24.066636Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"na_spread=na_spread_raw.groupby(['crypto','counter']).agg({'start':len})","metadata":{"execution":{"iopub.status.busy":"2021-12-05T14:22:24.068835Z","iopub.execute_input":"2021-12-05T14:22:24.069103Z","iopub.status.idle":"2021-12-05T14:22:24.141044Z","shell.execute_reply.started":"2021-12-05T14:22:24.069045Z","shell.execute_reply":"2021-12-05T14:22:24.140131Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"na_spread.reset_index(inplace=True)","metadata":{"execution":{"iopub.status.busy":"2021-12-05T14:22:24.142354Z","iopub.execute_input":"2021-12-05T14:22:24.142597Z","iopub.status.idle":"2021-12-05T14:22:24.148874Z","shell.execute_reply.started":"2021-12-05T14:22:24.142567Z","shell.execute_reply":"2021-12-05T14:22:24.148128Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"na_spread","metadata":{"execution":{"iopub.status.busy":"2021-12-05T14:22:24.150388Z","iopub.execute_input":"2021-12-05T14:22:24.150849Z","iopub.status.idle":"2021-12-05T14:22:24.169561Z","shell.execute_reply.started":"2021-12-05T14:22:24.150807Z","shell.execute_reply":"2021-12-05T14:22:24.168759Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"na_spread[na_spread.crypto=='Binance Coin']","metadata":{"execution":{"iopub.status.busy":"2021-12-05T14:22:24.174304Z","iopub.execute_input":"2021-12-05T14:22:24.174783Z","iopub.status.idle":"2021-12-05T14:22:24.191952Z","shell.execute_reply.started":"2021-12-05T14:22:24.174736Z","shell.execute_reply":"2021-12-05T14:22:24.191039Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"na_spread[na_spread.crypto=='Ethereum']","metadata":{"execution":{"iopub.status.busy":"2021-12-05T14:22:24.193116Z","iopub.execute_input":"2021-12-05T14:22:24.193644Z","iopub.status.idle":"2021-12-05T14:22:24.212287Z","shell.execute_reply.started":"2021-12-05T14:22:24.193606Z","shell.execute_reply":"2021-12-05T14:22:24.211602Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"na_spread_raw","metadata":{"execution":{"iopub.status.busy":"2021-12-05T14:22:24.213422Z","iopub.execute_input":"2021-12-05T14:22:24.214043Z","iopub.status.idle":"2021-12-05T14:22:24.231284Z","shell.execute_reply.started":"2021-12-05T14:22:24.214007Z","shell.execute_reply":"2021-12-05T14:22:24.230699Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import plotly.express as px","metadata":{"execution":{"iopub.status.busy":"2021-12-05T14:22:24.232197Z","iopub.execute_input":"2021-12-05T14:22:24.232705Z","iopub.status.idle":"2021-12-05T14:22:25.46616Z","shell.execute_reply.started":"2021-12-05T14:22:24.232674Z","shell.execute_reply":"2021-12-05T14:22:25.465254Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"for crp in na_spread.crypto.unique():\n\n  fig = px.bar(na_spread[na_spread.crypto==crp],x='counter',y='start',title=crp)\n  fig.show()\n","metadata":{"execution":{"iopub.status.busy":"2021-12-05T14:22:25.467657Z","iopub.execute_input":"2021-12-05T14:22:25.468184Z","iopub.status.idle":"2021-12-05T14:22:27.385221Z","shell.execute_reply.started":"2021-12-05T14:22:25.468136Z","shell.execute_reply":"2021-12-05T14:22:27.384316Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Check gap by quarter\n\n","metadata":{"execution":{"iopub.status.busy":"2021-12-05T14:22:27.38643Z","iopub.execute_input":"2021-12-05T14:22:27.38666Z","iopub.status.idle":"2021-12-05T14:22:27.390909Z","shell.execute_reply.started":"2021-12-05T14:22:27.386632Z","shell.execute_reply":"2021-12-05T14:22:27.389679Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}