{"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":"# EDA  - predict value from other assets- \n\nIn this notebook, we describe the results of predicting the transition of one asset from the values of other assets.\n\nSpecifically, Predict the value of mean for asset ID = 0 from the value of mean for asset ID = 1 to 13 at the same time..\n\nThe purpose of this paper is to obtain insights for score improvement from the discrepancies in the predicted values.\nFor example, if the discrepancy is large, it can be inferred that a large change has occurred only in that asset ID.","metadata":{}},{"cell_type":"markdown","source":"## import","metadata":{}},{"cell_type":"code","source":"import pandas as pd\nimport numpy as np\nimport time\nimport datetime\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nsns.set(\"talk\")\n\nfrom sklearn.linear_model import LinearRegression\n\n\nTRAIN_CSV = '../input/g-research-crypto-forecasting/train.csv'\n\nASSET_DETAILS_CSV = '../input/g-research-crypto-forecasting/asset_details.csv'\nOUTPUT_DIR = '../model/'","metadata":{"execution":{"iopub.status.busy":"2022-01-05T07:19:58.651333Z","iopub.execute_input":"2022-01-05T07:19:58.651752Z","iopub.status.idle":"2022-01-05T07:19:58.658036Z","shell.execute_reply.started":"2022-01-05T07:19:58.651709Z","shell.execute_reply":"2022-01-05T07:19:58.657068Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Read train data\n- read csv data\n- merge asset details","metadata":{}},{"cell_type":"code","source":"train = pd.read_csv(TRAIN_CSV)\ntrain.dropna(subset=[\"Target\"], inplace=True)\n    \nasset_details = pd.read_csv(ASSET_DETAILS_CSV).sort_values(\"Asset_ID\")\nprint(train.shape)\nprint(asset_details.shape)\ndisplay(train.head())\ndisplay(asset_details)","metadata":{"execution":{"iopub.status.busy":"2022-01-05T07:19:59.695886Z","iopub.execute_input":"2022-01-05T07:19:59.696458Z","iopub.status.idle":"2022-01-05T07:20:58.727658Z","shell.execute_reply.started":"2022-01-05T07:19:59.696416Z","shell.execute_reply":"2022-01-05T07:20:58.726715Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#merge assetID\ndef add_asset_details(train, asset_details):\n    return train.merge(\n        asset_details,\n        how = \"left\", on = \"Asset_ID\"\n    )\n\ntrain = add_asset_details(train, asset_details)\n\n#calculate mean\ntrain[\"Mean\"] = train[['Open', 'High', 'Low', 'Close']].mean(axis=1)","metadata":{"execution":{"iopub.status.busy":"2022-01-05T07:20:58.729998Z","iopub.execute_input":"2022-01-05T07:20:58.730541Z","iopub.status.idle":"2022-01-05T07:21:07.138769Z","shell.execute_reply.started":"2022-01-05T07:20:58.730500Z","shell.execute_reply":"2022-01-05T07:21:07.136724Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## set time for train\n\n","metadata":{}},{"cell_type":"code","source":"#window for train\ntrain_start = \"21/08/2020\"\ntrain_end = \"21/08/2021\"\n\ndef set_time_train(train, train_start, train_end):\n    totimestamp = lambda s: np.int32(time.mktime(datetime.datetime.strptime(s, \"%d/%m/%Y\").timetuple()))\n    train_window = [totimestamp(train_start), totimestamp(train_end)]\n    train = train.query(\"@train_window[0] < timestamp < @train_window[1]\")\n    return train\n\ntrain = set_time_train(train, train_start, train_end)\nprint(train.shape)","metadata":{"execution":{"iopub.status.busy":"2022-01-05T07:21:07.143234Z","iopub.execute_input":"2022-01-05T07:21:07.143865Z","iopub.status.idle":"2022-01-05T07:21:10.041020Z","shell.execute_reply.started":"2022-01-05T07:21:07.143820Z","shell.execute_reply":"2022-01-05T07:21:10.040062Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## get pivot table\n- index:timestamp\n- columns:Asset_ID\n- values:Mean","metadata":{}},{"cell_type":"code","source":"#get the table which index is time stamp\ndf_pivot = train.pivot_table(index=\"timestamp\", columns=\"Asset_Name\", values=\"Mean\")\ndf_pivot = df_pivot.fillna(method=\"ffill\")","metadata":{"execution":{"iopub.status.busy":"2022-01-05T07:21:10.043941Z","iopub.execute_input":"2022-01-05T07:21:10.044442Z","iopub.status.idle":"2022-01-05T07:21:16.996992Z","shell.execute_reply.started":"2022-01-05T07:21:10.044398Z","shell.execute_reply":"2022-01-05T07:21:16.996409Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_pivot.head()","metadata":{"execution":{"iopub.status.busy":"2022-01-05T07:21:16.998153Z","iopub.execute_input":"2022-01-05T07:21:16.998522Z","iopub.status.idle":"2022-01-05T07:21:17.017325Z","shell.execute_reply.started":"2022-01-05T07:21:16.998474Z","shell.execute_reply":"2022-01-05T07:21:17.016542Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### check nan","metadata":{}},{"cell_type":"code","source":"df_pivot.isnull().sum()","metadata":{"execution":{"iopub.status.busy":"2022-01-05T07:21:17.018867Z","iopub.execute_input":"2022-01-05T07:21:17.019278Z","iopub.status.idle":"2022-01-05T07:21:17.047311Z","shell.execute_reply.started":"2022-01-05T07:21:17.019245Z","shell.execute_reply":"2022-01-05T07:21:17.046439Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## predict (in case of Bitcoin)","metadata":{}},{"cell_type":"markdown","source":"##E linear regression by other asset","metadata":{}},{"cell_type":"code","source":"target = \"Bitcoin\"\nfeats = df_pivot.drop(target, axis=1).columns","metadata":{"execution":{"iopub.status.busy":"2022-01-05T07:21:17.048643Z","iopub.execute_input":"2022-01-05T07:21:17.048982Z","iopub.status.idle":"2022-01-05T07:21:17.075850Z","shell.execute_reply.started":"2022-01-05T07:21:17.048936Z","shell.execute_reply":"2022-01-05T07:21:17.074940Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#linear regression\nlr = LinearRegression()\nlr.fit(df_pivot[feats], df_pivot[target])","metadata":{"execution":{"iopub.status.busy":"2022-01-05T07:21:17.077409Z","iopub.execute_input":"2022-01-05T07:21:17.077685Z","iopub.status.idle":"2022-01-05T07:21:17.301651Z","shell.execute_reply.started":"2022-01-05T07:21:17.077658Z","shell.execute_reply":"2022-01-05T07:21:17.300978Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def get_df_pred(lr, feats, target, df_pivot):\n    #get prediction datafram\n    df_pred = pd.DataFrame()\n    df_pred[\"pred\"] = lr.predict(df_pivot[feats])\n    df_pred[\"true\"] = df_pivot[target].values\n    df_pred[\"pred/true\"] = df_pred[\"pred\"] / df_pred[\"true\"]\n    df_pred[\"time\"] = df_pivot.index\n    df_pred[\"time\"] = df_pred[\"time\"].apply(lambda x:datetime.datetime.fromtimestamp(x))\n    return df_pred\n\ndf_pred = get_df_pred(lr, feats, target, df_pivot)\ndf_pred.head()","metadata":{"execution":{"iopub.status.busy":"2022-01-05T07:21:17.303168Z","iopub.execute_input":"2022-01-05T07:21:17.303803Z","iopub.status.idle":"2022-01-05T07:21:18.059533Z","shell.execute_reply.started":"2022-01-05T07:21:17.303758Z","shell.execute_reply":"2022-01-05T07:21:18.058703Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### check prediction error","metadata":{}},{"cell_type":"code","source":"fig, ax = plt.subplots(1,1,figsize=(20,5))\n\ndef plot_pred(df_pred, target, ax):\n    ax.plot(df_pred[\"time\"], df_pred[\"true\"], label=\"true\")\n    ax.plot(df_pred[\"time\"], df_pred[\"pred\"], label=\"pred\")\n    ax.set_title(target)\n    ax.legend()\n    \nplot_pred(df_pred, target, ax)","metadata":{"execution":{"iopub.status.busy":"2022-01-05T07:21:18.061544Z","iopub.execute_input":"2022-01-05T07:21:18.061730Z","iopub.status.idle":"2022-01-05T07:21:19.428402Z","shell.execute_reply.started":"2022-01-05T07:21:18.061707Z","shell.execute_reply":"2022-01-05T07:21:19.427495Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"- The predictions are generally good, but there are times when the errors are large.","metadata":{}},{"cell_type":"markdown","source":"### predict (in cases of all assets)","metadata":{}},{"cell_type":"code","source":"fig, ax = plt.subplots(13,1,figsize=(20,40), sharex=True)\n\nlr_list = []\nfor ax_i, asset_i in zip(ax, df_pivot.columns):\n    print(asset_i)\n    target = asset_i\n    feats = df_pivot.drop(asset_i, axis=1).columns\n    \n    lr = LinearRegression()\n    lr.fit(df_pivot[feats], df_pivot[target])\n    lr_list.append(lr)\n    \n    df_pred = get_df_pred(lr, feats, target, df_pivot)\n    plot_pred(df_pred, target, ax_i)","metadata":{"execution":{"iopub.status.busy":"2022-01-05T07:21:19.429744Z","iopub.execute_input":"2022-01-05T07:21:19.430041Z","iopub.status.idle":"2022-01-05T07:21:47.328709Z","shell.execute_reply.started":"2022-01-05T07:21:19.429996Z","shell.execute_reply":"2022-01-05T07:21:47.327808Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"point\n- The error tends to be larger where there is more change.","metadata":{}},{"cell_type":"markdown","source":"## check correlation of predicion error and target\nCheck if there is a correlation between the error and the target, the log return in case of bitcoin.","metadata":{}},{"cell_type":"code","source":"target_pivot = train.pivot_table(index=\"timestamp\", columns=\"Asset_Name\", values=\"Target\")\ntarget_pivot.head()","metadata":{"execution":{"iopub.status.busy":"2022-01-05T07:21:47.330082Z","iopub.execute_input":"2022-01-05T07:21:47.330436Z","iopub.status.idle":"2022-01-05T07:21:53.925564Z","shell.execute_reply.started":"2022-01-05T07:21:47.330398Z","shell.execute_reply":"2022-01-05T07:21:53.924708Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#check with figure\ntarget = \"Bitcoin\"\nfeats = df_pivot.drop(target, axis=1).columns\ndf_pred = get_df_pred(lr_list[1], feats, target, df_pivot)\n\nfig, ax = plt.subplots(1,1,figsize=(20,5))\n\ndef plot_pred(df_pred, target, ax):\n    ax.plot(df_pred[\"time\"], df_pred[\"true\"], label=\"true\")\n    ax.plot(df_pred[\"time\"], df_pred[\"pred\"], label=\"pred\")\n    ax.set_title(target)\n    ax.legend()\n    \nplot_pred(df_pred, target, ax)\nax2 = ax.twinx()\nax2.plot(df_pred[\"time\"], target_pivot[target], color=\"black\", alpha=0.3)\nax2.grid(False)","metadata":{"execution":{"iopub.status.busy":"2022-01-05T07:21:53.927397Z","iopub.execute_input":"2022-01-05T07:21:53.927904Z","iopub.status.idle":"2022-01-05T07:21:56.321237Z","shell.execute_reply.started":"2022-01-05T07:21:53.927863Z","shell.execute_reply":"2022-01-05T07:21:56.320332Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"- Unfortunately, I don't see any significant correlation...\n- Whether this prediction error feature can be used or not will be verified in the future.","metadata":{}},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}